Linq Lambda groupby and projection problem

Question

If I have some data like: Model ModelName Code Aris 1.2 1 Aris 1.3 2 Corolla 1.1 3 Corolla 1.4 4

I would like to create a groupby query with a new projection something like:

var vehicles = _vehicleRepository.GroupBy(x=>new {x.Model,x.ModelName,x.Code})
                    .Select(g => new { Text = g.Key.Model + " - " + g.Key.ModelName, g.Key.Code })
                    .ToList();

It seems I am having difficulty with the Text = g.Key.Model + " - " + g.Key.ModelName and it causes SQL errors

Any tips appreciated

Answer 1

The simplest fix for this may simply be to perform the concatenation at the client instead:

var vehicles = _vehicleRepository.GroupBy(x => new {x.Model,x.ModelName,x.Code})
                .Select(g => g.Key)
                .AsEnumerable()
                .Select(k => new { Text = k.Model + " - " + k.ModelName, 
                                   k.Code })
                .ToList();

Here the AsEnumerable effectively makes the rest of the query just run at the call site, in .NET. I've included an extra projection to just get the key from each group, so that no more data is transferred than necessary.

Another option might be to use Distinct:

var vehicles = _vehicleRepository.Select(x => new {x.Model,x.ModelName,x.Code})
                .Distinct()
                .AsEnumerable()
                .Select(k => new { Text = k.Model + " - " + k.ModelName, 
                                   k.Code })
                .ToList();

It's possible that you don't need the call to AsEnumerable here - you could certainly try removing it.