What makes "SqlMethods" Methods Work?

Simply put, what makes a method like SqlMethods.DateDiffDay work?

The method signature looks like this:

public static int DateDiffDay(DateTime startDate, DateTime endDate);

So what's going on inside (or outside via some magic) that makes this work:

var query = from a in db.TableA
            group a by SqlMethods.DateDiffDay(a.Start, a.End) into g
            select g.Key;

...and why would hiding it within my own method make it fail (not that I'm try开发者_运维问答ing to do this for any reason, just trying to understand it better):

var query = from a in db.TableA
            group a by MyOwnDateDiffDay(DateTime startDate, DateTime endDate) into g
            select g.Key;

public static int MyOwnDateDiffDay(DateTime startDate, DateTime endDate)
{
    return SqlMethods.DateDiffDay(startDate, endDate);
}

Very simply: the parsing engine, when looking at the expression-tree, is hard-coded to spot those methods and interpret them in a specific way in TSQL. It never actually calls the method - which is why your MyOwnDateDiffDay fails - it isn't programmed to spot MyOwnDateDiffDay.

You can do this to a small degree in your own code by using the [Function(...)] code that normally encapsulates UDFs for L2S - for example, here is a cheeky way of wrapping up the NEWID inbuilt SQL-Server function:

partial class MyDataContext
{
     [Function(Name="NEWID", IsComposable=true)] 
     public Guid Random() 
     { // to prove not used by our C# code... 
         throw new NotImplementedException(); 
     }
}

Using the LINQ expression group x by y actually calls (by name) GroupBy on the source. If the source implements IEnumerable, this would be one common method that is called, and which accepts an arbitrary function from the source type to some key value:

• Enumerable.GroupBy<TSource, TKey>(IEnumerable<TSource>, Func<TSource, TKey>)

Some enumerable classes (such as the LINQ to SQL classes) do not only implement IEnumerable<T> but also IQueryable<T>. This means that the following method is also available:

• Queryable.GroupBy<TSource, TKey>(IQueryable<TSource>, Expression<Func<TSource, TKey>>)

Here, the method receives an expression tree which can be parsed. In the case of LINQ to SQL, the presence of SqlMethods.* is detected and, rather than executing the function, it is translated to the corresponding SQL Server operation and performed on the SQL Server (in your case, it's DATEDIFF).