Esempi di query c# Linq e relative conversioni in T-SQL

Quelle che seguono sono degli esempi di query Linq C# fatte con Entity Framework 3.5 sul database AdventureWorksLT2008 e di come vengono convertite in T-SQL 2008.

Select sulla tabella Product che ritorna tutti i campi

C#Copia

using (AWEntities ctx = new AWEntities())
{
  var q = from p in ctx.Product
		select p;
  var items = q.ToList();
}

SQLCopia

SELECT 
1 AS [C1], 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[Name] AS [Name], 
[Extent1].[ProductNumber] AS [ProductNumber], 
[Extent1].[Color] AS [Color], 
[Extent1].[StandardCost] AS [StandardCost], 
[Extent1].[ListPrice] AS [ListPrice], 
[Extent1].[Size] AS [Size], 
[Extent1].[Weight] AS [Weight], 
[Extent1].[ProductModelID] AS [ProductModelID], 
[Extent1].[SellStartDate] AS [SellStartDate], 
[Extent1].[SellEndDate] AS [SellEndDate], 
[Extent1].[DiscontinuedDate] AS [DiscontinuedDate], 
[Extent1].[ThumbNailPhoto] AS [ThumbNailPhoto], 
[Extent1].[ThumbnailPhotoFileName] AS [ThumbnailPhotoFileName], 
[Extent1].[rowguid] AS [rowguid], 
[Extent1].[ModifiedDate] AS [ModifiedDate], 
[Extent1].[ProductCategoryID] AS [ProductCategoryID]
FROM [SalesLT].[Product] AS [Extent1]	

Query sulla tabella Product filtrata per ID che ritorna solo due campi

C#Copia

var q = from p in ctx.Product
		where p.ProductID >= 600 && p.ProductID < 700
		select new { p.ProductID, p.Name };

SQLCopia

SELECT 
1 AS [C1], 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[Name] AS [Name]
FROM [SalesLT].[Product] AS [Extent1]
WHERE ([Extent1].[ProductID] >= 600) AND ([Extent1].[ProductID] < 700)

Come la precedente ma ritorna solo i primi 10 elementi. Il metodo Take(10) corrisponde a SELECT TOP 10

C#Copia

var q = (from p in ctx.Product
		where p.ProductID >= 600 && p.ProductID < 700
		select new { ID = p.ProductID, ProductName = p.Name }).Take(10);

SQLCopia

SELECT 
[Limit1].[C1] AS [C1], 
[Limit1].[ProductID] AS [ProductID], 
[Limit1].[Name] AS [Name]
FROM ( SELECT TOP (10) 
	[Extent1].[ProductID] AS [ProductID], 
	[Extent1].[Name] AS [Name], 
	1 AS [C1]
	FROM [SalesLT].[Product] AS [Extent1]
	WHERE ([Extent1].[ProductID] >= 600) AND ([Extent1].[ProductID] < 700)
)  AS [Limit1]

Query con ordinamento discendente

C#Copia

var q = from p in ctx.Product
		where p.ProductID >= 600 && p.ProductID < 700
		orderby p.ProductID descending
		select new {p.ProductID, p.Name };

SQLCopia

SELECT 
[Project1].[C1] AS [C1], 
[Project1].[ProductID] AS [ProductID], 
[Project1].[Name] AS [Name]
FROM ( SELECT 
	[Extent1].[ProductID] AS [ProductID], 
	[Extent1].[Name] AS [Name], 
	1 AS [C1]
	FROM [SalesLT].[Product] AS [Extent1]
	WHERE ([Extent1].[ProductID] >= 600) AND ([Extent1].[ProductID] < 700)
)  AS [Project1]
ORDER BY [Project1].[ProductID] DESC

Un esempio di query che ritorna i dati presi da due tabella Product e ProductCategory messe automaticamente in join dal motore di Entity Framework

var q = from p in ctx.Product
		select new { ID= p.ProductID, Name = p.Name, Category = p.ProductCategory.Name };

SQLCopia

