使用EF+Linq或Sql存储过程获取报表数据优缺点分析-开发框架文库

使用EF+Linq或Sql存储过程获取报表数据优缺点分析

前言

本文讲解使用EF+Linq或Sql存储过程获取报表数据优缺点分析。

下面以员工档案报表举例说明。

EFCore+Linq查询

优点：无数据迁移相关问题（无Sql脚本，无存储过程）

缺点：查询算法程序硬编码，当报表增删字段，需要重新发布版本才能使用。

C# 全选

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());

Sql 存储过程

优点：将查询算法写到存储过程，C#代码与Sql代码分离，代码可读性好。当报表增删字段，只需要更改存储过程，不需要发布版本。

缺点：数据库迁移相当麻烦，需要重新编写不同数据库的存储过程。

C#，调用存储过程：

C# 全选

var pInfoID = _Database.CreateParameter("InfoID", input.InfoID);
ds = _Database.GetDataSet("usp_EmployeeQueryReportData", CommandType.StoredProcedure, pInfoID);

Sql存储过程

SQL 全选

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