Query types

ALL THE EXAMPLES FROM THIS POST ARE EXTRACTED FROM THE FWKLIGHT DEMO. TO FIND OUT HOW TO GET STARTED WITH THE DEMO, READ THIS POST.

There are 5 implementations of queries in FwkLight, and each time you should choose the appropriate one based on what you need by following this rule:

1. if you are using a stored procedure (you should do that very rarely), use a NamedQuery
2. if you are not using a stored procedure, try to use a Linq query
3. if 2 is not possible, try to use a MultiCriteria query
4. is 3 is not possible, try to use a Criteria query
5. if 4 is not possible, use a HQL query

 

HqlQuery

This is a query which can be configured with NHibernate’s HQL API.

It is quite flexible, but you should use it only if none of the others work because it has 2 big drawbacks: you will use strings to build the queries, and you don’t have good support for loading complex hierarchies.

When deciding to use it, you should also consider that this type of query cannot load complex aggregates efficiently and cannot use fetching strategies. If you need to load a list of aggregates in which every aggregate contains a complex hierarchy, your only good choice is MultiCriteriaQuery.

Example:

public class CalculatedPrimitiveQuery1 : HqlQuery<Client2, int>
{
    private Client2 _client;

    public decimal Execute(ISession session, Client2 client)
    {
        _client = client;
        return LoadUnique(session);
    }

    protected override string HQLForActiveItems
    {
        get
        {
            return @"select max(dl.DiscountPercentage) from DiscountLevel2 as dl
              where dl.DiscountPercentage <=
                 (select max(dl1.DiscountPercentage)
                        from DiscountLevel2 as dl1
                          where dl1.ClientTypeWithLimit = :ClientType
                          or
                          not exists (select dl1.DiscountPercentage from DiscountLevel2 as dl1
                            where dl1.ClientTypeWithLimit = :ClientType)
                )";
        }
    }

    protected override string HQLForAllItems
    {
        get { throw new NotImplementedException(); }
    }

    public override void SetParameters(IQuery query)
    {
        query.SetInt32("ClientType", (int)_client.ClientType);
    }

    protected override string Sorting
    {
        get
        {
            return "";
        }
    }
}

 

CriteriaQuery

This is a query which can be configured with NHibernate’s Criteria API.

It still uses some strings, but you will hardcode less information than with a HQL query.

When deciding to use it, you should also consider that this type of query cannot load complex aggregates efficiently and cannot use fetching strategies. If you need to load a list of aggregates in which every aggregate contains a complex hierarchy, your only good choice is MultiCriteriaQuery

This type of query is the best choice for paged UI lists.

Example:

public class CalculatedPrimitiveQuery2 : CriteriaQuery<CustomerOrder2, int>
{
  private Client2 _client;

  public override void GetMainCriteria(ICriteria criteria)
  {
    criteria.Add(Restrictions.Eq("IsLaunched", true))
      .Add(Restrictions.Eq("Client2", _client))
      .SetProjection(Projections.Id());
  }

  public bool Execute(ISession session, Client2 client)
  {
    _client = client;
    return Load(session).Count > 0;
  }

  public override void FilterOnlyActive(ICriteria criteria){}

  public override void Sort(ICriteria criteria){}
}

 

MultiCriteriaQuery

This is FwkLight’s implementation for a query, which uses a fluent interface (IListMultiCriteriaFetcher) to generate an NHibernate multicriteria.

To configure the fetching strategy of the query, you override the FetchingStrategy property.

public class AggregateListQuery3 : MultiCriteriaQuery<CustomerOrder1>
{
    private readonly CustomerOrder1Fetching _customerOrder1Fetching;
    private decimal _minPrice;

    public AggregateListQuery3(CustomerOrder1Fetching customerOrder1Fetching)
    {
        _customerOrder1Fetching = customerOrder1Fetching;
    }

    public IList<CustomerOrder1> Execute(ISession session, decimal minPrice)
    {
        _minPrice = minPrice;
        return Load(session);
    }

