Monday, August 20, 2007

LINQ to SQL Conversion Operations

by wwagner via Bill Wagner: C# Development Blog | MySmartChannels on 8/20/2007 8:17:00 PM

Throughout the LINQ to SQL samples, I've been stressing the deferred execution nature of LINQ queries. To refresh, deferred execution means that when you create a LINQ query, the query expression holds an Expression Tree, not the results of the query. The expression tree gets converted to SQL, to be executed at the server. The actual results are only returned when the query is executed.

That is the behavior you want most of the time. It minimizes the number of trips to the database. You see, each additional query expression merely creates a new Expression Tree (which still contains the old BLOCKED EXPRESSION, rather than making another trip to the database to grab new content. You only make a trip to the database when it's time to actually enumerate all those entries.

But, other times, you want to operate on the series locally. When that's the case, you can direct LINQ to SQL to execute the query and return the results immediately. There are a number of APIs that do this for you. They differ in what kind of a collection they build.

First, there is the AsEnumerable() method. AsEnumerable simply changes the compile time type of a query from something that implements IEnumerable<T> to IEnumerable<T>. Sounds pretty useless, right? Not so fast. AsEnumerable() does do something: It removes any specific implementations a class may have made to any extension method defined on IEnumerable<T> in the Queryable class. It sounds very strange, but an example will make it very clear.

This query:

var q = from p in db.Products
    where p.UnitPrice > 10m
    select p;

			

uses the Table<T>.Where() method. That's because db.Products is a database table. The result of that behavior means that the query is translated into SQL, and gets executed at the server.

However, AsEnumerable() changes that:

var q = from p in db.Products.AsEnumerable()
    where p.UnitPrice > 10m
    select p;

			

Now, AsEnumerable() means the compiler views the db.Products as an IEnumerable<T>, not as a Table<T>. The result is that your query executes locally. However, you still need to get all the products. In this version, the entire product table is returned because you'll enumerate the entire table. The filter (p.UnitPrice > 10m) will be executed client side.

One reason you would do this is that sometimes you might make a query that can't be converted to T SQL. Consider this bit of code:

public void LinqToSqlConversion01() {
    var q =
        from p in db.Products.AsEnumerable()
        where isValidProduct(p)
        select p;

    ObjectDumper.Write(q);
}

private bool isValidProduct(Product p) {
    return p.ProductName.LastIndexOf('C') == 0;
}

				

Well, isValidProduct can't very well be turned into T-SQL, so you need the AsEnumerable() conversion on the table. (If you remove it, the example above will throw an exception).

AsEnumerable() doesn't actually change anything, it just modifies how the compiler views a sequence. Two other methods: ToArray() and ToList() actually modify the sequence, copying it into an Array, or List<T>, respectively. Why would you do that? Well, that caches the results of the query on the client side. That would mean you could continue to reuse that collection repeatedly in your application. For example, in the Northwind database, I'd consider grabbing the entire categories table, rather than querying it each time.

Finally, there is ToDictionary(). ToDictionary() translates a sequence into a dictionary, which stores key / value pairs. You write one, or two lambda expressions that describe how to map your sequence into a set of keys, and optional values.

For example, this creates a set of keys containing the product ID, and the value is the product.

var q =

				from p in db.Products

				where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued

				select p;

Dictionary<int, Product> qDictionary = q.ToDictionary(p => p.ProductID);

			

This version creates a set of Key / Value pairs where the key is the ID, and the value is an anonymous type containing the product name, the supplier, and the units in stock:

var q =

				from p in db.Products

				where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued

				select p;

// Var needed now because of the anonymous type
var qDictionary = q.ToDictionary(p => p.ProductID,
    p => new { p.ProductName, p.Supplier.CompanyName, p.UnitsInStock });

			

That's all for today. See you soon with direct SQL in LINQ to SQL.