Kamil Kliczbor @ asptip.net

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

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 restrykcji w zapytaniach. Jeżeli piszesz instrukcję SELECT i chcesz określić w niej warunek, musisz użyć słowa kluczowego WHERE w podstawowej formule w następujący sposób:

SELECT NazwyKolumn FROM NazwaTabeli WHERE Warunek

Wyrażenia używane w warunku są budowane przy użyciu operatorów algebraicznych, logicznych i ciągów znakowych. W tym wpisie zostały przedstawione przykłady użycia klauzuli WHERE.

Spis zapytań

• Prosty przykład
• Wyrażenia <, <=, ==, !=, >, >=
• Wyrażenie Between
• Wyrażenie In
• Wyrażenie Like
• Wyrażenie Like bez uwzględniania wielkości liter
• Wyrażenie Is Null
• Wyrażenie Not
• Wyrażenie Exists / IsNotEmpty
• Wyrażenie Not Exists / IsEmpty
• Łączenie wyrażeń (koniunkcja i alternatywa - AND i OR)
• Wyrażenia z aliasem

Do poczytania

• Restrictions on Data Selection
• Conjunctions and Disjunctions
• Proste zapytania w języku SQL

Prosty przykład

Przykład prostego zapytania z wykorzystaniem klauzuli WHERE został umieszczony poniżej. Należy zauważyć, że LINQ zachowuje się tutaj co najmniej nieoczekiwanie, gdyż w chwili dodania porównania do wartości true, generowany jest dosyć zakręcony warunek z instrukcją CASE ... WHEN ... THEN (zgłoszone jako bug).

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  this_.Discontinued = 1

Zapytania

// HQL

this.Session.CreateQuery("from Product p where p.Discontinued = :isDiscontinued")
    .SetBoolean("isDiscontinued", true)
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.Eq("Discontinued", true))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add<Product>(x => x.Discontinued == true)
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .Where(x => x.Discontinued == true)
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(x => x.Discontinued)
    //.Where(x => x.Discontinued == true) //-> generates case ... when ... then
    .ToList();

// LINQ (2)
var query = from p in this.Session.Query<Product>()
            //where p.Discontinued
            where p.Discontinued == true  //-> generates case ... when ... then
            select p;
query.ToList();

Wyrażenia <, <=, ==, !=, >, >=

Wyrażenia te można stosować w miarę zamiennie w zapytaniach, używając operatorów dostępnych w języku C#. W przypadku HQL-a zamiast operatora '==' używa się '='. Pisząc zapytania w oparciu o "klasyczne" ICriteria API, stosuje się "nazwane" klasy których znaczenie odpowiada wymienionym wcześniej operatorom. Odpowiedniki prezentuje zestawienie znajdujące się poniżej.

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  not (this_.ReorderLevel = 5)

Zapytania

W przypadku HQL-a oraz LINQ wyrażenie NOT(ReorderLevel = 5) zostało zastąpione wyrażeniem ReorderLevel <> 5.

//can replace with <, <=, ==, !=, >, >=

// HQL and LINQ generates '<>' operator, whilst Criteria and QueryOver Not(ReorderLevel = 5)

// HQL
this.Session.CreateQuery("select p from Product p where p.ReorderLevel <> :level")
    .SetInt32("level", 5)
    .List<Product>();

// ICriteria API (1) //here Use e.g. Expression.Gt, Expression.Ge etc
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.Not(Restrictions.Eq("ReorderLevel", 5)))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add<Product>(x => x.ReorderLevel != 5)
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .Where(x => x.ReorderLevel != 5)
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(x => x.ReorderLevel != 5)
    .ToList();

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

Wyrażenie Between

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  this_.ReorderLevel between 1 and 3

Zapytania

W przypadku LINQ nie ma odpowiednika do wyrażenia Between. Należy użyć składni w postaci WHERE ReorderLevel >= 1 AND ReorderLevel < = 3.

// HQL
this.Session.CreateQuery("select p from Product p where p.ReorderLevel between :lo and :hi")
    .SetInt32("lo", 1)
    .SetInt32("hi", 3)
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.Between("ReorderLevel", 1, 3))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.Between<Product>(x => x.ReorderLevel, 1, 3))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .WhereRestrictionOn(x => x.ReorderLevel).IsBetween(1).And(3)
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(p => p.ReorderLevel >= 1 && p.ReorderLevel <= 3)
    .ToList();

