Entity Framework - Linq - Comparing Nullable objects - NotSupportedException

Question

I need to compare an object with a parameter that I'm passing in. The logic being:

• If territory == null. Return all Orders
• If territory != null and the Territory entity from Orders is != null. Return Orders matching on the Id property (PK) of both entities.

I have the following LINQ statement:

The cut down version of my method is (there are normally other filters in the WHERE clause):

  public void Execute(Territory territory)
  {
    using (var context = DatabaseHelper.CreateContext())
    {
      var orders = context.Orders.Where(x =>
        (
          (territory == null) ||
          (x.Territory != null && x.Territory.Id == territory.Id)
          )
        );

      if (!orders.Any()) //Exception occurs here on materialising the query
      {
        //Do something
      }
    }
  }

I receive the exception NotSupportedException and the message:

Unable to create a constant value of type 'ENTITY'. Only primitive types or enumeration types are supported in this context.

Whilst I understand the error as I'm not passing in a primitive type. How can I change the LINQ query so it returns the expected results?

Answer 1

Try this:

var t = territory == null;
var orders = context.Orders.Where(x =>        
       t || (x.Territory != null && x.Territory.Id == territory.Id));

The reason behind this is it tries to translate territory into a constant in SQL query but of course territory is not primitive or any equivalent type on server, so it throws the exception. You can however cache a constant boolean outside (but still in the effective scope of checking territory) and use that constant in the query.