Thursday, June 23, 2011

DateDiff in linq EF 4.0

We can use this System.Data.Objects.SqlClient.SqlFunctions.DateDiff (.Net 4) function.
This is a sample query:
using System.Data.Objects.SqlClient;

var query = (from c in context.MyContents
             join v in context.MyContentVersions
             on new { contentId = c.ContentId, versiongmt = (DateTime)c.PublishedVersionGmt }
            equals new { contentId = v.ContentId, versiongmt = v.VersionGmt }
            where
            c.PublishedVersionGmt != null
            && c.CatalogId == ConfigHelper.CatalogId
            && SqlFunctions.DateDiff("day",v.VersionGmt, DateTime.Now) < ConfigHelper.Days
            select new
            {
                ContentId = v.ContentId,
                VesrionGmt = v.VersionGmt,
                ContentFormat = c.ContentFormatId,
                CatalogId = c.CatalogId
            }).OrderByDescending(a => a.ContentId).Take(20);

However, we should pay attention when we use DateDiff, this query might not be very efficient because it is not sargable because there is a function on the column, whenever you use a function on the column you will not get an index seek but an index scan. so makes the query very slow. A query is called to be sargable if the database engine can take advantage of an index to speed up the execution of the query using index seek instead of index scan.

0 comments:

Post a Comment