I have two entities A and B where A has one-to-many relationship with B. I would like to create an NHibernate query that fetches all A entities with all the B records, where:
A entity is activeB entities are within a date range (I have 2 c# DateTime objects). entity A +----+--------+ | id | active | +----+--------+ | 1 | 1 | | 2 | 0 | | 3 | 1 | +----+--------+ entity B +----+------+-------+------------+ | id | year | month | foreign_id | +----+------+-------+------------+ | 1 | 2000 | 11 | 1 | | 2 | 2001 | 12 | 2 | | 3 | 2002 | 4 | 1 | +----+------+-------+------------+
So far I have tried this:
return this.sessionFactory.GetCurrentSession()
.QueryOver<A>()
.Where(x => x.Active)
.JoinQueryOver(x => x.BList)
.WhereRestrictionOn(y => y.Year * 12 + y.Month) // the problem is here, exception below
.IsBetween(2000 * 12 + 1) // january 2000
.And(2010 * 12 + 3) // march 2010
.List();
System.InvalidOperationException: variable 'x' of type 'Domain.A' referenced from scope '', but it is not defined
Generally I don't like the approach to calculate all dates in number of months (my app doesn't care about days, hours, etc...) However, I don't want to change my mappings for now (shown below).
I would like some help fixing this piece of code, or advise how can I do it better (or both preferably).
More details:
My c# entities look like this:
public class A
{
public virtual int Id { get; set; }
public virtual int Active { get; set; }
public virtual IEnumerable<B> BList { get; set; }
}
public class B
{
public virtual int Month { get; set; }
public virtual int Year { get; set; }
}
internal class AMapping: ClassMap<A>
{
public AMapping()
{
Table("AObjects");
Id(x => x.Id, "id");
Map(x => x.Active, "active");
HasMany(x => x.BList)
.Table("Bobjects")
.KeyColumn("foreign_id")
.Component(y => {
y.Map(b => b.Month, "month");
y.Map(b => b.Year, "year");
});
}
}
I think a way to go here is using filters. The first thing to do is to create a filter definition via a filter class, like this:
public class MonthsFilter : FilterDefinition
{
public MonthsFilter()
{
WithName("MonthsFilter")
.AddParameter("startMonths", NHibernateUtil.Int32)
.AddParameter("endMonths", NHibernateUtil.Int32);
}
}
Then you need to add the filter to your ClassMap for A, to the BList property, via the ApplyFilter method:
internal class AMapping : ClassMap<A>
{
public AMapping()
{
Table("AObjects");
Id(x => x.Id, "id");
Map(x => x.Active, "active");
HasMany(x => x.BList)
.Table("BObjects")
.KeyColumn("foreign_id")
.Component(y => {
y.Map(b => b.Month, "month");
y.Map(b => b.Year, "year");
}).ApplyFilter<MonthsFilter>("year * 12 + month BETWEEN :startMonths and :endMonths");
}
}
And finally, you will need to enable the filter before issuing the query, similar to this:
using (var session = sessionFactory.OpenSession())
{
// Enable filter and pass parameters
var startMonthsValue = 2000 * 12 + 1; // january 2000
var endMonthsValue = 2010 * 12 + 3; // march 2010
session.EnableFilter("MonthsFilter")
.SetParameter("startMonths", startMonthsValue)
.SetParameter("endMonths", endMonthsValue);
// Create and execute query (no filter for B needed here)
var list = session.QueryOver<A>()
.Fetch(x => x.BList).Eager // Eager fetch to avoid the N+1 problem due to BList lazy load
.Where(x => x.Active)
.TransformUsing(Transformers.DistinctRootEntity) // List only distinct A entities, to avoid duplicated entries due to eager fetch one-to-many relation
.List();
// Do whatever you want with the results
foreach (var item in list)
{
Console.WriteLine("A id: {0} - B children count: {1}", item.Id, item.BList.Count());
}
}
A full working example:
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Mapping;
using NHibernate;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
namespace NHibernateTests
{
public class A
{
public virtual int Id { get; set; }
public virtual bool Active { get; set; }
public virtual IEnumerable<B> BList { get; set; }
}
public class B
{
public virtual int Month { get; set; }
public virtual int Year { get; set; }
}
internal class AMapping : ClassMap<A>
{
public AMapping()
{
Table("AObjects");
Id(x => x.Id, "id");
Map(x => x.Active, "active");
HasMany(x => x.BList)
.Table("BObjects")
.KeyColumn("foreign_id")
.Component(y => {
y.Map(b => b.Month, "month");
y.Map(b => b.Year, "year");
}).ApplyFilter<MonthsFilter>("year * 12 + month BETWEEN :startMonths and :endMonths");
}
}
public class MonthsFilter : FilterDefinition
{
public MonthsFilter()
{
WithName("MonthsFilter")
.AddParameter("startMonths", NHibernateUtil.Int32)
.AddParameter("endMonths", NHibernateUtil.Int32);
}
}
class Program
{
static void Main(string[] args)
{
var sessionFactory = CreateNHibernateSessionFactory();
using (var session = sessionFactory.OpenSession())
{
// Enable filter and pass parameters
var startMonthsValue = 2000 * 12 + 1; // january 2000
var endMonthsValue = 2010 * 12 + 3; // march 2010
session.EnableFilter("MonthsFilter")
.SetParameter("startMonths", startMonthsValue)
.SetParameter("endMonths", endMonthsValue);
// Create and execute query (no filter needed here)
var list = session.QueryOver<A>()
.Fetch(x => x.BList).Eager // Eager fetch to avoid the N+1 problem due to BList lazy load
.Where(x => x.Active)
.TransformUsing(Transformers.DistinctRootEntity) // List only distinct A entities, to avoid duplicated entries due to eager fetch one-to-many relation
.List();
// Do whatever you want with the results
foreach (var item in list)
{
Console.WriteLine("A id: {0} - B children count: {1}", item.Id, item.BList.Count());
}
}
Console.WriteLine("Press ENTER to continue...");
Console.ReadLine();
}
static ISessionFactory CreateNHibernateSessionFactory()
{
FluentConfiguration fc = Fluently.Configure()
.Database(MsSqlConfiguration.MsSql2012.ConnectionString("Server=.\\SQLEXPRESS;Database=NHibernateTests;Trusted_Connection=True;"))
.Mappings(m => {
m.FluentMappings
.AddFromAssembly(Assembly.GetExecutingAssembly());
});
var config = fc.BuildConfiguration();
return config.SetProperty(NHibernate.Cfg.Environment.ReleaseConnections, "on_close")
.BuildSessionFactory();
}
}
}
More info about filters: