11-6.從一個”模型定義”函數里返回一個複雜類型問題想要從一個”模型定義”函數返回一個複雜類型解決方案假設我們有一個病人(patient)和他們訪客(visit)的模型,如 Figure 11-6所示 .Figure 11-6. A model for patient visits我們想要創建一個...
11-6.從一個”模型定義”函數里返回一個複雜類型
問題
想要從一個”模型定義”函數返回一個複雜類型
解決方案
假設我們有一個病人(patient)和他們訪客(visit)的模型,如 Figure 11-6所示 .
Figure 11-6. A model for patient visits
我們想要創建一個”模型定義”函數,返回一個概要信息,包括:病人名字,病人的訪客數,和病人累積的賬單. 此外,我們只過濾出年齡超過40歲的病人:
1. 在模型設計視圖上,右擊, 新建 ➤ 複雜類型.
2.在模型瀏覽器里右擊新建的複雜類型,重命名為VisitSummary, 然後給複雜屬性添加下列屬性::
a. Name: String,不可為null
b. TotalVisits: Int32, 不可為null
c. TotalCost:Decimal, 不可為null
3. 在解決方案資源管理器中右擊.edmx 文件, 打開方式 ➤ XML 編輯器.
4. 在.edmx 文件的概念模型conceptual models)的<Schema> 標簽下插入Listing 11-11所示的代碼,這樣函數主定義好了.
Listing 11-11. The GetVisitSummary() Model-Defined Function
<Function Name="GetVisitSummary" ReturnType="Collection(EFRecipesModel.VisitSummary)">
<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代碼輸出結果如下:
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
它是如何工作的?
我們先在模型里添加一個複雜類型,接著創建如Listing 11-11的GetVisitSummary() 函數,它能返回包含這個新建的複雜類型的集合.註意:複雜類型的構造函數接受參數的順序要與我們定義它的屬性時的順序一致. 你可能需要檢查.edmx文件,確保設計器按我們添加的順序是一致.
由於我們函數返回IQueryable<VisitSummary>, 所以我們需要實現引導代碼. 同樣,因為我們需要訪問ObjectContext的QueryProvider,所以我們需要在EFRecipesEntities類中實現運行時方法.
如果我們把這個函數用在LINQ查詢中,你可能需要讓這個方法返回IQueryable<DbDataRecord>給匿名類型.雖然我們的這個集合不能進一步過濾,但是包含複雜類型的集合是可以被進一點過濾的.
