Setting default for DefaultIfEmpty method in LINQ when the default has multiple with-many relationships?

Question

I have three objects:

1. Event (relationships: has many EventProducts)

2. Product (relationships: has many EventProducts)

3. EventProduct (relationships: with many Events, with many Products, has properties like quantity_sold, quantity_allocated, etc which should be stored per product and per event)

In my application, when someone clicks an Event, a new window initializes with a list of all products. From there they can modify cells I need to populate a datagrid in WPF such that when someone clicks an event. So in the context of this code, the Event is known and constant.

The issue I'm having is with creating default types of EventProduct.

What I need is to have a query where if there is no EventProduct in the DB, it will instantiate an EventProduct with EventProduct.Event = currentEvent (currentEvent will be constant for all EventProducts created with this query) and EventProduct.Product = product (product will change for each row)

This code works well when there is an associated EventProduct in the database. But if there isn't, my selection returns my Product just fine but the entire EventProduct is null.

var query2 = from product in dbContext.Products
             join eventProduct in dbContext.EventProducts
                 on new { pIndex = product.index, eIndex = currentEvent.index }
                 equals new { pIndex = eventProduct.Product.index, eIndex = eventProduct.Event.index } into temp
             from eventProduct in temp.DefaultIfEmpty() // this is my problem line
             select new { 
                    Product = product,
                    EventProduct = eventProduct
              };

I have tried creating a constructor for EventProduct(Event e, Product p) and passing in the values to the constructor in my DefaultIfEmpty() method but I get errors that my constructor has to have 0 arguments to be used in that way. I can't do that because there is no way to tell my EventProduct() object which Event and Product it should be associated with if I do that.

I have also tried no constructor, just creating a new EventProduct and setting its properties but I get the error "The entity or complex type ...EventProduct cannot be constructed in a LINQ to Entites query".

At the end result, I want to select my Product and EventProduct. If there is no EventProduct associated with both that Product and Event, then my EventProduct selection should be set to a default which has the currentEvent, the current row's Product, and all properties set to a default value (all are decimals and should be 0 in this case).

EDIT: I've just tried this query and it also gives me an Unsupported error:

var query2 = from product in dbContext.Products
             join eventProduct in dbContext.EventProducts
                 on new { pIndex = product.index, eIndex = currentEvent.index }
                 equals new { pIndex = eventProduct.Product.index, eIndex = eventProduct.Event.index } into temp
             from eventProduct in temp.DefaultIfEmpty()
             select new { 
                 Product = product,
                 EventProduct = eventProduct != null ?
                 eventProduct : new EventProduct
                 {
                     Product = product,
                     Event = currentEvent,
                     quantity_allocated = 0,
                     quantity_sold = 0,
                     quantity_sampled = 0
                 }
             };

EDIT: Resolved with this technique:

1) Create an object because anonymous objects are read-only:

    class Associations
    {
        public class ProductEventProduct
        {
            public Product Product { get; set; }
            public EventProduct EventProduct { get; set; }
        }
    }

2) Foreach null object in the dataset, replace with a default object

var query = from product in dbContext.Products
                         join eventProduct in dbContext.EventProducts
                             on new { pIndex = product.index, eIndex = currentEvent.index }
                                equals new { pIndex = eventProduct.Product.index, eIndex = eventProduct.Event.index } into temp
                         from eventProduct in temp.DefaultIfEmpty()
                         select new Associations.ProductEventProduct { 
                             Product = product,
                             EventProduct = eventProduct
                         };
            var dataSource = query.ToList();

            foreach (Associations.ProductEventProduct entry in dataSource)
            {
                if (entry.EventProduct == null)
                {
                    entry.EventProduct = new EventProduct
                    {
                        Product = entry.Product,
                        Event = currentEvent,
                        quantity_allocated = 0,
                        quantity_sold = 0,
                        quantity_sampled = 0
                    };
                }
            }

Answer 1

It appears that the issue you are having is that you are asking Entity Framework to create a query that it doesn't know how to create.

Keep in mind that Linq uses deferred execution, so when you write your query, it's just a placeholder of where and how to get the data rather than the actual data. It isn't until some other code asks for the data that the query actually runs and your information is populated.

For Linq to Entities, this means it will build up a query in the DB language and hold on to it until you need the data, then execute the query as you would expect through whatever DB provider you are using when you call for it, and store it in memory to work against.

So when you try to construct a new EventProduct in your query to fill the null as you have it, the DB doesn't know what that is and you get the error, since you are still in the part of the query where the DB is responsible for objects. The DB Provider doesn't know how to construct that new object, so EF can't translate the query and you get the error.

The solution is to "hydrate" your information before you try to construct the new EventProduct, by running your query sans EventProduct constructor through a call to ToList() or ToArray or similar, which forces the query to run right then. Then, AFTER the data is hydrated (in memory), go through all of the objects you created in the query and if the EventProduct is null, construct a new one and add it dynamically, however you want to do it. (A Second query with a Select statement maybe?) This should get you around the error you mentioned.

I'm a bit out of touch on if your original query, which produces anonymous objects, would be tracked by the DBContext or not, but keep that in mind as well and test for it. If you add a bunch of EventProduct instances that are tied to those anonymous objects, you could have those included by default in tracking, and a call to SaveChanges() might inadvertently write them all to the DB when you weren't intending to do so. Something to keep in mind.

Hope this helps, let me know!

Answer 2

Have you tried setting default value in case of null in the select clause?

......
select new 
{ 
    Product = product,
    EventProduct = eventProduct != null 
        ? eventProduct 
        : new EventProduct { .... set default values}
}

UPD

There are possible workarounds to this problem, either introduce DTO (or another anonymous type) as this answer suggests:

select new 
{ 
    Product = product,
    EventProduct = new 
    {
         Product = product,
         EventId = currentEvent.Id, // possibly you will need to copy every field by hand 
         quantity_allocated = eventProduct == null ? 0 : eventProduct .quantity_allocated ,
         ....
    }
}

Or just replace nulls after the query to db cause you should have all the needed data already:

var query2 = from product in dbContext.Products
         join eventProduct in dbContext.EventProducts
             on new { pIndex = product.index, eIndex = currentEvent.index }
             equals new { pIndex = eventProduct.Product.index, eIndex = eventProduct.Event.index } into temp
         from eventProduct in temp.DefaultIfEmpty()
         select new { 
             Product = product,
             EventProduct = eventProduct
         };
var results = query2.ToList();
foreach(var r in results)
{
      r.EventProduct = r.EventProduct != null 
            ? r.EventProduct 
            : new EventProduct { .... set default values}
}