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 bGROUP BY emp_fname, b.date, b.BeginTime, b.EndTime, depart_name, b.msORDER 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; }