实体框架LINQ查询加上SQL函数嵌套查询
问题描述:
我有以下LINQ查询实体框架LINQ查询加上SQL函数嵌套查询
public IEnumerable<DealershipWithDealersViewModel> Get(float latitude, float longitude)
{
return from dealer in Db.Dealerships
join i in Db.NearestDealers(latitude, longitude)
on dealer.DealerID equals i.DealerID
select new DealershipWithDealersViewModel
{
DealerID = dealer.DealerID,
Dealer = dealer.Dealer,
DoSales = dealer.DoSales,
DoService = dealer.DoService,
AddressProvinceID = dealer.AddressProvinceID,
AddressLocationID = dealer.AddressLocationID,
Address1 = dealer.Address1,
Address2 = dealer.Address2,
Tel = dealer.Tel,
Fax = dealer.Fax,
MapLat = dealer.MapLat,
MapLong = dealer.MapLong,
Location = dealer.Location.LocationName,
DealerUsers = dealer.DealerUsers
.Select(y => new DealerUserViewModel
{
DealerUserID = y.DealerUserID,
FirstName = y.Firstname,
Surname = y.Surname,
LandLine = y.LandLine,
Email = y.Email,
Position = y.DealerType.DealerPosition
})
};
}
我不断收到以下错误嵌套查询不具备相应的按键。我在网上找不到任何关于它的事情。如果我在没有DealerUsers的情况下加载上面的代码,它会按预期工作,但我需要嵌套数据。谢谢!下面的工作方式。
public IEnumerable<DealershipWithDealersViewModel> Get(float latitude, float longitude)
{
return from dealer in Db.Dealerships
join i in Db.NearestDealers(latitude, longitude)
on dealer.DealerID equals i.DealerID
select new DealershipWithDealersViewModel
{
DealerID = dealer.DealerID,
Dealer = dealer.Dealer,
DoSales = dealer.DoSales,
DoService = dealer.DoService,
AddressProvinceID = dealer.AddressProvinceID,
AddressLocationID = dealer.AddressLocationID,
Address1 = dealer.Address1,
Address2 = dealer.Address2,
Tel = dealer.Tel,
Fax = dealer.Fax,
MapLat = dealer.MapLat,
MapLong = dealer.MapLong,
Location = dealer.Location.LocationName
};
}
更新
这也适用。
return Db.Dealerships.Select(x => new DealershipWithDealersViewModel
{
DealerID = x.DealerID,
Dealer = x.Dealer,
DoSales = x.DoSales,
DoService = x.DoService,
AddressProvinceID = x.AddressProvinceID,
AddressLocationID = x.AddressLocationID,
Address1 = x.Address1,
Address2 = x.Address2,
Tel = x.Tel,
Fax = x.Fax,
MapLat = x.MapLat,
MapLong = x.MapLong,
Location = x.Location.Location1,
DealerUsers = x.DealerUsers.Select(y => new DealerUserViewModel
{
DealerUserID = y.DealerUserID,
FirstName = y.Firstname,
Surname = y.Surname,
LandLine = y.LandLine,
Email = y.Email,
Position = y.DealerType.DealerType1
})
});
答
这是一个可复合的问题。 EF总是会尝试将您的查询转换为SQL。如果这个成功,那很好。如果没有,它不会尝试并使其工作,例如通过切换到linq下的引擎盖下的对象(如linq to sql可能)。您试图将存储过程结果加入到SQL查询中。这甚至不能在平面SQL中完成,因为sproc结果不可组合,更不用说EF。
您只能使用Db.Dealerships.AsEnumerable()
和Db.NearestDealers(latitude, longitude)
将结果加入内存中。
因此,如果您可以将过滤器参数DealerID
添加到过程的签名将会非常有用。
+0
感谢您的帮助。刚刚写完一个存储过程并完全绕过了linq到sql。 – TYRONEMICHAEL 2013-02-21 07:01:27
是的,我是。实体框架5. – TYRONEMICHAEL 2013-02-17 18:57:31
你可以尝试'DealerID = dealer.DealerId'而不是'DealerID = i.DealerId'吗? – Pawel 2013-02-17 19:05:40
我做了并没有区别。有点难倒了这一个。将更新答案,以免人们困惑。 – TYRONEMICHAEL 2013-02-17 19:14:50