在ABP中通过EF直接执行原生Sql的解决方案

一般情况下,使用EF中的查询语法和方法语法可以帮助我们完成绝大部分业务,但是也有特殊的情况需要直接执行Sql语句。比如,我们的业务过于复杂繁琐,或是有些业务使用EF操作时比较复杂,但是使用Sql时会很简单等等。

今天做项目的时候,就由于使用EF来处理逻辑时格外棘手,最终选择了直接执行Sql语句,下面说说具体的业务。

一共关系到三张表,分别是Operators(运营商表),Orders(订单表)和DeviceCoinsRecords(设备投币记录表),相应表的定义已经截图如下,就不在这里编写代码了。Operators里放运营商的数据信息,Orders是手机端扫码下单时产生的订单数据,DeviceCoinsRecords是向机器设备投币时产生的数据。

用下面的一张图解释一下:

在ABP中通过EF直接执行原生Sql的解决方案

下面是三张表的结构:

在ABP中通过EF直接执行原生Sql的解决方案

在ABP中通过EF直接执行原生Sql的解决方案

在ABP中通过EF直接执行原生Sql的解决方案

三张表之间的关联关系是:Orders和和DeviceCoinsRecords通过OperatorId关联Operators表的Id主键。

要实现的业务是:

Sql语句表达:

SELECT ROW_NUMBER() OVER(ORDER BY Id ) AS RowId,Id,Name,AllocateRatio,
(SELECT SUM(PayFee) FROM dbo.Orders WHERE Status NOT IN(0,3,4) AND OperatorID=dbo.Operators.Id  AND YEAR(OrderDate)=@Year1 AND MONTH(OrderDate)=@Month1) AS TotalScanCode,
(SELECT SUM(TheDayMoney) FROM dbo.DeviceCoinsRecords WHERE dbo.DeviceCoinsRecords.OperatorID=dbo.Operators.Id AND Year=@Year2 AND Month=@Month2) AS TotalCoinsCast
 FROM dbo.Operators

视图表达:
在ABP中通过EF直接执行原生Sql的解决方案

自然语言表达:

汇总运营商的年度和月度报表,也可选择具体的运营商和年月进行汇总。

一开始我是拒绝在EF中使用SQL查询的,但是花了好长时间,实在是找不到可以替代以上sql的linq语句或者Lambda方法语法,有哪位能写出来的,欢迎挑战一下,但是写出来的很复杂的话,也没这个必要了。

下面说说具体的实现【首先要声明的是我使用的是ABP框架】。

1.在核心层(Core层)的IRepositories文件下创建一个接口IOperatorReportRepository

在ABP中通过EF直接执行原生Sql的解决方案

2.该接口的定义如下代码所示:

  public interface IOperatorReportRepository:IRepository<Operators>
    {
        Task<IEnumerable<OperatorYearOrMonthReport>> QueryMonthReports(int year, int month, int operatorId);
        Task<IEnumerable<OperatorYearOrMonthReport>> QueryYearReports(int year, int operatorId);
    }

