c# - SQL查询到linq(linq到实体)
问题描述
我是 c# 和 linq 的新手,我需要在链接到实体中进行查询。对于初学者,我设法用 SQL 编写了整个查询,现在我没有成功在 linq 中创建这个查询。
SELECT s.store_name, p.product_name, p.product_id, p.list_price, SUB1.quantity_orders, st.quantity
FROM(
SELECT store_id, product_id, SUM(oi.quantity) quantity_orders
FROM orders as o inner join order_items as oi on o.order_id = oi.order_id
WHERE o.order_status != 4 and o.order_status != 3
GROUP BY store_id, product_id) AS SUB1
join stores s on s.store_id = SUB1.store_id
join products p on p.product_id = SUB1.product_id
join stocks st on st.store_id = SUB1.store_id and st.product_id = SUB1.product_id
WHERE SUB1.quantity_orders >
(select s.quantity
from stocks as s
where s.store_id = SUB1.store_id and s.product_id = SUB1.product_id)
谢谢。
解决方案
我用类模拟以获得正确的语法。
class Program
{
static void Main(string[] args)
{
Context context = new Context();
var orderQuery = (from o in context.orders.Where(x => (x.order_status != 4) && (x.order_status != 3))
join oi in context.order_items on o.order_id equals oi.order_id
select new { order = o, order_id = oi }
).ToList();
var groups = orderQuery.GroupBy(x => new { product_id = x.order.product_id, store_id = x.order.store_id }).ToList();
var results = groups.SelectMany(x => context.stock.Where(y => (x.Key.product_id == y.product_id) && (x.Key.store_id == y.store_id) && (x.Sum(z => z.order_id.quantity) > y.quantity))
.Select(y => new {
store_id = x.Key.store_id,
product_id = x.Key.product_id,
order_quantity = x.Sum(z => z.order_id.quantity),
store_quantity = y.quantity,
product = context.products.Where(z => z.product_id == x.Key.product_id).FirstOrDefault()
}).Select(y => new {
store_id = y.store_id,
product_id = y.product_id,
order_quantity = y.order_quantity,
store_quantity = y.store_quantity,
product_name = y.product.name,
price = y.product.list_price
})).ToList();
}
}
public class Context
{
public List<Stock> stock { get; set; }
public List<Order> orders { get; set; }
public List<Product> products { get; set; }
public List<Order_Items> order_items { get; set; }
public List<Store> stores { get; set; }
}
public class Stock
{
public int store_id { get; set; }
public int product_id { get; set; }
public int quantity { get; set; }
}
public class Order
{
public int order_status { get; set; }
public int store_id { get;set ;}
public int order_id { get; set; }
public int product_id { get; set; }
}
public class Order_Items
{
public int order_id { get; set; }
public int quantity { get; set; }
}
public class Product
{
public int product_id { get; set; }
public string name { get; set; }
public decimal list_price { get; set; }
}
public class Store
{
public int store_id { get; set; }
public string store_name { get; set; }
}
这是第二个版本:
class Program
{
static void Main(string[] args)
{
Context context = new Context();
var orderQuery = (from o in context.orders.Where(x => (x.order_status != 4) && (x.order_status != 3))
join oi in context.order_items on o.order_id equals oi.order_id
select new { order = o, order_id = oi }
).ToList();
var groups = orderQuery.GroupBy(x => new { product_id = x.order.product_id, store_id = x.order.store_id }).ToList();
var productStock = (from s in context.stock
join p in context.products on s.product_id equals p.product_id
select new { store_id = s.store_id, product_id = s.product_id, quantity = s.quantity, name = p.name, price = p.list_price }
).ToList();
var results = groups.SelectMany(x => productStock.Where(y => (x.Key.product_id == y.product_id) && (x.Key.store_id == y.store_id) && (x.Sum(z => z.order_id.quantity) > y.quantity))
.Select(y => new {
store_id = x.Key.store_id,
product_id = x.Key.product_id,
order_quantity = x.Sum(z => z.order_id.quantity),
store_quantity = y.quantity,
product_name = y.name,
price = y.price
}).ToList()).ToList();
}
}
public class Context
{
public List<Stock> stock { get; set; }
public List<Order> orders { get; set; }
public List<Product> products { get; set; }
public List<Order_Items> order_items { get; set; }
public List<Store> stores { get; set; }
}
public class Stock
{
public int store_id { get; set; }
public int product_id { get; set; }
public int quantity { get; set; }
}
public class Order
{
public int order_status { get; set; }
public int store_id { get;set ;}
public int order_id { get; set; }
public int product_id { get; set; }
}
public class Order_Items
{
public int order_id { get; set; }
public int quantity { get; set; }
}
public class Product
{
public int product_id { get; set; }
public string name { get; set; }
public decimal list_price { get; set; }
}
public class Store
{
public int store_id { get; set; }
public string store_name { get; set; }
}
推荐阅读
- spring-boot - Autowire Bean 和 application.yml 文件在另一个 jar 文件中
- python - 框架不显示
- visual-studio-code - 如何撤消我使用 .reg 脚本所做的事情?(标题可能会混淆,所以只需检查这篇文章的内容)
- java - 当我尝试开始我的春季项目时,我得到了这个例外......任何想法
- node.js - 为什么我的 NPM 缓存文件夹在 ./false/_cacache 中?
- c# - 我如何使用 microsoft graph 获取通话记录
- json - 保存到文件后,REST JSON 响应显示在单行中
- linux - 使用 shell 脚本操作文本文件
- java - 在 JVM 级别控制 2 的平方?
- netcdf - 使用“自以来”的时间单位解码 netcdf 数据时出现 xarray 错误