SQL to Linq: left join

Question

I need to write linq query. I tried to write example in sql and it works great but I didn't manage to convert it to proper linq

This is my working sql query:

SELECT
   pr.descr as product,
     prm.Descr
   , px.Value AS ParamValue
    , prm.Unit AS Unit
    , gx.TcPos
FROM [Product] pr
    JOIN [PrGroup] prg ON pr.GroupId = prg.Id
    LEFT JOIN [PrParamGroup] pg ON ISNULL(pr.PrParamGroupId, prg.PrParamGroupId) = pg.Id
    CROSS JOIN [PrParam] prm
    LEFT JOIN [PrParam2ProductX] px ON pr.Id = px.ProductId AND prm.Id = px.PrParamId
    LEFT JOIN [PrParam2GroupX] gx ON prm.Id = gx.PrParamId AND pg.Id = gx.PrParamGroupId
WHERE
    pr.Id = 123 AND 
     (px.PrParamId IS NOT NULL OR gx.PrParamId IS NOT NULL)
     AND (gx.PrParamId <> -1 OR gx.PrParamId IS NULL)
     AND (gx.PrParamId IS NOT NULL)

This is my linq attempt:

var productsDesc = (from pr in context.Product.Where(m => m.Id == 123)
                                    join prg in context.PrGroup
                                    on pr.GroupId equals prg.Id
                                    join pg in context.PrParamGroup
                                    on pr.PrParamGroupId equals pg.Id
                                    from prm in context.PrParam
                                    join px in context.PrParam2ProductX
                                    on new { a = pr.Id, b = px.ProductId } equals new { a = prm.Id, b = px.PrParamId } 
                                    join gx in context.PrParam2GroupX
                                    on new { prm.Id = gx.PrParamId } equals new {  pg.Id = gx.PrParamGroupId }
                                    select new
                                    {
                                        Name = prm.Descr,
                                        Value = px.Value,
                                        Unit = prm.Unit ?? ""
                                    }).ToList();

What is your solution doing differently than what is expected? — Itamar Mushkin
– Itamar Mushkin, Commented Jul 4, 2019 at 6:46
Part where left join shoud be is not working. Problem is in lines where i try to join on multiple parameters.\ — Ignas Jasulaitis
– Ignas Jasulaitis, Commented Jul 4, 2019 at 6:49
What I mean is - please provide an example of what your solution is doing, and more importantly - what do you wish it to do. The "how to ask" page may help: [stackoverflow.com/help/how-to-ask] — Itamar Mushkin
– Itamar Mushkin, Commented Jul 4, 2019 at 6:50

FLYFLU · Accepted Answer · 2019-07-04 06:59:32Z

Your linq dont implement left join and cross join try this

var productsDesc = (from pr in context.Product.Where(m => m.Id == 123)
                                join prg in context.PrGroup
                                on pr.GroupId equals prg.Id
                                join pg in context.PrParamGroup
                                on pr.PrParamGroupId equals pg.Id into tmp1
                                from t1 in tmp1.DefaultIfEmpty()
                                from prm in context.PrParam
                                join px in context.PrParam2ProductX
                                on new { a = pr.Id, b = px.ProductId } equals new { a = prm.Id, b = px.PrParamId } into tmp2
                                from t2 in tmp2.DefaultIfEmpty()
                                join gx in context.PrParam2GroupX
                                on new { prm.Id = gx.PrParamId } equals new {  pg.Id = gx.PrParamGroupId } into tmp3
                                from t3 in tmp3.DefaultIfEmpty()
                                select new
                                {
                                    Name = prm.Descr,
                                    Value = t2.Value,
                                    Unit = prm.Unit ?? ""
                                }).ToList();

Collectives™ on Stack Overflow

SQL to Linq: left join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related