Shaping EF LINQ Query Results Using Multi-Table Includes

I have a simple LINQ EF query below using the method syntax. I'm using my Include statement to join four tables: Event and Doc are the two main tables, EventDoc is a many-to-many link table, and开发者_开发问答 DocUsage is a lookup table.

My challenge is that I'd like to shape my results by only selecting specific columns from each of the four tables. But, the compiler is giving a compiler is giving me the following error:

'System.Data.Objects.DataClasses.EntityCollection does not contain a definition for "Doc' and no extension method 'Doc' accepting a first argument of type 'System.Data.Objects.DataClasses.EntityCollection' could be found.

I'm sure this is something easy but I'm not figuring it out. I haven't been able to find an example of someone using the multi-table include but also shaping the projection.

Thx,Mark

var qry= context.Event
    .Include("EventDoc.Doc.DocUsage")
    .Select(n => new
    {
        n.EventDate,
        n.EventDoc.Doc.Filename,  //<=COMPILER ERROR HERE
        n.EventDoc.Doc.DocUsage.Usage
    })
    .ToList();

    EventDoc ed;
    Doc d = ed.Doc;  //<=NO COMPILER ERROR SO I KNOW MY MODEL'S CORRECT
    DocUsage du = d.DocUsage;

Very difficult to know what is going on without a screencap of your model, including the navigational properties on each entity.

But if your saying it's a many-to-many between Event and Doc (with EventDoc being the join table), and assuming your join table has nothing but the FK's and therefore doesn't need to be mapped, then shouldn't a single Event have many Doc's?

This query:

var query = ctx.Event.Include("EventDoc.Doc");

Would imply (based on the lack of pluralization): a single Event has a single EventDoc which has a single Doc.

But shouldn't that be: a single Event has a single EventDoc which has many Doc's.

Therefore your projection doesn't really make sense. Your trying to project to an anonymous type, with EventDate and Filename for a single Doc, but an Event has many Docs.

Maybe a projection like this would be more suitable:

var query = ctx.Event.Include("EventDoc.Docs.DocUsage")
                     .Select(x => new
                     {
                        EventDate = x.EventDate,
                        DocsForEvent = x.EventDocs.Docs
                     }).ToList();

And for that you work you need to fix up your model. Im surprised it even validates/compiles.

Either your model is wrong or your description of the database cardinalities in your question is. :)

Of course, i could be completely misunderstanding your database and/or model - so if i am let me know and i'll remove this answer.