    protected override void ApplyMainFilters(IListMultiCriteriaFetcher<CustomerOrder1> criteria)
    {
        criteria.Where(p => p.OrderPrice).Gt(_minPrice);
    }

    protected override IFetchingStrategy<CustomerOrder1> FetchingStrategy
    {
        get
        {
            return _customerOrder1Fetching;
        }
    }

    protected override void FilterOnlyActive(IListMultiCriteriaFetcher<CustomerOrder1> criteria) { }

    protected override void Sort(IListMultiCriteriaFetcher<CustomerOrder1> criteria) { }
}

Here is how the fetching strategy looks like:

public class CustomerOrder1Fetching: IFetchingStrategy<CustomerOrder1>
{
    public void ApplyStrategyOn(IMultiCriteriaFetcher<CustomerOrder1> repository)
    {
        repository
            .InitFetchingHierarchy()
            .WhichAlsoContainsList(p => p.OrderDetails);
    }
}

 

LinqQuery

This is a query which can be configured through Linq means, and uses NHibernate2Linq. This is the best way to define queries, will be upgraded and maintained by the community, but it is very new, not yet mature, and has many limitations. You can use it only in very simple scenarios.

public class BonusHistory2ListQuery : LinqQuery<BonusHistory2>
{
    private DateTime _startDate;
    private DateTime? _endDate;
    private Client2 _client;

    public IList<BonusHistory2> Execute(ISession session, DateTime startDate, DateTime? endDate, Client2 client)
    {
        _startDate = startDate;
        _endDate = endDate;
        _client = client;

        return Load(session);
    }

    protected override void GetMainQuery()
    {
        _queryable = from q in _queryable
                     where q.Year >= _startDate.Year
                           && q.Month >= _startDate.Month
                           && q.Client2 == _client
                     select q;

        if (_endDate != null)
        {
            _queryable = from q in _queryable
                         where q.Year <= _endDate.Value.Year
                               && q.Month <= _endDate.Value.Month
                         select q;
        }
    }

    public override void FilterOnlyActive() { }

    public override void Sort()
    {
        _queryable = (from q in _queryable
                      orderby q.Month descending
                                    orderby q.Year descending
                      select q);
    }
}

 

AllItemsQuery

This is a Linq query which loads all the entities of a certain type. It is a shortcut to load all the information from small database tables.

 

NamedQuery

This is a query which works with a stored procedure.

public class BonusStatisticsQuery : NamedQuery<BonusStatistic>
{
    private Client2 _client;

    public BonusStatistic Execute(ISession session, Client2 client)
    {
        _client = client;
        return UniqueResult(session);
    }

    protected override void SetParameters(IQuery query)
    {
        query.SetParameter("clientId", _client.Id);
    }

    protected override string QueryName
    {
        get
        {
            return "ClientBonusStatistics";
        }
    }
}

The BonusStatistic entity looks like this:

public class BonusStatistic
{
    public virtual int Id { get; set; }
    public virtual decimal TotalBonusGained { get; set; }
}

The entity mapping also maps the stored procedure:

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="dbo" assembly="FwkLightDemo.Domain"  namespace="FwkLightDemo.Domain.Entities">
  <class name="BonusStatistic" table="BonusStatistic">
        <id name="Id" column="Id" type="Int32">
            <generator class="assigned"></generator>
        </id>

        <property name="TotalBonusGained"/>

  </class>

    <sql-query name="ClientBonusStatistics">
        <return class="BonusStatistic" />
        exec dbo.[FwkLightDemo_ClientBonusStatistics] :clientId
    </sql-query>

</hibernate-mapping>

And the stored procedure expects the clientId parameter:

CREATE PROCEDURE [dbo].[FwkLightDemo_ClientBonusStatistics] 

@clientId int

AS
BEGIN

    select
    sum(BonusGained) as TotalBonusGained,
    convert(int, rand()* 100000) as Id
    from BonusHistory2
    where Client2Id = @clientId

END

Advertisement

About this entry

You’re currently reading “Query types,” an entry on The FwkLight blog

Published:
April 27, 2010 / 1:52 pm
Category:
Uncategorized
Tags: