NHibernate Criteria API help needed please

Question

I'm trying to create a criteria query that grabs "RejectedRecords" by useruploaded that are not flagged as being deleted or the Facility in the RejectedRecord is in a list of facilities that a user is assigned to (user.UserFacilities). I have the first part working fine (By User and Not Deleted) but I'm not sure how to add the OR clause to take records that are in the collection of user-facilities. In SQL it would look like:

SELECT * 

FROM RejectedRecords

WHERE (UserUploaded = 1 AND IsDeleted = 0) 
OR FacilityId IN (SELECT FacilityId FROM UserFacility WHERE UserId = 1)

Here's my attempt in C# (Not sure how to perform the subquery):

    public IList<RejectedRecord> GetRejectedRecordsByUser(User u)
    {
        return base._session.CreateCriteria(typeof(RejectedRecord))
            .Add(
                    (
                        Expression.Eq(RejectedRecord.MappingNames.UserUploaded, u)
                        && Expression.Eq(RejectedRecord.MappingNames.IsDeleted, false)

                    )
            )
            .List<RejectedRecord>();
    }

score 3 · Accepted Answer · edited May 23 '17 at 11:55

3

The Key is to use Disjunction and Conjunction combined with a Subquery.

var facilityIdQuery = DetachedCriteria.For<UserFacility>()
   .Add(Expression.Eq("User.Id", u))
   .SetProjection(Projections.Property("Facility.Id"));

var results = session.CreateCriteria<RejectedRecords>()
    .Add(
        Restrictions.Disjunction()
        .Add(
            Restrictions.And(
              Restrictions.Eq(RejectedRecord.MappingNames.UserUploaded, u),
              Restrictions.Eq(RejectedRecord.MappingNames.IsDeleted, false)
            )
        )
        .Add(Subqueries.PropertyIn("FacilityId",facilityIdQuery))
    ).List();

edited May 23 '17 at 11:55

Community

1
1

answered Jun 30 '11 at 14:11

Mark Perry

1,705
10
12

the concept is correct but you're writting it wrong i think.... it should written as (pseudocode for simplicity) Disjunction.Add(Expression.And(Eq.useruploaded, Eq.isdeleted)).Add(suquery.propertyin) – Jaguar Jul 01 '11 at 08:36
@Jaguar You are right I had the chain wrong. I have now edited the answer and tested the syntax and SQL for correctness. – Mark Perry Jul 01 '11 at 08:54

NHibernate Criteria API help needed please

1 Answers1