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();

Answer 1

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))                 
                });