How to convert SQL multiple table query to Linq

Question

I tried to convert sql query to linq but couldn't get it correctly. Can someone help me to convert below to linq please?

SELECT stockdiary.datenew, locations.ID AS LOCATIONID, locations.NAME AS LOCATIONNAME, 
                    products.REFERENCE, products.NAME, 
                    products.CATEGORY, categories.NAME AS CATEGORYNAME, 
                    products.SUPPLIER, 
                    SUM(CASE WHEN stockdiary.UNITS <0 THEN stockdiary.UNITS ELSE 0 END) AS UNITSOUT, 
                    SUM(CASE WHEN stockdiary.UNITS <0 THEN stockdiary.UNITS * stockdiary.PRICE ELSE 0 END) AS TOTALOUT, 
                    SUM(CASE WHEN stockdiary.UNITS >=0 THEN stockdiary.UNITS ELSE 0 END) AS UNITSIN, 
                    SUM(CASE WHEN stockdiary.UNITS >=0 THEN stockdiary.UNITS * stockdiary.PRICE ELSE 0 END) AS TOTALIN, 
                    SUM(stockdiary.UNITS) AS UNITSDIFF, 
                    SUM(stockdiary.UNITS * stockdiary.PRICE) AS TOTALDIFF 
                FROM stockdiary JOIN locations ON stockdiary.LOCATION = locations.ID, 
                    products LEFT OUTER JOIN categories ON products.CATEGORY = categories.ID 
                WHERE products.ID = stockdiary.PRODUCT 
                GROUP BY locations.ID, locations.NAME, products.REFERENCE, products.NAME, products.CATEGORY, categories.NAME 
                ORDER BY locations.ID, categories.NAME, products.NAME

====== below is my linq query which gives me wrong result.

(from sd in Stockdiaries
join loc in Locations on sd.Location equals loc.Id
join prod in Products on sd.Product equals prod.Id
join cat in Categories on prod.Category equals cat.Id
select new 
{
    Location = loc.Name,
    Category = cat.Name,
    Reference = prod.Reference,
    Product = prod.Name,
    UnitsOut = sd.Units < 0 ? sd.Units:0,
    TotalOut = sd.Units < 0 ? sd.Units * sd.Price:0,
    UnitsIn = sd.Units >= 0 ? sd.Units:0,
    TotalIn = sd.Units >= 0 ? sd.Units * sd.Price:0,
    UnitsDiff = sd.Units,
    TotalDiff = sd.Units * sd.Price

})

Where is the code? this is just the query, perhaps this help sqltolinq.com — DomCR
– DomCR, Commented Sep 5, 2019 at 10:22
@Dom93 this isnt a supported application any more, and hasnt been since mid 2016. — Simon Price
– Simon Price, Commented Sep 5, 2019 at 10:30
As I said, is difficult to help without the code, I don't know what have you tried, or where it throws an error — DomCR
– DomCR, Commented Sep 5, 2019 at 10:33
@Dom93 what I have up there is just an SQL query. The query retrieves fine. But I need is to convert this query to C# Linq query so that I could get strongly typed results in my C# app. I don't know how to write above sql in equivalent linq! — Suraj
– Suraj, Commented Sep 5, 2019 at 14:24
@Dom93 I tried LinqPad but my results are different as opposed to the SQL query result! — Suraj
– Suraj, Commented Sep 5, 2019 at 14:27

pwilcox · Accepted Answer · 2019-09-05 15:41:03Z

You've got some old join syntax in that original sql! This is in addition to the left join. This is abbreviated here:

from        stockdiary 
join        locations on stockdiary.location = locations.id
,           products 
left join   categories on products.category = categories.id 
where       products.id = stockdiary.product

See if the following approach works out for you. It may not output exactly as you desire it, but it's hopefully close and sufficient for you to work with it after that. Although that being said, I don't have any sample data from you on which to test it, so the only thing I can confirm right now is that it won't error out.

I've got comments within the code describing its components.

var query = 
    from sd in Stockdiaries
    join loc in Locations on sd.Location equals loc.Id

    // Your old syntax join should work like an inner join
    join prod in Products on sd.Product equals prod.Id

    // This is a left join.  So you've got to do the 'into' hoop and 
    // then 'overwrite' the cat table.
    join cat in Categories on prod.Category equals cat.Id into pCat
    from cat in pCat.DefaultIfEmpty()

    // put it all together into one result set
    select new {
        Location = loc.Name,
        Category = cat?.Name, // Because it's a left join, it may be null, hence the '?'
        Reference = prod.Reference,
        Product = prod.Name,
        sd.Units,
        sd.Price

    } into cnd

    // group as appropriate, and remember that in linq 
    // grouping is a separate operation from aggregation
    group cnd by new { cnd.Location, cnd.Reference, cnd.Product, cnd.Category } into g

    // aggregate
    select new {
        g.Key.Location,
        g.Key.Reference,
        g.Key.Product,
        g.Key.Category,
        UnitsOut = g.Sum(row => row.Units < 0 ? row.Units : 0),
        TotalOut = g.Sum(row => row.Units < 0 ? row.Units * row.Price : 0),
        UnitsIn = g.Sum(row => row.Units >= 0 ? row.Units : 0),
        TotalIn = g.Sum(row => row.Units >= 0 ? row.Units * row.Price : 0),
        UnitsDiff = g.Sum(row => row.Units),
        TotalDiff = g.Sum(row => row.Units * row.Price)
    };

query.Dump();

Perfect! This is absolutely what I needed. Thanks a million.

Suraj · Accepted Answer · 2019-09-05 16:20:59Z

@pwilcox and all other friends,

Here is the final linq query which gave me the absolute result! I added orderby expression in addition to the above query.

(from sd in Stockdiaries
join loc in Locations on sd.Location equals loc.Id
join prod in Products on sd.Product equals prod.Id
join cat in Categories on prod.Category equals cat.Id into pCat
from cat in pCat.DefaultIfEmpty()
select new {
    Location = loc.Name,
    Category = cat.Name,
    Reference = prod.Reference,
    Product = prod.Name,
    sd.Units,
    sd.Price
    } into cnd
group cnd by new {cnd.Location,cnd.Reference, cnd.Product, cnd.Category} into g
orderby g.Key.Location, g.Key.Category, g.Key.Product
select new {
        g.Key.Location,
        g.Key.Reference,
        g.Key.Product,
        g.Key.Category,
        UnitsOut = g.Sum(row => row.Units < 0 ? row.Units : 0),
        TotalOut = g.Sum(row => row.Units < 0 ? row.Units * row.Price : 0),
        UnitsIn = g.Sum(row => row.Units >= 0 ? row.Units : 0),
        TotalIn = g.Sum(row => row.Units >= 0 ? row.Units * row.Price : 0),
        UnitsDiff = g.Sum(row => row.Units),
        TotalDiff = g.Sum(row => row.Units * row.Price)

})

Collectives™ on Stack Overflow

How to convert SQL multiple table query to Linq

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related