Entity Framework - load data from table with 1:1 table involved using one query?

Question

Hello,

I have the following command:

var query = from x in context.FirstTable.Include("SecondTable")
where x.TestColumn == 5 &&
x.SecondTable.SecondTestColumn == 3
select x;

Now I also want to load the entries from a third table named "ThirdTable". But I can only reference it via the SecondTable table. There is a foreign key from FirstTable to SecondTable and one from SecondTable to ThirdTable, but none from FirstTable to ThirdTable.

using the following query was not possible. The exception was that it can not navigate from FirstTable to ThirdTable:

var query = from x in context.FirstTable.Include("SecondTable").Include("ThirdTable")
where x.TestColumn == 5 &&
x.SecondTable.SecondTestColumn == 3
select x;

Or will I need to do an additional query on ThirdTable for every resultset I get back from that query?

Thank you very much in advance!

Craig Stuntz:

I have the following foreign keys: table1 <-> table2 <-> table3
let's say the tables are the following way: orders <-> customers <-> customer_preferences

So I have no need to make a foreign key from orders to customer_preferences.
It would be unnecessary most of the time. Just this time i want to safe some extra database roundtrips.

Answer 1

If I remember correctly the Include only works for the first table. You can do something like this instead:

var query = from x in Invoices
   join p in Products 
   on x.Invoice_id equals p.Invoice_id
   join c in Customers 
   on x.Customer_id equals c.Customer_id
   where p.Customer_id == 123 
   && c.Description == "some description"
   select x;

You could also try something like this

var query = from x in context.FirstTable.Include("SecondTable").Include("SecondTable.ThirdTable")
where x.TestColumn == 5 &&
x.SecondTable.SecondTestColumn == 3
select x;

Answer 2

You can do this via eager loading:

var query = from x in context.FirstTable.Include("SecondTable.ThirdTable") // remember, these are property names, not table names
            where x.TestColumn == 5 
                      && x.SecondTable.SecondTestColumn == 3
            select x;

Or (this is the method I use, mostly) projection:

var query = from x in context.FirstTable
            where x.TestColumn == 5 
                      && x.SecondTable.SecondTestColumn == 3
            select new PresentationModel
            {
                FirstTableColumn = x.Something,
                SecondTableColumn = x.SecondTable.SomethingElse,
                ThirdTableColumn = x.SecondTable.ThirdTable.StillMore
            };

This presumes SecondTable:ThirdTable is 1:1 (you don't say). If it's 1:*, you'd do something like:

var query = from x in context.FirstTable
            where x.TestColumn == 5 
                      && x.SecondTable.SecondTestColumn == 3
            select new PresentationModel
            {
                FirstTableColumn = x.Something,
                SecondTableColumn = x.SecondTable.SomethingElse,
                ThirdTableStuff = from y in x.SecondTable.ThirdTable
                                  select y.StillMore
            };