Entity Framework Casting Error

Question

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.IQueryable1' to type 'System.Linq.IQueryable1'. 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 => 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();
        }

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();

Could you post more full example which can be used to reproduce your problem? In my attempts both statements works without exception in .NET 4.0. For .NET 3.5 as the destination both statements produce exception, but another one. — Oleg, Apr 06 '11 at 20:31
Done, the whole methods are there. Thanks for looking at this — Adam Rackis, Apr 06 '11 at 21:00
@Adam Rackis: Before I start to reproduce your problem I want recommend you to look at two my previous answer which describes how you can do the same thing which you do in your examples, but much more easy. [The answer](http://stackoverflow.com/questions/5140809/linq-dynamic-query-library/5158477#5158477) describes the main idea. [The demo](http://www.ok-soft-gmbh.com/jqGrid/jqGridDemo.zip) from [the answer](http://stackoverflow.com/questions/5500805/asp-net-mvc-2-0-implementation-of-searching-in-jqgrid/5501644#5501644) construct the EF query dynamically based on the user input. — Oleg, Apr 06 '11 at 21:17
Ugh - I should probably tell you that Property is abstract, and there are a few other classes that all inherit from property (hence the OfType() in the query. Still though, no matter what the data model, I can't for the life of me understand why pulling a sub-step out into a local variable would make a difference. — Adam Rackis, Apr 06 '11 at 22:32
And thank you, I plan on reading your previous answers shortly — Adam Rackis, Apr 06 '11 at 22:33

score 1 · Answer 1 · edited May 23 '17 at 12:11

1

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.

edited May 23 '17 at 12:11

Community

1
1

answered Feb 25 '11 at 18:24

Shiraz Bhaiji

64,065
34
143
252

Both calls use the same exact code. The difference is that the first one goes through the extra step of declaring an intermediary query explicitly. For some reason EF requires this declaration, even though `PropertyQuery.Select(p => p.PropertyId)` will be seen as IQueryable in both cases. – Adam Rackis Feb 25 '11 at 19:07
@Adam, I have updated my answer, I think that it is related to how EF handles identity columns – Shiraz Bhaiji Feb 25 '11 at 19:27

score 1 · Answer 2 · answered Mar 03 '11 at 10:30

1

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.

answered Mar 03 '11 at 10:30

smartcaveman

41,281
29
127
212

Thank you for your answer. Those two lines are 100% identical from the compiler's perspective, and I'm fairly certain neither will blow up. From my question it seems as though the ExpressionTree-parsing mechanism in EF wants the subquery broken out into a simpler piece, like what you pasted above. What I was wanting to know was **why** - I think I'm going to send this Q to the EF team and see if they'd be willing to look at it. +1 for your time though :) – Adam Rackis Mar 03 '11 at 14:55
Sure, let us know what you find out. – smartcaveman Mar 03 '11 at 15:02

score 1 · Accepted Answer · answered Apr 12 '11 at 07:43

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.

+1 for the good answer, but as my last edit will show, this probably isn't it. — Adam Rackis, Apr 12 '11 at 14:47
Ok, from my last last edit, I think your answer is essentially right. It looks like EF doesn't want any sub queries when (and only when) you have an OfType existing in the query. — Adam Rackis, Apr 12 '11 at 14:58

Entity Framework Casting Error

3 Answers3