LINQ - 嵌套查询
问题描述:
我有一个SQL语句,我试图转换为LINQ查询。我需要这样做,因为我不能编辑我的数据库:(无论如何,我有一个SQL语句如下所示:LINQ - 嵌套查询
SELECT
CustomerID,
FirstName,
LastName,
Gender,
BirthMonth,
(SELECT COUNT(ID) FROM PurchaseOrder WHERE [CustomerID]=CustomerID) as TotalPurchases
FROM
MyEntities
我知道该怎么做的一切在LINQ除外的嵌套查询部分目前,我有以下几点:?
var results = from x in context.MyEntities
select new Customer()
{
CustomerID = x.CustomerID,
FirstName = x.FirstName,
LastName = x.LastName,
Gender = x.Gender,
BirthMonth = x.BirthMonth,
TotalPurchases = ?
};
如何执行在LINQ嵌套查询来获取TotalPurchases值
谢谢你这么多
答
!你可以只嵌套的LINQ to Entities查询,以及:
var results = from x in context.MyEntities
select new Customer()
{
CustomerID = x.CustomerID,
FirstName = x.FirstName,
LastName = x.LastName,
Gender = x.Gender,
BirthMonth = x.BirthMonth,
TotalPurchases = context.PurchaseOrders
.Where(po=>po.CustomerId == x.CustomerId)
.Count()
};
答
只是做一个报数“个PurchaseOrder”,我认为是对客户的实体的导航属性的。
TotalPurchases = x.PurchaseOrders.Count()
答
通过BrokenGlass答案使用LINQ到实体时,并导致错误,指出“无法创建类型‘myEntity所’的恒定值,并没有为我工作。只有基本类型或枚举类型在这个支持语境“。
为了克服这一点,我使用了以下内容:
var results = from x in context.MyEntities
join po in context.PurchaseOrders on x.CustomerID equals po.CustomerID into purchaseOrders
select new Customer()
{
CustomerID = x.CustomerID,
FirstName = x.FirstName,
LastName = x.LastName,
Gender = x.Gender,
BirthMonth = x.BirthMonth,
TotalPurchases = purchaseOrders.Count()
};