Reading Objects with LINQ to SQL

Using LINQ and Databases
Relational databases contain a large amount of valuable data that applications want to use. An application written in a language such as C# can significantly benefit from querying and transforming this data using the power of LINQ. A straightforward but naive way of utilizing the LINQ capabilities would be to bring all the rows from one or more tables in memory as objects and then apply standard query operators to get exactly the objects you want. This would be very inefficient for two reasons: First, a large amount of data that is not a part of the query result may have to be brought from the database, wasting bandwidth, memory, and processing. Second, it would not utilize the power of the relational database query processor to optimize queries.

Relational databases such as Microsoft SQL Server provide very powerful query processors. The query processor includes a sophisticated optimizer that can find an efficient execution plan for complex queries and large amounts of data using indexes, statistics, and advanced algorithms. However, it is designed for processing SQL, which is all about tables and columns. If we want to get objects by querying this data using LINQ, we need to find a way to translate LINQ to SQL. As the name suggests, that is what LINQ to SQL is designed for. It provides the richness of LINQ while executing queries using the power of relational databases.

Translating LINQ to SQL

The magic of translating LINQ to SQL involves a beautiful dance between the C# compiler and the LINQ to SQL runtime that is a part of the .NET framework. The C# compiler translates LINQ queries to expression trees at compile time. Recall that this is how code is treated as data for easy composition and convenient collaboration among components. At runtime, LINQ to SQL translates the expression tree to SQL, executes the generated SQL, and converts the records obtained into objects. It uses the relational ADO.NET APIs to execute SQL and return results as records. Figure 8.1 shows this interaction between the components.



Figure 8.1 Compile-time and runtime handling of LINQ queries.

Consider the following simplified and & ? = classes to understand query translation:

using System.Data.Linq; [Table(Name="Customers")]

public class Customer

{

[Column(lsPrimaryKey=true)] public string CustomerlD; [Column]

public string City; [Column]

public string Country;

}
public partial class NorthwindDataContext : DataContext

{

public Table<Customer> Customers;

public NorthwindDataContext(string connection): base(connection) {}

}

You can use a very convenient logging feature of the ? = to monitor the generated SQL as follows. You need to use the appropriate connection string for the Northwind database on your machine. 

NorthwindDataContext db = new NorthwindDataContext(connectionstring);

db.Log = Console.Out;

Using this logger, you can see the translation of the following LINQ query for a class with ?, , and properties mapped to respective columns in the Northwind database:
 

var CustomerQuery = from c in db.Customers where c.Country == "Spain" select c;

The following SQL statement shows what is sent to the database for execution when the previous LINQ query is translated. The literal % is passed in as a parameter and is shown as a comment in the formatted SQL. 

SELECT [tO].[CustomerlD], [tO].[City],[tO].[Country] FROM [dbo].[Customers] AS [tO]

57ERE [tO].[Country] = IpO

---- IpO: lnput NrarChar (SiUe = <; Prec = O; Scale = O) [Spain]

Interestingly, the C# compiler knows nothing about LINQ to SQL. It simply determines the right extension method corresponding to the property in the ? = and the query operators and produces an expression tree. LINQ to SQL takes the expression tree and looks up the mapping to translate from expression tree to SQL. The mapping also helps LINQ to SQL materialize objects from the retrieved records. Thus, LINQ to SQL is one of many possible consumers of expression trees that uses its own mapping information to bring relational data smoothly into the world of LINQ.

Understanding the Nuances of Translation

LINQ queries can contain all kinds of expressions, like the rest of the C# program. In the previous query, the expression involved comparing the member with a constant. You also could have used another comparison operator or logical operators to form a more complex expression. Likewise, you could imagine using a method call as well. However, LINQ to SQL has to be able to translate the expression to its SQL counterpart. Hence, certain constraints on the expressions are supported. Three key categories of methods are supported:

• A rich subset of .NET framework methods and C# language operators
• Additional LINQ to SQL utility methods
• Mapped methods wrapping user-defined functions

The first category covers methods on the types defined in the base class library, such as and ? . LINQ to SQL supports most commonly used methods that can be reasonably and efficiently translated into SQL. For example, the following query returns customers based on a text search for cities starting with the string C+C. It uses the method 5 to do the text matching.
 

var CustomerQuery = from c in db.Customers

where c.City.StartsWith("M")select c;

The 5 method in this LINQ query is translated into the ; operator in SQL as follows:

SELECT [tO].[CustomerlD], [tO].[City], [tO].[Country] FROM [dbo].[Customers] AS [tO]

WHERE [tO].[City] LlKE IpO

----  IpO: lnput NrarChar (Size = 2; Prec = O; Scale = O) [M%]

The second category covers a few nifty T-SQL functions that don't have direct counterparts in the .NET framework. LINQ to SQL adds a small number of static methods specific to SQL Server to the namespace ?  $ $ in the $ + class. The methods and their overloads cover the following:

• The LIKE operator in T-SQL
• The difference between ? types in different units
• The raw length of a byte array, its LINQ to SQL counterpart-binary
  and string

Here is an example of such a method. It requires an additional statement.

using System.Data.Linq.SqlClient;

...

var CustQuery = from c in db.Customers

where SqlMethods.Like(c.City, "%on%")select c;

The third category covers scalar user-defined functions (UDFs) from the database mapped to a C# method. The details of this subject are covered in Chapter 10. Think of a method mapped to a UDF as a method for which a call can be translated into the corresponding UDF call while generating SQL. LINQ to SQL takes care of binding the parameters appropriately.

LINQ to SQL cannot translate a method or operator that does not belong to any of these categories. There is no direct way to take arbitrary C# code and its execution environment and produce corresponding SQL. As mentioned in the preceding section, the C# compiler does not know about LINQ to SQL or its translation constraints. Hence, the compiler may successfully translate a LINQ query containing such a call to an expression tree.

However, LINQ to SQL throws an exception at runtime when it attempts to translate the method call from the expression tree to SQL. This is the upshot of the clean separation between expression tree generation at compile time and its translation at runtime. Although LINQ queries provide a significant amount of protection through compile-time checking compared to SQL, they do not insulate you from runtime exceptions.

The Compiler Creates an Expression Tree; LINQ to SQL Creates SQL

LINQ to SQL uses mapping to translate the class members to database column references or SQL functions. It also understands common framework methods and provides additional utility functions similar to SQL functions. The compiler catches your mistakes if you don't use the right member reference or have a type mismatch. LINQ to SQL throws an exception if you use an unmapped class, property, or method. In either case, the error you get is in terms of your object model, so your life is simpler while debugging.