Linq query in controller takes too long

Question

I am trying to learn a bit about latest ASP.NET MVC 6 and I have a super basic controller which does a linq query that is taking several minutes to complete...

The method I have in the controller is something like this:

public IActionResult GetSampleData(string date)
{
    string[] date = date.Split(new char[] { '/' }, StringSplitOptions.RemoveEmptyEntries);
    int month = Convert.ToInt32(date[0]);
    int day = Convert.ToInt32(date[1]);
    int year = Convert.ToInt32(date[2]);

    var results =
        (from c in db.Book
         where c.Author.Equals("abc") 
         && (c.Created.CompareTo(new DateTime(year, month, day, 0, 0, 0)) >= 0) 
         && (c.Created.CompareTo(new DateTime(year, month, day, 23, 59, 59)) < 0)
         select new ObjectExample
         {
             Property1 = c.Field1,
             Property2 = c.Field2,
             Property3 = c.Field3,
             Property4 = c.Field4,
             Property5 = c.Field5,
         });

    return View(results.ToList());
}

Well... that method is either extremely slow (takes more than 5 minutes to complete) or it ends up on a Gateway issue like this:

HTTP Error 502.3 - Bad Gateway

The specified CGI application encountered an error and the server terminated the process

Detailed Error Information:

Module httpPlatformHandler

Notification ExecuteRequestHandler

Handler httpPlatformHandler

Error Code 0x80072ee2

If I run the exact same query but on the SQL Server, it doesn't even take a second to complete... the results are just 7 entries.

What am I missing here? This code is the exact same code that I have on another ASP.NET web application (the other one is using Silverlight) and it works super fast.

Is there anything that I am missing I should take a look at? Also, any recommendations for debugging this? (although it is pretty straight forward...)

Finally, this is my appsettings.json that contains the connection string

    {
      "Data": {
        "DefaultConnection": {
          "ConnectionString": "Data Source=DataBaseServerSample01;Initial Catalog=SampleDB;Integrated Security=True;App=EntityFramework"
        }

},
      "Logging": {
        "IncludeScopes": false,
        "LogLevel": {
          "Default": "Verbose",
          "System": "Information",
          "Microsoft": "Information"
        }
      }
    }

Note. I am using ASP.NET MVC6 RC (Update 1) and I created the model and the controller using the scaffolding through the command line.

Thanks!

The idea of the CompareTo() is to get the matches where the day is within that date range. The database also contains hours, minutes, seconds and milliseconds for the date so I can't just do a c.Created.Equals(nee DateTime...) but I need to make sure I am within the range for that particular day. I hope it makes sense! :) — user3587624, Dec 08 '15 at 05:21
What if you move the date creation outside the query: `var startDate = new DateTime(year, month, day, 0, 0, 0);` then use it inside: `&& (c.Created.CompareTo(startDate) >= 0) ` — Mark Schultheiss, Dec 08 '15 at 05:35

score 1 · Answer 1 · edited May 23 '17 at 10:27

1

Well i suppose you're looking for this solution in LinQToSql:

 var date = new DateTime(year, month, day);
 var results =
        (from c in db.Book
         where c.Author.Equals("abc") 
         && c.Created.Year == date.Year
         && c.Created.Month == date.Month
         && c.Created.Day == date.Day)
         select new ObjectExample
         {
             Property1 = c.Field1,
             Property2 = c.Field2,
             Property3 = c.Field3,
             Property4 = c.Field4,
             Property5 = c.Field5,
         });

As you can see i created date object once like @Mark Schultheiss advices you. And i checking only date part of your DateTime field.

Honestly there is another posibility for solve your problem. You should convert your SQL dates to String and then you can compare. As long as LinqToSQL doesn't support fucntions like .ToString("yyyy-MM-dd") one of the ways is to use DbFunctions check this answer if you need high perfomance.

edited May 23 '17 at 10:27

Community

1
1

answered Dec 08 '15 at 07:14

teo van kot

12,350
10
38
70

I've tried this option and also @Mark's option and I get the exact same issue which is this: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." However, if I remove the Date statement out of the linq query, it works perfectly... Why the date thing is messing up everything? – user3587624 Dec 08 '15 at 19:46
@user3587624 can you profile your code? To measure where is everything stops? – teo van kot Dec 08 '15 at 19:48
You can measure how much memury each method eat and measure estimated time of work in vs you have profiler project type – teo van kot Dec 08 '15 at 19:56
1

I think there is a problem with the framework itself... I created a new question as the query works perfectly on EF6 http://stackoverflow.com/questions/34186304/linq-on-ef7-doesnt-work-with-joins-and-dates – user3587624 Dec 09 '15 at 18:32

score 1 · Answer 2 · answered Dec 08 '15 at 13:01

1

I suggest you to use Glimpse its an open source diagnostics platform, with that you can see how many querys Entity Framework are creating, and how. Besides that, you can use AsNoTracking() for your querys that are read-only, it's more performatic.

answered Dec 08 '15 at 13:01

Pedro Benevides

1,970
18
19

+1 Glimpse is a great tool for helping a dev discover what is going on under the hoof of their ASP.NET MVC app. – Jason Evans Dec 08 '15 at 13:04

Linq query in controller takes too long

2 Answers2