Comma separated values (with defined ordering) from groups by LINQ; can't create a constant value, only primitive types or enumeration types supported

Question

I have the following 2 tables.

Table1:

Number     Table2ID       Count
======     ========       =====
1          1              3
1          2              5
1          4              2
1          5              4
2          1              6
2          3              2
2          2              4
2          5              3


Table2:

ID     Code     Sequence
==     ====     ========
1      AA       1
2      BB       2
3      CCC      3
4      D        4
5      EE       5

Using these tables, I want to get the following result:

Number     Codes
======     =====
1          AA, BB, D, EE
2          AA, BB, CCC, EE

For this purpose, I wrote the following query (according to this answer):

from tempResult in (from t1 in Table1
                    join t2 in Table2
                    on t1.Table2ID equals t2.ID
                    select new
                    {
                        Number = t1.Number,
                        Code = t2.Code,
                        Sequence = t2.Sequence
                    })
group tempResult by tempResult.Number into groupedTempResult
select new
{
   Number = groupedTempResult.Key,
   Codes = string.Join(", ", groupedTempResult.OrderBy(x => x.Sequence).Select(x => x.Code))
}

Upon executing this query, I faced the following exception (as mentioned in the comment of that answer):

LINQ to Entities does not recognize the method 'System.String Join(System.String, System.String[])' method, and this method cannot be translated into a store expression.

To fix this exception, I made the following correction according to this answer:

from tempResult in (from t1 in Table1
                    join t2 in Table2
                    on t1.Table2ID equals t2.ID
                    select new
                    {
                        Number = t1.Number,
                        Code = t2.Code,
                        Sequence = t2.Sequence
                    }).AsEnumerable()
group tempResult by tempResult.Number into groupedTempResult
select new
{
   Number = groupedTempResult.Key,
   Codes = string.Join(", ", groupedTempResult.OrderBy(x => x.Sequence).Select(x => x.Code))
}

After this modification, I started getting the following exception while executing the query:

Error: Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context.

To understand in which select clause I am having problem, I declared a class for each of the select clause - for this reason the query got modified to this:

from tempResult in (from t1 in Table1
                    join t2 in Table2
                    on t1.Table2ID equals t2.ID
                    select new TempResult
                    {
                        Number = t1.Number,
                        Code = t2.Code,
                        Sequence = t2.Sequence
                    }).AsEnumerable()  // converted the result as enumerable 
group tempResult by tempResult.Number into groupedTempResult
select new Result
{
   Number = groupedTempResult.Key,
   Codes = string.Join(", ", groupedTempResult.OrderBy(x => x.Sequence).Select(x => x.Code))
}

After this modification, I got the following exception:

Error: Unable to create a constant value of type 'Namespace.Name.Result'. Only primitive types or enumeration types are supported in this context.

So from my understanding, the last select clause is where the exception is happening.

I tried to follow the answers of the following 1, 2 questions as much as I could - resulting a new version of the query.

from tempResult in (from t1 in Table1
                    join t2 in Table2
                    on t1.Table2ID equals t2.ID
                    select new TempResult
                    {
                        Number = t1.Number,
                        Code = t2.Code,
                        Sequence = t2.Sequence
                    }).AsEnumerable()
                      .GroupBy(x => x.Number)
                      .Select(x => new Result { Number = x.Key, Codes = string.Join(", ", x.OrderBy(y => y.Sequence).Select(y => y.Code)) })
select tempResult

This did not solve the exception mentioned earlier.

At this point, I am very much out of ideas about how can I get my desired result.

Any help regarding this issue is highly appreciated.

A point to note that I have to join the result of this query / operation with another query. So breaking this query to multiple statements / operations is not what I am looking for.

Edit: Let me try to clarify on how I am trying to use this query actually.

