Change property of Linq To Sql Entity in query

Question

I have 2 tables Products and ProductDetails. Products contains all information about my stock and ProductDetails is used to allow product price changes to be updated in the system in advance of when they come into action.

I am trying to write what should be a simple query that returns all products and also updates the Cost and Price of the product if a matching row is found in ProductDetails where the CommenceDate is less than or equal to the current date. What I have so far is:

var products = from p in Products
               from pd in ProductDetails
                .Where(pd => pd.ProductID == p.ID && pd.CommenceDate <= DateTime.Now)
                .DefaultIfEmpty()
               where p.InUse == true
               select p;

I first thought that I could achieve what I was after by declaring a new product in the select e.g:

var products = from p in Products
               from pd in ProductDetails
                .Where(pd => pd.ProductID == p.ID && pd.CommenceDate <= DateTime.Now)
                .DefaultIfEmpty()
               where p.InUse == true
               select new Product {
                    ID = p.ID,
                    Description = p.Description,
                    ....
                    Cost = pd.Cost.HasValue ? pd.Cost : p.Cost,
                    Price = pd.Price.HasValue ? pd.Price : p.Price,
                    ...
               };

This code gives me the following error:

Explicit construction of entity type 'LINQPad.User.Product' in query is not allowed

Is there a simple way to say 'return product and optionally replace some property values'?

Answer 1

You have 3 options:

• process the items after fetching them - i.e.

  var products = ( /* first query */ ).ToList();
  foreach(var item in products) {
      item.Cost = ...;
      item.Price = ...;
  }
  // and probably submit changes
  

• create an unrelated type (outside of the model) to project into; even an anon type:

  var products = /* most of second query */
                 select new { // <==== not a Product
                      ID = p.ID,
                      Description = p.Description,
                      ....
                      Cost = pd.Cost.HasValue ? pd.Cost : p.Cost,
                      Price = pd.Price.HasValue ? pd.Price : p.Price,
                      ...
                 };
  

• pretty much the same as the above, but breaking composition (via AsEnumerable(), which means we revert back to LINQ-to-Objects for whatever follows) and creating a changed version outside of the data-context

  var products = from prod in (/* first query */).AsEnumerable()
                 select new Product {
                      ID = p.ID,
                      Description = p.Description,
                      ....
                      Cost = pd.Cost.HasValue ? pd.Cost : p.Cost,
                      Price = pd.Price.HasValue ? pd.Price : p.Price,
                      ...
                 };
  

---

Edit re comments; another option is an iterator block; iterator blocks are streaming devices; the compiler weaves a lot of magic to ensure that your code only processes one item ("yield return") at a time - it doesnt read all the data before returning anything:

static IEnumerable<Product> SomeMethod(IEnumerable<Product> products) {
    foreach(p in products) {
        p.Foo = ...
        p.Bar = ...
        yield return p;
    }
}

Now pass the sequence of products through that method.