CODEWITHSUNDEEP
c#nhibernatefetchrecursive-queryselect-n-plus-1
Roland Mayerhofer
Roland Mayerhofer

Reputation: 31

NHibernate Select N+1 and Recursive

I've got a couple of data classes:

public class RecordGroup
{
    public virtual DataRecord RootDataRecord;
}

public class DataRecord 
{
    public virtual string Name { get; set; }
    public virtual RecordGroup RecordGroup { get; set; }
    public virtual IList<DataRecord> Children { get; set; }
    public virtual DataRecord Parent { get; set; }
    public virtual IList<DataProperty> DataProperties { get; set; }
    public virtual IList<Foto> Fotos { get; set; }
}

public class DataProperty
{
    public virtual string Name { get; set; }
    public virtual string Value { get; set; }
    public virtual IList<Foto> Fotos { get; set; }
}

public class Foto
{
    public virtual string Name { get; set; }
    public virtual byte[] Data { get; set; }
}

So 1 RecordGroup is "connected" to several DataRecords, having several children (which again got children etc.) each of them having several Properties and Fotos. I need all the DataRecords including Children, Properties and Fotos according to a certain RecordGroup.

Doing this within raw SQL is a simple statement with a few joins, but when i try to do this with linq and nhibernate it results in 1500 Select N+1 Statements, and an enormous slowdown.

I already tried .FetchMany( x => x.Children );

How is it possible to get the whole "datatree" of 1 Recordgroup within 1 Query?

Thanks in advance!!!!

Upvotes: 1

Views: 811

Answers (1)

Anton
Anton

Reputation: 1583

I think, you need something like this:

   var recordGroupId = // your recordGroup Id
   Session.QueryOver<DataRecord>()
     .Where(dataRecord.RecordGroup.Id == recordGroupId)
     .Fetch(dataRecord  => dataRecord.Children).Eager
     .Fetch(dataRecord  => dataRecord.DataProperties).Eager
     .Fetch(dataRecord  => dataRecord.Fotos).Eager
     .TransformUsing(Transformers.DistinctRootEntity)
     .List<DataRecord>();

Upvotes: 1

Related Questions