Thursday, December 17, 2009

LINQ to SQL : Understanding Query Execution

Query Execution

Whether you write a query as a high-level query expression or build one out of the individual operators, the query that you write is not an imperative statement executed immediately. It is a description. For example, in the declaration below the local variable q refers to the description of the query not the result of executing it.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

The actual type of q in this instance is IQueryable<Customer>. It's not until the application attempts to enumerate the contents of the query that it actually executes. In this example the foreach statement causes the execution to occur.

An IQueryable object is similar to an ADO.NET command object. Having one in hand does not imply that a query was executed. A command object holds onto a string that describes a query. Likewise, an IQueryable object holds onto a description of a query encoded as a data structure known as an Expression. A command object has an ExecuteReader() method that causes execution, returning results as a DataReader. An IQueryable object has a GetEnumerator() method that causes the execution, returning results as an IEnumerator<Customer>.

Therefore, it follows that if a query is enumerated twice it will be executed twice.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
// Execute first time
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);
// Execute second time
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

This behavior is known as deferred execution. Just like with an ADO.NET command object it is possible to hold onto a query and re-execute it.

Of course, application writers often need to be very explicit about where and when a query is executed. It would be unexpected if an application were to execute a query multiple times simply because it needed to examine the results more than once. For example, you may want to bind the results of a query to something like a DataGrid. The control may enumerate the results each time it paints on the screen.

To avoid executing multiple times convert the results into any number of standard collection classes. It is easy to convert the results into a list or array using the standard query operators ToList() or ToArray().

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
// Execute once using ToList() or ToArray()
var list = q.ToList();
foreach (Customer c in list)
   Console.WriteLine(c.CompanyName);
foreach (Customer c in list)
   Console.WriteLine(c.CompanyName);

This post is modified version of article "Introduction to LINQ" given at MSDN

Namaste !

Anugrah Atreya


Tuesday, December 15, 2009

LINQ : CopyToDataTable Where the Generic Type T Is Not a DataRow

Hi All,

Last week I was working on a LINQ2DataSet and LINQ2XML based application. There I happened to use .CopyToDataTable() method.

DataSet ds = new DataSet();

ds.ReadXml("C:\TestXML");

var query = from row in ds.Tables[0].AsEnumerable()

where row.Field<string>("IsActive") == "1"

select new { ID = row.Field<string>("ID"), UserName = row.Field<string>("UserName") };

DataTable dtResults = query.CopyToDataTable();

I got an error something like this:

The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable<T>(System.Collections.Generic.IEnumerable<T>)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'.

This error will go if you select complete DataRow like this:

DataSet ds = new DataSet();

ds.ReadXml("C:\TestXML");

var query = from row in ds.Tables[0].AsEnumerable()

where row.Field<string>("IsActive") == "1"

select row;

DataTable dtResults = query.CopyToDataTable();

The root cause of this issue is the defination of CopyToDataTable ().

Simply right click on function name and select "GoTo Defination".

public static DataTable CopyToDataTable<T>(this IEnumerable<T> source) where T : DataRow;

You can see that CopyToDataTable<T> is restricted for DataRow by "where T : DataRow" clause.

In case you have to have this working on the purely generic type (i.e.runtime DataRow object) you need to use custom method CopyToDataTable2() for which you need to manually add these extra files to your project as mentioned on these links:

http://msdn.microsoft.com/en-us/library/bb669096.aspx

Namaste !

Anugrah Atreya