Creating methods that DO translate to SQL?

Question

In the database of an application I'm using, there is a record that has a "Date" field. In many places in the code, we need to translate that into a Fiscal Year for grouping, selecting, and filtering. So here are a few examples of what a query might look like:

var query = from r in DB.records
            let fy = r.Date.Month >= 10 ? r.Year + 1 : r.Year
            where fy = 2010
            select r;

Now I know I could write an Expression method to use the same filter, but what if I want to use it for selecting and grouping? I don't want to have to write the second line over and over in all of the queries. It would be nice to just be able to do:

var query = from r in DB.records
            let fy = r.Date.GetFY()
            where fy = 2010
            group r by fy into g
            select g.Count();

That way, different clients could have different FY definitions and such, and have it all be defined in the same place. Is there any way to do this? Writing a normal GetFY method just throws the "Can't translate to SQL" (or whatever it actually is) exception. Obviously, this can be translated to SQL on some level, I just don't know if it's possible to recreate in an easy way for LINQ to SQL to reuse.

Thanks in advance.

Answer 1

If you can write it as an SQL function, you can create a dummy C# function to use in the LINQ code, and tell LINQ to map it to your SQL function. The mapping is done through an attribute.

You would need to do something like:

[Function(Name = "udf_GetFY", IsComposable = true)]
public int  getFY(DateTime t)
{
    return 5; // value doesn't matter
}

and then:

 var query = from r in DB.records 
        let fy = GetFY(r.Date) 
        where fy = 2010 
        group r by fy into g 
        select g.Count();