from otherResult1 in resultFromAnotherQuery1
join result in (from tempResult in (from t1 in Table1
                                    join t2 in Table2
                                    on t1.Table2ID equals t2.ID
                                    select new TempResult
                                    {
                                        Number = t1.Number,
                                        Code = t2.Code,
                                        Sequence = t2.Sequence
                                    }).AsEnumerable()
                                      .GroupBy(x => x.Number)
                                      .Select(x => new Result { Number = x.Key, Codes = string.Join(", ", x.OrderBy(y => y.Sequence).Select(y => y.Code)) })
                select tempResult).ToList()
on otherResult1.Number equals result.Number
join otherResult2 in resultfromAnotherQuery2
on otherResult1.ColumnA equals otherResult2.ColumnB
.....
select new FinalResult
{
    .......
    Codes = result.Codes,
    .......
}

If I skip this joining with result and otherResult1 and ignore populating the Codes field in FinalResult class - just work with otherResult1 and otherResult2, there is no problem to execute the query. But when I try to do this join, I face the exception mentioned in the question.

I have edited the question to show how I am trying to use the query I provided. — Muntasir, Jul 19 '20 at 02:18

Gert Arnold · Accepted Answer · 2020-07-19T09:56:16.200

In the end, it all boils down to a construction like

from q in resultFromAnotherQuery1
join o from listOfObjects on ...
select new { }

Here listOfObjects is everything between from tempResult in and ToList() in the last code snippet. The join with resultfromAnotherQuery2 isn't important for the answer.

I wish the creators of EF would have come up with a somewhat more palatable exception message for this frequently occurring error. Something like:

EF is trying to translate the entire statement [statement] into SQL, but the local sequence 'listOfObjects' can only be translated if it contains primitive values.

Unfortunately, the part that created the local sequence initially threw another exception ("LINQ to Entities does not recognize the method..."). After you solved that, you bumped into this second exception. Since you were totally focused on the local sequence and weren't helped by the cryptic message, you kept looking for a solution there. But the exception had now silently moved to the entire statement.

The solution is to compose everything until select new FinalResult from IQueryables. Only then add AsEnumerable() and then .Select(x => new FinalResult { ... }) where you can do stuff that EF doesn't support.

That means that you have to suspend the string.Join part until the very end:

.Select(x => new FinalResult
{
    .......
    Codes = string.Join(", ", x.Codes))
    .......
})

...where x.Codes is composed by something like...

Codes = groupedTempResult.OrderBy(x => x.Sequence).Select(x => x.Code)

Because my query did not create the `FinalResult` class inside the `.Select()` method, I had to enclose my existing query with brackets and then chain it with `.ToList()` and then `.Select()` consecutively. But yes, eventually your suggestion solved the problem. Thanks for the suggestions and explanation. — Muntasir, Jul 19 '20 at 13:52

score -1 · Answer 2 · answered Jul 18 '20 at 15:28

In short: what you need here is the forced execution of the first part of the query. Switch the .AsEnumerable() to .ToList() to force the execution at that point.

A bit longer: the first query fails because it fails to translate String.Join to a SQL equivalent. Introducing the AsEnumerable doesn't force the immediate execution and in this case just moves the problem down the line (I'm not sure about the details, but the look at the implementation of GroupBy should explain the reason). Hence the ToList to force the execution.

Alternatively you could look at ToLookup instead of GroupBy.

(based on this older answer, but also see documentation and also this answer might shed some light on the differences between AsEnumerable and ToList)

I tried both replacing the `.AsEnumerable()` with `.ToList()` and replacing the `GroupBy()` with `ToLookup()` separately in the query resulting no change in outcome in each case. You can have a look at the updated question to see if you can suggest something else. — Muntasir, Jul 19 '20 at 02:31
`.AsEnumerable()` doesn't force execution, but it does force execution in memory. Your answer isn't correct and it was based on incomplete code in the original question. — Gert Arnold, Jul 20 '20 at 07:07

Comma separated values (with defined ordering) from groups by LINQ; can't create a constant value, only primitive types or enumeration types supported

2 Answers2