Group Join Select With Comma Separated

Question

I have a query Like below in .net Core 2.2 with EF.

var data = context.Customer
                    .GroupJoin(context.Orders, c=> c.Id, o => o.CustoerId, (c, o) => new
                    {
                        customer = c,
                        orders= o
                    }).Select(s => new
                    {
                        s.customer.Name,
                        s.customer.Id,
                        AllOrdersRef = s.orders == null ? null : string.Join(", ", s.orders.Select(x => x.UniquRef))
                    });

It gives Error

The LINQ expression 'Count()' could not be translated and will be evaluated locally.

All I want is Comma Separated Value in AllOrdersRef. Also I dont want to use ToList().

Are you sure this is where you get the error? There is no `.Count` here — Gilad Green, Mar 18 '19 at 07:05
I think `string.Join` will not be use in `IQuerable` where you use linq on `DbSet` and `DbSet` so that why. might be you got some othe exception also — er-sho, Mar 18 '19 at 07:08
I believe you getting error on the right after of "c.customer.Id" because you dont have comma. — justinmontalban, Mar 18 '19 at 07:09
Why on earth would you want to string join UniqueRefs into a single string? — Erik Philips, Mar 18 '19 at 08:48

Slava Utesinov · Answer 1 · 2019-03-18T11:34:45.190

0

You should make join query and then process it's result on client side:

var data = (from c in context.Customer
            join o in context.Orders on c.Id equals o.CustomerId into subs
            from sub in subs.DefaultIfEmpty()
            select new 
            {
                 c.Id,
                 c.Name,
                 UniquRef = sub == null ? null : sub.UniquRef
            }).ToList();

var result = data.GroupBy(x => new { x.Id, x.Name }).Select(x => new 
             {
                 x.Key.Id,
                 x.Key.Name, 
                 AllOrdersRef = (x.Count() == 1 && x.First().UniquRef == null) ? null
                                    : String.Join(", ", x.Select(y => y.UniquRef))
             });

edited Mar 18 '19 at 11:34

answered Mar 18 '19 at 07:30

Slava Utesinov

13,410
2
19
26

I think OP doesn't want `.ToList()` where he/she expect query return `IQuerable`. – er-sho Mar 18 '19 at 07:33
1

@er-sho, `String.Join` can't be translated to SQL, so `IQuerable` also can't be achived, only `IEnumerable`. – Slava Utesinov Mar 18 '19 at 07:37
@SlavaUtesinov, there I performed join. But the Problem is I don't want it to evaluated locally by using ToList(). By the way thanks for the answer. – AKASH Mar 19 '19 at 03:54

Amir Molaei · Answer 2 · 2019-03-18T09:01:36.173

Regarding Sql Server (if it's your DBMS), there are two main functions that do the string concatenation on group by output, XML Path and String_Agg (from Sql Server 2017) none of which is supported in linq to sql. To the best of my knowledge, it is not supported for other DBMSes either, so the only way you have is to cast it to Enumerable before string.Join.
For this, you just need to call ToList() before Select:

var data = context.Customer
                    .GroupJoin(context.Orders, c=> c.Id, o => o.CustoerId, (c, o) => new
                    {
                        customer = c,
                        orders= o
                    })
                    .ToList()
                    .Select(s => new
                    {
                        s.customer.Name,
                        s.customer.Id
                        AllOrdersRef = s.orders == null ? null : string.Join(", ", s.orders.Select(x => x.UniquRef))
                    });

Group Join Select With Comma Separated

2 Answers2