String concatenation in GroupBy

Question

This query below doesn't work because String.Join is not translatable.

PostgreSQL has the string_agg(expression, delimiter) feature though.

Is there anyway to use it from Linq?

var vwTourWithCategorieses = Context.Tours
                .Join(Context.TourCategories, t => t.TourId, tc => tc.TourId,
                    (t, tc) => new { t.TourId, t.Name, tc.CategoryId})
                .Join(Context.Categories, x => x.CategoryId, c => c.CategoryId,
                    (x, c) => new { x.TourId, TourName = x.Name, CategoryName = c.Name})
                .GroupBy(x => new { x.TourId, x.TourName },
                    (key, c) => new VwTourWithCategories
                    {
                        TourId = key.TourId,
                        Name = key.TourName,
                        Categories = string.Join(",", c.Select(i => i.CategoryName))
                    })
                .ToList();

ocuenca · Accepted Answer · 2016-04-22T17:22:37.313

Yes, unfortunately String.Join is not supported by EF, but I think you could project the result that you expect using Linq to objects after you materialize your query:

var query= Context.Tours
            .Join(Context.TourCategories, t => t.TourId, tc => tc.TourId,
                (t, tc) => new { t.TourId, t.Name, tc.CategoryId})
            .Join(Context.Categories, x => x.CategoryId, c => c.CategoryId,
                (x, c) => new { x.TourId, TourName = x.Name, CategoryName = c.Name})
            .GroupBy(x => new { x.TourId, x.TourName }).ToList()


var result=query.Select( g=> new VwTourWithCategories
                {
                    TourId = g.Key.TourId,
                    Name = g.Key.TourName,
                    Categories = string.Join(",", g.Select(i => i.CategoryName))
                });

If you want to see all the CLR methods that are supported, you can check this link.

Update

Your query could be simpler if you use navigation properties. I think that is a many to many relationship, so you could do something like this:

var query= Context.Tours.Select(t=> new 
                                    {
                                      t.TourId, 
                                      t.Name,
                                      CategoryNames = t.TourCategories.Select(tc=>tc.Category.Name)
                                    } 
                               ).ToList();

var result=query.Select( g=> new VwTourWithCategories
                {
                    TourId = g.Key.TourId,
                    Name = g.Key.TourName,
                    Categories = string.Join(",", g.Select(i => i.CategoryName))                 
                });

That's returning much more data though. I think its better to fetch the tours and then fetch the relevant categories separately. — Ian Warburton, Apr 22 '16 at 16:35
But why so you say is fetching more data that you expect? in the last join you re not projecting just three columns?Another question, are you using navigation properties to represent the relationships between your entities? — ocuenca, Apr 22 '16 at 16:55
Oh sorry, I thought your query had left my join intact. Had that been the case, then it would have returned the same tour data for every category assigned. That's why I figured it would be more data. I don't know how your database query gets translated to SQL. — Ian Warburton, Apr 22 '16 at 17:08

String concatenation in GroupBy

1 Answers1

Update