Question
I'm retrieving columns from database using a stored procedure and binding values into my DTO class through EF code first approach.
I'm facing issue to get the values for ReferenceNumber, BookingNumber properties. I'm getting empty values for these properties.
Executing stored procedure through EF code first approach:
SqlParameter param1 = new SqlParameter("@Id", 1);
var stocks = await context.Database.SqlQuery<Stock>("dbo.[GetStocks @Id", param1).ToListAsync();
Stored procedure to get stock list:
CREATE PROCEDURE [dbo].[GetStocks]
(@id INTEGER)
AS
BEGIN
SELECT ID, name, RefNo, BookingNo
FROM dbo.Stock
END
DTO class to get the values
namespace ConsoleApp6
{
using System;
public partial class Stock
{
public int ID { get; set; }
public string name { get; set; }
public string ReferenceNumber { get; set; }
public string BookingNumber { get; set; }
}
}
{ "ID" : "1" , "Name":"ABC", "ReferenceNumber":"", "BookingNumber":"" }
Do we any option to get these values?
Accepted Answer
Your select statement does not return any column with the name ReferenceNumber or BookingNumber. Property names in the objects must match those in data reader for this to work. You can either rename the database columns to match exact properties names on your model or rename property names on your model to match database column names.
Alternatively, you can rewrite your SP as below:
CREATE PROCEDURE [dbo].[GetStocks]
(
(@id INTEGER
)
AS
BEGIN
SELECT ID, name, RefNo as 'ReferenceNumber ', BookingNo as 'BookingNumber ' from
FROM dbo.Stock where id =@id
END
Since you are retrieving a single entity from db based on id paramter, there is no need to call ToList extension method. Use .SingleOrDefaultAsync()
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
