EDIT I remade an entire project for this one problem. And thus, I remade the question.
I want to be able to efficiently avoid N+1 and Cartesian joins joining together a 4 level deep entity with a composite key on the third level.
I am looking for this to be done in only a few queries, not lazy loaded, and not just join all the tables together.
A -(many)-> B -(many)-> C -(composite, single)-> D
Something like:
Select * From A Left Join B On A.Id = B.AId
Select * From B Left Join C On B.Id = C.BId Inner Join D On C.DId = D.Id
Here is the code used This is a fully functional app. I used NuGet to install Sqlite x86, StructureMap, NHProf, Fluent NH.
StructureMapServiceLocator:
namespace MyTest.NHibernateTest
{
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.Practices.ServiceLocation;
using StructureMap;
public class StructureMapServiceLocator : ServiceLocatorImplBase
{
private readonly IContainer _container;
public StructureMapServiceLocator(IContainer container)
{
_container = container;
}
public IContainer Container { get { return _container; } }
protected override object DoGetInstance(Type serviceType, string key)
{
return string.IsNullOrEmpty(key)
? _container.GetInstance(serviceType)
: _container.GetInstance(serviceType, key);
}
protected override IEnumerable<object> DoGetAllInstances(Type serviceType)
{
return _container.GetAllInstances(serviceType).Cast<object>().AsEnumerable();
}
public override TService GetInstance<TService>()
{
return _container.GetInstance<TService>();
}
public override TService GetInstance<TService>(string key)
{
return _container.GetInstance<TService>(key);
}
public override IEnumerable<TService> GetAllInstances<TService>()
{
return _container.GetAllInstances<TService>();
}
}
}
AppRegistry
namespace MyTest.NHibernateTest
{
using System;
using System.Collections.Generic;
using System.Linq;
using StructureMap.Configuration.DSL;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Cfg;
using NHibernate;
using NHibernate.Tool.hbm2ddl;
using FluentNHibernate.Automapping;
using FluentNHibernate.Data;
public class AppRegistry : Registry
{
public AppRegistry()
{
var dbConfiguration = SQLiteConfiguration.Standard
.ConnectionString("Data Source=sqlite.db;Version=3;New=True;");
dbConfiguration.ShowSql();
var cfg = Fluently.Configure()
.Database(dbConfiguration)
.Mappings(m =>
{
m.AutoMappings.Add(AutoMap.AssemblyOf<Program>().Where(t =>
{
return typeof(Entity).IsAssignableFrom(t);
}));
})
.ExposeConfiguration(c =>
{
if (RebuildSchema.Value)
new SchemaExport(c).Create(false, true);
});
var sessionFactory = cfg.BuildSessionFactory();
For<ISessionFactory>().Singleton().Use(sessionFactory);
For<ISession>().HybridHttpOrThreadLocalScoped().Use(cx =>
{
var session = cx.GetInstance<ISessionFactory>().OpenSession();
session.FlushMode = FlushMode.Commit;
return session;
});
}
}
}
Listing Entities:
namespace MyTest.NHibernateTest.Entities
{
using System;
using System.Collections.Generic;
using System.Linq;
using FluentNHibernate.Data;
public class Listing : Entity
{
public Listing()
{
Items = new List<ListingItem>();
}
public virtual IList<ListingItem> Items { get; set; }
}
public class ListingItem : Entity
{
public ListingItem()
{
Values = new List<ListingItemValue>();
}
public virtual IList<ListingItemValue> Values { get; set; }
}
public class ListingItemValue : Entity
{
public virtual ListingItem ListingItem { get; set; }
public virtual ListingItemField ListingItemField { get; set; }
}
public class ListingItemField : Entity
{
public virtual string Value { get; set; }
}
}
Program (console):
namespace MyTest.NHibernateTest
{
using System;
using System.Collections.Generic;
using System.Linq;
using StructureMap;
using HibernatingRhinos.Profiler.Appender.NHibernate;
using Microsoft.Practices.ServiceLocation;
using NHibernate;
using System.Threading;
using NHibernate.Transform;
using MyTest.NHibernateTest.Entities;
public static class RebuildSchema
{
public static bool Value { get; set; }
}
class Program
{
static void Main(string[] args)
{
RebuildSchema.Value = true;
Setup();
BuildData();
Work();
Console.ReadLine();
}
static void Setup()
{
NHibernateProfiler.Initialize();
ObjectFactory.Initialize(x =>
{
x.Scan(s =>
{
s.TheCallingAssembly();
s.LookForRegistries();
});
});
ServiceLocator.SetLocatorProvider(() => new StructureMapServiceLocator(ObjectFactory.Container));
}
static void BuildData()
{
var s = ObjectFactory.GetInstance<NHibernate.ISession>();
using (var t = s.BeginTransaction())
{
var listing = new Listing();
s.Save(listing);
var item = new ListingItem();
listing.Items.Add(item);
s.Save(item);
var item2 = new ListingItem();
listing.Items.Add(item2);
s.Save(item2);
var field = new ListingItemField();
field.Value = "A";
s.Save(field);
var field2 = new ListingItemField();
field2.Value = "B";
s.Save(field2);
var value = new ListingItemValue();
value.ListingItem = item;
value.ListingItemField = field;
item.Values.Add(value);
s.Save(value);
var value2 = new ListingItemValue();
value2.ListingItem = item;
value2.ListingItemField = field2;
item.Values.Add(value2);
s.Save(value2);
var value3 = new ListingItemValue();
value3.ListingItem = item2;
value3.ListingItemField = field;
item2.Values.Add(value3);
s.Save(value3);
t.Commit();
}
}
static void Work()
{
var s = ObjectFactory.GetInstance<ISession>();
IList<Listing> foo;
using (var t = s.BeginTransaction())
{
foo = s.QueryOver<Listing>()
.Left.JoinQueryOver<ListingItem>(x => x.Items)
.Left.JoinQueryOver<ListingItemValue>(x => x.Values)
.Left.JoinQueryOver<ListingItemField>(x => x.ListingItemField)
.TransformUsing(Transformers.DistinctRootEntity)
.List();
t.Commit();
}
try
{
Thread.Sleep(100);
var x1 = foo[0];
Thread.Sleep(100);
var x2 = x1.Items[0];
Thread.Sleep(100);
var x3 = x2.Values[0];
Thread.Sleep(100);
var x4 = x2.Values[0].ListingItemField.Value;
}
catch (Exception) { }
}
}
}
Listing, or a fully loadedListingItem? DoListingandListingItemalso haveIdproperties? Could you post the mapping forListingItemValue? – Toomay