一般情况下,使用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中存储过程(包括输出参数的情况)的调用。
本文转自tkbSimplest博客园博客,原文链接:http://www.cnblogs.com/farb/p/SqlQueryInEF.html,如需转载请自行联系原作者