DbContext timeout while running linq query

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

Question

I have the following async method that queries for some data on a database.

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();
            }
        }

This code, fails with a timeout exception in cases where the date range is too wide. I tried to increase the timeout by doing this:

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

            base.Database.SetCommandTimeout(300);

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

Is there anything that I am doing wrong here? If I run the SQL query on the database itself, it takes close to 18 seconds for the same date range...

I am using ASP.NET 5 MVC6 and EF7.

How can I get rid of the timeout exception for these cases where the time range is too wide and takes more time for the db to return the data?

1
2
5/4/2016 5:50:49 PM

Accepted Answer

Most probably the issue is related to so called Parameter Sniffing Problem, similar to EntityFramework LINQ query count fails but query returns result. How to optimize LINQ query?.

You can take a modified version of the helper class from the link:

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);
    }
}

then change your query to:

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
           });

and see if that helps.

2
5/23/2017 11:59:40 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow