Kamil Kliczbor @ asptip.net

24Jan/111

101 przykładów zapytań w NHibernate 3.x – HQL, Criteria API, QueryOver i Linq – Projekcje

Wprowadzenie

Wpis ten jest częścią artykułu poświęconemu wykonywaniu zapytań w NHibernate przy użyciu różnych sposobów jakie udostępnia ten mapper - HQL, Criteria API, QueryOver i Linq.

Część ta opisuje stosowanie projekcji w zapytaniach. Jeżeli piszesz instrukcję SELECT i chcesz określić jakie kolumny lub jakie przekształcenia na kolumnach chcesz wykonać, możesz użyć składni SQL-a w następujący sposób:

SELECT NazwaKolumny, funkcja_przekształcająca(NazwaKolumny) FROM NazwaTabeli

Ponieważ dane uzyskiwane w wyniku wykonania zapytania prawie zawsze różnią się od danych mapowanych do encji, stosuje się klasy transformujące wyniki na obiekty DTO. W przypadku HQl-a, Criteria API stosuje się konstrukcję SetResultTransformer(Transformers.AliasToBean(typeof(NazwaKlasyDto))), w przypadku QueryOver - TransformUsing().

Spis zapytań

Do poczytania

Prosty przykład

Ponieważ w tym przykładzie zaznaczana jest tylko jedna kolumna, to wystarczy zrzutować jej wartości na zwracany typ, bez konieczności używania transformacji.

SQL

SELECT this_.ProductName as y0_
FROM   Products this_

Zapytania

// HQL
this.Session.CreateQuery("select p.Name from Product p")
    .List<string>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .SetProjection(Projections.Property("Name"))
    .List<string>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .SetProjection(LambdaProjection.Property<Product>(p => p.Name))
    .List<string>();

// QueryOver
this.Session.QueryOver<Product>()
    .SelectList(l => l.Select(x => x.Name))
    .List<string>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Select(x => x.Name)
    .ToList();

// LINQ (2)
var query = from p in this.Session.Query<Product>()
            select p.Name;
query.ToList();

Funkcje agregujące (MAX, AVG)

Warto prześledzić sobie poniższe przykłady - generowany SQL jest zależny od typu, na który rzutowane są dane.

SQL

select max(product0_.UnitPrice) as col_0_0_,
       avg(product0_.UnitPrice) as col_1_0_
from   Products product0_

DTO

public class ProductCalculationsDto
{
    public decimal MaxUnitPrice { get; set; }
    public double AverageUnitPrice { get; set; }
}

Zapytania

ProductCalculationsDto dtoAlias = null;

