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?

Answer 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.