Can I project an optional object reference into an optional reference such as a projection result?

Question

Let's say I have two objects:

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 (null) reference in Order (perhaps the system supports "anonymous" orders).

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

 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 Customer is a required navigation property in Order , I could use the following projector, 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 client:

• EF complains that the materialized value for o.Customer.SalesLevel is NULL and cannot be stored in the int property, and not in the nullable CustomerViewModel.SalesLevel . This is not surprising, and the problem can be solved by creating an CustomerViewModel.SalesLevel int? CustomerViewModel.SalesLevel int? (or zero properties at all)

• But I would prefer OrderViewModel.CustomerViewModel materialize as NULL when the order has no customer.

For 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 raises the notorious LINQ to Entities exception:

Cannot create persistent value of type "CustomerViewModel". Only primitive types (e.g. 'Int32', 'String' und 'Guid' ') are supported in this context.

I assume that : null is a "constant value" for CustomerViewModel , which is not allowed.

Since NULL assignment does not seem to be allowed, I tried to enter 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 also does not work and throws an exception:

The type "CustomerViewModel" is displayed in two structurally incompatible initializations within the same LINQ to Entities query. A type can be initialized in two places in one request, but only if the same properties are set in both places, and these 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 is not a very nice workaround to populate all properties with dummy values ​​or NULL explicitly.

Questions:

• Is the last piece of code the only workaround, with the exception that all CustomerViewModel properties can be null?

• Is it not possible to materialize an optional NULL reference in a projection?

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

(I am setting a common tag infrastructure for this question because I assume this behavior is version independent, but I'm not sure. I tested the code snippets above using EF 4.2 / DbContext / Code - First. Edit: two more are added tag.)

Answer 1

I cannot get the projection to work with the implementation of IQueryable DbQuery. If you are looking for a workaround, then why not do a projection after the data has been extracted from Db, and it is no longer EF 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 that you retrieve all Order and Customer columns from Db. You can limit this by selecting only the necessary columns 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();