Entityframework single select from nested tables

Question

I have a problem to make a single query that doesn't take all columns from database. Let's say i have schema:

table1 {
    id,
    column1,
    column2,
    column3
}

table2 {
    id,
    table1ID,
    column1,
    column2,
    column3
}
table3 {
    id,
    table2ID,
    column1,
    column2,
    column3
}
...
tableN {
    id,
    table(N-1)ID,
    column1,
    column2,
    column3
}

I'd like to make a linq query to select table1.column1 and all data from table2..tableN structure.

Since I want all fields from table2..tableN I can create DTOs with corresponding fields and use AutoMapper(I don't really see a problem with that, however I've seen some discouragement online).

Solutions I know:

I could simply take all data with context.table1.include(table2).include(table3)..include(tableN).ToList() and then use AutoMapper for table2..tableN and manually take fields I want for table1, but the query generated takes all the table1 fields.

AutoMapper in Linq doesn't work

context.table1.select(new table1DTO {x =>
    field1 = x.field1,
    table2 = AutoMapper<table2, table2DTO>(x.table2)
};

This one works

context.table1.select(x => new table1DTO {
    field1 = x.field1,
    table2 = x.table2.select(y => new table2DTO {
        field1 = y.field1,
        ..
        table3 = y.table3.select(z => new table3DTO {
            field1 = z.field1,
            ..
            table4 = ..
)};

but is painful to write if I have really nested structure. I could write script that would generate Linq code.

Write two different selects, one for table1 and other for table2..tableN

Do you know any better solutions?

AutoMapper has a ProjectTo method, that's the one you should use. — Gert Arnold
– Gert Arnold, Commented Aug 31, 2016 at 9:55

share pvv · Accepted Answer · 2016-08-31 16:09:37Z

1

with your database structure. you should create a front view with all the fields(All column of all Table) you want, and then use LINQ query.

edited Aug 31, 2016 at 16:09

answered Aug 31, 2016 at 9:58

share pvv

814 bronze badges

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

4 Comments

Gert Arnold Over a year ago

and then use LINQ query Well, that's the point. How? To me, your answer isn't more than a comment.

ilovegame Over a year ago

I don't know what do you mean by 'front view'

share pvv Over a year ago

we can create a View table in Database with all column in all table. and then use Linq on this View Table.

share pvv Over a year ago

how to create View in Transact-SQL? this my answer: msdn.microsoft.com/en-us/library/ms187956.aspx

Collectives™ on Stack Overflow

Entityframework single select from nested tables

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related