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

Does it cause SQL errors, or does it cause exceptions on the client side about not being able to translate to SQL or something else related but not about an error executing the query? There is a huge difference. — jason, Nov 15 '10 at 08:38

score 2 · Accepted Answer · answered Nov 15 '10 at 07:15

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.

As I know your AsEnumerable() fetches all data, Is this true? If is, it's better to run a stored procedure for this not using linq. — Saeed Amiri, Nov 15 '10 at 09:32

Linq Lambda groupby and projection problem

1 Answers1

Linked