c# - 如何将 SQL 多表查询转换为 Linq
问题描述
我试图将 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();
推荐阅读
- java - Docker&Java:构建不反映新文件
- google-apps-script - 是否可以从谷歌表中隐藏谷歌脚本?
- c# - Polly - 请在调用异步 ExecuteAsync(和类似)方法时使用异步定义的策略
- python - 制作石头剪刀布游戏时的值错误
- php - WordPress 类别搜索简码不显示选项
- r - R ggplot2 TCGA 表达数据的分组箱线图
- java - Collection.parallelStream() 是否暗示发生前的关系?
- python - python字典按字母键排序
- mysql - 从工作台生成的 SQL 脚本在 MariaDB 服务器上不起作用
- spring - 使用 TestRestController 调用 @PatchMapping 注释方法