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?

Answer 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)
        };

Answer 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!