2

I have this LINQ query, I have problem with its Aggregate part: 

 Adresses = m.RelatedMultipleWorks.Count == 0 ? 
        m.RelatedCity + " + " + m.RelatedCounty + " + " + m.RelatedDistrict + " + " +
        m.RelatedNeighborhood + " + " + m.RelatedStreet + " + " + m.Road :
        m.RelatedCity + " + " + m.RelatedCounty + " + " + m.RelatedDistrict + " + " +
        m.RelatedNeighborhood + " + " + m.RelatedStreet + m.RelatedRoad + " | "
        m.RelatedMultipleWorks.Select(z => z.RelatedCity + " + " + z.RelatedCounty + " + " +
        z.RelatedDistrict + " + " + z.RelatedNeighborhood   + " + " + z.RelatedStreet + " + " + z.RelatedRoad)
            .Aggregate((current, next) => current + " | " + next),

And I get this exception.

{"LINQ to Entities does not recognize the method 'System.String Aggregate[String](System.Collections.Generic.IEnumerable1[System.String], System.Func3[System.String,System.String,System.String])' method, and this method cannot be translated into a store expression."}

Why am I getting this exception? How can I get rid of it? Thanks.

jason
  • 6,962
  • 36
  • 117
  • 198

2 Answers2

4

Aggregate has not translation to SQL, so Enumerate the results to memory:

.AsEnumerable().Aggregate((current, next) => current + " | " + next);

OR:

.ToList().Aggregate((current, next) => current + " | " + next);
Zein Makki
  • 29,485
  • 6
  • 52
  • 63
  • I added .AsEnumerable() and still getting the same error. Thanks. – jason Jun 29 '16 at 06:06
  • @jason Can you post the entire Query ? – Zein Makki Jun 29 '16 at 06:08
  • Other parts of the query is the same as this one. It's a very long query containing hundreds of line. – jason Jun 29 '16 at 06:12
  • @jason Do you have other `Aggregate` calls in other parts ? – Zein Makki Jun 29 '16 at 06:14
  • Yes, and I added AsEnumerable() before them as well – jason Jun 29 '16 at 06:15
  • @jason Are you sure you're getting the exact same error ? Maybe Linq To Entities is unable to recognize another different method. I think i can't help without posting the entire query. – Zein Makki Jun 29 '16 at 06:25
  • An Enumerable IS an IQueryable, and therefore won't return the objects. You have to call GetEnumerator() or ToList() to let the query execute. – DevilSuichiro Jun 29 '16 at 06:48
  • @DevilSuichiro Technically the query is not executed yet, but the query-definition stops at that point. Anything added (`.Where(..)`, etc) will not be translated to SQL, and will be executed in-memory. The whole thing (query + in-memory-processing) will not be started until you iterate over it (by calling `.ToList()`, `.ToArray()` or use it in a foreach). – Maarten Jun 29 '16 at 07:04
  • @Maarten I added `.ToList()` but I still get the same exception. – jason Jun 29 '16 at 07:28
  • @jason I think the linq statement you've shown is part of a bigger LINQ statement, and therefore calling `.ToList()` somewhere in the middle of it won't make a difference. You have to completely separate the part-that-can-be-translated-to-SQL from the part-that-cannot-be-translated. Can you show your complete LINQ query? – Maarten Jun 29 '16 at 07:33
3

The problem is that the .Aggregate() call is part of the projection to some type, and adding a .ToList() call inside the projection won't work since that projection is translated to sql as a whole. You cannot tell EF to translate half of the projection to SQL, and the other half not. You have to split the projection in two parts, and tell EF to translate the first part, but not the second.

Ok, to solve this. At the moment you have something like this. I can't be specific, and it will be different from your query, since you do not show the full query.

var result = ctx.SomeTable
    .Where(...whatever...)
    .Select(x => new SomeEntity {
        // lots of properties
        Adresses = m.RelatedMultipleWorks.Count == 0 ? 
            m.RelatedCity + " + " + m.RelatedCounty + " + " + m.RelatedDistrict + " + " +
            m.RelatedNeighborhood + " + " + m.RelatedStreet + " + " + m.Road :
            m.RelatedCity + " + " + m.RelatedCounty + " + " + m.RelatedDistrict + " + " +
            m.RelatedNeighborhood + " + " + m.RelatedStreet + m.RelatedRoad + " | "
            m.RelatedMultipleWorks.Select(z => z.RelatedCity + " + " + z.RelatedCounty + " + " +
            z.RelatedDistrict + " + " + z.RelatedNeighborhood   + " + " + z.RelatedStreet + " + " + z.RelatedRoad)
                .Aggregate((current, next) => current + " | " + next),
        // other properties
    })
    .ToList();

To eliminate the .Aggregate() call in the projection, you need to leave the datasource of the Aggregate intact so L2E can take that data from the database. After that you can apply the .Aggregate() in-memory.

var result = ctx.SomeTable
    .Where(...whatever...)
    // Change: Do not project to an entity, but to an anonymous type
    .Select(x => new {
        // lots of properties
        // Change: Removed the Aggregate-part
        Adresses = m.RelatedMultipleWorks.Count == 0 ? 
            m.RelatedCity + " + " + m.RelatedCounty + " + " + m.RelatedDistrict + " + " +
            m.RelatedNeighborhood + " + " + m.RelatedStreet + " + " + m.Road :
            m.RelatedCity + " + " + m.RelatedCounty + " + " + m.RelatedDistrict + " + " +
            m.RelatedNeighborhood + " + " + m.RelatedStreet + m.RelatedRoad + " | ",
        // Added: New property for the aggregate-datasource
        RelatedAdresses = m.RelatedMultipleWorks
            .Select(z => 
                z.RelatedCity + " + " + z.RelatedCounty + " + " + z.RelatedDistrict + " + " +
                z.RelatedNeighborhood   + " + " + z.RelatedStreet + " + " + z.RelatedRoad
            )
        // other properties
    })
    // Added: Call AsEnumerable to stop translating to SQL
    .AsEnumerable()
    // Added: Project again, fairly simple since all properties are already ok, but now call the Aggregate
    .Select(x => new SomeEntity {
        // lots of properties       
        Adresses = Adresses + RelatedAdresses.Aggregate((current, next) => current + " | " + next)
        // other properties
    })
    .ToList();
Maarten
  • 22,527
  • 3
  • 47
  • 68