Database Reference
In-Depth Information
Following is the output of the code in Listing 3-16:
Books (using LINQ)
'F# In Practice' is in category: Programming
'The Joy of SQL' is in category: Databases
Books (using ESQL)
'F# In Practice' is in category: Programming
'The Joy of SQL' is in category: Databases
How It Works
For the LINQ query, we build a simple list of category names and include the list in the query along with the LINQ
Contains query operator. The observant reader will note that we start with the cats collection and determine if it
contains any category names. Entity Framework translates the Contains clause to a SQL statement with an
in
clause,
as shown in Listing 3-17.
Listing 3-17.
The SQL Statement Created for the LINQ Expression from Listing 3-16
SELECT
[Extent1].[BookId] AS [BookId],
[Extent1].[Title] AS [Title],
[Extent1].[CategoryId] AS [CategoryId]
FROM [chapter3].[Books] AS [Extent1]
LEFT OUTER JOIN [chapter3].[Category] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[CategoryId]
WHERE [Extent2].[Name] IN (N'Programming',N'Databases')
It is interesting to note that the generated SQL statement in Listing 3-17 does not use parameters for the items
in the
in
clause. This is different from the generated code we would see with LINQ to SQL, where the items in the list
would be parameterized. With this code, we don't run the risk of exceeding the parameters limit that is imposed by
SQL Server.
If we are interested in finding all of the topics in a given list of categories that are not yet categorized, we simply
include null in the category list. The generated code is shown in Listing 3-18.
Listing 3-18.
The SQL Statement Created for a LINQ Expression Like the One in Listing 3-16, but with a
Null
in the
List of Categories
SELECT
[Extent1].[BookId] AS [BookId],
[Extent1].[Title] AS [Title],
[Extent1].[CategoryId] AS [CategoryId]
FROM [chapter3].[Books] AS [Extent1]
LEFT OUTER JOIN [chapter3].[Category] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[CategoryId]
WHERE [Extent2].[Name] IN (N'Programming',N'Databases')
OR [Extent2].[Name] IS NULL
For parity, we also include an Entity SQL version of the query, in which we explicitly include a SQL
IN
clause.
