0

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.

Improve this question
3
  • Is there a CustID column in the database? Commented Sep 25, 2017 at 10:22
  • Yes, there is @OJ Raqueno Commented Sep 25, 2017 at 10:25
  • When I checked the InnerException of that error it says that it is null. I tried applying the answers I saw with this same question but it won't work. Commented Sep 25, 2017 at 10:30

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.