0

I have a table, Measurements, which has a Date column.
This column is indexed (is a non unique index though)

Using EntityFramework I want to get all items from a certain month and year.

Question: What alternative is better in terms of performance?

Alternative 1:

  var results = db.Measurements.Where(m => m.Date.Month == month && m.Date.Year == year);

(The resultant SQL uses the DATEPART function)

WHERE (((DATEPART (month, [Extent1].[Date])) = @p__linq__1) OR ((DATEPART (month, [Extent1].[Date]) IS NULL) AND (@p__linq__1 IS NULL))) AND (((DATEPART (year, [Extent1].[Date])) = @p__linq__2) OR ((DATEPART (year, [Extent1].[Date]) IS NULL) AND (@p__linq__2 IS NULL)))

(...though I'm not sure why there is a "OR null" case)

Alternative 2:

  DateTime firstDateOfMonth = new DateTime(year, month, 1);
  DateTime lastDateOfMonth = new DateTime(year, month, DateTime.DaysInMonth(year, month));

  var results = db.Measurements.Where(m => m.Date >= firstDateOfMonth && m.Date <= lastDateOfMonth);

(The resultant SQL uses operators >= and <=)

  WHERE ([Extent1].[Date] >= @p__linq__1) AND ([Extent1].[Date] <= @p__linq__2)
sports
  • 7,851
  • 14
  • 72
  • 129
  • This may depend on whether the YEAR and MONTH functions are sargable (which means their involvement still allows the use of an index) but I have no idea if they are. Still, it might give you something to search for. Hopefully someone else knows something more definite. – Lasse V. Karlsen Feb 14 '15 at 19:29
  • Sargable? thats something new for me... can I specify the column to be sargable within the `OnModelCreating` of my `DbContext`? – sports Feb 14 '15 at 19:31
  • No, whether a function is sargable or not is a property of the function being used, not something you can opt into or force. – Lasse V. Karlsen Feb 14 '15 at 19:32
  • The `DATEPART` function (SQL Server) is sargable? – sports Feb 14 '15 at 19:35
  • 3
    Using functions including datepart in the field will prevent SQL Server from using indexes to search with it – James Z Feb 14 '15 at 19:39
  • @JamesZ so that is telling me I should opt for the alternative 2 – sports Feb 14 '15 at 19:49
  • 1
    Yes, definitely use the one with dates directly in the criteria – James Z Feb 14 '15 at 19:54
  • Just wanted to add that the reason for the "or null" case is that the LINQ expression is in .NET and therefore `null == null` should return `true`. But `NULL = NULL` is `false` in SQL and therefore EF has to add a special case to check for it. – ChrisV Feb 14 '15 at 21:42
  • @ChrisV but why is that case considered? in my code the month and year variables are nullable, they are `int?`, but I pass `month.Value` and `year.Value` to the query... so in the query it should never be null – sports Feb 14 '15 at 21:46

0 Answers0