Getting rows from database to list with Entity Framework

Question

I have this complex model:

public class complexModel
{
    public aTable aObject { get; set; }
    public List<bTable> bObjectList { get; set; }
    public List<cTable> cObjectList { get; set; }
}

Then in the controller, I declare this object:

var AAA = new complexModel();

Now this code works fine

AAA.bObjectList= (from tdrc in db.bTable
                  select new bTable
                             {
                                ID = tdrc.ID ,
                                bName = tdrc.bName,,
                             }).ToList();

but when I write this

AAA.cObjectList= (from tdrc in db.cTable
                  where tdrc.cID == id
                  select new cTable
                             {
                                 cID = tdrc.cID,
                                 cName = tdrc.cName,
                             }).ToList();

I get this error:

System.NotSupportedException: 'The entity or complex type 'Project.DAC.cTable' cannot be constructed in a LINQ to Entities query.'

Possible duplicate of [The entity cannot be constructed in a LINQ to Entities query](https://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query) — Drag and Drop, Oct 24 '18 at 13:14
So I have to make another model for that ? it's a bit complicated, model for this, model for that, a lot of models in my project. I thought there will be some easier way. and I still don't understand why the first query is working ? — David, Oct 24 '18 at 13:21
I've edited my question, had a mistake, I'm crating cObjectList from cTable so they are not the same — David, Oct 24 '18 at 13:26
the only difference is "where" statement. That's all. why the second query has error ? — David, Oct 24 '18 at 13:28
By design, EF doesn't allow you to project the results of a query onto a mapped entity. It can be weird the first time but it's logic when you think about update. — Drag and Drop, Oct 24 '18 at 13:32

tschmit007 · Answer 1 · 2018-10-24T14:45:59.670

AAA.cObjectList= (from tdrc in db.cTable
                               // ^
              where tdrc.cID == id
              select new cTable
                      // ^
                         {
                             cID = tdrc.cID,
                             cName = tdrc.cName,
                         }).ToList();

You cannot select new SomeType where SomeType is an entity type. That is a type/class declared in the context.

Anonymous type should do

AAA.cObjectList= (from tdrc in db.cTable
              where tdrc.cID == id
              select new 
                         {
                             cID = tdrc.cID,
                             cName = tdrc.cName,
                         }).ToList();

Or if you want the entity type:

AAA.cObjectList= (from tdrc in db.cTable
              where tdrc.cID == id
              select tdrc).ToList();

Or use a non entity type.

Getting rows from database to list with Entity Framework

1 Answers1