I have a following query, which is causing me performance issues in SQL server.
ctx.Articles
.Where(m => m.Active)
.Where(m => m.PublishDate <= DateTime.Now)
.Where(m => m.Sponsored == false)
.WhereIf(request.ExcludeFirstNews, m => m.PositionForCategory != 1)
.WhereIf(category != null, m => m.RootCategoryId == category.RootCategoryId)
.WhereIf(request.TimeRange != 0 && request.TimeRange == TimeRange.today, m => m.PublishDate.Value >= today)
.WhereIf(request.TimeRange != 0 && request.TimeRange == TimeRange.yesterday, m => m.PublishDate.Value >= yesterday)
.WhereIf(request.TimeRange != 0 && request.TimeRange == TimeRange.week, m => m.PublishDate.Value >= week &&
m.PublishDate.Value <= DateTime.Now)
.WhereIf(request.TimeRange != 0 && request.TimeRange == TimeRange.month, m => m.PublishDate.Value >= month &&
m.PublishDate.Value <= DateTime.Now)
.OrderByDescending(m => m.ArticleViewCountSum.ViewCountSum)
Is there a way to make entity framework to use inner join instead of left outer join for order by syntax part of the lambda expression? I've checked query in profiler, and it generaters left outer join, which is not necessary in this case, and runs much faster if i change query manually in SQL.
EDIT: Per questions, in the comments, this is code for WhereIf
public static IQueryable<T> WhereIf<T>(
this IQueryable<T> source,
bool condition,
Expression<Func<T, bool>> predicate)
{
if (condition)
{
return source.Where(predicate);
}
return source;
}