如何在多个连接中使用多个groupby，在linq中使用leftjoin到sql， [英] How to use multiple groupby in multiple joins and leftjoin in linq to sql ,

问题描述

我想在linq的多列上分组。



下面是sql查询我有蚂蚁在我的linq生成类似类型的组

Hi ,

I want to group by on multiple columns in linq.

Below is sql query I have I ant to generate similar type of group by in my linq

SELECT p.ProductID,p.Name, p.Code,st.Month,st.Year,st.StartDate,SUM(ISNULL(st.Qty,0)) 
	AS Qty, sum(ISNULL(st.Value,0)) AS Value
	FROM UserToProduct up
	INNER JOIN Product p ON up.ProductID = p.ProductID
	LEFT OUTER JOIN SalesTarget st ON p.ProductID = st.ProductID AND st.IsInActive = 0
	AND st.UserID = @UserID AND st.Year = @Year
	WHERE up.IsInActive = 0 AND p.IsInActive = 0 AND up.UserID = @UserID AND p.IsLiterature = 0
	GROUP BY p.ProductID,p.Name, p.Code,st.Month,st.Year,st.StartDate

我的尝试：

What I have tried:

var result = from up in db.UserToProducts.Where(up => up.IsInActive == false && up.UserID == userID)
                       join p in db.Products.Where(p => p.IsInActive == false && p.IsLiterature == false)
                       on up.ProductID equals p.ProductID
                       join s in db.Sales.Where(s => s.IsInActive == false && s.UserID == userID
                       && s.Date.Year == year && s.Date.Month == month)
                       on p.ProductID equals s.ProductID into joined
                       from j in joined.DefaultIfEmpty()
                       select new SalesachievementBO
                       {

                       };

推荐答案

var result = from p in db.Products.Where(p => p.IsInActive == false && p.IsLiterature == false)
                         join up in db.UserToProducts.Where(up => up.IsInActive == false
                         && up.UserID == userID) on p.ProductID equals up.ProductID
                         join ul in db.UserToLocations.Where(ul => ul.IsInActive == false)
                         on up.UserID equals ul.UserID
                         join l in db.Locations.Where(l => l.IsInActive == false)
                         on ul.LocationID equals l.LocationID
                         join s in db.Sales.Where(s => s.IsInActive == false && s.UserID == userID && s.Date.Month == month
                         && s.Date.Year == year && s.WholeSalerID == wholeSalerId) on l.LocationID equals s.LocationID into joined
                         from merged in joined.DefaultIfEmpty()
                         group new { up.Product, ul.Location, merged }
                         by new
                         {
                             LocationID = ul.Location.LocationID,
                             ul.Location.Name,
                             ProductID = up.Product.ProductID,
                             ProdictName = up.Product.Name,
                             up.Product.Code,
                             month = merged.Date.Month,
                             year = merged.Date.Year,
                             merged.Date
                         } into g
                         select new SalesachievementBO()
                         {
                             ProductID = g.Key.ProductID,
                             ProductName = g.Key.ProdictName,
                             ProductCode = g.Key.Code,
                             LocationId = g.Key.LocationID,
                             LocationName = g.Key.Name,
                             Qty = g.Sum(p => (p.merged.Qty == null ? 0 : p.merged.Qty)),
                             Price = g.Sum(p => p.merged.Price == null ? 0 : p.merged.Price)
                         };

您需要做的就是使用 GroupBy 声明+ 选择，类似于以下示例：

All you need to do is to use GroupBy statement + Select, similar to below example:

var query = dataContext.GroupBy(x=>new {A = x.Field1, B= x.Field2, C = x.Field3)
                       .Select(grp=> new {
                            Field1 = grp.Key.A,
                            Field2Sum = grp.Sum(a=>a.B)}):

看看这里：

如何：分组查询结果（C＃编程指南） [ ^ ]

101 CQ中的LINQ示例 [ ^ ]

这篇关于如何在多个连接中使用多个groupby，在linq中使用leftjoin到sql，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！