Chapter 8 : Reading Objects with LINQ to SQL

It uses the mapping of classes to tables to translate LINQ queries to SQL commands and then materializes objects from the rows returned. The objects can be related to each other in a graph of objects that is managed by LINQ to SQL on your behalf.

Performance and Security

LINQ to SQL supports a powerful set of abstractions defined in the C# language. In addition to enabling a rich set of query operators, it lets you navigate relationships and handles inheritance hierarchies. LINQ to SQL accomplishes this by automatically providing efficient translation to SQL. However, knowledge of a few more details is helpful for ensuring the best performance and secure usage. This section looks at some additional aspects of query translation to help you avoid common traps and pitfalls.

Query Versus Results

LINQ to SQL uses the deferred execution model defined for all LINQ components. There is no explicit " = " " method that you need to call to get the results of a LINQ to SQL query. Aquery is executed only when you enumerate its results. As explained in Chapter 4, "C# 3.0 Technical Overview," this enables composition at almost no cost. The simplest example is the properties of type in the & ? = class. , E , and so on are all queries corresponding to entire tables in the database; they are not local collections containing all the or E entities in your app domain. The same is true of query expressions composed on top of tables-say, customers from Spain. They represent queries that can be and need to be executed for results after all the desired composition. They let you avoid retrieving a large result set for local execution. You don't need all entities in memory first to find customers from Spain. Such is the essential power of deferred execution. However, deferred execution also has two key implications for performance: latency for the first object in the result, and repeated execution if the results are not stored in a local collection.

First, the cost of query translation and execution is deferred until the results are enumerated. Hence, when you " over the results, the call
to # is where you pay the price. Merely defining a query does not eliminate the latency for subsequent usage. Generally this is a beneficial feature, because you can define queries without worrying about their usage on various code paths. If you do not enumerate the results of a query, you do not pay the cost of translation and execution. Second, re-enumerating the same query triggers another generation and execution of the corresponding SQL commands. No implicit caching of results occurs. Again, this is essential if you need to get the current results of the query when the second enumeration is done. However, this can result in significant cost if you treat a query like a list and try to enumerate it multiple times, as shown in the following code fragment:

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

II Some more work ...

II GetEnumerator() causes query translation, SQL query execution

II and object materialization. foreach(Customer c in CustomerQuery) {...} II The query is executed again foreach(Customer c in CustomerQuery) {...}

Fortunately, here you can have your cake and eat it, too. If you want to prepay the price of query translation and execution, or if you want to enumerate the results multiple times, you can simply put the results in a list by calling  or 6 on the query at the point of your choice, as shown in the following code line. Then you can use the list or array or whatever local collection you want to use as many times as you like without incurring any additional translation, execution, or object materialization
costs.

List<Customer> CustomerList = CustomerQuery.ToList();

The list or array is a little "cache" of results that you can reuse. Of course, as in the case of any cache, you have to think about whether it is worth prepaying the cost, whether all objects in the cache are really needed, and whether stale data is a problem. But its creation and use are completely under your control.  and 6 are convenience APIs for getting a list and an array of the result types, respectively. However, you could just as easily add the objects to a collection of your choice and use it like a cache. Another example of the difference between a deferred query and a result is a query expression of a nondeferrable type. The following queries return results of type and , respectively. Hence, they do not require any enumeration and do not provide deferred execution. The results are produced when the assignment statement is executed.

int count = db.Customers.Count(); Customer cust = db.Customers.First();

Compiled Queries

Caching by enumerating the results is a fine way to reuse the results. But quite often you want to use the same query with different parameters. For example, if a form shows Orders for a customer, you want to be able to execute a query for orders for a given customer. Many applications provide results based on a set of parameters obtained from a UI form or another application. In such cases, cached results may not be useful, because the parameter values change from one execution to another, so caching the results for all parameter values may be impractical. Furthermore, if the data in the database keeps changing, the cached results of a query are likely to get stale. Compiled queries provide an interesting capability to handle such cases.

They allow you to compile or pretranslate a LINQ query with slots for specified parameters. Then, as soon as you know the values for the parameters, you can execute the "compiled" or pretranslated query by plugging in the values. If you use the query 10 times, the cost of translating the query is amortized over 10 executions. The greater the number of executions or the more complex the query, the more you are likely to save with compiled queries.

II Define a compiled query

var OrdByCustld = CompiledQuery.Compile( (NorthwindDataContext context, string custld) => from o in context.Orders

where o.CustomerlD == custld select o);

II Execute the compiled query string cust = 276AROUT”;

var q = OrdByCustld(db, cust);

In fact, you can use this in a more stylized fashion by statically defining a set of compiled queries and then using them in response to a request.For each new request, you can simply execute the compiled query with the appropriate parameters. This pattern is particularly handy in case of web applications where you want to handle a large number of parameterized requests with minimum response time without holding a lot of state. Thus, compiled queries provide a mid-tier alternative to a T-SQL stored procedure or table-valued function and can be used as a complementary technology.

static class Queries

{

public static Func<NorthwindDataContext, string, lQueryable<Order>> OrdByCustld = CompiledQuery.Compile(

(NorthwindDataContext context, string custld) =>
from o in context.Orders where o.CustomerlD == custld select o);
 
II More compiled queries for common query patterns
}

Compiled queries are in some ways analogous to the query and stored procedure cache maintained by a relational database such as SQL Server. However, the SQL Server cache can be used across multiple executions of applications. LINQ to SQL constructs, like compiled queries, live only as long as the containing CLR application domain lives. Hence, SQL Server uses implicit caching, whereas LINQ to SQL enables explicit caching and avoids implicit caching. This allows the application developer to decide which queries should be compiled and cached for use in a CLR application domain's lifetime.

The translation of compiled queries is independent of the data. Hence, the translation can be materially different in certain cases. The most important distinction is the use of a value for a parameter. In the case of noncompiled queries, if the value of a parameter is known to be at the
time of translation, LINQ to SQL generates a special check for required by SQL- &X or &E &X, as appropriate. However, in the case of a compiled query, the translation is independent of data. Hence, values should not be passed to a compiled query; they must be handled separately as constants in a query rather than as parameters.