Count values from a column

I want to count distinct values in a column. Column contains data like this

Locations
Street1
Street2
Street3
Street1,Street2
Stree开发者_如何学运维t2,
Street1,Street2,Street3

Now count should be like

Street1 (3)

Street2 (4)

Street3 (2)

I can't think of an easy way of doing this with LINQ to SQL.

An easy way of course (if you can afford to do it), would be to bring all the rows in the table back to the client and then do the querying with LINQ to Objects.

var query = myDataContext
           .MyTable
           .AsEnumerable()
           .SelectMany(row => row.Locations.Split(','))
           .GroupBy(location => location)
           .Select(group => new { Location = group.Key, Count = group.Count() });

If you want a sequence of strings instead in that format, replace the last line with:

.Select(group => string.Format("{0} ({1})", group.Key, group.Count());

On another note, I would strongly recommend normalizing your database schema. It isn't a good idea to store delimited lists in a column.

var result = Locations
    .SelectMany(s => s.Split(','))
    .GroupBy(s => s, (loc,b) => new { loc, b.ToList().Count});