How to filter specific columns from a child table Entity Framework

Question

I have 3 tables with 1:many parent child relation

TableA
TableB (child of TableA)
TableC (child of TableB)

I am able to retrieve all of the data from these tables with query

var data = dbContext.tableA.where(a => a.ID == rowID)
           .Include(a => a.tableB.Select(n => n.tableC)).SingleOrDefault();

For TableC, I don't want all of the columns to be retrieved from database. I just want ID, TableC_FK columns data to be retrieved for TableC.

How do I do that?

What do you mean by anonymous type here? var data? If so, no.. I just gave as an example. — Ravi Teja, Sep 09 '16 at 12:34
I know that it is type of `TableA`. I try to say that it will not be type of `TableA` if you want to do custom select. is it OK for you? — Adil Mammadov, Sep 09 '16 at 12:40
You'll have to project the results (fields) you want. You can either create a ViewModel/DTO or project the results into an anonymous type. You have to look into the .Select(). — jpgrassi, Sep 09 '16 at 12:47
"You cannot (and should not be able to) project onto a mapped entity. You can, however, project onto an annonymous type or onto a DTO:" See this: http://stackoverflow.com/questions/5325797/the-entity-cannot-be-constructed-in-a-linq-to-entities-query — jpgrassi, Sep 09 '16 at 14:36

score 0 · Accepted Answer · answered Sep 09 '16 at 13:53

I think you cannot project into TableA unless you have scheme that support it. IMHO The best option is either anonymous project or Use Viewmodel/DTO suggested by @jpgrassi

You can use anonymous here, more tutorial of anonymous projection here

var data = dbContext.tableA
                    .where(a => a.ID == rowID)
                    .Select(tableA=> new 
                    {
                       firstColumn = tableA.FirstColumn,
                       tableC = tableA.SelectMany(tableB=>tableB.TableC),
                    }.SingleOrDefault();

How to filter specific columns from a child table Entity Framework

1 Answers1