SELECT 
1 AS [C1], 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[Name] AS [Name], 
[Extent2].[Name] AS [Name1]
FROM  [SalesLT].[Product] AS [Extent1]
LEFT OUTER JOIN [SalesLT].[ProductCategory] AS [Extent2]
  ON [Extent1].[ProductCategoryID] = [Extent2].[ProductCategoryID]

Query con raggruppamento group <tabella> by <campo> into <aliasRisultato>

C#Copia

var q = from p in ctx.Product
	group p by p.Color into g
	select g;

SQLCopia

SELECT 
[Project2].[Color] AS [Color], 
[Project2].[C1] AS [C1], 
[Project2].[C3] AS [C2], 
[Project2].[C2] AS [C3], 
[Project2].[ProductID] AS [ProductID], 
[Project2].[Name] AS [Name], 
[Project2].[ProductNumber] AS [ProductNumber], 
[Project2].[Color1] AS [Color1], 
[Project2].[StandardCost] AS [StandardCost], 
[Project2].[ListPrice] AS [ListPrice], 
[Project2].[Size] AS [Size], 
[Project2].[Weight] AS [Weight], 
[Project2].[ProductModelID] AS [ProductModelID], 
[Project2].[SellStartDate] AS [SellStartDate], 
[Project2].[SellEndDate] AS [SellEndDate], 
[Project2].[DiscontinuedDate] AS [DiscontinuedDate], 
[Project2].[ThumbNailPhoto] AS [ThumbNailPhoto], 
[Project2].[ThumbnailPhotoFileName] AS [ThumbnailPhotoFileName], 
[Project2].[rowguid] AS [rowguid], 
[Project2].[ModifiedDate] AS [ModifiedDate], 
[Project2].[ProductCategoryID] AS [ProductCategoryID]
FROM ( SELECT 
	[Distinct1].[Color] AS [Color], 
	1 AS [C1], 
	CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2], 
	[Extent2].[ProductID] AS [ProductID], 
	[Extent2].[Name] AS [Name], 
	[Extent2].[ProductNumber] AS [ProductNumber], 
	[Extent2].[Color] AS [Color1], 
	[Extent2].[StandardCost] AS [StandardCost], 
	[Extent2].[ListPrice] AS [ListPrice], 
	[Extent2].[Size] AS [Size], 
	[Extent2].[Weight] AS [Weight], 
	[Extent2].[ProductCategoryID] AS [ProductCategoryID], 
	[Extent2].[ProductModelID] AS [ProductModelID], 
	[Extent2].[SellStartDate] AS [SellStartDate], 
	[Extent2].[SellEndDate] AS [SellEndDate], 
	[Extent2].[DiscontinuedDate] AS [DiscontinuedDate], 
	[Extent2].[ThumbNailPhoto] AS [ThumbNailPhoto], 
	[Extent2].[ThumbnailPhotoFileName] AS [ThumbnailPhotoFileName], 
	[Extent2].[rowguid] AS [rowguid], 
	[Extent2].[ModifiedDate] AS [ModifiedDate], 
	CASE WHEN ([Extent2].[ProductID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
	FROM   (SELECT DISTINCT 
		[Extent1].[Color] AS [Color]
		FROM [SalesLT].[Product] AS [Extent1] ) AS [Distinct1]
	LEFT OUTER JOIN [SalesLT].[Product] AS [Extent2] ON ([Extent2].[Color] = [Distinct1].[Color]) OR (([Extent2].[Color] IS NULL) AND ([Distinct1].[Color] IS NULL))
)  AS [Project2]
ORDER BY [Project2].[Color] ASC, [Project2].[C3] ASC

Le query sono state rilevate tramite SQL Profiler.
Fai attenzione che la definizione della query:

C#Copia

var q = from p in ctx.Product
	select p;

non esegue la query la query su SQL. La query viene eseguita solamente quando si recuperano i dati, ad esempio con la seguente espressione:

C#Copia

var items = q.ToList();

la conversione in lista ToList() produce l'effettiva esecuzione della query su SQL Server.