如何将 SQL 多表查询转换为 Linq

debugcn 发表于 Dev

苏拉杰

我试图将 sql 查询转换为 linq，但无法正确获取。有人可以帮我将下面的转换为 linq 吗？

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

====== 下面是我的 linq 查询，它给了我错误的结果。

(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

})

普威尔考克斯

您在原始 sql 中有一些旧的连接语法！这是对左连接的补充。这里缩写为：

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

看看以下方法是否适合您。它可能不会完全按照您的意愿输出，但希望它很接近并且足以让您在此之后使用它。尽管如此，我没有你提供的任何样本数据来测试它，所以我现在唯一可以确认的是它不会出错。

我在描述其组件的代码中有注释。

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。