创建DO转换到SQL方法？ [英] Creating methods that DO translate to SQL?

问题描述

在我使用的应用程序的数据库中，有一个具有日期字段的记录。在中的代码中的许多的地方，我们需要它转换成一个会计年度进行分组，选择和过滤。所以这里有一个什么样的查询可能看起来像一个例子：

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

这样，不同的客户端可以有不同的FY定义，这样，并拥有这一切的定义一样的地方。有没有办法做到这一点？编写一个正常的GetFY方法只是抛出无法转换为SQL（或任何它实际上是）例外。显然，这可以转换成SQL在一定程度上，我只是不知道是否有可能在LINQ to SQL来重复使用一个简单的方法来重新创建。

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.

谢谢在前进。

推荐答案

如果你可以把它写成一个SQL函数，你可以创建一个虚拟的C＃功能在LINQ使用代码，并告诉LINQ到它映射到SQL函数。该映射通过属性来完成

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.

您需要做的是这样的：

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

和则：

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

这篇关于创建DO转换到SQL方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！