C# 3.0 Cookbook, 3rd Edition

1.9. Being Selective About Your Query Results

Problem

You want to be able to get a dynamic subset of a query result.

Solution

Use the TakeWhile extension method to retrieve all results until the criteria is matched:

	Northwind dataContext = new Northwind(Settings.Default.NorthwindConnectionString);

	var query =
	    dataContext.Suppliers.GroupJoin(dataContext.Products,
	        s => s.SupplierID, p => p.SupplierID,
	        (s, products) => new
	        {
	            s.CompanyName,
	            s.ContactName,
	            s.Phone,
	            Products = products
	        }).OrderByDescending(supplierData => supplierData.Products.Count())
	            .TakeWhile(supplierData => supplierData.Products.Count() > 3);


	Console.WriteLine("Suppliers that provide more than three products: {0}", query.
	Count());
	foreach (var supplierData in query)
	{
	    Console.WriteLine("    Company Name : {0}",supplierData.CompanyName);
	    Console.WriteLine("    Contact Name : {0}", supplierData.ContactName);
	    Console.WriteLine("    Contact Phone : {0}", supplierData.Phone);
	    Console.WriteLine("    Products Supplied : {0}", supplierData.Products.Count());
	    foreach (var productData in supplierData.Products)
	    {
	        Console.WriteLine("        Product: " + productData.ProductName);
	    }
	}

You can also use the SkipWhile extension method to retrieve all results once the criteria are matched:

	Northwind dataContext = new Northwind(Settings.Default.NorthwindConnectionString);

	var query =
	    dataContext.Suppliers.GroupJoin(dataContext.Products,
	        s => s.SupplierID, p => p.SupplierID,
	        (s, products) => new
	        {
	            s.CompanyName,
	            s.ContactName,
	            s.Phone,
	            Products = products
	        }).OrderByDescending(supplierData => supplierData.Products.Count())
	            .SkipWhile(supplierData =>
	      {
	        return supplierData.Products.Count() > 3;
	      });

	Console.WriteLine("Suppliers that provide three or less products: {0}",
	                 query.Count());
	foreach (var supplierData in query)
	{
	    Console.WriteLine("    Company Name : {0}",supplierData.CompanyName);
	    Console.WriteLine("    Contact Name : {0}", supplierData.ContactName);
	    Console.WriteLine("    Contact Phone : {0}", supplierData.Phone);
	    Console.WriteLine("    Products Supplied : {0}", supplierData.Products.Count());
	    foreach (var productData in supplierData.Products)
	    {
	        Console.WriteLine(" Product: " + productData.ProductName);
	    }
	}

Discussion

In this example using LINQ to SQL, the number of products each supplier provides is determined, and the result set is sorted in descending order by product count:

	var query =
	    dataContext.Suppliers.GroupJoin(dataContext.Products,
	        s => s.SupplierID, p => p.SupplierID,
	        (s, products) => new
	        {
	            s.CompanyName,
	            s.ContactName,
	            s.Phone,
	            Products = products
	        }).OrderByDescending(supplierData => supplierData.Products.Count())

From that result, the supplier data for suppliers is only accepted into the final result set if they provide more than 3 products and the results are displayed. TakeWhile is used with a lambda expression to determine if the product count is greater than 3, and if so, the supplier is accepted into the result set:

	       .TakeWhile(supplierData =>
	{
	  return supplierData.Products.Count() > 3;
	});

If SkipWhile was used instead, all of the suppliers that provide 3 or fewer products would be returned:

	      .SkipWhile(supplierData =>
	{
	  return supplierData.Products.Count() > 3;
	});

Being able to write code-based conditions allows for more flexibility than the regular Take and Skip methods, which are absolute based on record count, but keep in mind that once the condition is hit for either TakeWhile or SkipWhile, you get all records after that, which is why sorting the result set before using these is important.

The query also uses GroupJoin, which is comparable to a SQL LEFT or RIGHT OUTER JOIN, but the result is not flattened. GroupJoin produces a hierarchical result set instead of a tabular one, which is used to get the collection of Products by Supplier in this example:

	dataContext.Suppliers.GroupJoin(dataContext.Products,
	   s => s.SupplierID, p => p.SupplierID,

See Also

The "Enumerable.TakeWhile method," "Enumerable.SkipWhile method," and "Enumerable.GroupJoin method" topics in the MSDN documentation.