Wednesday, June 2, 2010

LINQ for getting unique counts of an item in a DataTable

Here is an efficient way to get a unique count using LINQ from the results of a DataTable:

// DataTable produced by LLBGen stored procedure method DataTable
servicesInOrder = RetrievalProcedures.GetMyServicesWithinGroups(orderId);


// Do a quick count to see whether multiple services are within each service group associated with this order

var result = from row in servicesInOrder.AsEnumerable()

group row by new

{
ServiceGroup = row.Field("PackageGroupName")
} into grp

select new
{
ServiceGroup = grp.Key.ServiceGroup,
Services = grp.Count(),

UniqueServices = (from p in grp
select p.Field
("ServiceID")).Distinct().
Count()
};

The Results from a runtime watch look like:




I changed it up a bit here to make the query more specific by returning the count for each specific group. I then use the value of this count to determine further processing logic. Below is the revised LINQ query, and the usage of its results. All relevant entries are highlighted in yellow.

if (servicesInOrder != null)
{
// If services are found, find the DataTable column, and append the services and associated values to the DataTable row.
for (int j = 0; j <>
{
DataRow serviceRow = servicesInOrder.Rows[j];
if (serviceRow != null)
{

// Do a quick count to see whether multiple services are within each service group associated with this order
var result = from row in servicesInOrder.AsEnumerable()
where row.Field("PackageGroupName") ==
serviceRow["PackageGroupName"].ToString()

group row by new
{

ServiceGroup = row.Field("PackageGroupName")
} into grp
select new
{
ServiceGroup = grp.Key.ServiceGroup,
Services = grp.Count(),
UniqueServices = (from p in grp
select p.Field("ServiceID")).Distinct().Count()
} ;

if (serviceRow["PackageServiceID"] != DBNull.Value)
{
// Add services within service group if order has it
int thisServiceGroupId = Convert.ToInt32(serviceRow["PackageGroupID"]);
if (thisServiceGroupId == serviceGroupId)
{
if ((result.Count() > 0) && (result.ElementAt(0).UniqueServices > 1))
newDataRow[col.ColumnName] += "
" + serviceRow["ServiceName"].ToString();

else
newDataRow[col.ColumnName] = serviceRow["ServiceName"].ToString();

if (serviceRow["PackageServiceID"] != DBNull.Value)
{
// Add services within service group if order has it
int thisServiceGroupId = Convert.ToInt32(serviceRow["PackageGroupID"]);
if (thisServiceGroupId == serviceGroupId)
{
if ((result.Count() > 0) && (result.ElementAt(0).UniqueServices > 1))
newDataRow[col.ColumnName] += "
" + serviceRow["ServiceName"].ToString();

else
newDataRow[col.ColumnName] = serviceRow["ServiceName"].ToString();