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

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

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

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

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

下面是三张表的结构：

三张表之间的关联关系是：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

视图表达：

自然语言表达：

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

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

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

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

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中存储过程（包括输出参数的情况）的调用。

如果您认为这篇文章还不错或者有所收获，您可以通过右边的“打赏”功能 打赏我一杯咖啡【物质支持】，也可以点击右下角的【店长推荐】按钮【精神支持】，因为这两种支持都是我继续写作，分享的最大动力！

作者： tkb至简

来源： http://farb.cnblogs.com/

声明：原创博客请在转载时保留原文链接或者在文章开头加上本人博客地址，如发现错误，欢迎批评指正。凡是转载于本人的文章，不能设置打赏功能，如有特殊需求请与本人联系！

---

已将所有赞助者统一放到单独页面！签名处只保留最近10条赞助记录！查看赞助者列表

衷心感谢打赏者的厚爱与支持！也感谢点赞和评论的园友的支持！支付宝打赏后可添加朋友，作为潜在项目合作伙伴！
打赏者	打赏金额	打赏日期
支付宝：东莞-曾永权	50.00	2016-06-21
微信：东莞-曾永权	26.00	2016-06-21
微信：李俊	15.59	2016-06-21
微信：恬淡追平静	8.80	2016-06-15
微信：逗逗	4.00	2016-06-15
微信：403	10.24	2016-06-15
微信：Yu Jian	44.00	2016-06-14
微信：岁月如歌	2.00	2016-06-13
微信：eagle	50.00	2016-07-09
支付宝：绘	6.60	2016-07-11