var q1 = _Database.GetQueryable<dt_Employee>();
var q2 = _Database.GetQueryable<dt_EmployeeContract>();
var q3 = _Database.GetQueryable<dt_EmployeePositionAdjustment>();

var qTmp = (from a in q1
            join b in q2 on a.InfoID equals b.InfoID into tmp1
            from b in tmp1.DefaultIfEmpty()

            join c in q3 on a.InfoID equals c.InfoID into tmp2
            from c in tmp2.DefaultIfEmpty()

            where a.InfoID == input.InfoID
            select new res_EmployeeReport
            {
                isid = a.isid,
                InfoID = a.InfoID,
                Birthday = a.Birthday,
                CID = a.CID,
                CodeID = a.CodeID,
                CreatedBy = a.CreatedBy,
                CreationDate = a.CreationDate,
                DeptCode = a.DeptCode,
                Nation = a.Nation,
                Native = a.Native,
                NativeAddress = a.NativeAddress,
                Phone = a.Phone,
                UIDExpire = a.UIDExpire,
                UIDValid = a.UIDValid,
                AppLoginPwd = a.AppLoginPwd,
                Education = a.Education,
                EmpName = a.EmpName,
                EntryFactoryDate = a.EntryFactoryDate,
                FlagAppLogin = a.FlagAppLogin,
                FlagSales = a.FlagSales,
                HealthStatus = a.HealthStatus,
                IntroduceName = a.IntroduceName,
                IsMarry = a.IsMarry,
                JobId = a.JobId,
                KitasID = a.KitasID,
                LastUpdateDate = a.LastUpdateDate,
                LastUpdatedBy = a.LastUpdatedBy,
                NowAddress = a.NowAddress,
                PID = a.PID,
                PositionId = a.PositionId,
                ProcessId = a.ProcessId,
                Relation = a.Relation,
                Remark = a.Remark,
                Sex = a.Sex,
                Speciality = a.Speciality,
                UID = a.UID,

                ContractNo = b == null ? "" : b.ContractNo,
                ValidateDateBegin = b == null ? null : b.ValidateDateBegin,
                ValidateDateEnd = b == null ? null : b.ValidateDateEnd,
                DeptAfter = c == null ? "" : c.DeptAfter,

                Ages = 0,
                WorkYears = 0,
            });

var listM = qTmp.ToList();

//计算工龄和年龄
listM.ForEach(item =>
{
    item.WorkYears = ((DateTime.Today - item.EntryFactoryDate)?.TotalDays / 365d).ToInt();
    item.Ages = ((DateTime.Today - item.Birthday)?.TotalDays / 365d).ToInt();
});

var listD = q2.Where(w => w.InfoID == input.InfoID).ToList();

ds.Tables.Add(listM.ToDataTable());
ds.Tables.Add(listD.ToDataTable());

ALTER  PROCEDURE [dbo].[usp_EmployeeQueryReportData]
	@InfoID varchar(50)
AS
BEGIN
	/**********************************************************************
	* 查询员工资料
	* --------------------------------------------------------------------	
	 修改记录：
		2023/08/29 : 创建
	--------------------------------------------------------------------
	SELECT * FROM dt_EmployeE
	SELECT * FROM dt_EmployeePositionAdjustment

	EXEC [usp_EmployeeQueryReportData] '8001';
	 
	*********************************************************************/
	--返回员工表
	SELECT TOP 1 e.*,
		ec.ContractNo,ec.ValidateDateBegin,ec.ValidateDateEnd,
		epa.DeptAfter,--最新部门	
		Ages=DATEDIFF(YY,e.Birthday,GETDATE()), --年龄
		WorkYears=DATEDIFF(YY,e.EntryFactoryDate,GETDATE()) --连续工龄
	FROM dt_Employee e
	left join dt_EmployeeContract ec on e.InfoID=ec.InfoID
	left join dt_EmployeePositionAdjustment epa on e.InfoID=epa.InfoID
	WHERE e.InfoID = @InfoID 
	ORDER BY ec.CreationDate,epa.CreationDate DESC

	--返回员工合同表
	SELECT * FROM dt_EmployeeContract WHERE InfoID=@InfoID ORDER BY CreationDate DESC

END