c# - 协助将我的 SQL 查询转换为 LINQ?
问题描述
我需要一些帮助来将此查询转换为 LINQ:
SELECT
st.ProductId, st.ProductType, st.StockValue, st.InOut, st.SupplierCmdId, st.CreationDate,
(SELECT su.Id FROM Suppliers AS su
WHERE su.Id = (SELECT suo.SupplierId FROM SupplierOrders AS suo
WHERE suo.Id = st.SupplierCmdId)) AS SupplerId
FROM
StockDetails AS st
WHERE
st.ProductType = 'Yarn'
AND st.ProductId = 2835
ORDER BY
st.CreationDate DESC
输出:
ProductId ProductType StockValue InOut SupplierCmdId CreationDate SupplerId
2835 Yarn 10 1 1450 2020-03-12 15:25:54.000 151
2835 Yarn 5 0 NULL 2019-03-04 00:00:00.000 NULL
2835 Yarn 5 0 NULL 2018-12-23 00:00:00.000 NULL
2835 Yarn 10 1 1398 2018-12-17 10:51:17.000 151
提前致谢
我努力了:
var ProductType = "Yarn";
var ProductId = 2835;
stocks = (from st in _context.StockDetails
join sn in _context.StockStatus on st.StatusId equals sn.Id
where st.ProductId == ProductId
&& st.ProductType == ProductType
orderby st.CreationDate descending
select new StockList
{
StockValue = st.StockValue,
InOut = st.InOut,
SupplierCmdId = st.SupplierCmdId,
CreationDate = st.CreationDate
});
在此我需要找到供应商 ID(请参阅 SQL 查询)
解决方案
我相信这应该是等价的:
var productType = "Yarn";
var productId = 2835;
var query =
from st in ctx.StockDetails
where st.ProductType == productType
where st.ProductId == productId
orderby st.CreationDate descending
let suppliers =
from suo in ctx.SupplierOrders
join su in ctx.Suppliers on suo.SupplierId equals su.Id
where suo.Id == st.SupplierCmdId
select su
from su in suppliers.DefaultIfEmpty()
select new
{
st.ProductId,
st.ProductType,
st.StockValue,
st.InOut,
st.SupplierCmdId,
st.CreationDate,
SupplierId = su.Id,
};
推荐阅读
- json - 我正在尝试从 api 获取数据并将其显示在 DropDownButton 中。问题是快照返回 null
- html - 使用浏览器的自定义样式表 - 如何隐藏 netflix 上的特定节目?
- python - 防止 Python 在循环期间存储输入
- reactjs - useEffect 和功能组件主体中的代码有什么区别?
- python - Matplotlib 在所有 Jupyter 笔记本中显示空白数字(无错误)
- python - 用户定义的函数在python中返回错误
- excel - 如何根据 If 语句的结果填充单元格
- python - Python pandas 中“in”关键字的使用
- node.js - URN Problem Forge Model Derivative API - 无效的设计参数
- java - java - 为什么Java中没有简单的将日期转换为LocalDate的inbult转换器?