Entity Framework Casting Error
The following works perfectly:
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();
But the following blows up:
IQueryable<Property> PropertyQuery = PropertyDAO.SearchWithAdditionalParameters(/* elided */);
var relevantFMVs = PropertyDAO.db.FMVHistories.Where(f => PropertyQuery.Select(p => p.PropertyId).Contains(f.PropertyId)).ToList();
(note that instead of creating propertyIdQuery separately, I just substituted the query itself where the variable had been)
Exception is
Unable to cast the type 'System.Linq.IQueryable
1' to type 'System.Linq.IQueryable
1'. LINQ to Entities only supports casting Entity Data Model primitive types.
Can someone shed some light on what EF (4) is doing under the covers to make only the first query work, even though they're ostensibly equivalent?
I know IQueryable<T>
and Expression Trees do a lot of stuff under the covers, but how is it that saving an intermediate step into a local variable would affect the outcome?
EDIT
By request, here's the full method that's being called, and the methods that that method calls:
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;
}
EDIT
I really wanted Akash's answer to be correct, but if it was, I would expect the middle query here to blow up, but in fact all three work just fine.
I'm beginning to suspect that the inheritance structure I have on type Property
(from the original example) might have something to do with this.
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 => Books开发者_Go百科FromIdQuery.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();
}
EDIT
I added in some inheritance, and now the bottom two queries fail. It looks like any time you have an OfType()
in the query, EF simply does not want to parse through entire queries within queries; you have to break out your sub-steps into local variables.
I'll award Akash the bounty tonight unless someone has something else to add.
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();
f => PropertyQuery.Select(p => p.PropertyId).Contains(f.PropertyId)
Above is a linq Expression, everything after f=> is an expression tree. Composite Linq can only expand query based on expressions, but not delegates.
Both your statement sets are logically correct but from compiler's perspective they are different. If you notice your expanded (where on where or select) will only work on same type of template parameter. Where else your IQueryable of int will not work as your linq is expecting IQueryable of T.
Secondly, when you are performing Select on T, and returning IQueryable of T, there is no way runtime to know that earlier T was of what type.
In short, saving intermediate step as local variable breaks your expression tree. I suggest you look at Reflector to see source code of what is actually generated.
Your entire lambda expression is actually built using expression node and entire tree is built and given back to Where method. Where else in your first example, expression tree is different, it involves execution of something else within an expression.
Try writing the first query but instead of
IQueryable<long> propertyIdQuery = PropertyQuery.Select(p => p.PropertyId);
var propertyIdQuery = PropertyQuery.Select(p => p.PropertyId);
Does it throw the error? This is the only obvious difference in the queries to me.
The error message states that it only supports primitive types.
In the code that works you have specified that it is IQueryable<long>
.
My guess is that the code that does not work uses IQueryable<decimal>
therefore the cast error.
You are returning an identity column. An Identity column can have several types. Decimal is the data type that can handle all possible identity types.
Why does select SCOPE_IDENTITY() return a decimal instead of an integer?
In the code that works the compiler gets a hint to use long.
精彩评论