Mapping <subselect>

In the recipe Using calculated properties we learned how to use SQL expressions to provide custom calculations for specific properties. Using a <subselect> mapping we can to the same for an entire class.

Getting ready

Complete the Getting ready instructions at the beginning of this chapter.

How to do it…

  1. Add a new folder named MappingSubselects to the MappingRecipes project.
  2. Add a new class named PageHit to the folder:
    using System;

namespace MappingRecipes.MappingSubselects
{
  public class PageHit
  {
    public virtual int Id { get; protected set; }
    public virtual string Url { get; set; }
    public virtual DateTime PageViewDateTime { get; set; }
  }
}
  3. Add a new class named PageStatisticsEntry to the folder:
    namespace MappingRecipes.MappingSubselects
{
  public class PageStatisticsEntry
  {
    public virtual string Url { get; protected set; }

    public virtual int ViewCount { get; protected set; }
  }
}
  4. Add an embedded mapping named Mappings.hbm.xml to the folder:
    <?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
  assembly="MappingRecipes"
  namespace="MappingRecipes.MappingSubselects">
  <class name="PageHit">
    <id name="Id">
      <generator class="native"/>
    </id>
    <property name="Url" not-null="true"/>
  </class>
  <class name="PageStatisticsEntry" mutable="false">
    <subselect>
      SELECT Url, COUNT(*) as ViewCount 
      FROM PageHit GROUP BY Url
    </subselect>
    <synchronize table="PageHit"/>
    <id name="Url"/>
    <property name="ViewCount"/>
  </class>
</hibernate-mapping>
  5. Add a new class named Recipe to the folder:
    using System;
using NH4CookbookHelpers;
using NHibernate;

namespace MappingRecipes.MappingSubselects
{
  public class Recipe : HbmMappingRecipe
  {
    protected override void AddInitialData(
ISessionFactory sessionFactory)
    {
      var random = new Random();
      using (var session = sessionFactory
.OpenStatelessSession())
      {
        for (var i = 0; i < 100; i++)
        {
          session.Insert(new PageHit
          {
            Url = random.Next(10).ToString(),
            PageViewDateTime = DateTime.Now
          });
        }
      }
    }

    public override void RunQueries(ISession session)
    {
      var stats = session.QueryOver<PageStatisticsEntry>()
        .Where(x => x.ViewCount > 2)
        .OrderBy(x => x.ViewCount).Desc.List();

      foreach (var entry in stats)
      {
        Console.WriteLine("Url: {0}, View count: {1}", 
          entry.Url, entry.ViewCount);
      }
    }
  }
}
  6. Run the application and start the MappingSubselects recipe.

How it works…

Subselect mappings are in a way the full class version of a formula mapping, but a better description is perhaps that it works similar to a SQL VIEW. It defines a SQL query which takes the place of a table, and all the returned columns can be queried, as if they were actual columns on a table.

In our recipe, we created a PageHit table and mapping, and a <subselect> mapping using that very same table. However, it may just as well refer to tables which have not been included in any other mappings:

<class name="PageStatisticsEntry" mutable="false">
  <subselect>
    SELECT Url, COUNT(*) as ViewCount 
    FROM PageHit GROUP BY Url
  </subselect>
  <synchronize table="PageHit"/>
  <id name="Url"/>
  <property name="ViewCount"/>
</class>

The subselect element is included in a regular class mapping, and takes the place of a table attribute. Instead, it defines the query that will be used as a table. One or more <synchronize> elements can be also added. They inform NHibernate of which tables are used in the query, which is crucial if we add caching to our application. If something modifies the PageHit table, any cached values for PageStatisticsEntry will be invalidated in the cache.

We also added mutable="false" to the mapping, to specify that we cannot update entities of this type. The resulting SQL would not make any sense.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.118.144.248