// LINQ (2)
var query = from p in this.Session.Query<Product>()
            where p.ReorderLevel >= 1 && p.ReorderLevel <= 3
            select p;
query.ToList();

Wyrażenie In

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  this_.ProductName in ('name1', 'name2' ,'name3')

Zapytania

var names = new string[] { "name1", "name2", "name3" };

// HQL
this.Session.CreateQuery("select p from Product p where p.Name in (:name1, :name2, :name3) ")
    .SetString("name1", "name1")
    .SetString("name2", "name2")
    .SetString("name3", "name3")
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.In("Name", new string[] { "name1", "name2", "name3" }))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.In<Product>(x => x.Name, new string[] { "name1", "name2", "name3" }))
    .List<Product>();

// ICriteria API (3) Generic In
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.InG("Name", new string[] { "name1", "name2", "name3" }))
    .List<Product>();

// ICriteria API (4) Generic In
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.InG<Product, string>(x => x.Name, new string[] { "name1", "name2", "name3" }))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .WhereRestrictionOn(x => x.Name).IsIn(new string[] { "name1", "name2", "name3" })
    .List<Product>();

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

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

Wyrażenie Like

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  this_.ProductName like 'N%'

Zapytania

W przypadku zapytań tworzonych przy użyciu LINQ możemy skorzystać z trzech postaci wybierania szukanej frazy:

• dla StartsWith() generowane jest zapytanie w postaci 'fraza%'
• dla EndsWith() generowane jest zapytanie w postaci '%fraza'
• dla Contains() generowane jest zapytanie w postaci '%fraza%'.

// HQL
this.Session.CreateQuery("select p from Product p where p.Name like :name")
    .SetString("name", "N%")
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.Like("Name", "N%"))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.Like<Product>(x => x.Name, "N%"))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .WhereRestrictionOn(x => x.Name).IsLike("N%")
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(x => x.Name.StartsWith("N")) // --> like 'N%'
    //.Where(x => x.Name.Contains("N")) //--> like '%N%'
    //.Where(x => x.Name.EndsWith("N")) // --> like '%N'
    .ToList();

// LINQ (2)
var query = from p in this.Session.Query<Product>()
            where p.Name.StartsWith("N") //--> like 'N%'
            //where p.Name.Contains("N") //--> like '%N%'
            //where p.Name.EndsWith("N") //--> like '%N'
            select p;
query.ToList();

Wyrażenie Like bez uwzględniania wielkości liter

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  lower(this_.ProductName) like 'n%'

Zapytania

W HQL można zastosować jednostronną funkcję lower(), jednakże nalezy zadbać, aby przekazywany ciąg znakowy przekształcić używając metody ToLower() . Tak jak w przypadku zwykłego LIKE w LINQ, wykonanie takiego zapytania nie jest wspierane.

// HQL (1)
this.Session.CreateQuery("select p from Product p where lower(p.Name) like lower(:name)")
    .SetString("name", "N%")
    .List<Product>();

// HQL (2)
this.Session.CreateQuery("select p from Product p where lower(p.Name) like :name")
    .SetString("name", "N%".ToLower())
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.InsensitiveLike("Name", "N%"))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.InsensitiveLike<Product>(x => x.Name, "N%"))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .WhereRestrictionOn(x => x.Name).IsInsensitiveLike("N%")
    .List<Product>();

// LINQ - Not supported

Wyrażenie Is Null

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  this_.UnitPrice is null

Zapytania

Trzeba uświadomić sobie również, że stworzenie kryterium SqlExpression.IsNull<Product>(x=> x. UnitPrice) jest różne od wywołania .Add<Product>(x => x.UnitPrice), gdyż w pierwszym przypadku otrzymamy UnitPrice IS NULL, a w drugim UnitPrice = NULL.

// HQL
this.Session.CreateQuery("select p from Product p where p.UnitPrice is null")
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.IsNull("UnitPrice"))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.IsNull<Product>(x => x.UnitPrice))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .WhereRestrictionOn(x => x.UnitPrice).IsNull
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(x => x.UnitPrice == null)
    .ToList();

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

Wyrażenie Not

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  not (this_.ReorderLevel > 5)

Zapytania

W przypadku zapytania w HQL została dokonana optymalizacja i operator not z '>' został zamieniony na operator '<='.

