CODEWITHSUNDEEP
c#linqlinq-to-entitiesaggregate
Avrohom Yisroel
Avrohom Yisroel

Reputation: 9440

"An expression tree may not contain an assignment operator" using Aggregate in a Select clause

I have some Linq to Entities code that pulls data from the store and creates a collection of DTOs from it...

  List<VRTSystemOverview> systems = query.OrderBy(s => s.DHRNumber)
    .Select(s => new VRTSystemOverview {
      ID = s.ID,
      SystemNumber = s.SystemNumber
      // other properties removed for clarity...
    })
    .ToList();

query is a non-enumerated query, based on the current context, with some filtering applied.

Now, I want to add a new property to this DTO that is a pipe-delimited string, made up of code from a child property on the original system object. This is to be used for filtering on the client.

The navigation works as follows, each system has a product configuration, and each product configuration has a number of price book entries, each of which has a code. Therefore, I figured that I should be able to do the following...

  List<VRTSystemOverview> systems = vrtSystemsBasicQuery.OrderBy(s => s.DHRNumber)
    .Select(s => new VRTSystemOverview {
      ID = s.ID,
      SystemNumber = s.SystemNumber,
      PriceBookCodes = s.ProductConfiguration
        .ProductConfigurations_PriceBook
        .Select(pb => pb.PriceBook.ProductCode)
        .Aggregate("", (s1, a) => s1 += "|" + a)
    })
    .ToList();

However, the second "s1" in the Aggregate method is underlined in red, and the compiler gives an error of "An expression tree may not contain an assignment operator"

Anyone able to explain what this means, and how I get around it? I've done some searching, and can't find what the problem is. I tried reading up on expression trees, but couldn't really work out what they are, which could be the problem.

I've done exactly the same elsewhere in this project, but that was working with an in-memory collection, and not one using Linq to Entities, which may be the difference.

Upvotes: 2

Views: 6742

Answers (3)

Avrohom Yisroel
Avrohom Yisroel

Reputation: 9440

OK, well it seems that this can't be done. Thinking about it, it makes sense, as you can't do the equivalent of Aggregate in SQL, so L2E can't translate it.

I found help in a similar question right here on SO, specifically the suggestion there (from adrift) to create an anonymous type that contained the data required, then enumerate the query, and finally (when we're dealing with an in-memory collection, so are using Linq To Objects) do the aggregation.

This worked correctly, but increased the execution time of the query by a factor of ten! I decided to add the price book codes as a child collection on the main DTO, which was fast and simple, and then filter on the collection in the client. This makes the data being sent to the client slightly larger, but keeps the query execution speed down.

Hope this helps someone. Thanks to those who replied.

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726489

Your aggregate shouldn't assign the value, it should return the value of the sum:

List<VRTSystemOverview> systems = vrtSystemsBasicQuery.OrderBy(s => s.DHRNumber)
 .Select(s => new VRTSystemOverview {
   ID = s.ID,
  SystemNumber = s.SystemNumber,
  PriceBookCodes = s.ProductConfiguration
    .ProductConfigurations_PriceBook
    .Select(pb => pb.PriceBook.ProductCode)
    .Aggregate("", (s1, a) => s1 + "|" + a)
 })
.ToList();

I am not sure if this is going to work well in EF (in fact, I'm almost sure that you are going to see a run-time error), but it should help you get past the compile issue.

Starting with .NET 4.0 you could use string.Join method with IEnumerable<T>, like this:

List<VRTSystemOverview> systems = vrtSystemsBasicQuery.OrderBy(s => s.DHRNumber)
 .Select(s => new VRTSystemOverview {
   ID = s.ID,
  SystemNumber = s.SystemNumber,
  PriceBookCodes = s.ProductConfiguration
    .ProductConfigurations_PriceBook
    .Select(pb => string.Join("|", pb.PriceBook.ProductCode))
 })
.ToList();

This should work well for in-memory operations.

To get past the runtime error, try this:

List<VRTSystemOverview> systems = vrtSystemsBasicQuery.OrderBy(s => s.DHRNumber)
 .Select(s => new VRTSystemOverview {
   ID = s.ID,
   SystemNumber = s.SystemNumber,
   PriceBookCodeList = s.ProductConfiguration
    .ProductConfigurations_PriceBook
    .Select(pb => pb.PriceBook.ProductCode)
    .ToList()
 })
.AsEnumerable()
.Select(s => new {
    ID = s.ID,
    SystemNumber = s.SystemNumber,
    PriceBookCodes = string.Join("|", s.PriceBookCodeList)
});

The idea is to bring the formation of the string into memory.

Upvotes: 2

Ocelot20
Ocelot20

Reputation: 10800

The += on the .Aggregate( line is like: s1 = s1 + "|". Do you actually mean to change the value of s1? If not, just use s1 + "|" + a.

Upvotes: 1

Related Questions