Linq To Entities 'Only primitive types or enum

问题:

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?

回答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)
        };

回答2:

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.

回答3:

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.

回答4:

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!