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?
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.