How to join using NHibernate QueryOver

nhibernatequeryover

I have the following situation in my code and am not able to solve it. Situation –

var grpA = Session.QueryOver(() => _employeeGroupRelation));
var grpB = Session.QueryOver(() => _employeeGroup));

// var grpC should join grpA and grpB on _employeeGroupRelation.groupID = _employeeGroup.ID

Question –
Is there some way to join grpA and grpB using QueryOver syntax?
Is it possible to do this without using the List() on grpA or grpB, because each of them will hold close to 10,000 records and I don't want to dump them into memory.
Is this the right use of QueryOver? Are there cleaner ways of achieving what I'm trying to solve?

It maybe a basic doubt, but I'm a newbie to NHib and QueryOver.

Edit –

select * from employeeGroup a
inner join employeeGroupRelation b on a.ID = b.ID

This is what I'm trying to do in SQL.

Best Answer

The easiest way to do it:

session.QueryOver<EmployeeGroup>()
   .JoinQueryOver(employeeGroup => employeeGroup.EmployeeGroupRelation)
   .Take(1000) // The same as top in SQL, if you don't want load all of entities
   .TransformUsing(Transformers.DistinctRootEntity)
   .List();

"JoinQueryOver" gets "EmployeeGroup" and related "EmployeeGroupRelation" as proxy (depends from mapping) for LazyLoad

If you don't want to use LazyLoad, you can do this:

session.QueryOver<EmployeeGroup>()
   .Fetch(employeeGroup => employeeGroup.EmployeeGroupRelation).Eager
   .Take(1000) // The same as top in SQL, if you don't want load all of entities
   .TransformUsing(Transformers.DistinctRootEntity)
   .List();

"Fetch" gets "EmployeeGroup" and related "EmployeeGroupRelation" (not proxy)

Related Solutions

C# – NHibernate using QueryOver with WHERE IN

I've found the solution!! :-)

var qOver = _HibSession.QueryOver<MyModel>(() => baseModel)
    .JoinAlias(() => baseModel.Submodels, () => subModels)
    .WhereRestrictionOn(() => subModels.ID).IsIn(IDsSubModels)
    .List<MyModel>();

NHibernate QueryOver: How to join unrelated entities

In NHibernate 5.1+ it's possible for QueryOver/Criteria via Entity Join:

int associatedId = 123;

MyObject alias = null;
DatabaseView viewAlias = null;

var results = session.QueryOver<MyObject>(() => alias)
    .JoinEntityAlias(() => viewAlias, () => viewAlias.ObjectId == alias.ObjectId && viewAlias.AssociatedId == associatedId)
    .List();

Criteria example:

int associatedId = 123;
var results = session.CreateCriteria<MyObject>("alias")
    .CreateEntityAlias(
            "viewAlias",
            Restrictions.EqProperty("viewAlias.ObjectId", "alias.ObjectId")
            && Restrictions.Eq("viewAlias.AssociationId", associatedId),
            JoinType.InnerJoin,
            typeof(DatabaseView).FullName)
    .List();

Best Answer

Related Solutions

C# – NHibernate using QueryOver with WHERE IN

NHibernate QueryOver: How to join unrelated entities

Related Topic