運行linq查詢時DbContext超時

asp.net5 c# entity-framework entity-framework-core linq

我有以下異步方法來查詢數據庫上的某些數據。

private async Task<List<MyObject>> GetTotalConcert(DateTime d1, DateTime d2, string[] name)
        {
            using (RegistrationDbContext context = new RegistrationDbContext())
            {
                IQueryable<MyObject> results;

                results = (from t1 in context.Table1
                           join t2 in context.Table2 on t1.Id equals t2.Id
                           where (t2.CreatedOn >= d1 && t2.CreatedOn < d2)
                           && (name.Contains(t2.Name))
                           && t1.EventName.Equals("Concert")
                           select new MyObject
                           {
                               Id = t2.Id,
                               EventName = t1.EventName,
                               Status = t2.Status,
                               ProjectName = t2.Name
                           });

                return await results.Distinct().ToAsyncEnumerable().ToList();
            }
        }

在日期範圍太寬的情況下,此代碼會因超時異常而失敗。我嘗試通過這樣做來增加超時:

public class RegistrationDbContext : DbContext
    {
        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            base.Database.SetCommandTimeout(300);

        }
        // Some more code here....
    }

我在這裡做錯了什麼嗎?如果我在數據庫本身上運行SQL查詢,則相同日期範圍需要接近18秒...

我使用的是ASP.NET 5 MVC6和EF7。

如何在時間範圍太寬並且db需要更多時間返回數據的情況下,如何擺脫超時異常?

一般承認的答案

最有可能的問題與所謂的參數嗅探問題有關 ,類似於EntityFramework LINQ查詢計數失敗但查詢返回結果。如何優化LINQ查詢?

您可以從鏈接中獲取輔助類的修改版本:

using System;
using System.Linq;
using System.Linq.Expressions;

public static class QueryableUtils
{
    public static IQueryable<T> WhereIn<T>(this IQueryable<T> source, Expression<Func<T, DateTime>> dateSelector, DateTime startDate, DateTime endDate)
    {
        var startCond = Expression.GreaterThanOrEqual(dateSelector.Body, Expression.Constant(startDate));
        var endCond = Expression.LessThan(dateSelector.Body, Expression.Constant(endDate));
        var predicate = Expression.Lambda<Func<T, bool>>(Expression.AndAlso(startCond, endCond), dateSelector.Parameters[0]);
        return source.Where(predicate);
    }

    public static IQueryable<T> WhereIn<T>(this IQueryable<T> source, Expression<Func<T, DateTime?>> dateSelector, DateTime startDate, DateTime endDate)
    {
        var startCond = Expression.GreaterThanOrEqual(dateSelector.Body, Expression.Constant(startDate, typeof(DateTime?)));
        var endCond = Expression.LessThan(dateSelector.Body, Expression.Constant(endDate, typeof(DateTime?)));
        var predicate = Expression.Lambda<Func<T, bool>>(Expression.AndAlso(startCond, endCond), dateSelector.Parameters[0]);
        return source.Where(predicate);
    }
}

然後將您的查詢更改為:

results = (from t1 in context.Table1
           join t2 in context.Table2.WhereIn(x => x.CreatedOn, d1, d2) on t1.Id equals t2.Id
           where (name.Contains(t2.Name))
           && t1.EventName.Equals("Concert")
           select new MyObject
           {
               Id = t2.Id,
               EventName = t1.EventName,
               Status = t2.Status,
               ProjectName = t2.Name
           });

看看是否有幫助。



Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow