Can I project an optional reference of an entity into an optional reference of the projection's result type?

Question

Say, I have two entities:

public class Customer
{
    public int Id { get; set; }
    public int SalesLevel { get; set; }
    public string Name { get; set; }
    public string City { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public DateTime DueDate { get; set; }
    public string ShippingRemark { get; set; }

    public int? CustomerId { get; set; }
    public Customer Customer { get; set; }
}

Customer is an optional (nullable) reference in Order (maybe the system supports "anonymous" orders).

Now, I want to project some properties of an order into a view model including some properties of the customer if the order has a customer. I have two view model classes then:

public class CustomerViewModel
{
    public int SalesLevel { get; set; }
    public string Name { get; set; }
}

public class OrderViewModel
{
    public string ShippingRemark { get; set; }
    public CustomerViewModel CustomerViewModel { get; set; }
}

If the Customer would be a required navigation property in Order I could use the following projection and it works because I can be sure that a Customer always exists for any Order:

OrderViewModel viewModel = context.Orders
    .Where(o => o.Id == someOrderId)
    .Select(o => new OrderViewModel
    {
        ShippingRemark = o.ShippingRemark,
        CustomerViewModel = new CustomerViewModel
        {
            SalesLevel = o.Customer.SalesLevel,
            Name = o.Customer.Name
        }
    })
    .SingleOrDefault();

But this does not work when Customer is optional and the order with Id someOrderId does not have a customer:

• EF complains that the materialized value for o.Customer.SalesLevel is NULL and cannot be stored in the int, not nullable property CustomerViewModel.SalesLevel. That's not surprising and the problem could be solved by making CustomerViewModel.SalesLevel of type int? (or generally all properties nullable)

• But I would actually prefer that OrderViewModel.CustomerViewModel is materialized as null when the order has no customer.

To achieve this I tried the following:

OrderViewModel viewModel = context.Orders
    .Where(o => o.Id == someOrderId)
    .Select(o => new OrderViewModel
    {
        ShippingRemark = o.ShippingRemark,
        CustomerViewModel = (o.Customer != null)
            ? new CustomerViewModel
              {
                  SalesLevel = o.Customer.SalesLevel,
                  Name = o.Customer.Name
              }
            : null
    })
    .SingleOrDefault();

But this throws the infamous LINQ to Entities exception:

Unable to create a constant value of type 'CustomerViewModel'. Only primitive types (for instance ''Int32', 'String' und 'Guid'') are supported in this context.

I guess that : null is the "constant value" for CustomerViewModel which is not allowed.

Since assigning null does not seem to be allowed I tried to introduce a marker property in CustomerViewModel:

public class CustomerViewModel
{
    public bool IsNull { get; set; }
    //...
}

And then the projection:

OrderViewModel viewModel = context.Orders
    .Where(o => o.Id == someOrderId)
    .Select(o => new OrderViewModel
    {
        ShippingRemark = o.ShippingRemark,
        CustomerViewModel = (o.Customer != null)
            ? new CustomerViewModel
              {
                  IsNull = false,
                  SalesLevel = o.Customer.SalesLevel,
                  Name = o.Customer.Name
              }
            : new CustomerViewModel
              {
                  IsNull = true
              }
    })
    .SingleOrDefault();

This doesn't work either and throws the exception:

The type 'CustomerViewModel' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.

The exception is clear enough how to fix the problem:

OrderViewModel viewModel = context.Orders
    .Where(o => o.Id == someOrderId)
    .Select(o => new OrderViewModel
    {
        ShippingRemark = o.ShippingRemark,
        CustomerViewModel = (o.Customer != null)
            ? new CustomerViewModel
              {
                  IsNull = false,
                  SalesLevel = o.Customer.SalesLevel,
                  Name = o.Customer.Name
              }
            : new CustomerViewModel
              {
                  IsNull = true,
                  SalesLevel = 0, // Dummy value
                  Name = null
              }
    })
    .SingleOrDefault();

This works but it's not a very nice workaround to fill all properties with dummy values or null explicitly.

Questions:

1. Is the last code snippet the only workaround, aside from making all properties of the CustomerViewModel nullable?

2. Is it simply not possible to materialize an optional reference to null in a projection?

3. Do you have an alternative idea how to deal with this situation?

(I'm only setting the general entity-framework tag for this question because I guess this behaviour is not version specific, but I am not sure. I have tested the code snippets above with EF 4.2/DbContext/Code-First. Edit: Two more tags added.)

Answer 1

I cannot get the projection to work on the IQueryable implementation of DbQuery either. If you're looking for a workaround then why not do the projection after the data has been retrieved from the Db and it's not an E.F. DbQuery anymore...

OrderViewModel viewModel = context.Orders
    .Where(o => o.Id == someOrderId)
     // get from db first - no more DbQuery
    .ToList()
    .Select(o => new OrderViewModel
    {
        ShippingRemark = o.ShippingRemark,
        CustomerViewModel = o.Customer == null ? null : new CustomerViewModel
        {
            SalesLevel = o.Customer.SalesLevel,
            Name = o.Customer.Name
        }
    })
    .SingleOrDefault();

The downside is you're fetching all the Order and Customer columns from the Db. You can limit this by selecting only the columns you require from Order into an anonymous type and then...

OrderViewModel viewModel = context.Orders
    .Where(o => o.Id == someOrderId)
    .Select(o => new { ShippingRemark = o.ShippingRemark, Customer = o.Customer })
     // get from db first - no more DbQuery
    .ToList()
    .Select(o => new OrderViewModel
    {
        ShippingRemark = o.ShippingRemark,
        CustomerViewModel = o.Customer == null ? null : new CustomerViewModel
        {
            SalesLevel = o.Customer.SalesLevel,
            Name = o.Customer.Name
        }
    })
    .SingleOrDefault();