// HQL
this.Session.CreateQuery(@"select max(p.UnitPrice) as MaxUnitPrice,
                                  avg(p.UnitPrice) as AverageUnitPrice
                             from Product p")
    .SetResultTransformer(Transformers.AliasToBean(typeof(ProductCalculationsDto)))
    .UniqueResult<ProductCalculationsDto>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .SetProjection(Projections.ProjectionList()
                    .Add(Projections.Max("UnitPrice").As("MaxUnitPrice"))
                    .Add(Projections.Avg("UnitPrice").As("AverageUnitPrice")))
    .SetResultTransformer(Transformers.AliasToBean(typeof(ProductCalculationsDto)))
    .UniqueResult<ProductCalculationsDto>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .SetProjection(Projections.ProjectionList()
                              .Add(LambdaProjection.Max<Product>(p => p.UnitPrice).As("MaxUnitPrice"))
                              .Add(LambdaProjection.Avg<Product>(p => p.UnitPrice).As("AverageUnitPrice")))
    .SetResultTransformer(Transformers.AliasToBean(typeof(ProductCalculationsDto)))
    .List<ProductCalculationsDto>();

// QueryOver
this.Session.QueryOver<Product>()
    .SelectList(builder => builder
        .SelectMax(x => x.UnitPrice).WithAlias(() => dtoAlias.MaxUnitPrice)
        .SelectAvg(x => x.UnitPrice).WithAlias(() => dtoAlias.AverageUnitPrice))
    .TransformUsing(Transformers.AliasToBean(typeof(ProductCalculationsDto)))
    .List<ProductCalculationsDto>();

Wywołanie funkcji MS-SQL ROUND

SQL

SELECT this_.ProductName as y0_,
       round(UnitPrice
               * UnitsInStock,
             -2) as ProductsPrice
FROM   Products this_

DTO

public class ProductDescriptionDto
{
    public string ProductName { get; set; }
    public decimal ProductsPrice { get; set; }
}

Zapytania

ProductDescriptionDto dtoAlias = null;

// HQL
this.Session.CreateQuery(@"select p.Name as ProductName,
                                  round(p.UnitPrice * p.UnitsInStock, -2) as ProductsPrice
                             from Product p")
    .SetResultTransformer(Transformers.AliasToBean(typeof(ProductDescriptionDto)))
    .List<ProductDescriptionDto>();

IProjection roundSqlProjection = Projections.SqlProjection(@"round(UnitPrice * UnitsInStock, -2) as ProductsPrice",
                                    new string[] { "ProductsPrice" },
                                    new IType[] { NHibernateUtil.Decimal });

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
   .SetProjection(Projections.ProjectionList()
                   .Add(Projections.Property("Name").As("ProductName"))
                   .Add(roundSqlProjection))
   .SetResultTransformer(Transformers.AliasToBean(typeof(ProductDescriptionDto)))
   .List<ProductDescriptionDto>();

// ICriteria API (2)
this.Session.CreateCriteria(typeof(Product))
   .SetProjection(Projections.ProjectionList()
                   .Add(Projections.Alias(Projections.Property("Name"), "ProductName"))
                   .Add(roundSqlProjection))
   .SetResultTransformer(Transformers.AliasToBean(typeof(ProductDescriptionDto)))
   .List<ProductDescriptionDto>();

string ProductName = null; //case sensitive

// ICriteria API (3)
this.Session.CreateCriteria<Product>()
    .SetProjection(Projections.ProjectionList()
                              .Add(LambdaProjection.Property<Product>(p => p.Name).As(() => ProductName))
                              .Add(roundSqlProjection))
    .SetResultTransformer(Transformers.AliasToBean(typeof(ProductDescriptionDto)))
    .List<ProductDescriptionDto>();

// ICriteria API (4)
this.Session.CreateCriteria<Product>()
    .SetProjection(Projections.ProjectionList()
                              .Add(LambdaProjection.Alias(LambdaProjection.Property<Product>(p => p.Name), () => ProductName))
                              .Add(roundSqlProjection))
    .SetResultTransformer(Transformers.AliasToBean(typeof(ProductDescriptionDto)))
    .List<ProductDescriptionDto>();

// QueryOver
this.Session.QueryOver<Product>()
    .SelectList(builder => builder
        .Select(x => x.Name).WithAlias(() => dtoAlias.ProductName)
        .Select(roundSqlProjection).WithAlias(() => dtoAlias.ProductsPrice))
    .TransformUsing(Transformers.AliasToBean(typeof(ProductDescriptionDto)))
    .List<ProductDescriptionDto>();

Funkcja COUNT(*)

SQL

SELECT count(* ) as y0_
FROM   Products this_

Zapytania

// HQL
this.Session.CreateQuery("select count(*) from Product p")
    .UniqueResult<long>();

// Criteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .SetProjection(Projections.RowCount())
    .UniqueResult<int>();

// Criteria API (2)
this.Session.CreateCriteria(typeof(Product))
    .SetProjection(Projections.RowCountInt64())
    .UniqueResult<long>();

// QueryOver (1)
this.Session.QueryOver<Product>()
    .RowCount();

// QueryOver (2)
this.Session.QueryOver<Product>()
    .RowCountInt64();

// QueryOver (3)
this.Session.QueryOver<Product>()
    .ToRowCountQuery()
    .SingleOrDefault<int>();

// QueryOver (3)
this.Session.QueryOver<Product>()
    .ToRowCountInt64Query()
    .SingleOrDefault<long>();

// LINQ
this.Session
    .Query<Product>()
    .Count();

Funkcja COUNT(NazwaKolumny)

SQL

SELECT count(this_.ProductId) as y0_
FROM   Products this_

Zapytania

// HQL
this.Session.CreateQuery("select count(p.Id) from Product p")
    .UniqueResult<long>();

// Criteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .SetProjection(Projections.Count("Id"))
    .UniqueResult<int>();

// Criteria API (2)
this.Session.CreateCriteria<Product>()
    .SetProjection(LambdaProjection.Count<Product>(x => x.Id))
    .UniqueResult<int>();

// QueryOver
this.Session.QueryOver<Product>()
    .SelectList(s => s.SelectCount(x => x.Id))
    .SingleOrDefault<int>();

Funkcja COUNT DISTINCT

SQL

SELECT count(distinct this_.ProductId) as y0_
FROM   Products this_

Zapytania

// HQL
this.Session.CreateQuery("select count(distinct p.Id) from Product p")
    .UniqueResult<long>();

// Criteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .SetProjection(Projections.CountDistinct("Id"))
    .UniqueResult<int>();

// Criteria API (2)
this.Session.CreateCriteria(typeof(Product))
    .SetProjection(Projections.CountDistinct<Product>(x => x.Id))
    .UniqueResult<int>();

// QueryOver
this.Session.QueryOver<Product>()
    .SelectList(x => x.SelectCountDistinct(p => p.Id))
    .SingleOrDefault<int>();
Comments (1) Trackbacks (0)
  1. Świetne przykłady, przydały się. Dzięki.


Leave a comment

No trackbacks yet.