CODEWITHSUNDEEP
c#sqllinq
Ignas Jasulaitis
Ignas Jasulaitis

Reputation: 13

SQL to Linq: left join

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();

Upvotes: 0

Views: 58

Answers (1)

FLYFLU
FLYFLU

Reputation: 103

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();

Upvotes: 1

Related Questions