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, May 04 '16 at 17:51
I do not own the database at all. This is something I can't change... — user3587624, May 04 '16 at 17:54
Try this approach http://stackoverflow.com/questions/10549640/how-to-set-commandtimeout-for-dbcontext — Eric J., May 04 '16 at 17:54
What approach, sorry? I can see different implementations on that question... Is the one marked as right answer? — user3587624, May 04 '16 at 17:56

score 2 · Accepted Answer · edited May 23 '17 at 11:59

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 '17 at 11:59

Community

1
1

answered May 04 '16 at 18:17

Ivan Stoev

195,425
15
312
343

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." – user3587624 May 04 '16 at 18:43
So `CreatedOn` field is `DateTime?` ? – Ivan Stoev May 04 '16 at 18:53
Yup, it seems to be of type DateTime? – user3587624 May 04 '16 at 18:58
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.. – user3587624 May 04 '16 at 19:59
Are you using the exact updated method (with `typeof(DateTime?)` at the end of the two `Expression.Constant` methods)? I've tried it before posting in my EF and it works w/o problem. – Ivan Stoev May 04 '16 at 20:03
Oops, I'm using EF6. Will try EF7 when have time. – Ivan Stoev May 04 '16 at 20:04
Btw, don't change your model, the new function expects `CreatedOn` to be `DateTime?`. I've tested it with my table having nullable DateTime field. – Ivan Stoev May 04 '16 at 20:09
1

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. – Ivan Stoev May 04 '16 at 20:16
I found the issue was related to the db and the dates were a bit funky... your solution works perfectly though! – user3587624 May 05 '16 at 15:45

DbContext timeout while running linq query

1 Answers1