Entity Framework: selecting from multiple tables

Question

I have a statement:

var items = from e in db.Elements
    join a in db.LookUp
    on e.ID equals a.ElementID
    where e.Something == something
    select new Element
    {
        ID = e.ID,
        LookUpID = a.ID
        // some other data get populated here as well
    };

As you can see, all I need is a collection of Element objects with data from both tables - Elements and LookUp. This works fine. But then I need to know the number of elements selected:

int count = items.Count();

... this call throws System.NotSupportedException:

"The entity or complex type 'Database.Element' cannot be constructed in a LINQ to Entities query."

How am I supposed to select values from multiple tables into one object in Entity Framework? Thanks for any help!

Aducci · Accepted Answer · 2011-09-14 15:30:22Z

2

You are not allowed to create an Entity class in your projection, you have to either project to a new class or an anonymous type

select new 
{
        ID = e.ID,
        LookUpID = a.ID
        // some other data get populated here as well
};

answered Sep 14, 2011 at 15:30

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

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

Comments

Ladislav Mrnka · Accepted Answer · 2011-09-14 15:33:05Z

1

Your code doesn't work at all. The part you think worked has never been executed. The first time you executed it was when you called Count.

As exception says you cannot construct mapped entity in projection. Projection can be made only to anonymous or non mapped types. Also it is not clear why you even need this. If your class is correctly mapped you should simply call:

var items = from e in db.Elements
            where e.Something == something
            select e;

If LookupID is mapped property of your Element class it will be filled. If it is not mapped property you will not be able to load it with single query to Element.

answered Sep 14, 2011 at 15:33

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

3 Comments

Kizz Over a year ago

Do I have to map tables into a single object for every join that I will ever need? I guess I'm simply missing something here.

Ladislav Mrnka Over a year ago

Is LookupID part of Element table? If yes you don't need to do nothing more. If not you cannot load that property in entity mapped to Element table - it is not allowed. You must instead create new class (not mapped) with all needed properties and project result of your query into that new class instead of Element.

Kizz Over a year ago

That's what I was afraid of. No, the LookUpID is not in the Elements table, the point of joining those two tables was to get that value together with the Element. Makes the one think why to use that EF stuff in the first place :) Thanks for the clarification, though.

Collectives™ on Stack Overflow

Entity Framework: selecting from multiple tables

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related