// HQL
this.Session.CreateQuery("select p from Product p where not(p.ReorderLevel > :level)")
    .SetInt32("level", 5)
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.Not(Restrictions.Gt("ReorderLevel", 5)))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.Not<Product>(p => p.ReorderLevel > 5))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .WhereNot(x => x.ReorderLevel > 5)
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(x => !(x.ReorderLevel > 5))
    .ToList();

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

Wyrażenie Exists / IsNotEmpty

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  exists (select 1
               from   OrderLines
               where  this_.ProductId = ProductId)

Zapytania

Zapytania nie różnią się od siebie bardzo, za wyjątkiem HQLa i LINQ, które zamiast liczby "1" wybierają identyfikator encji OrderLine.

// HQL
this.Session.CreateQuery("select p from Product p where exists (from OrderLine ol where ol.Product = p)")
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.IsNotEmpty("OrderLines"))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.IsNotEmpty<Product>(x => x.OrderLines))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .WhereRestrictionOn(x => x.OrderLines).IsNotEmpty
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(x => x.OrderLines.Any())
    .ToList();

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

Wyrażenie Not Exists / IsEmpty

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  not exists (select 1
                   from   OrderLines
                   where  this_.ProductId = ProductId)

Zapytania

W zasadzie zapytania są takie same jak te wyżej, tyle że są z wykonane z zaprzeczeniem - słówko NOT.

// HQL
this.Session.CreateQuery("select p from Product p where not exists (from OrderLine ol where ol.Product = p)")
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.IsEmpty("OrderLines"))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria<Product>()
    .Add(SqlExpression.IsEmpty<Product>(x => x.OrderLines))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .WhereRestrictionOn(x => x.OrderLines).IsEmpty
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(x => !x.OrderLines.Any())
    .ToList();

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

Łączenie wyrażeń (koniunkcja i alternatywa - AND i OR)

SQL

SELECT this_.ProductId       as ProductId6_0_,
       this_.ProductName     as ProductN2_6_0_,
       this_.QuantityPerUnit as Quantity3_6_0_,
       this_.UnitPrice       as UnitPrice6_0_,
       this_.UnitsInStock    as UnitsInS5_6_0_,
       this_.UnitsOnOrder    as UnitsOnO6_6_0_,
       this_.ReorderLevel    as ReorderL7_6_0_,
       this_.Discontinued    as Disconti8_6_0_,
       this_.SupplierId      as SupplierId6_0_,
       this_.CategoryId      as CategoryId6_0_
FROM   Products this_
WHERE  (this_.UnitPrice is null
         or (this_.ReorderLevel >= 5
             and not (this_.Discontinued = 1)))

Zapytania

// HQL
this.Session.CreateQuery("select p from Product p where p.UnitPrice is null or (p.ReorderLevel >= :level and not (p.Discontinued))")
    .SetInt32("level", 5)
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.Or(
            Restrictions.IsNull("UnitPrice"),
            Restrictions.And(
                Restrictions.Ge("ReorderLevel", 5),
                Restrictions.Not(Restrictions.Eq("Discontinued", true)))))
    .List<Product>();

// ICriteria API (2)
this.Session.CreateCriteria(typeof(Product))
    .Add(Restrictions.Disjunction()
    .Add(Restrictions.IsNull("UnitPrice"))
    .Add(Restrictions.Conjunction()
            .Add(Restrictions.Ge("ReorderLevel", 5))
            .Add(Restrictions.Not(Restrictions.Eq("Discontinued", true)))))
    .List<Product>();

// ICriteria API (3)
this.Session.CreateCriteria<Product>()
    .Add(Restrictions.Or(
            SqlExpression.IsNull<Product>(x => x.UnitPrice),
            Restrictions.And(
                SqlExpression.CriterionFor<Product>(x => x.ReorderLevel > 5),
                SqlExpression.CriterionFor<Product>(x => x.Discontinued == false))))
    .List<Product>();

// ICriteria API (4)
this.Session.CreateCriteria<Product>()
     .Add(Restrictions.Disjunction()
     .Add(SqlExpression.IsNull<Product>(x => x.UnitPrice))
     .Add(Restrictions.Conjunction()
        .Add(SqlExpression.CriterionFor<Product>(x => x.ReorderLevel > 5))
        .Add(SqlExpression.CriterionFor<Product>(x => !x.Discontinued))))
    .List<Product>();

