Creating methods that translate SQL to SQL?

Question

There is an entry in the database of the application that I am using with the Date field. In many places in the code, we need to translate this into a fiscal year to group, select and filter. So, here are some examples of what the 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 that I can write an expression method to use the same filter, but what if I want to use it to select and group? I do not want to write the second line again and again in all queries. It would be nice to just be able to:

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

Thus, different clients may have different definitions of FY, etc., and they will all be defined in the same place. Is there any way to do this? Writing a normal GetFY method simply raises a “Can not translate to SQL” exception (or whatever it is). Obviously this can be translated into SQL at some level, I just don’t know if LINQ to SQL can be easily recreated to reuse.

Thanks in advance.

Answer 1

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

You will 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();