Entity Framework 6 Recipes 2nd Edition（11-12）译 -> 定义内置函数

11-12. 定义内置函数

问题

想要定义一个在eSQL 和LINQ 查询里使用的内置函数.

解决方案

我们要在数据库中使用IsNull 函数,但是EF没有为eSQL 或LINQ发布这个函数. 假设我们已有一个WebProduct实体模型,如Figure 11-12所示.

 

Figure 11-12. A WebProduct entity in our model

为我们的查询发布函数:

1. 在解决方案资源管理器里右击.edmx 文件, 打开方式 ➤ XML 编辑器.

2.在.edmx 文件的存储模型(storage models)节里<Schema>标签下,插入如Listing 11-19所示 的代码. 这样我们就在存储层定义好了函数.

Listing 11-19. Defining Our Function in the Storage Layer

<Function Name="ISNULL" ReturnType="varchar" BuiltIn="true" Schema="dbo">

<Parameter Name="expr1" Type="varchar" Mode="In" />

<Parameter Name="expr2" Type="varchar" Mode="In" />

</Function>

3.插入和查询模型的代码如下列的Listing 11-19所示.

Listing 11-20. Using the ISNULL() Function in an eSQL and LINQ Query

    class Program

    {

        static void Main(string[] args)

        {

            RunExample();

        }

        static void RunExample()

        {

            using (var context = new EFRecipesEntities())

            {

                context.Database.ExecuteSqlCommand("delete from chapter11.webproduct");

                var w1 = new WebProduct

                {

                    Name = "Camping Tent",

                    Description = "Family Camping Tent, Color Green"

                };

                var w2 = new WebProduct { Name = "Chemical Light" };

                var w3 = new WebProduct

                {

                    Name = "Ground Cover",

                    Description = "Blue ground cover"

                };

                context.WebProducts.Add(w1);

                context.WebProducts.Add(w2);

                context.WebProducts.Add(w3);

                context.SaveChanges();

            }

            using (var context = new EFRecipesEntities())

            {

                Console.WriteLine("Query using eSQL...");

                var esql = @"select value

                            EFRecipesModel.Store.ISNULL(p.Description,p.Name)

                            from EFRecipesEntities.WebProducts as p";

                var objectContext = (context as IObjectContextAdapter).ObjectContext;

                var prods = objectContext.CreateQuery<string>(esql);

                foreach (var prod in prods)

                {

                    Console.WriteLine("Product Description: {0}", prod);

                }

            }

            using (var context = new EFRecipesEntities())

            {

                Console.WriteLine();

                Console.WriteLine("Query using LINQ...");

                var prods = from p in context.WebProducts

                            select BuiltinFunctions.ISNULL(p.Description, p.Name);

                foreach (var prod in prods)

                {

                    Console.WriteLine(prod);

                }

            }

        }

    }

    public class BuiltinFunctions

    {

        [EdmFunction("EFRecipesModel.Store", "ISNULL")]

        public static string ISNULL(string check_expression, string replacementvalue)

        {

            throw new NotSupportedException("Direct calls are not supported.");

        }

    }

 

上述Listing 11-20 代码输出结果如下:

Query using eSQL...

Product Description: Family Camping Tent, Color Green

Product Description: Chemical Light

Product Description: Blue ground cover

 

Query using LINQ...

Family Camping Tent, Color Green

Chemical Light

Blue ground cover

它是如何工作的?

如Listing 11-18所示 的 ISNULL() 函数的定义,函数名必须与数据库里的函数名一致(大小写不必一致), 这次不像本章前面小节的函数在概念层定义, 是在数据存储层. 这个函数原本在数据库里就是可用的,我们只是简单的在存储层定义一下.当在eSQL 语句中使用该函数时,必须使用完整的命名空间.( EFRecipesModel.Store.ISNULL()).当在LINQ查询中使用该函数时,需创建引导方法,由于不返回IQueryable<T>类型,所以不必实现方法体.

 

附：创建示例用到的数据库的脚本文件