// QueryOver
this.Session.QueryOver<Product>()
    .Where(x => x.UnitPrice == null || (x.ReorderLevel > 5 && !x.Discontinued))
    .List<Product>();

// LINQ (1)
this.Session
    .Query<Product>()
    .Where(x => x.UnitPrice == null || (x.ReorderLevel > 5 && !x.Discontinued))
    .ToList();

// LINQ (2)
var query = from p in this.Session.Query<Product>()
            where p.UnitPrice == null || (p.ReorderLevel > 5 && !p.Discontinued)
            select p;
query.ToList();

Wyrażenia z aliasem

SQL

SELECT this_.ProductId         as ProductId6_1_,
       this_.ProductName       as ProductN2_6_1_,
       this_.QuantityPerUnit   as Quantity3_6_1_,
       this_.UnitPrice         as UnitPrice6_1_,
       this_.UnitsInStock      as UnitsInS5_6_1_,
       this_.UnitsOnOrder      as UnitsOnO6_6_1_,
       this_.ReorderLevel      as ReorderL7_6_1_,
       this_.Discontinued      as Disconti8_6_1_,
       this_.SupplierId        as SupplierId6_1_,
       this_.CategoryId        as CategoryId6_1_,
       orderline1_.OrderLineId as OrderLin1_4_0_,
       orderline1_.UnitPrice   as UnitPrice4_0_,
       orderline1_.Quantity    as Quantity4_0_,
       orderline1_.Discount    as Discount4_0_,
       orderline1_.OrderId     as OrderId4_0_,
       orderline1_.ProductId   as ProductId4_0_
FROM   Products this_
       inner join OrderLines orderline1_
         on this_.ProductId = orderline1_.ProductId
WHERE  this_.QuantityPerUnit = '6' /* @p0 */
       and orderline1_.Quantity = 1 /* @p1 */

Zapytania

Zapytania przez alias polegają na tym, aby złączyć powiązane encje, a następnie zaaplikować na nich odpowiednie restrykcje. Złączenie można zrealizować przy pomocy klauzuli JOIN.

// HQL
this.Session.CreateQuery(@"select p
                             from Product p
                            inner join p.OrderLines ol
                            where p.QuantityPerUnit = :quantityPerUnit
                               or ol.Quantity > :quantity")
    .SetString("quantityPerUnit", "6")
    .SetInt32("quantity", 1)
    .List<Product>();

// ICriteria API (1)
this.Session.CreateCriteria(typeof(Product))
    .CreateAlias("OrderLines", "orderLinesAlias")
    .Add(Restrictions.Eq("QuantityPerUnit", "6"))
    .Add(Restrictions.Gt("orderLinesAlias.Quantity", 1))
    .List<Product>();

// ICriteria API (2)
OrderLine orderLineAlias = null;

this.Session.CreateCriteria<Product>()
    .CreateAlias<Product>(x => x.OrderLines, () => orderLineAlias)
    .Add<Product>(x => x.QuantityPerUnit == "6")
    .Add<OrderLine>(x => orderLineAlias.Quantity == 1)
    .List<Product>();

// QueryOver (1)
this.Session.QueryOver<Product>()
    .JoinAlias(x => x.OrderLines, () => orderLineAlias)
    .Where(x => x.QuantityPerUnit == "6" && orderLineAlias.Quantity == 1)
    .List<Product>();

// QueryOver (2)
this.Session.QueryOver<Product>()
    .Where(p => p.QuantityPerUnit == "6")
    .JoinQueryOver<OrderLine>(x => x.OrderLines)
    .Where(ol => ol.Quantity == 1)
    .List<Product>();

// LINQ (1)
this.Session.Query<Product>()
    .Join(this.Session.Query<OrderLine>(), p => p.Id, ol => ol.Product.Id, (p, ol) => new { p, ol })
    .Where(@t => @t.p.QuantityPerUnit == "6" && @t.ol.Quantity == 1)
    .Select(@t => @t.p);

// LINQ (2)
var query = from p in this.Session.Query<Product>()
            join ol in this.Session.Query<OrderLine>()
                 on p.Id equals ol.Product.Id
            where p.QuantityPerUnit == "6" && ol.Quantity == 1
            select p;
query.ToList();