Entity Framework stored column mapping issue with DTO

.net c# entity-framework-6 linq sql-server

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?

1
1
9/16/2019 5:44:54 PM

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()

0
9/16/2019 6:12:33 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow