Linq To Entities [Unable to create a constant value of type '...']

Question

I'm having a hard time with a linq query. See below.

var userHelper = new UserHelper(dbContext);

var currentUser = (from u in dbContext.Users
                     where u.UserID == request.VTIUser.UserID
                     select u).SingleOrDefault();

var userIds = (from u in dbContext.Users
               from r in dbContext.OrgRanges
               select u.UserID).ToList();

With only one from clause it works fine.

Exception message

Unable to create a constant value of type 'VTI.Entities.OrgRange'. Only primitive types ('such as Int32, String, and Guid') are supported in this context.

Stack trace

at System.Data.Objects.ELinq.ExpressionConverter.ConstantTranslator.TypedTranslate(ExpressionConverter parent, ConstantExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)  
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.NewArrayInitTranslator.<>c__DisplayClass77.<TypedTranslate>b__75(Expression e)
at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext()   
at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.EnumerableValidator`3.Validate(IEnumerable`1 argument, String argumentName, Int32 expectedElementCount, Boolean allowEmpty, Func`3 map, Func`2 collect, Func`3 deriveName)   
at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.EnumerableValidator`3.Validate()  
at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidation.CreateExpressionList(IEnumerable`1 arguments, String argumentName, Boolean allowEmpty, Action`2 validationCallback)
at System.Data.Common.CommandTrees.ExpressionBuilder.Internal.ArgumentValidation.ValidateNewCollection(IEnumerable`1 elements, DbExpressionList& validElements)
at System.Data.Objects.ELinq.ExpressionConverter.NewArrayInitTranslator.TypedTranslate(ExpressionConverter parent, NewArrayExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.ConstantTranslator.TypedTranslate(ExpressionConverter parent, ConstantExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateLambda(LambdaExpression lambda, DbExpression input, DbExpressionBinding& binding)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.OneLambdaTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, DbExpression& source, DbExpressionBinding& sourceBinding, DbExpression& lambda)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SelectManyTranslator.Translate(ExpressionConverter parent, MethodCallExpression call)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.SequenceMethodTranslator.Translate(ExpressionConverter parent, MethodCallExpression call, SequenceMethod sequenceMethod)
at System.Data.Objects.ELinq.ExpressionConverter.MethodCallTranslator.TypedTranslate(ExpressionConverter parent, MethodCallExpression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TypedTranslator`1.Translate(ExpressionConverter parent, Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.TranslateExpression(Expression linq)
at System.Data.Objects.ELinq.ExpressionConverter.Convert()
at System.Data.Objects.ELinq.ELinqQueryState.GetExecutionPlan(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Data.Entity.Internal.Linq.InternalQuery`1.GetEnumerator()
at System.Data.Entity.Infrastructure.DbQuery`1.System.Collections.Generic.IEnumerable<TResult>.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at CrewSheetWebService.Controllers.UsersController.GetUsers() in c:\Users\patrich.ISCDEVELOPMENT\Documents\Visual Studio 2010\WebSites\St Paul\App_Code\WebAPI\Controllers\UsersController.cs:line 53
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass13.<GetExecutor>b__c(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.<>c__DisplayClass5.<ExecuteAsync>b__4()
at System.Threading.Tasks.TaskHelpers.RunSynchronously[TResult](Func`1 func, CancellationToken cancellationToken)  

User model

  [Table("tblUsers")]
  public class User
  {
    [Key]
    public int UserID { get; set; }

    [StringLength(10)]
    public string EmpNumber { get; set; }

    [StringLength(10)]
    public string Appointment { get; set; }

    [StringLength(6)]
    public string OrgLevel1 { get; set; }

    [StringLength(6)]
    public string OrgLevel2 { get; set; }

    [StringLength(6)]
    public string OrgLevel3 { get; set; }

    [StringLength(200)]
    public string EmpName { get; set; }

    [StringLength(200)]
    public string Email { get; set; }

    [StringLength(15)]
    public string Phone { get; set; }

    public DateTime HireDate { get; set; }

    [StringLength(50)]
    public string Password { get; set; }

    public int? Supervisor { get; set; }

    public int? Role { get; set; }

    public int? PayCycleID { get; set; }

    public int DocumentGroupID { get; set; }

    public DateTime EffectedDate { get; set; }

    public DateTime ExpirationDate { get; set; }

    public int? GroupID { get; set; }

    public int? EmpPosition { get; set; }

    public int? EmpStatus { get; set; }

    public int PunchID { get; set; }

    [ForeignKey("PunchID")]
    public virtual PunchProfile PunchProfile { get; set; }

    [StringLength(50)]
    public string ADUserID { get; set; }

    [StringLength(10)]
    public string Pin { get; set; }

    [StringLength(10)]
    public string Union1 { get; set; }

    [StringLength(10)]
    public string Union2 { get; set; }

    [StringLength(10)]
    public string Union3 { get; set; }

    public decimal? HourlyRate { get; set; }

    public int? PayType { get; set; }

    public int? FLSAProfile { get; set; }

    public int? FMLVProfile { get; set; }

    public int? PercentFullTime { get; set; }

    public DateTime? LastModified { get; set; }

    public virtual ICollection<UserJob> UserJobs { get; set; }

    public virtual ICollection<UserSkill> UserSkills { get; set; }

    public virtual ICollection<UserSchedule> UserSchedules { get; set; }

    public virtual ICollection<UserRange> Ranges { get; set; }

    public static UserSchedule GetCurrentUserSchedule(IList<UserSchedule> userScheds)
    {
      return userScheds.Where(us => DateTime.Now.IsBetween(us.StartDate, us.EndDate)).FirstOrDefault();
    }

    public User()
    {
    }

    public bool HasRole(int role)
    {
      return (Role & role) == role;
    }
  }

OrgRange model

  [Table("tblOrgRanges")]
  public class OrgRange
  {
    [Key]
    public int RangeID { get; set; }

    public int GroupID { get; set; }

    [ForeignKey("GroupID")]
    public virtual Group Group { get; set; }

    [StringLength(30)]
    public string RangeName { get; set; }

    [StringLength(100)]
    public string RangeDescription { get; set; }

    [StringLength(6)]
    public string OrgLevel1Min { get; set; }

    [StringLength(6)]
    public string OrgLevel1Max { get; set; }

    [StringLength(6)]
    public string OrgLevel2Min { get; set; }

    [StringLength(6)]
    public string OrgLevel2Max { get; set; }

    [StringLength(6)]
    public string OrgLevel3Min { get; set; }

    [StringLength(6)]
    public string OrgLevel3Max { get; set; }

    public DateTime EffectiveDate { get; set; }

    public DateTime ExpirationDate { get; set; }

    // Don't use this function in a LINQ Where clause. It won't work.
    public bool Include(string orgLevel1, string orgLevel2, string orgLevel3)
    {
      return (OrgLevel1Min.CompareTo(orgLevel1) <= 0 && OrgLevel1Max.CompareTo(orgLevel1) >= 0)
        && (OrgLevel2Min.CompareTo(orgLevel2) <= 0 && OrgLevel2Max.CompareTo(orgLevel2) >= 0)
        && (OrgLevel3Min.CompareTo(orgLevel3) <= 0 && OrgLevel3Max.CompareTo(orgLevel3) >= 0);
    }

    public OrgRange()
    {
    }
  }

Answer 1

The problem is that the Entity Framework does not know how to translate an entity into SQL. I can not spot this problem in the code you provided but I guess you will be able to find the problematic code with the following example.

var user = GetUserById(42);

var orders = context.Orders.Where(order => order.Customer == customer);

This will fail because the Entity Framework does not know how to translate a comparison of two user entities into SQL. To fix this you have to rewrite this using primary key comparisons.

var user = GetUserById(42);

var orders = context.Orders.Where(order => order.Customer.Id == customer.Id);

This will work because the Entity Framework now sees a comparison of IDs, of integers, and is able to translate this into a SQL statement. I always wondered why the Entity Framework does not perform this rewriting on its own, just replace all comparisons of entities with comparisons of their primary keys, but there are probably edge case I am not aware of.