“Unable to create a constant value of type 'Us

Question

I have a method called UpdateOrders that is supposed to allow users to only edit orders that they own, unless the user is an administrator, in which case they can edit all orders.

I'm using Entity Framework 5 in .NET 4.5.

The following code produces the error "Unable to create a constant value of type 'User'" at the line var target = _context.Orders.FirstOrDefault....

public Order UpdateOrders(Order order)
{
    var userName = HttpContext.Current.User.Identity.Name.ToLower();

    var target = _context.Orders.FirstOrDefault(o => o.OrderID == order.OrderID
        && (o.User.UserName.ToLower() == userName || _context.Users.FirstOrDefault(u => u.UserName == userName).Role.RoleName == "Administrator"));

    if (target != null)
    {
        _context.Entry(target).CurrentValues.SetValues(order);
        _context.SaveChanges();
    }

    return target;
}

I can remove _context.Users.FirstOrDefault(u => u.UserName == userName).Role.RoleName to a separate line that sets the RoleName to a variable and put the variable in the code and it runs fine, but it means one more hit to the database.

If I use _context.Orders.Where doesn't produce the error and performs all of the logic in a single query. So this code actually works just fine:

public void TestMethod(Order order)
{
    var userName = HttpContext.Current.User.Identity.Name.ToLower();

    var target = _context.Orders.Where(o => o.OrderID == order.OrderID
        && (o.User.UserName.ToLower() == userName || _context.Users.FirstOrDefault(u => u.UserName == userName).Role.RoleName == "Administrator")).ToList();

    return;
}

Why is FirstOrDefault having problems that Where does not? Is there a way to get my desired result and keep all of the logic in a single query?

Thanks!

Answer 1

I think you've actually found a bug in Entity Framework. I'm envious!

(EDIT: It looks like this bug has been fixed in EF 6.0.0-rc1)

Here's why I think it's a bug: the machinery which is translating the expression predicate is definitely misbehaving for FirstOrDefault in a way that does not occur with Where. I can repro your issue against a similar schema with these contrived queries:

// works
var target = _context.Orders.Where (o => _context.Users.FirstOrDefault() != null).FirstOrDefault ();

// Exception: Unable to create a constant value...
target = _context.Orders.FirstOrDefault (o => _context.Users.FirstOrDefault() != null);

Not only does it throw an exception, but observing the SQL in LINQPad you can see that an effective SELECT * FROM Users query is being issued right before it blows up, as if it's trying to treat the _context.Users.FirstOrDefault() call as the IEnumerable version rather than the IQueryable version. Using an unmapped method as a predicate in the inner FirstOrDefault call results in the same "constant value" exception, rather than the "LINQ to Entities does not recognize the method" exception one would expect:

// throws "Unable to create a constant value..." exception!
target = _context.Orders.FirstOrDefault (o => _context.Users.FirstOrDefault(u => MyBogusMethod(u)) != null);

It's as if the translator for FirstOrDefault is aggressively trying to reduce an expression of type User down to an instance of type User by executing the expression somehow, but before the reduction is complete (before the inner FirstOrDefault is evaluated) it realizes that the resulting type is not going to be allowed and blows up. Very strange.

I briefly tried to figure out what's going wrong in the source, but it's way beyond me. I'd recommend filing a bug with the EF guys: http://entityframework.codeplex.com/workitem/list/basic

In the meantime, it seems to work correctly as long as you put the predicate in the Where clause rather than the FirstOrDefault, even if you tag FirstOrDefault onto the end of your query:

var target = _context.Orders.Where(o => 
              o.OrderID == order.OrderID
              && (o.User.UserName.ToLower() == userName 
                  || _context.Users.FirstOrDefault(u => 
                       u.UserName == userName).Role.RoleName == "Administrator")
              )
              .FirstOrDefault();

(You might also consider storing the fact that the user has the "Administrator" role on the User, to avoid the need for complex queries like this.)