ef使用expression 可以使嵌套的两个查询变为一个查询
后台代码
using (var db = new BaikeEntities())
{
var param = Expression.Parameter(typeof(Article));
var expr = Expression.GreaterThan(Expression.Property(param, "CreateDate"), Expression.Constant(minDate));
expr = Expression.And(expr, Expression.GreaterThan(Expression.Property(param, "RowStatus"), Expression.Constant(0)));
expr = Expression.And(expr, Expression.Equal(Expression.Property(param, "Limit"), Expression.Constant(0)));
var lambda = Expression.Lambda
>(expr, new ParameterExpression[] { param });
var q = db.Articles
.Where(lambda)
.OrderByDescending(p => p.CreateDate)
.Take(count);
/
ar q = db.Articles
// .Where(a => a.RowStatus == 0 & a.Limit == 0& a.CreateDate >= minDate)
// .OrderByDescending(p => p.CreateDate)
// .Take(count);
//articles = q.ToList();
}
优化前:
SELECT
`Project1`.`ArticleId`,
`Project1`.`KmArticleId`,
`Project1`.`KmCategoryId`,
`Project1`.`KmCategory`,
`Project1`.`KmParentCategoryId`,
`Project1`.`KmParentCategory`,
`Project1`.`KmGroupId`,
`Project1`.`KmGroup`,
`Project1`.`Title`,
`Project1`.`Intro`,
`Project1`.`Content`,
`Project1`.`Url`,
`Project1`.`ReadCount`,
`Project1`.`ReplyCount`,
`Project1`.`BookmarkCount`,
`Project1`.`DiggCount`,
`Project1`.`Authorship`,
`Project1`.`Staff`,
`Project1`.`DepartmentId`,
`Project1`.`CreateDate`,
`Project1`.`Selected`,
`Project1`.`IsCreateFromBaike`,
`Project1`.`BaikeProjectId`,
`Project1`.`RowStatus`,
`Project1`.`IsKMAllow`,
`Project1`.`Limit`,
`Project1`.`Attachments`,
`Project1`.`ComeFrom`,
`Project1`.`Type`,
`Project1`.`Hot`,
`Project1`.`Rank`,
`Project1`.`LastOperateTime`,
`Project1`.`OriginalUrl`,
`Project1`.`VideoAttas`
FROM (SELECT
`Extent1`.`ArticleId`,
`Extent1`.`KmArticleId`,
`Extent1`.`KmCategoryId`,
`Extent1`.`KmCategory`,
`Extent1`.`KmParentCategoryId`,
`Extent1`.`KmParentCategory`,
`Extent1`.`KmGroupId`,
`Extent1`.`KmGroup`,
`Extent1`.`Title`,
`Extent1`.`Intro`,
`Extent1`.`Content`,
`Extent1`.`Url`,
`Extent1`.`ReadCount`,
`Extent1`.`ReplyCount`,
`Extent1`.`BookmarkCount`,
`Extent1`.`DiggCount`,
`Extent1`.`Authorship`,
`Extent1`.`Staff`,
`Extent1`.`DepartmentId`,
`Extent1`.`CreateDate`,
`Extent1`.`Selected`,
`Extent1`.`IsCreateFromBaike`,
`Extent1`.`BaikeProjectId`,
`Extent1`.`RowStatus`,
`Extent1`.`IsKMAllow`,
`Extent1`.`Limit`,
`Extent1`.`Attachments`,
`Extent1`.`ComeFrom`,
`Extent1`.`Type`,
`Extent1`.`Hot`,
`Extent1`.`Rank`,
`Extent1`.`LastOperateTime`,
`Extent1`.`OriginalUrl`,
`Extent1`.`VideoAttas`
FROM `Article` AS `Extent1`
WHERE ((0 = `Extent1`.`RowStatus`) AND (0 = `Extent1`.`Limit`)) AND ((`Extent1`.`CreateDate`) >= @p__linq__0)) AS `Project1`
ORDER BY
`Project1`.`CreateDate` DESC LIMIT 5
优化后:
SELECT
`Extent1`.`ArticleId`,
`Extent1`.`KmArticleId`,
`Extent1`.`KmCategoryId`,
`Extent1`.`KmCategory`,
`Extent1`.`KmParentCategoryId`,
`Extent1`.`KmParentCategory`,
`Extent1`.`KmGroupId`,
`Extent1`.`KmGroup`,
`Extent1`.`Title`,
`Extent1`.`Intro`,
`Extent1`.`Content`,
`Extent1`.`Url`,
`Extent1`.`ReadCount`,
`Extent1`.`ReplyCount`,
`Extent1`.`BookmarkCount`,
`Extent1`.`DiggCount`,
`Extent1`.`Authorship`,
`Extent1`.`Staff`,
`Extent1`.`DepartmentId`,
`Extent1`.`CreateDate`,
`Extent1`.`Selected`,
`Extent1`.`IsCreateFromBaike`,
`Extent1`.`BaikeProjectId`,
`Extent1`.`RowStatus`,
`Extent1`.`IsKMAllow`,
`Extent1`.`Limit`,
`Extent1`.`Attachments`,
`Extent1`.`ComeFrom`,
`Extent1`.`Type`,
`Extent1`.`Hot`,
`Extent1`.`Rank`,
`Extent1`.`LastOperateTime`,
`Extent1`.`OriginalUrl`,
`Extent1`.`VideoAttas`
FROM `Article` AS `Extent1`
WHERE ((`Extent1`.`CreateDate` > @gp1) AND (0 = `Extent1`.`RowStatus`)) AND (0 = `Extent1`.`Limit`)
ORDER BY
`Extent1`.`CreateDate` DESC LIMIT 5