Dapper > I cannot retrieve object with child object

dapper

Question

I have the following Quote and Share classes:

public class Quote
{
    public Quote(string ticker, string name, decimal lastPrice)
    {
        this.Ticker = ticker;
        this.Name = name;
        this.LastPrice = lastPrice;
    }

    public string Ticker { get; private set; }

    public string Name { get; private set; }

    public decimal LastPrice { get; private set; }
}

public class Share
{
    public Share(Quote quote, int quantity)
    {
        this.Quote = quote;
        this.Quantity = quantity;
    }

    public Quote Quote { get; private set; }
    public int Quantity { get; private set; }
}

And these tables:

CREATE TABLE [dbo].[quotes](
[ticker] [char](5) NOT NULL,
[name] [varchar](60) NOT NULL,
[last_price] [money] NOT NULL,
[bid_price] [money] NULL,
[bid_quantity] [int] NULL,
[ask_price] [money] NULL,
[ask_quantity] [int] NULL,
[high] [money] NULL,
[low] [money] NULL,
[previous_close] [money] NULL,
[created_by] [varchar](12) NULL,
[created_date] [datetime] NULL,
[modified_by] [varchar](12) NULL,
[modified_date] [datetime] NULL,
CONSTRAINT [PK_quotes] PRIMARY KEY CLUSTERED 
(
[ticker] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  =     ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[shares](
[ticker] [char](5) NOT NULL,
[broker_id] [char](12) NOT NULL,
[quantity] [int] NOT NULL,
[created_by] [varchar](12) NOT NULL,
[created_date] [datetime] NOT NULL,
[modified_by] [varchar](12) NULL,
[modified_date] [datetime] NULL,
CONSTRAINT [PK_shares] PRIMARY KEY CLUSTERED 
(
[ticker] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,            IGNORE_DUP_KEY        =     OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)     ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[shares]  WITH CHECK ADD  CONSTRAINT [FK_shares_quotes]     FOREIGN KEY([ticker])
REFERENCES [dbo].[quotes] ([ticker])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[shares] CHECK CONSTRAINT [FK_shares_quotes]
GO

And finally this code:

        string sql = "SELECT quantity, s.ticker, name, last_price FROM shares s " +
                     "INNER JOIN quotes q ON q.ticker = s.ticker " +
                     "WHERE s.ticker=@ticker AND broker_id = @broker_Id";

        IEnumerable<Share> shares = connection.Query<Share, Quote, Share>(sql,
                                        (s, q) =>
                                        {
                                            Share share = new Share(q, s.Quantity);
                                            return share;
                                        },
                                        new { ticker = ticker, broker_Id = brokerId }
                                        , splitOn: "ticker");

        return shares.FirstOrDefault();

When I execute that query, I get the following error:

"Specified method is not supported" (???)

If I modify the sql statement by the following (with "last_price as lastPrice"):

string sql = "SELECT quantity, s.ticker, name, last_price as lastPrice FROM shares s " +
                     "INNER JOIN quotes q ON q.ticker = s.ticker " +
                     "WHERE s.ticker=@ticker AND broker_id = @broker_Id";

I get the following error:

"A parameterless default constructor or one matching signature (System.String ticker, System.String name, System.Decimal lastPrice) is required for Footstock.Domain.Model.Quote materialization"

What am I doing wrong?

Accepted Answer

For the Share class, you need to either add a parameterless constructor or one that takes only a quantity parameter.

Before Dapper calls the method that composes the result to be returned, it needs to build the parameters to pass in to (s, q) => ... In your case you need a Share and a Quote class, both of which must have a constructor that can be called using the data Dapper has. For Quote, the constructor matches the properties in the query. For Share, Dapper only has the quantity and does not have a Quote object yet.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why