Linq To Entities 'Only primitive types or enumeration types are supported' Error

Question

I am using LinqPad to test my query. This query works when the LInqPad connection is to my database (LInq to SQL) but it does not work when I change the connection to use my Entity Framework 5 Model.dll. (Linq to Entity). This is in C#.

I have two tables called Plan and PlanDetails. Relationship is one Plan to many PlanDetails.

var q = from pd in PlanDetails
        select new {
            pd.PlanDetailID,
            ThePlanName = (from p in this.Plans
                    where p.PlanID == pd.PlanID
                    select p.PlanName)
        };
var results = q.ToList();
q.Dump(); //This is a linqpad method to output the result.

I get this error "NotSupportedException: Unable to create a constant value of type 'Domain.Data.Plan'. Only primitive types or enumeration types are supported in this context." Any ideas why this only works with Linq to SQL?

Solution 1

basically it means you are using some complex datatype inside the query for comparison. in your case i suspect from p in this.Plans where p.PlanID == pd.PlanID is the culprit.

And it depends on DataProvider. It might work for Sql Data Provider, but not for SqlCE data Provider and so on.

what you should do is to convert your this.Plans collection into a primitive type collection containing only the Ids i.e.

var integers = PlanDetails.Plans.Select(s=>s.Id).ToList();

and then use this list inside.

var q = from pd in PlanDetails
        select new {
            pd.PlanDetailID,
            ThePlanName = (from p in integers
                    where p == pd.PlanID
                    select pd.PlanName)
        };

Solution 2

I got this error when i was trying to null check for a navigational property in the entity framework expression

I resolved it by not using the not null check in the expression and just using Any() function only.

  protected Expression<Func<Entities.Employee, bool>> BriefShouldAppearInSearchResults(
        IQueryable<Entities.Employee> briefs, string username)
    {
       var trimmedUsername = NameHelper.GetFormattedName(username);

        Expression<Func<Entities.Employee, bool>> filterExpression = cse =>                
             cse.Employee.Cars.All(c => 
                 c.Employee.Cars!=null &&  <--Removing this line resolved my issue
                 c.Employee.Cars.Any(cur => cur.CarMake =="Benz")));

        return filterExpression;
    }

Hope this helps someone!

Solution 3

This is a Linqpad bug if you like (or a peculiarity). I found similar behaviour myself. Like me, you may find that your query works with an ObjectContext, but not a DbContext. (And it works in Visual Studio).

I think it has to do with Linqpad's inner structure. It adds MergeAs (AppendOnly) to collections and the context is a UserQuery, which probably contains some code that causes this bug.

This is confirmed by the fact that the code does work when you create a new context instance in the Linqpad code and run the query against this instance.

Solution 4

If the relationship already exists.

Why not simply say.

var q = from pd in PlanDetails
        select new {
            pd.PlanDetailID,
            ThePlanName = pd.Plan.PlanName
        };

Of course i'm assuming that every PlanDetail will belong to a Plan.

Update

To get better results from LinqPad you could tell it to use your own assembly (which contains your DbContext) instead of the default Datacontext it uses.

View more solutions

Comments

[scartag]

I'm worried about why he needs to do a nested query when he says One Plan to Many PlanDetails .. which means PlanDetails can't stand alone .. they have to be related to a Plan ... So if you have a PlanDetail it should have a Navigational property called Plan (if not renamed)

[duyn9uyen]

You are so right. I totally forgot about the Navigational property. Can you tell I just started to use EF? Thanks!

[Manish Mishra]

exactly, he doesn't need to do the condition, but for the sake of this error.

[duyn9uyen]

Thanks! I've been playing with EF and this also makes sense. I will try this out on two non-related entities.

[Gert Arnold]

OK with me, but while this solves the error in hand, it does not explain it. It is a Linqpad glitch, whichever way you look at it.

[Manish Mishra]

It's not a Linqpad glitch, or its not only a linqqpad glitch, try same code with a .sdf file as your datasource, so whatever be the reason, solution is to extract your comparing values in a separate primitive list and use this list inside. :).

[Gert Arnold]

Well, the OP is about linqpad behaviour, not about different SQL providers. It is a fact that linqpad does not run queries like this in EF DbContext while the code runs in other environments.

[scartag]

This line PlanDetails.Plans.Select(s=>s.Id).ToList(); will never run. I'm sure you meant Plans.Selecte(s=> s.Id).ToList();

[Manish Mishra]

ohh..yeah obviously, PlanDetails is a list, he will have to extact Plans.Select(s=>s.Id) from Single PlanDetail

[juniper]

Your answer helped my greatly! I had a similar problem even with a simpler 'select new' clause where I simply create an instance of my own class with properties being references to entities (without another nested 'from' clause). Using my own context instance it worked in LINQPad, too (as it already did in VS).

[Jay Prajapati]

hello all, what if i want to join 3-4 lists and get the results from it using linq what to do? i am joinig that lists but same error occurs

[Fjarskiptagervitungl]

this resolved it for me, thanks! I removed the null check and used Any()