以下内容完美无误:
但以下代码会导致程序崩溃:
请注意,我只是将查询本身替换到变量的位置,而不是单独创建propertyIdQuery。
异常信息为:
无法将类型“System.Linq.IQueryable
有人能解释一下EF(4)在底层做了什么来使得第一个查询有效,即使它们表面上是等效的吗?
我知道IQueryable<T>和表达式树在底层执行了很多工作,但是为什么将中间步骤保存到本地变量会影响结果呢?
编辑: 按要求,这里是被调用的完整方法以及该方法调用的方法:
IQueryable<Property> PropertyQuery = PropertyDAO.SearchWithAdditionalParameters(/* elided */);
IQueryable<long> propertyIdQuery = PropertyQuery.Select(p => p.PropertyId);
var relevantFMVs = PropertyDAO.db.FMVHistories.Where(f => propertyIdQuery.Contains(f.PropertyId)).ToList();
但以下代码会导致程序崩溃:
IQueryable<Property> PropertyQuery = PropertyDAO.SearchWithAdditionalParameters(/* elided */);
var relevantFMVs = PropertyDAO.db.FMVHistories.Where(f => PropertyQuery.Select(p => p.PropertyId).Contains(f.PropertyId)).ToList();
请注意,我只是将查询本身替换到变量的位置,而不是单独创建propertyIdQuery。
异常信息为:
无法将类型“System.Linq.IQueryable
1”强制转换为类型“System.Linq.IQueryable
1”。LINQ to Entities仅支持将Entity Data Model原始类型强制转换。有人能解释一下EF(4)在底层做了什么来使得第一个查询有效,即使它们表面上是等效的吗?
我知道IQueryable<T>和表达式树在底层执行了很多工作,但是为什么将中间步骤保存到本地变量会影响结果呢?
编辑: 按要求,这里是被调用的完整方法以及该方法调用的方法:
public IQueryable<Property> BasicSearchFromConstraints(PropertyInvoiceConstraints constraints) {
return ExecuteSearchFromConstraints((dynamic)constraints.PropertyInst, constraints.CompanyNumber, constraints.TaxSubType, constraints.PhaseID, constraints.State, constraints.County, constraints.City, constraints.Jurisdiction);
}
private IQueryable<T> ExecuteSearchFromConstraints<T>(T property, int CompanyNumber, byte SubType, byte PhaseID, string State, string County, string City, string Jurisdiction) where T : Property {
IQueryable<T> result = base.db.Properties.OfType<T>();
if (SubType > 0)
result = result.Where(p => p.TaxSubTypeId == SubType);
if (CompanyNumber > 0)
result = result.Where(p => p.CompanyNum == CompanyNumber);
if (!String.IsNullOrEmpty(State))
result = result.Where(p => p.State == State);
if (!String.IsNullOrEmpty(County))
result = result.Where(p => p.County == County);
if (!String.IsNullOrEmpty(City))
result = result.Where(p => p.City == City);
if (!String.IsNullOrEmpty(Jurisdiction))
result = result.Where(p => p.Jurisdiction == Jurisdiction);
if (PhaseID > 0)
result = result.Where(p => p.PhaseId == PhaseID);
return result;
}
public virtual IQueryable<Property> SearchWithAdditionalParameters(DataLayer.DAO.PropertyInvoiceConstraints constraints, string propertyNumber = "", string altDesc = "", string countyAcctNumber = "", string City = "", string Jurisdiction = "", string secondaryStateID = "", string LegalDesc = "", string status = "", int? TaxYear = null) {
IQueryable<Property> result = BasicSearchFromConstraints(constraints);
if (!String.IsNullOrEmpty(status))
result = result.Where(p => p.Status == status);
if (!String.IsNullOrEmpty(propertyNumber))
result = result.Where(p => p.PropertyNum.Contains(propertyNumber));
if (!String.IsNullOrEmpty(altDesc))
result = result.Where(p => p.AltDescription.Contains(altDesc));
if (!String.IsNullOrEmpty(countyAcctNumber))
result = result.Where(p => p.CountyAccountNum.Contains(countyAcctNumber));
if (!String.IsNullOrEmpty(City))
result = result.Where(p => p.City.Contains(City));
if (!String.IsNullOrEmpty(Jurisdiction))
result = result.Where(p => p.Jurisdiction.Contains(Jurisdiction));
if (TaxYear.HasValue)
result = result.Where(p => p.FMVHistories.Any(f => f.TaxYear == TaxYear));
if (constraints.FMVPhaseID > 0)
result = result.Where(p => p.FMVHistories.Any(f => f.PhaseId == constraints.FMVPhaseID));
if (!String.IsNullOrEmpty(secondaryStateID))
if (constraints.PropertyInst is WellDetail)
result = result.OfType<WellDetail>().Where(w => w.SecondaryStateId == secondaryStateID);
else
throw new ApplicationException("Invalid use -> Secondary State ID can only be set when searching for Well property types");
if (!String.IsNullOrEmpty(LegalDesc))
if (constraints.PropertyInst is RealEstateDetail)
result = result.OfType<RealEstateDetail>().Where(r => r.LegalDescr.Contains(LegalDesc));
else if (constraints.PropertyInst is RealEstateServicingDetail)
result = result.OfType<RealEstateServicingDetail>().Where(r => r.LegalDescr.Contains(LegalDesc));
else throw new ApplicationException("Invalid use -> Legal Description can only be set when searching for either real estate or real estate servicing property types");
return result;
}
编辑
我非常希望Akash的答案是正确的,但如果是这样,我会预计这里的中间查询会崩溃,但实际上这三个查询都可以正常工作。
我开始怀疑我在类型 Property
(来自原始示例)上拥有的继承结构可能与此有关。
DummyBookModelEntities db = new DummyBookModelEntities();
IQueryable<int> BookIds = db.Books.Where(b => b.id < 4).Select(b => b.id);
IQueryable<Book> BooksFromIdQuery = db.Books.Where(b => b.id < 4);
try {
var l1 = db.Books.Where(b => BookIds.Contains(b.id)).ToList();
Console.WriteLine("ID Query With ID Local Var Worked: count = {0}", l1.Count);
} catch (Exception E) {
Console.WriteLine("ID Query Failed:");
Console.WriteLine(E.ToString());
Console.WriteLine();
}
try {
var l1 = db.Books.Where(b => BooksFromIdQuery.Select(b_inner => b_inner.id).Contains(b.id)).ToList();
Console.WriteLine("ID Query With Whole Book Local Var Worked: count = {0}", l1.Count);
} catch (Exception E) {
Console.WriteLine("ID Query With Whole Book Local Var Failed:");
Console.WriteLine(E.ToString());
Console.WriteLine();
}
try {
var l1 = db.Books.Where(b => BooksFromIdQuery.Contains(b)).ToList();
Console.WriteLine("Whole Book sub query without select worked: count = {0}", l1.Count);
} catch (Exception E) {
Console.WriteLine("Whole Book sub query without select:");
Console.WriteLine(E.ToString());
Console.WriteLine();
}
编辑
我添加了一些继承,现在底部的两个查询失败了。看起来每当你在查询中使用OfType()
时,EF 没有解析完整个查询内的查询,你必须将子步骤拆分为局部变量。
除非有其他东西要补充,否则我今晚将奖励给 Akash。
DummyBookModelEntities db = new DummyBookModelEntities();
IQueryable<int> BookIds = db.Books.OfType<SciFiBook>().Where(b => b.id < 4).Select(b => b.id);
IQueryable<Book> BooksFromIdQuery = db.Books.OfType<SciFiBook>().Where(b => b.id < 4);
try {
var l1 = db.Books.Where(b => BookIds.Contains(b.id)).ToList();
Console.WriteLine("ID Query With ID Local Var Worked: count = {0}", l1.Count);
} catch (Exception E) {
Console.WriteLine("ID Query Failed:");
Console.WriteLine(E.Message);
Console.WriteLine();
}
try {
var l1 = db.Books.Where(b => BooksFromIdQuery.Select(b_inner => b_inner.id).Contains(b.id)).ToList();
Console.WriteLine("ID Query With Whole Book Local Var Worked: count = {0}", l1.Count);
} catch (Exception E) {
Console.WriteLine("ID Query With Whole Book Local Var Failed:");
Console.WriteLine(E.Message);
Console.WriteLine();
}
try {
var l1 = db.Books.Where(b => BooksFromIdQuery.Contains(b)).ToList();
Console.WriteLine("Whole Book sub query without select worked: count = {0}", l1.Count);
} catch (Exception E) {
Console.WriteLine("Whole Book sub query without select:");
Console.WriteLine(E.Message);
Console.WriteLine();
}
Console.WriteLine();