Linq Lambda 表达式 - 左连接

Question

1. 我table1了一组标准值monthname和monthnumber。

2. 另一个table2有很多列，但我想与列名进行比较CreateDate.Month.

3. 想要从table2条件获取所有记录是(1月至4月数据)

4. 在表 2 中，如果记录在相应的月份不可用，则计数应为 0 表示不可用的月份

我正在寻找一个答案作为 Linq 语句。

List<monthNames> defaultMonthNames = new List<monthNames>
{
new monthNames { monthNumber = 1, monthName = "Jan"},
new monthNames { monthNumber = 2, monthName = "Feb"},
new monthNames { monthNumber = 3, monthName = "Mar"},
new monthNames { monthNumber = 4, monthName = "Apr"},
new monthNames { monthNumber = 5, monthName = "May"},
new monthNames { monthNumber = 6, monthName = "Jun"},
new monthNames { monthNumber = 7, monthName = "Jul"},
new monthNames { monthNumber = 8, monthName = "Aug"},
new monthNames { monthNumber = 9, monthName = "Sep"},
new monthNames { monthNumber =10, monthName = "Oct"},
new monthNames { monthNumber = 11, monthName = "Nov"},
new monthNames { monthNumber = 12, monthName = "Dec"},
};
var Toprecords = somedata.Join(defaultMonthNames,
tc => tc.CreatedDate.Month, dmn => 
dmn.monthNumber,
(tc, dmn) => new 
{ TopClients = tc.CreatedDate.Month, 
defaultMonthNames = dmn 
}).GroupBy(a => new
{
a.ClientId,
a.CreatedDate.Month
}).Select(m => new
{
label = m.Select(a => 
a.ClientName).FirstOrDefault(),
NoOfOrders = m.Count(),
monthnumber =                           
m.Select(a=>a.CreatedDate.Month).FirstOrDefault()
}).ToList();

表1：

EmpName createdDate
Name1    12-01-2019
Name2    02-02-2019
Name3    05-02-2019
Name4    08-02-2019
Name5    02-03-2019
Name6    04-09-2019

表2：

monthName monthnumber
Jan         1
Feb         2
Mar         3
Apr         4

预期输出：

EmpName  MonthName  count
Name1    Jan          1
Name1    Feb          0
Name1    Mar          0
Name1    Apr          0
Name2    Jan          0
Name2    Feb          1
Name2    Mar          0 

Answer 1

您要做的非常繁琐，我认为在这种情况下，混合使用 LINQ 查询和 lambda 语法可能会更清楚。作为起点，下面的代码应该会得到你想要的结果，所以我希望它能有所帮助！

var someData = new List<SomeData>
{
new SomeData {EmpName = "Name 1", CreatedDate = new DateTime(2019, 1, 12)},
new SomeData {EmpName = "Name 2", CreatedDate = new DateTime(2019, 2, 2)},
new SomeData {EmpName = "Name 3", CreatedDate = new DateTime(2019, 2, 5)},
new SomeData {EmpName = "Name 4", CreatedDate = new DateTime(2019, 2, 8)},
new SomeData {EmpName = "Name 5", CreatedDate = new DateTime(2019, 3, 2)},
new SomeData {EmpName = "Name 6", CreatedDate = new DateTime(2019, 4, 9)},
};
var defaultMonthNames = new List<MonthNames>
{
new MonthNames {MonthName = "Jan", MonthNum = 1},
new MonthNames {MonthName = "Feb", MonthNum = 2},
new MonthNames {MonthName = "Mar", MonthNum = 3},
new MonthNames {MonthName = "Apr", MonthNum = 4},
new MonthNames {MonthName = "May", MonthNum = 5},
new MonthNames {MonthName = "Jun", MonthNum = 6},
new MonthNames {MonthName = "Jul", MonthNum = 7},
new MonthNames {MonthName = "Aug", MonthNum = 8},
new MonthNames {MonthName = "Sep", MonthNum = 9},
new MonthNames {MonthName = "Oct", MonthNum = 10},
new MonthNames {MonthName = "Nov", MonthNum = 11},
new MonthNames {MonthName = "Dec", MonthNum = 12},
};
var crossJoined = from sd in someData
from dmn in defaultMonthNames.Where(dmn => dmn.MonthNum <= 4)
select new
{
sd.EmpName,
dmn.MonthName,
dmn.MonthNum
};
var dataMonthCounts = someData
.GroupBy(sd => new { sd.EmpName, sd.CreatedDate.Month })
.Select(group => new
{
EmpName = group.Key.EmpName,
MonthNum = group.Key.Month,
Count = group.Count()
});
var results = from cj in crossJoined
join dmc in dataMonthCounts
on new { cj.EmpName, cj.MonthNum } equals new { dmc.EmpName, dmc.MonthNum } into combined
from c in combined.DefaultIfEmpty()
select new
{
cj.EmpName,
cj.MonthName,
Count = c?.Count ?? 0
};