Changing the Linq To Sql Entity Property in a Request

Question

I have 2 Products and ProductDetails tables. Products contain all the information about my warehouse, and ProductDetails is used to ensure that price changes for products are updated on the system before they take effect.

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 the corresponding row is found in ProductDetails, where CommenceDate is less than or equal to the current date. What I still have:

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;

At first I thought that I could achieve what happened after by declaring a new product in select, for example:

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 an easy way to say "return the product and possibly replace some property values"?

Answer 1

3 :

• , ..

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

• ( ) ; :

  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,
                      ...
                 };
  

• , , ( AsEnumerable(), , LINQ-to-Objects ) >

  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,
                      ...
                 };
  

---

; - ; - ; , , ( " " ) - -:

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

.