Linq多条件关联查询实现方式
1、1。首先是构造两个Datatable数据集，进行关联 ，这里的两个DateTable，分别包含姓名和日期两个关联字段，但是两个DateTable字段名不一致。数据集1的数据如下
2、数据集2是个基础数据，这个基础数据的数据集如下所示
3、两个数据集准备完毕后，下面是拼接两个数据集的数据
4、由于多关联查询时，需要两个字段的字段名相同，因此此处我们用new同时用两个别名分别代替原先的字段来进行关联。
5、关联查询之后，赋值给相对应的VIewModel，然后把ViewModel传值到界面显示。
6、最终查询出来的结果如下所示。可以看出，以姓名和日期为基本信息，左连接查询考勤信息。
7、这里的源代码如下：
public List<SignInVM> GetSign(string BeginTime, string EndTime, string Employee)
{
OAContext oa = new OAContext();
DataTable dtUser = CommUtils.GetUserList(BeginTime, EndTime, Employee);
string where = "";
if (!string.IsNullOrEmpty(Employee))
{
where = string.Format(@" and a.emp_fname like'%{0}%'", Employee);
}
string strSQL = string.Format(@"SELECT b.emp_fname, b.depart_name,b.date,b.BeginTime,b.EndTime,
( CASE
WHEN ms = 0
AND CONVERT(CHAR(10), BeginTime, 108) < '12:00:00' THEN '下班未打卡'
WHEN ms = 0
AND CONVERT(CHAR(10), BeginTime, 108) > '12:00:00' THEN '上班未打卡'
WHEN 540 - ms BETWEEN 0 AND 30 THEN '迟到或早退30分钟以内'
WHEN 540 - ms > 30 THEN '迟到或早退30分钟以上'
ELSE '正常'
END) AS 状态
FROM (SELECT TOP 100 PERCENT a.emp_fname,
b.depart_name,
CONVERT(CHAR(10), sign_time, 120) date,
Min(c.sign_time) AS BeginTime,
Max(c.sign_time) AS EndTime,
Datediff(minute, Min(c.sign_time), Max(c.sign_time)) AS Ms
FROM Employee a
LEFT JOIN Departs b
ON a.depart_id = b.depart_id
LEFT JOIN TimeRecords c
ON a.emp_id = c.emp_id
WHERE
CONVERT(CHAR(10), sign_time, 120) BETWEEN '{0}' AND '{1}' {2}
AND a.emp_id IS NOT NULL
GROUP BY depart_name,
emp_fname,
CONVERT(CHAR(10), sign_time, 120)
ORDER BY depart_name,
emp_fname,
CONVERT(CHAR(10), sign_time, 120) ASC) AS b
GROUP BY emp_fname,
b.date,
b.BeginTime,
b.EndTime,
depart_name,
b.ms
ORDER BY b.depart_name,
b.emp_fname
", BeginTime, EndTime, where);
DataTable dt = DbNoarkKQSQL.Query(strSQL).Tables[0];
List<Models.SignInVM> items = (from m in dtUser.AsEnumerable()
join
n in dt.AsEnumerable() on
new { Name = m.Field<string>("UName"), Date = m.Field<string>("WorkDate") } equals
new { Name = n.Field<string>("emp_fname"), Date = n.Field<string>("date") }
into mn
from x in mn.DefaultIfEmpty()
select new SignInVM
{
Guid = Guid.NewGuid(),
UserName = m.Field<string>("UName"),
Department = m.Field<string>("ONAME"),
Date = m.Field<string>("WorkDate"),
DateType = string.Format(@"{0}({1})", m.Field<string>("DateType"), m.Field<string>("Remark")),
BeginTime = x == null ? null : x.Field<DateTime?>("BeginTime"),
Endtime = x == null ? null : x.Field<DateTime?>("EndTime"),
Status = x == null ? (m.Field<string>("Remark")!="工作日"?"正常":"未打卡") : x.Field<string>("状态"),
Remark = m.Field<string>("Remark"),
}).ToList();
return items;
}