Using a Linq query to group and crosstab/pivot data

April 11, 2011 Uncategorized Comments Off on Using a Linq query to group and crosstab/pivot data

I needed to get values from a grid. On the server side I didn’t have a rows collection so I needed to resort to using the Request.Form to get all the values that were sent to the server.

By using Linq with a group by I was able to get the values grouped and crosstabed/pivoted to store the values in the database.

The following is the linq that I used:

private IEnumerable<KeyValuePair<string, string>> KeyValuePair(NameValueCollection nvc) {
    if (nvc == null) {
        throw new ArgumentNullException("Named Value Collection");
    }
    return nvc.Cast<string>().Select(key => new KeyValuePair<string, string>(key, nvc[key]));
}
var query = from KeyValuePair<string, string> kvp in KeyValuePair(Request.Form)
            let index = kvp.Key.IndexOf("_") > 0 ? kvp.Key.IndexOf("_") : kvp.Key.Length
            let Key = kvp.Key.Substring(0, index)
            group kvp by new { Key } into g
            where g.Key.Key.Contains("Results$cell")
            select new
            {
                Key = g.Key.Key,
                id = g.Where(k => k.Key.Contains("id")).First().Value,
                completed = g.Where(k => k.Key.Contains("completed")).First().Value,
                comments = g.Where(k => k.Key.Contains("comments")).First().Value,
            };

Comments are closed.