So I am working on converting an SQL query to something I can use in Entity Framework
This is my SQL query
SELECT *
FROM [UUT_RESULT]
LEFT JOIN [STEP_RESULT] ON [UUT_RESULT].[ID] = [STEP_RESULT].[UUT_RESULT]
LEFT JOIN [PROP_RESULT] ON [STEP_RESULT].ID = [PROP_RESULT].STEP_RESULT
LEFT JOIN [STEP_NUMERICLIMIT1] ON [STEP_RESULT].ID = [STEP_NUMERICLIMIT1].[STEP_RESULT]
LEFT JOIN [STEP_NUMERICLIMIT2] ON [STEP_NUMERICLIMIT1].ID = [STEP_NUMERICLIMIT2].[PROP_RESULT]
WHERE [UUT_RESULT].[UUT_SERIAL_NUMBER] = '170607014G100100539F'
After doing some googling I came up with this code for EF.
var sqlResults2 = db.UUT_RESULT.Where(x => x.UUT_SERIAL_NUMBER == "170607014G100100539F")
.GroupJoin(db.STEP_RESULT, x => x.ID, y => y.UUT_RESULT,
(f, bs) => new {UUT_RESULT = f, STEP_RESULT = bs.DefaultIfEmpty()}).ToList();
This looks like it has taken care of the first part with the first join between UUT_RESULT and STEP_RESULT and runs fine but how do I do the other joins? When ever I try I always get syntax errors.
I also did try using linq and .Include but the query took for ever to run i.e SQL query took 5 seconds to get the data vs linq query took 20 minutes.
UPDATE: The answer to this post Writing a nested join with LINQ to Entities does solve my issue but can this also be done using groupjoin? Here is my code using the answer in the post.
(from u in db.UUT_RESULT
where u.UUT_SERIAL_NUMBER == "170607014G100100539F"
join sr in db.STEP_RESULT on u.ID equals sr.UUT_RESULT into usr
select new
{
u,
usr = (from x in usr
join pr in db.PROP_RESULT on x.ID equals pr.STEP_RESULT into prs
join sn1 in db.STEP_NUMERICLIMIT1 on x.ID equals sn1.STEP_RESULT into sn1s
select new
{
x,
prs = prs.ToList(),
sn1s = (from y in sn1s
join sn2 in db.STEP_NUMERICLIMIT2 on y.ID equals sn2.PROP_RESULT into sn1sn2
select new
{
y,
sn1sn2 = sn1sn2.ToList()
}).ToList()
}).ToList()
}).ToList();
var tempList = new List<UUT_RESULT>();
foreach (var result in results3)
{
tempList.Add(result.u);
}