-1

I've been trying to convert this SQL that I have into a lambda expression. However i've spent too many hours trying to solve it now and hope that maybe you guys could explain a solution to me please.

SELECT *
FROM Products
INNER JOIN Details on Products.ID = Details.ID
INNER JOIN Orders ON Details.ID = Orders.ID

Attempts:

db.Products.Include(x => x.Details)
           .Include("Details.Orders");

Attempted to use an Join method to no avail either. It needs to be returned as a Iqueryable. I thought using the include would simplify the join process however it seems more complicated. No matter which tables I include with the Include it doesn't seem to be possible to get the Orders.

NetMage
  • 26,163
  • 3
  • 34
  • 55
Jeremy
  • 447
  • 1
  • 4
  • 12

1 Answers1

0

If you are using EF, you can just do:

db.Products.Include(x => x.Details)
           .Include(x => x.Details.Orders);

Otherwise, using LINQ to SQL, you can translate the SQL as:

from p in db.Products
join d in db.Details on p.ID equals d.ID
join o in db.Orders on d.ID equals o.ID
select new { p, d, o};

which translates into lambda syntax as:

db.Products.Join(db.Details, p => p.ID, d => d.ID, (p,d) => new { p, d })
           .Join(db.Orders, d => d.ID, o => o.ID, (pd, o) => new { pd.p, pd.d, o });
NetMage
  • 26,163
  • 3
  • 34
  • 55
  • That's how I hoped to do it originally.However details does not contain a definition for Orders even though they're linked in the database – Jeremy Feb 28 '18 at 16:51
  • 1
    @Jeremy You need to fix your model then. – NetMage Feb 28 '18 at 16:54
  • I thought it would work though because the model was generated from the database – Jeremy Feb 28 '18 at 16:55
  • EF needs some help in the column names to realize there is a foreign key relationship. I find it peculiar that you have Products.ID == Details.ID == Orders.ID (e.g. 1:1:1 relationship). – NetMage Feb 28 '18 at 16:56
  • Can you link any resources for me to look at about assisting EF with the relationship – Jeremy Feb 28 '18 at 17:21
  • Here is an answer that explain how to create it in the designer: https://stackoverflow.com/a/19591602/2557128 - it appears you need to have the relationship defined in the database on the server for the model generation to pick it up, otherwise you must manually add them to the model. – NetMage Feb 28 '18 at 18:20