CODEWITHSUNDEEP
nhibernateorchardcms
Olga Zemskova
Olga Zemskova

Reputation: 280

NHibernate JoinQueryOver query in Orchard

I have an Orchard website. There are two connected entities: agencies (AgencyPartRecord ) and facilities (FacilityPartRecord), they are connected n-to-n with AgencyFacilitiesPartRecord. Here are the corresponding records:

 public class AgencyPartRecord : ContentPartRecord
 {
    ...

    public virtual IList<AgencyFacilitiesPartRecord> AgencyFacilitiesPartRecords { get; set; }
    ...
}

public class AgencyFacilitiesPartRecord
{
    public virtual int Id { get; set; }

    public virtual AgencyPartRecord AgencyPartRecord { get; set; }

    public virtual FacilityPartRecord FacilityPartRecord { get; set; }
}

public class FacilityPartRecord : ContentPartRecord
{
    public virtual string Name { get; set; }

    public virtual string Description { get; set; }
}

Now I need to filter out agencies by the set of facilities, so that only agencies having all the facilities in list should by selected.

In the end I want to get SQL like this:

SELECT *
FROM AgencyPartRecord
WHERE Id IN
(
    SELECT a.AgencyPartRecord_Id
    FROM AgencyFacilitiesPartRecord a              
    WHERE a.FacilityPartRecord_Id IN (... filter values here ...)
    GROUP BY a.AgencyPartRecord
    HAVING COUNT(a.Id) = <number of filter values>
)

I have written the following query (filter.Facilities is of type List<int>):

IQueryOver<AgencyPartRecord, AgencyPartRecord> agencies = ... // apply other filters
AgencyFacilitiesPartRecord facility = null;    

var fsub = QueryOver.Of<AgencyFacilitiesPartRecord>(() => facility)                    
                .WhereRestrictionOn(r => r.FacilityPartRecord.Id).IsIn(filter.Facilities)
                .SelectList(list =>
                    list                            
                        .SelectGroup(a => a.AgencyPartRecord.Id)
                        .SelectCount(a => a.FacilityPartRecord.Id))
                .Where(Restrictions.Eq(
                    Projections.Count(
                        Projections.Property(() => facility.FacilityPartRecord.Id)), filter.Facilities.Count))
                .SelectList(list => list.Select(a => a.AgencyPartRecord.Id));


 agencies = agencies
                .WithSubquery.WhereProperty(a => a.Id).In(fsub);

The problem is this query does not produce GROUP BY clause in SQL:

SELECT ...  
FROM AgencyPartRecord this_ 
WHERE ...
    and this_.Id in 
(
    SELECT this_0_.AgencyPartRecord_id as y0_ 
    FROM AgencyFacilitiesPartRecord this_0_ 
    WHERE this_0_.FacilityPartRecord_id in (@p2, @p3) 
    HAVING count(this_0_.FacilityPartRecord_id) = @p4
)

What am I doing wrong?

Thanks!

Upvotes: 1

Views: 220

Answers (1)

Olga Zemskova
Olga Zemskova

Reputation: 280

I finally got it! :) The right code is:

AgencyFacilitiesPartRecord facility = null;

var fsub = QueryOver.Of<AgencyFacilitiesPartRecord>(() => facility)
    .WhereRestrictionOn(r => r.FacilityPartRecord.Id).IsIn(filter.Facilities)
    .SelectList(list => list
                   .SelectGroup(r => r.AgencyPartRecord.Id)
                )
    .Where(Restrictions.Eq(
        Projections.Count(Projections.Property(() => facility.FacilityPartRecord.Id)), filter.Facilities.Count));

agencies = agencies.WithSubquery.WhereProperty(a => a.Id).In(fsub);

Andrew, thanks again for your QueryOver series (http://blog.andrewawhitaker.com/queryover-series/)!

Upvotes: 1

Related Questions