Esempi di query c# Linq e relative conversioni in T-SQL
14 agosto 2010
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
Query sulla tabella Product filtrata per ID che ritorna solo due campi
Come la precedente ma ritorna solo i primi 10 elementi. Il metodo Take(10) corrisponde a SELECT TOP 10
Query con ordinamento discendente
Un esempio di query che ritorna i dati presi da due tabella Product e ProductCategory messe automaticamente in join dal motore di Entity Framework
Query con raggruppamento group <tabella> by <campo> into <aliasRisultato>
Le query sono state rilevate tramite SQL Profiler.
Fai attenzione che la definizione della query:
non esegue la query la query su SQL. La query viene eseguita solamente quando si recuperano i dati, ad esempio con la seguente espressione:
la conversione in lista ToList() produce l'effettiva esecuzione della query su SQL Server.
Select sulla tabella Product che ritorna tutti i campi
C#
using (AWEntities ctx = new AWEntities())
{
var q = from p in ctx.Product
select p;
var items = q.ToList();
}
SQL
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#
var q = from p in ctx.Product
where p.ProductID >= 600 && p.ProductID < 700
select new { p.ProductID, p.Name };
SQL
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#
var q = (from p in ctx.Product
where p.ProductID >= 600 && p.ProductID < 700
select new { ID = p.ProductID, ProductName = p.Name }).Take(10);
SQL
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#
var q = from p in ctx.Product
where p.ProductID >= 600 && p.ProductID < 700
orderby p.ProductID descending
select new {p.ProductID, p.Name };
SQL
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 };
SQL
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#
var q = from p in ctx.Product
group p by p.Color into g
select g;
SQL
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#
var q = from p in ctx.Product
select p;
C#
var items = q.ToList();