I did raw SQL query below to select only certain fields from a table.
{
List<CustEmpVM> CustomerVMlist = new List<CustEmpVM>();
var cid = db.Customers.SqlQuery("select SchedDate from Customer where CustID = '@id'").ToList<Customer>();
}
But i keep getting the error of:
System.Data.Entity.Core.EntityCommandExecutionException
occurred in EntityFramework.SqlServer.dll but was not handled in user code
Additional information: The data reader is incompatible with the specified
ALFHomeMovers.Customer
. A member of the type,CustID
, does not have a corresponding column in the data reader with the same name.
The exception message is pretty straightforward: the query expected to return full entity of Customer
table but only SchedDate
column returned, hence EF cannot done mapping other omitted columns including CustID
.
Assuming Customers
is a DbSet<Customer>
, try return all fields from Customer
instead:
// don't forget to include SqlParameter
var cid = db.Customers.SqlQuery("SELECT * FROM Customer WHERE CustID = @id",
new SqlParameter("id", "[customer_id]")).ToList();
If you want just returning SchedDate
column, materialize query results and use Select
afterwards:
var cid = db.Customers.SqlQuery("SELECT * FROM Customer WHERE CustID = @id",
new SqlParameter("id", "[customer_id]"))
.AsEnumerable().Select(x => x.SchedDate).ToList();
NB: I think you can construct LINQ based from the SELECT query above:
var cid = (from c in db.Customers
where c.CustID == "[customer_id]"
select c.SchedDate).ToList();
Similar issue:
The data reader is incompatible with the specified Entity Framework