如何将LINQ查询与不同的where条件结合起来

本文关键字：where 条件 结合 起来 LINQ 查询 | 更新日期: 2023-09-27 18:18:36

我有三个LINQ查询，我想合并。每个查询都有不同的where条件，但是group by和select子句总是遵循相同的模式。任何帮助都非常感谢!

查询1:

    var querySales = from row in bookings.AsEnumerable()
                         where (row.Field<Int32>("t-account") >= tAccSalesFrom && row.Field<Int32>("t-account") <= tAccSalesTo)
                         group row by new
                         {
                             year = row.Field<DateTime>("Date").Year,
                             month = row.Field<DateTime>("Date").Month
                         } into grp
                         orderby grp.Key.year, grp.Key.month
                         select new
                         {
                             Year = grp.Key.year,
                             Month = grp.Key.month,
                             Sales = grp.Sum(row => row.Field<Decimal>("Sales_Assets") - row.Field<Decimal>("Sales_Debit"))
                         };
查询2:

    var queryLabourCosts = from row in bookings.AsEnumerable()
                               where (row.Field<Int32>("t-account") >= tAccLabFrom && row.Field<Int32>("t-account") <= tAccLabTo)
                               group row by new
                               {
                                   year = row.Field<DateTime>("Date").Year,
                                   month = row.Field<DateTime>("Date").Month
                               } into grp
                               orderby grp.Key.year, grp.Key.month
                               select new
                               {
                                   Year = grp.Key.year,
                                   Month = grp.Key.month,
                                   LabourCosts = grp.Sum(row => row.Field<Decimal>("Sales_Debit") - row.Field<Decimal>("Sales_Assets"))
                               };
查询3:

    var queryMaterial = from row in bookings.AsEnumerable()
                            where (row.Field<Int32>("t-account") >= tAccMatFrom && row.Field<Int32>("t-account") <= tAccMatTo)
                            group row by new
                            {
                                year = row.Field<DateTime>("Date").Year,
                                month = row.Field<DateTime>("Date").Month
                            } into grp
                            orderby grp.Key.year, grp.Key.month
                            select new
                            {
                                Year = grp.Key.year,
                                Month = grp.Key.month,
                                MaterialCosts = grp.Sum(row => row.Field<Decimal>("Sales_Debit") - row.Field<Decimal>("Sales_Assets"))
                            };

解决方案:Thanks to lazyberezovsky!

                var querySalesLabMat = from b in bookings.AsEnumerable()
                               group b by new
                               {
                                   b.Field<DateTime>("Date").Year,
                                   b.Field<DateTime>("Date").Month,
                               } into g
                               orderby g.Key.Year, g.Key.Month
                               select new
                               {
                                   g.Key.Year,
                                   g.Key.Month,
                                   Sales = g.Where(r => r.Field<Int32>("t-account") >= tAccSalesFrom && r.Field<Int32>("t-account") <= tAccSalesTo)
                                                    .Sum(r => r.Field<Decimal>("Sales_Assets") - r.Field<Decimal>("Sales_Debit")),
                                   LabourCosts = g.Where(r => r.Field<Int32>("t-account") >= tAccLabFrom && r.Field<Int32>("t-account") <= tAccLabTo)
                                                  .Sum(r => r.Field<Decimal>("Sales_Debit") - r.Field<Decimal>("Sales_Assets")),
                                   MaterialCosts = g.Where(r => r.Field<Int32>("t-account") >= tAccMatFrom && r.Field<Int32>("t-account") <= tAccMatTo)
                                                  .Sum(r => r.Field<Decimal>("Sales_Debit") - r.Field<Decimal>("Sales_Assets"))
                               };

提前感谢你的帮助，垫

如何将LINQ查询与不同的where条件结合起来

var querySalesLabMat = 
   from b in bookings.AsEnumerable()
   group b by new
   {
       b.Field<DateTime>("Date").Year,
       b.Field<DateTime>("Date").Month,
   } into g
   orderby g.Key.Year, g.Key.Month
   select new
   {
       g.Key.Year,
       g.Key.Month,
       LabourCosts = g.Where(r => r.Field<Int32>("t-account") >= tAccLabFrom && r.Field<Int32>("t-account") <= tAccLabTo)
                      .Sum(r => r.Field<Decimal>("Sales_Debit") - r.Field<Decimal>("Sales_Assets")),
       Sales = g.Where(r => r.Field<Int32>("t-account") >= tAccSalesFrom && r.Field<Int32>("t-account") <= tAccSalesTo)
                .Sum(r => r.Field<Decimal>("Sales_Assets") - r.Field<Decimal>("Sales_Debit")),
       MaterialCosts = g.Where(r => r.Field<Int32>("t-account") >= tAccMatFrom && r.Field<Int32>("t-account") <= tAccMatTo)
                        .Sum(r => r.Field<Decimal>("Sales_Debit") - r.Field<Decimal>("Sales_Assets"))
   };

你的意思是:

var querySales = from row in bookings.AsEnumerable()
                         where (row.Field<Int32>("t-account") >= tAccSalesFrom && row.Field<Int32>("t-account") <= tAccSalesTo)
                         where (row.Field<Int32>("t-account") >= tAccLabFrom && row.Field<Int32>("t-account") <= tAccLabTo)
                         where (row.Field<Int32>("t-account") >= tAccMatFrom && row.Field<Int32>("t-account") <= tAccMatTo)
                         group row by new
                         {
                             year = row.Field<DateTime>("Date").Year,
                             month = row.Field<DateTime>("Date").Month
                         } into grp
                         orderby grp.Key.year, grp.Key.month
                         select new
                         {
                             Year = grp.Key.year,
                             Month = grp.Key.month,
                             Sales = grp.Sum(row => row.Field<Decimal>("Sales_Assets") - row.Field<Decimal>("Sales_Debit"))
                             //Group by year and month and sum based on the t-account
                               Sales = grp.Where().Sum(),
                               LabourCosts = grp.Where().Sum(),
                               MaterialCosts = = grp.Where().Sum()
                         };

?

相关文章：