Interpreted Queries in LINQ .
In this article we will look deeper in to Interpreted Queries and how it works. LINQ provides 2 parallel architectures namely local queries and the interpreted queries. Look into the example for the clarity on concepts and the execution part on how the interpreted queries work.
Interpreted Queries in LINQ
LINQ provides two parallel architectures: local queries and interpreted queries.
Local queries operate over collections implementing IEnumerable<> interface and tend resolve to query operators in the Enumerable class. The delegates that they accept whether it is expressed in comprehension syntax, lambda syntax, or traditional delegates are fully local to Intermediate Language (IL) code just as any other C# method.
The query operators in Enumerable can actually work with IQueryable<> sequences. The difficulty is that the resultant queries always execute locally on the client—this is why a second set of query operators is provided in the Queryable class.
IQueryable<> is an extension of IEnumerable<> with additional methods for constructing expression trees. Most of the time you can ignore the details of these methods; they're called indirectly by the Framework.
There are two IQueryable implementations in the .NET Framework:
==> LINQ to SQL
==> LINQ to Entities
Courses table in SQL Server and populate it with a few names using the following SQL script:
create table Courses(
ID int not null primary key, Name varchar(30)
)
insert Courses values (1, 'Dot Net')
insert Courses values (2, 'Java')
insert Courses values (3, 'PhP')
insert Courses values (4, 'Oracle')
insert Courses values (5, 'AJAX')
With this table in place, we can write an interpreted LINQ query in C# to retrieve courses Contains "Net"
static void Main( ) {
DataContext dataContext = new DataContext ("connection string");
Table
IQueryable
c.Name.Contains ("Net") orderby
c.Name.Length select
c.Name.ToUpper( );
foreach (string name in query)
Console.WriteLine (name); } }
LINQ to SQL translates this query into the following SQL:
SELECT UPPER([t0].[Name]) AS [value]
FROM [Courses] AS [t0]
WHERE [t0].[Name] LIKE '%Net%'
ORDER BY LEN([t0].[Name])
Output
Dot Net How Interpreted Queries Work
Let's look into how the above query is processed by LINQ
First, the compiler converts the query from to lambda syntax.IQueryable
Now, the compiler will resolve the query operator methods in the queryable class instead of Enumerable class.
The source upon which the above LINQ works is the Course variable. It is internally made up of type Table<> and implements IQueryable<> interface. Note that this interface is of type IEnumerable interface.The compiler chooses Queryable.Where because its signature is a more specific match.
public static IQueryable
The syntax Queryable.Where accepts a predicate of type
Because Queryable.Where also returns IQueryable<>, the same process follows with the OrderBy and Select operators.Execution
Interpreted queries follow a deferred execution model—just like local queries. This means that the SQL statement is not generated until you start enumerating the query. Further, enumerating the same query twice results in the database being queried twice.
Under the covers, interpreted queries differ from local queries in how they execute. When you enumerate over an interpreted query, the outermost sequence runs a program that traverses the entire expression tree, processing it as a unit. In our example, LINQ to SQL translates the expression tree to a SQL statement, which it then executes, yielding the results as a sequence.
Nice tip, I used not to use LINQ in my code wherever I can, but I think that there times when you MUST use it. Selecting max values for me without T-SQL means to iterate through all the rows to get the max value. :(Thanks for your help! :)