3.实现接口代码:

 public class OperatorReportReposiroty:ChargeStationRepositoryBase<Operators>,IOperatorReportRepository
    {
        public OperatorReportReposiroty(IDbContextProvider<ChargeStationDbContext> dbContextProvider)
            : base(dbContextProvider)
        {
            
        }
        public async Task<IEnumerable<OperatorYearOrMonthReport>> QueryMonthReports(int year, int month,int operatorId=0)
        {
            string whereClause = string.Empty;
            if (operatorId>0)
            {
                whereClause += string.Format(" where Id={0} ",operatorId);
            }
            string sql = string.Format(@"
SELECT ROW_NUMBER() OVER(ORDER BY Id ) AS RowId,Id,Name,AllocateRatio,
(SELECT SUM(PayFee) FROM dbo.Orders WHERE Status NOT IN(0,3,4) AND OperatorID=dbo.Operators.Id  AND YEAR(OrderDate)=@Year1 AND MONTH(OrderDate)=@Month1) AS TotalScanCode,
(SELECT SUM(TheDayMoney) FROM dbo.DeviceCoinsRecords WHERE dbo.DeviceCoinsRecords.OperatorID=dbo.Operators.Id AND Year=@Year2 AND Month=@Month2) AS TotalCoinsCast
 FROM dbo.Operators {0}", whereClause);
            return await Context.Database.SqlQuery<OperatorYearOrMonthReport>(sql, 
                new SqlParameter("@Year1", year), 
                new SqlParameter("@Month1", month),
                new SqlParameter("@Year2", year), 
                new SqlParameter("@Month2", month)).ToListAsync();

        }


        public async Task<IEnumerable<OperatorYearOrMonthReport>> QueryYearReports(int year, int operatorId = 0)
        {
            string whereClause = string.Empty;
            if (operatorId>0)
            {
                whereClause += string.Format(" where Id={0} ", operatorId);
            }
            string sql = string.Format(@"
SELECT ROW_NUMBER() OVER(ORDER BY Id )AS RowId,Id,Name,AllocateRatio,
(SELECT SUM(PayFee) FROM dbo.Orders WHERE Status NOT IN(0,3,4) AND OperatorID=dbo.Operators.Id AND YEAR(OrderDate)=@Year3) AS TotalScanCode,
(SELECT SUM(TheDayMoney) FROM dbo.DeviceCoinsRecords WHERE dbo.DeviceCoinsRecords.OperatorID=dbo.Operators.Id AND Year=@Year4) AS TotalCoinsCast
 FROM dbo.Operators {0}", whereClause);
            
            return await Context.Database.SqlQuery<OperatorYearOrMonthReport>(sql, 
                new SqlParameter("@Year3", year),
                new SqlParameter("@Year4", year)).ToListAsync();

        }
    }

这里有点意思的是,如果SqlQuery的后面没有ToList()或者ToListAsync(),那么你会收获一个奇妙的礼物!你可以试试看!

4.在应用服务层调用

  public async Task<PagedResultOutput<GetOperatorReportsOutput>> GetOperatorReports(GetOperatorReportsInput input)
        {
           
            int operatorId = string.IsNullOrEmpty(input.Code)
                ? 0 : _operatorRepository.GetAll().Single(o => o.Code == input.Code).Id;
            IEnumerable<OperatorYearOrMonthReport> operatorMonthReports;
            if (input.Year.HasValue && input.Month.HasValue)
            {
                operatorMonthReports =await _operatorReportRepository.QueryMonthReports(input.Year.Value,input.Month.Value,operatorId);
            }else if (input.Year.HasValue)
            {
                operatorMonthReports =await _operatorReportRepository.QueryYearReports(input.Year.Value,operatorId);
            }
            else
            {
                if (input.IsThisYear)
                {
                    operatorMonthReports = await _operatorReportRepository.QueryYearReports(DateTime.Now.Year,operatorId);
                }
                else
                {
                    operatorMonthReports =await  _operatorReportRepository.QueryMonthReports(DateTime.Now.Year,DateTime.Now.Month,operatorId);
                }
            }
            var totalCount = operatorMonthReports.Count();
            var operatorReportOutputList = operatorMonthReports.MapTo<List<GetOperatorReportsOutput>>();
            return new PagedResultOutput<GetOperatorReportsOutput>(totalCount, operatorReportOutputList);
        }

好了,这次主要记录一下在EF中如何直接调用Sql语句的用法,以后有机会再说说关于在EF中存储过程(包括输出参数的情况)的调用。





本文转自tkbSimplest博客园博客,原文链接:http://www.cnblogs.com/farb/p/SqlQueryInEF.html,如需转载请自行联系原作者

上一篇:mysql太多连接问题及解决方案


下一篇:探讨一下如何打造稳定的报名系统之考务管理报名系统开发设计浅谈