Monday, November 8, 2010

Linq query operators :Part 3

AsEnumerable Operator

I found the AsEnumerable operator to be really important in understanding where the query gets executed, meaning is it going to get converted to SQL and the query would be performed on SQL server or LINQ to objects would be used and query would be performed in memory. The ideal use I have found for AsEnumerable would be when I know that a certain functionality is not available in SQL server, I can perform part of the query using LINQ to SQL (Iqueryable) and the rest executed as LINQ to objects (IEnumerable<T>). Basically, AsEnumerable is a hint to perform this part of the execution using LINQ to objects. This is how the prototype looks:

public static IEnumerable AsEnumerable(

this IEnumerable source);

The prototype operates on the source of IEnumerable<T> and also returns an IEnumerable<T>. This is because standard query operators operate on IEnumerable<T>, whereas LINQ to SQL operates on IQueryable<T>, which also happens to implement IEnumerable<T>. So when you execute an operator like on an IQueryable <T> (domain objects), it uses a LINQ to SQL implementation for the where clause. As a result, the query gets executed on SQL Server. But what if we knew in advance that a certain operator would fail on SQL Server since SQL Server has no implementation for it. It's good to use the AsEnumerable operator to tell the query engine to perform this part of the query in memory using LINQ to objects. Let's see an example:

public static void AsEnumerableExample()

{

NorthwindDataContext db = new NorthwindDataContext();

var firstproduct = (from product in db.Products

where product.Category.CategoryName == "Beverages"

select product

).ElementAt(0);

Console.WriteLine(firstproduct.ProductName);

}

When you run this query, it would throw an exception saying that elementat is not supported because SQL Server does not know how to execute elementAt. In this case, when I add as enumerable, the query would execute fine as follows:

public static void AsEnumerableExample()

{

NorthwindDataContext db = new NorthwindDataContext();

var firstproduct = (from product in db.Products

where product.Category.CategoryName == "Beverages"

select product

).AsEnumerable().ElementAt(0);

Console.WriteLine(firstproduct.ProductName);

}

DefaultIfEmpty

The DefaultIfEmpty operator returns a default element if the input sequence is empty. If the input sequence is empty, the DefaultIfEmpty operator returns a sequence with a single element of default (T) which, for reference types, is null. Furthermore, the operator also allows you to specify the default operator that will be returned.

public static void DefaultIfEmptyExample()

{

string[] fruits = { "Apple", "pear", "grapes", "orange" };

string banana = fruits.Where(f => f.Equals("Banana")).First();

Console.WriteLine(banana);

}

The above example throws an exception because the first operator requires that sequence not be empty. Therefore if we were to use defaultifEmpty, this is how it would look:

public static void DefaultIfEmptyExample1()

{

string[] fruits = { "Apple", "pear", "grapes", "orange" };

string banana =

fruits.Where(f => f.Equals("Banana")).DefaultIfEmpty("Not Found").First();

Console.WriteLine(banana);

}

Another interesting use of DefaultIfEmpty is to perform a left outer join using GroupJoin. Here is an example that illustrates that:

public class Category

{

public string CategoryName { get; set; }

}

public class Product

{

public string ProductName { get; set; }

public string CategoryName { get; set; }

}

public static void LeftOuterJoin()

{

Category[] categories = {

new Category{CategoryName="Beverages"},

new Category{CategoryName="Condiments"},

new Category{CategoryName="Dairy Products"},

new Category{CategoryName="Grains/Cereals"}

};

Product[] products = {

new Product{ProductName="Chai",

CategoryName="Beverages"},

new Product{ProductName="Northwoods Cranberry Sauce",

CategoryName="Condiments"},

new Product{ProductName="Butter",

CategoryName="Dairy Products"},

};

var prodcategory =

categories.GroupJoin(

products,

c => c.CategoryName,

p => p.CategoryName,

(category, prodcat) => prodcat.DefaultIfEmpty()

.Select(pc => new { category.CategoryName,

ProductName = pc != null ? pc.ProductName : "No" })

).SelectMany(s => s);

foreach (var product in prodcategory)

{

Console.WriteLine("Category :{0}, Product = {1}", product.CategoryName,

product.ProductName);

}

}

In the example above, I am using left outer join to list all categories, regardless of whether they have any products or not.

No comments: