LINQ to SQLCE: Paginazione
Ero curioso di vedere come LINQ risolvesse la paginazione su SQLCE 3.5 visto che adesso il provider funziona abbastanza bene.
A fronte di questa query lanciata da un metodo del Data Access Layer della nostra "ormai famosa" applicazione EstatesManagement che per semplicità e debug stampa sulla console i record estratti.
public void List(String idEstateType, Int32 pageSize, Int32 currentPage)
{
DataModelSQLCE.Estatesmanagement dc = new DataModelSQLCE.Estatesmanagement(ConfigurationManager.ConnectionStrings["DevLeap_EstatesManagement_DB_SQLCEConnectionString"].ConnectionString);
dc.Log = Console.Out;
var estates = (from c in dc.TabEstates
where c.IdEstateType == idEstateType
select c).Skip((currentPage - 1) * pageSize).Take(pageSize);
foreach (DataModelSQLCE.TabEstates estate in estates)
{
Console.WriteLine(estate.EstateDescription);
}
}
impostando pageSize a 4 e currentPage a 6 il risultato è il seguente:
SELECT TOP (4) [t0].[idEstate] AS [IdEstate], [t0].[idSalesman] AS [IdSalesman],
[t0].[idEstateType] AS [IdEstateType], [t0].[EstateDescription], [t0].[EstateAd
dress], [t0].[EstateEuroPrice], [t0].[EstateNotes], [t0].[EstateSold], [t0].[Est
ateImage], [t0].[EstateMetadata], [t0].[EstateGPSCoordinate], [t0].[rowguid] AS
[Rowguid]
FROM [tabEstates] AS [t0]
WHERE (NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP (20) [t1].[idEstate] AS [IdEstate]
FROM [tabEstates] AS [t1]
WHERE [t1].[idEstateType] = @p0
) AS [t2]
WHERE [t0].[idEstate] = [t2].[IdEstate]
))) AND ([t0].[idEstateType] = @p0)
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [AAAAAAAAAAAAAAAAA]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.20706.1
Impostando nella query una orderby sul prezzo
public void List(String idEstateType, Int32 pageSize, Int32 currentPage)
{
DataModelSQLCE.Estatesmanagement dc = new DataModelSQLCE.Estatesmanagement(ConfigurationManager.ConnectionStrings["DevLeap_EstatesManagement_DB_SQLCEConnectionString"].ConnectionString);
dc.Log = Console.Out;
var estates = (from c in dc.TabEstates
where c.IdEstateType == idEstateType
orderby c.EstateEuroPrice
select c).Skip((currentPage - 1) * pageSize).Take(pageSize);
foreach (DataModelSQLCE.TabEstates estate in estates)
{
Console.WriteLine(estate.EstateDescription);
}
}
il risultato è il seguente:
SELECT TOP (4) [t0].[idEstate] AS [IdEstate], [t0].[idSalesman] AS [IdSalesman],
[t0].[idEstateType] AS [IdEstateType], [t0].[EstateDescription], [t0].[EstateAd
dress], [t0].[EstateEuroPrice], [t0].[EstateNotes], [t0].[EstateSold], [t0].[Est
ateImage], [t0].[EstateMetadata], [t0].[EstateGPSCoordinate], [t0].[rowguid] AS
[Rowguid]
FROM [tabEstates] AS [t0]
WHERE (NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP (20) [t1].[idEstate] AS [IdEstate]
FROM [tabEstates] AS [t1]
WHERE [t1].[idEstateType] = @p0
ORDER BY [t1].[EstateEuroPrice]
) AS [t2]
WHERE [t0].[idEstate] = [t2].[IdEstate]
))) AND ([t0].[idEstateType] = @p0)
ORDER BY [t0].[EstateEuroPrice]
-- @p0: Input String (Size = 0; Prec = 0; Scale = 0) [AAAAAAAAAAAAAAAAA]
-- Context: SqlProvider(SqlCE) Model: AttributedMetaModel Build: 3.5.20706.1
peccato che le funzionalità di LINQ to SQL(CE) siano disponibili solo sul :NET Framework completo e non sul .NET CF e quindi ne relegano l'utilizzo agli scenari desktop e tablet.
UPDATED: Marco ha scritto qualche considerazione sul criterio e sui tipi di query: http://blogs.devleap.com/marco/archive/2007/11/26/paginazione-in-linq-to-sql.aspx