CODEWITHSUNDEEP
c#linq-to-sql
coure2011
coure2011

Reputation: 42444

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
Street2,
Street1,Street2,Street3

Now count should be like
Street1 (3)
Street2 (4)
Street3 (2)

Upvotes: 1

Views: 1166

Answers (2)

Dean Chalk
Dean Chalk

Reputation: 20461

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

Upvotes: 1

Ani
Ani

Reputation: 113402

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.

Upvotes: 2

Related Questions