c# - 多个表上的 Linq-to-SQL 查询 (AsEnumerable)
问题描述
这是一个 Linq-to-SQL 查询,它只使用我的 SQL Server 数据库中的一个表并且运行良好:
private void GetData()
{
DateTime d = DateTime.Now;
using (DataClasses1DataContext dc = new DataClasses1DataContext())
{
var qte = dc.ENTREES_STOCKS.AsEnumerable()
.Where(x => x.ENTSTK_LOT == lot)
.Where(x => x.ART_CODE == artCode)
.Where(x => x.ENTSTK_USER == null)
.Select(s => new
{
art = s.ART_CODE,
date = s.ENTSTK_DTENTREE,
numLot = s.ENTSTK_LOT,
pnet = s.ENTSTK_PNET,
nbu = s.ENTSTK_NBU
})
.GroupBy(g => new { g.art, g.date, g.numLot })
.Select(n => new
{
n.Key.art,
n.Key.date,
n.Key.numLot,
pnet = n.Sum(x => Math.Round(Convert.ToDecimal(x.pnet), 2)),
nbu = n.Sum(x => Math.Round(Convert.ToDecimal(x.nbu), 2)),
});
QEntreeTB.Text = qte.First().pnet.ToString();
NbuEntreeTB.Text = qte.First().nbu.ToString();
}
}
我如何修改此代码以将其他表加入此查询,例如:
private void GetData()
{
DateTime d = DateTime.Now;
using (DataClasses1DataContext dc = new DataClasses1DataContext())
{
var qte = dc.ENTREES_STOCKS.AsEnumerable()
// Thoseline of codes of course doesn't work
join art in dc.FICHES_ARTICLES on ENTREES_STOCKS.ART_CODE equals art.ART_CODE
join ent in dc.STK_ENT on art.ART_CODE equals ent.ART_CODE
....
//
.Where(x => x.ENTSTK_LOT == lot)
.Where(x => x.ART_CODE == artCode)
.Where(x => x.ENTSTK_USER == null)
.Select(s =>
new
{
art = s.ART_CODE,
date = s.ENTSTK_DTENTREE,
numLot = s.ENTSTK_LOT,
pnet = s.ENTSTK_PNET,
nbu = s.ENTSTK_NBU
}
)
.GroupBy(g => new { g.art, g.date, g.numLot })
.Select(n =>
new
{
n.Key.art,
n.Key.date,
n.Key.numLot,
pnet = n.Sum(x => Math.Round(Convert.ToDecimal(x.pnet), 2)),
nbu = n.Sum(x => Math.Round(Convert.ToDecimal(x.nbu), 2)),
}
);
QEntreeTB.Text = qte.First().pnet.ToString();
NbuEntreeTB.Text = qte.First().nbu.ToString();
}
}
或者是否有另一种方式来编码这个查询?因为实际上我只想加入多个表,对某些字段进行分组并对其他字段求和。
解决方案
首先,调用 AsEnumerable 有点多余。然后,您可以简单地使用 Join 扩展方法。
var qte = dc.ENTREES_STOCKS
.JOIN(dc.FICHES_ARTICLES,art=>art.ART_CODE, stock => stock.ART_CODE)
.JOIN(dc.STK_ENT,ent => ent.ART_CODE,stock => stock.ART_CODE)
.Where(x => x.ENTSTK_LOT == lot)
.Where(x => x.ART_CODE == artCode)
.Where(x => x.ENTSTK_USER == null)
....
推荐阅读
- jquery - 简化 jQuery If 语句
- javascript - jquery ajax POST中的字符串参数
- php - 从给定的几个月创建一个 12 个月的数组
- typescript - 无法使用比较器对我的 ag-grid 进行自定义排序
- angular - Kendo-ui Angular 7 如何将多个网格导出为一个 excel 文件?
- java - Spark 结构化流自定义 StateStoreProvide
- xslt-1.0 - XSLT 函数传递节点参数连接字符串然后返回元素值
- python - 将值归因于 pyhon 3.x 中的字典
- php - Laravel - 如何找到外键值
- r - R列表存储为字符串