Linq to Nhibernate using ANY

I have a requirement to query parent records if child records is one of given choice and i am unable to get this correctly

the following works i.e if i get all the records and apply where clause on the results

    var linqSesssion = Session.Linq<PurchaseRequisition>();
    linqSesssion.QueryOptions.RegisterCustomAction(
        c => c.SetResultTransformer(new DistinctRootEntityResultTransformer()));
    var requisitionsTypesAllowed = new[] {"MA", "SC", "BO", "XH", "UP"};
    var requisitions = (from purchaseRequisition in linqSesssion
                        where purchaseRequisition.RequisitionerCode == employeeNumber
                        orderby purchaseRequisition.RequisitionNumber descending
                        select purchaseRequisition);
    var filteredRequisitions = fetchClosedRequisitions
                             ? requisitions.ToList()
                             : requisitions.Where(r => r.State != "Closed").ToList();
    return filteredRequisitions.Where(r => r.DocumentTexts.Any(d =>(d.OutputType == "PRTYPE" && requisitionsTypesAllowed.Contains(d.NoteText)))).AsQueryable();

But when using Linq to Nhibernate as follows it doesnt return all the records

return fetchClosedRequisitions
                       ? requisitions.Where(r =>r.DocumentTexts.Any(d => (d.OutputType == "PRTYPE" && requisitionsTypesAllowed.Contains(d.NoteText))))
                       : requisitions.Where(r => r.State != "Closed" && r.DocumentTexts.Any(d => (d.OutputType == "PRTYPE" && requisitionsTypesAllowed.Contains(d.NoteText))));

Following is the SQL(WHERE CLAUSE) generated

WHERE this_.REQUISITIONER_CODE = :p0 and (not (this_.STATE = :p1) and this_.REQUISITION_NO in (SELECT this_0_.REQUISITION_NO as y0_ FROM PURCHASE_REQUISITION this_0_ inner join DOCUMENT_TEXT d1_ on this_0_.NOTE_ID=d1_.NOTE_ID WHERE exists(select 1 from DOCUMENT_TEXT where this_0_.REQUISITION_NO=NOTE_ID) and (d1_.OUTPUT_TYPE = :p2 and d1_.NOTE_TEXT in (:p3, :p4, :p5, :p6, :p7)))) ORDER BY this_.REQUISITION_NO desc;:p0 = '2919', :p1 = 'Closed', :p2 = 'PRTYPE', :p3 = 'MA', :p4 = 'SC', :p5 = 'BO', :p6 = 'XH', :p7 = 'UP'

ansaurus

tags:

views:

answers:

Linq to Nhibernate using ANY

related questions