DbContext timeout while running linq query

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?

18 seconds ? You probably need to do some optmization on the db side and the query. do you have proper indexes ? — Shyju
– Shyju, Commented May 4, 2016 at 17:51
I do not own the database at all. This is something I can't change... — user3587624
– user3587624, Commented May 4, 2016 at 17:54
What approach, sorry? I can see different implementations on that question... Is the one marked as right answer? — user3587624
– user3587624, Commented May 4, 2016 at 17:56

Community · Accepted Answer · 2017-05-23 11:59:40Z

2

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.

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered May 4, 2016 at 18:17

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

user3587624 Over a year ago

Thanks a lot! I am having some trouble at the moment with the lambda expression that defines (x => x.CreatedOn, d1, d2). That gives me a build time error since it can't convert from DateTime? to DateTime. If I try to cast it or put x.CreatedOn.value, then I get a runtime error that says "Conversion failed when convertind date and/or time from character string."

Ivan Stoev Over a year ago

So CreatedOn field is DateTime? ?

user3587624 Over a year ago

Yup, it seems to be of type DateTime?

user3587624 Over a year ago

Sorry for keep bothering but although that issue is resolved now, I am getting a new exception. The binary operator GreaterThanOrEqual is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.DateTime'. I believe this is happening because on my model I have defined it as Nullable? EDIT: After getting rid of the Nullable on my Model, the issue still persists. So maybe there is something else..

Ivan Stoev Over a year ago

Hehe, ok, but while waiting, try the last update - now there are two methods, one for DateTime and one for DateTime? field, so just pass your model property and the compiler will choose the right overload.

|

Collectives™ on Stack Overflow

DbContext timeout while running linq query

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related