0

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);
}
Gaz83
  • 2,293
  • 4
  • 32
  • 57
  • 2
    *Method syntax* is just a pain with query containing many joins. Consider using *query syntax* and *navigation properties*. – Ivan Stoev Aug 30 '17 at 10:13
  • 1
    *can this also be done using groupjoin?* -- `join - into` *is* GroupJoin. – Gert Arnold Aug 30 '17 at 12:46
  • sorry I wasn't clear. I meant using .GroupJoin. It's not essential I am just curious. – Gaz83 Aug 30 '17 at 12:49
  • Anyway, you're asking too many things in one question without giving enough details ("syntax errors" -- what errors, what syntax?). The performance question isn't answerable at all. – Gert Arnold Aug 30 '17 at 13:00

0 Answers0