11-6.從一個”模型定義”函數(shù)里返回一個復(fù)雜類型

問題

想要從一個”模型定義”函數(shù)返回一個復(fù)雜類型

解決方案

假設(shè)我們有一個病人(patient)和他們訪客(visit)的模型,如 Figure 11-6所示 .

Figure 11-6. A model for patient visits

我們想要創(chuàng)建一個”模型定義”函數(shù),返回一個概要信息,包括:病人名字,病人的訪客數(shù),和病人累積的賬單. 此外,我們只過濾出年齡超過40歲的病人:

1. 在模型設(shè)計視圖上,右擊, 新建 ? 復(fù)雜類型.

2.在模型瀏覽器里右擊新建的復(fù)雜類型,重命名為VisitSummary, 然后給復(fù)雜屬性添加下列屬性::

a. Name: String,不可為null

b. TotalVisits: Int32, 不可為null

c. TotalCost:Decimal, 不可為null

3. 在解決方案資源管理器中右擊.edmx 文件, 打開方式 ? xml 編輯器.

4. 在.edmx 文件的概念模型conceptual models)的<Schema> 標(biāo)簽下插入Listing 11-11所示的代碼,這樣函數(shù)主定義好了.

 Listing 11-11. The GetVisitSummary() Model-Defined Function

        <Function Name="GetVisitSummary"  ReturnType="Collection(EFRec

          <DefiningExPRession>

            select VALUE EFRecipesModel.VisitSummary(pv.Patient.Name,

            Count(pv.VisitId),Sum(pv.Cost))

            from EFRecipesEntities.PatientVisits as pv

            group by pv.Patient.Name

          </DefiningExpression>

        </Function>

5. 用如 Listing 11-12.所示的代碼來插入和查詢這個模型:

Listing 11-12. Using eSQL and LINQ with the VisitSummary() Function to Query the Model

    class Program

    {

        static void Main(string[] args)

        {

            RunExample();

        }

        static void RunExample()

        {

            using (var context = new EFRecipesEntities())

            {

                string hospital = "Oakland General";

                var p1 = new Patient { Name = "Robin Rosen", Age = 41 };

                var p2 = new Patient { Name = "Alex Jones", Age = 39 };

                var p3 = new Patient { Name = "Susan Kirby", Age = 54 };

                var v1 = new PatientVisit

                {

                    Cost = 98.38M,

                    Hospital = hospital,

                    Patient = p1

                };

                var v2 = new PatientVisit

                {

                    Cost = 1122.98M,

                    Hospital = hospital,

                    Patient = p1

                };

                var v3 = new PatientVisit

                {

                    Cost = 2292.72M,

                    Hospital = hospital,

                    Patient = p2

                };

                var v4 = new PatientVisit

                {

                    Cost = 1145.73M,

                    Hospital = hospital,

                    Patient = p3

                };

                var v5 = new PatientVisit

                {

                    Cost = 2891.07M,

                    Hospital = hospital,

                    Patient = p3

                };

                context.Patients.Add(p1);

                context.Patients.Add(p2);

                context.Patients.Add(p3);

                context.SaveChanges();

            }

            using (var context = new EFRecipesEntities())

            {

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

                var esql = @"Select value ps from EFRecipesEntities.Patients

                        as p join EFRecipesModel.GetVisitSummary()

                        as ps on p.Name = ps.Name where p.Age > 40";

                var objectContext = (context as IObjectContextAdapter).ObjectContext;

                var patients = objectContext.CreateQuery<VisitSummary>(esql);

                foreach (var patient in patients)

                {

                    Console.WriteLine("{0}, Visits: {1}, Total Bill: {2}",

                    patient.Name, patient.TotalVisits.ToString(),

                    patient.TotalCost.ToString("C"));

                }

            }

            using (var context = new EFRecipesEntities())

            {

                Console.WriteLine();

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

                //譯注:遇到了與11-5一樣的異常

                var patients = from p in context.Patients

                               join ps in context.GetVisitSummary() on p.Name equals

                               ps.Name

                               where p.Age >= 40

                               select ps;

                foreach (var patient in patients)

                {

                    Console.WriteLine("{0}, Visits: {1}, Total Bill: {2}",

                    patient.Name, patient.TotalVisits.ToString(),

                    patient.TotalCost.ToString("C"));

                }

            }

        }

    }

    partial class EFRecipesEntities

    {

        [EdmFunction("EFRecipesModel", "GetVisitSummary")]

        public IQueryable<VisitSummary> GetVisitSummary()

        {

            var objectContext = (this as IObjectContextAdapter).ObjectContext;

            return objectContext.CreateQuery<VisitSummary>(

                Expression.Call(Expression.Constant(this),

                  (MethodInfo)MethodInfo.GetCurrentMethod()).ToString());

        }

}

Listing 11-12代碼輸出結(jié)果如下:

Query using eSQL...

Robin Rosen, Visits: 2, Total Bill: $1,221.36

Susan Kirby, Visits: 2, Total Bill: $4,036.80

Query using LINQ...

Robin Rosen, Visits: 2, Total Bill: $1,221.36

Susan Kirby, Visits: 2, Total Bill: $4,036.80

它是如何工作的?

我們先在模型里添加一個復(fù)雜類型,接著創(chuàng)建如Listing 11-11的GetVisitSummary() 函數(shù),它能返回包含這個新建的復(fù)雜類型的集合.注意:復(fù)雜類型的構(gòu)造函數(shù)接受參數(shù)的順序要與我們定義它的屬性時的順序一致. 你可能需要檢查.edmx文件,確保設(shè)計器按我們添加的順序是一致.

由于我們函數(shù)返回IQueryable<VisitSummary>, 所以我們需要實現(xiàn)引導(dǎo)代碼. 同樣,因為我們需要訪問ObjectContext的QueryProvider,所以我們需要在EFRecipesEntities類中實現(xiàn)運行時方法.

如果我們把這個函數(shù)用在LINQ查詢中,你可能需要讓這個方法返回IQueryable<DbDataRecord>給匿名類型.雖然我們的這個集合不能進一步過濾,但是包含復(fù)雜類型的集合是可以被進一點過濾的.

附：創(chuàng)建示例用到的數(shù)據(jù)庫的腳本文件