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'?

Marc Gravell · Accepted Answer · 2010-12-12 09:13:47Z

5

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.

edited Dec 12, 2010 at 9:13

answered Dec 12, 2010 at 0:05

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Macros Over a year ago

This is what I was hoping to avoid - I have a lot of products in the database so looping through isn't an option. Would be dead easy in Sql! (or in Linq To Sql if I could instantiate an entity in the query)

Marc Gravell Over a year ago

@Macros, well you can always use TSQL with ExecuteQuery<T>.

Marc Gravell Over a year ago

@macros actually it could be written in an iterator block so it is lazy/per item; then you wouldn't have any initial extra delay - would this help?

Macros Over a year ago

@Marc - looks like that is going to be the best option. I've been trying to avoid Sql as I am fairly new to Linq and it feels like cheating rather than finding the proper 'Linq-to-sql' way of doing things but guess it is just a case of knowing when to use each.

Macros Over a year ago

@Marc, I'll also be displaying products in a grid so even if they are lazy loading it is still going to be slower than a set based approach

|

Collectives™ on Stack Overflow

Change property of Linq To Sql Entity in query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related