'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Question

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.

Answer 1

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

Answer 2

Use below query instead of raw query:

{
 List<CustEmpVM> CustomerVMlist = new List<CustEmpVM>();
 var cid = db.Customers.Where(w=>w.Id == YOURCUSTOMERID).Select(s=>new Customer{SchedDate = s.SchedDate }).ToList();
}

It will give compile time error rather than run time error.