FluentNHibernate filter with parameterized IN parameter

Question

In Fluent NHibernate, can I add a parameter to a filter of type List<int> so that the filter condition generates WHERE SomeColumn IN (@x, @y, @z) ?

My use case is to receive an invoice and a subset of its rows, taking into account the invoice identifier and the list of invoice number numbers. I want to wish to receive the lines in the same reverse direction as the invoice. I suppose this is done something like this, but I cannot find the correct type declaration for the parameter type:

Domain Objects:

 public class Invoice { public int Id {get;set;} public List<InvoiceLine> Lines {get;set;} } public class InvoiceLine { public int Id {get;set} public int LineNumber {get;set;} } 

Display:

 public class InvoiceMap : ClassMap<Invoice> { public InvoiceMap() { Id(x => x.Id); HasMany(x => x.Lines).ApplyFilter<OnlyLinesWithNumbersFilter>(); } } public class InvoiceLineMap : ClassMap<InvoiceLine> { public InvoiceLineMap() { Id(x => x.Id); Map(x => x.LineNumber); } } 

Filter Definition:

 public class OnlyLinesWithNumbersFilter : FilterDefinition { public OnlyLinesWithNumbersFilter() { WithName("OnlyLinesWithNumbers"); WithCondition("LineNumber IN (:LineNumbers)"); AddParameter("LineNumbers",?? What to put here ??); } } 

Query:

 var filterName = "OnlyLinesWithNumbers"; session.EnableFilter(filterName).SetParameterList("LineNumbers", new[] {1,2,3}); var query = session.QueryOver<Invoice>() .Where(i => i.Id == 42) .Fetch(i => i.Lines).Eager .TransformUsing(new DistinctRootEntityResultTransformer()); var result = query.SingleOrDefault(); session.DisableFilter(filterName); 

Answer 1

Take-2

 InvoiceLine invoiceLineAlias = null; var list = session.QueryOver<Invoice>() .Where(x => x.Id == 1) .JoinQueryOver(x => x.Lines, () => invoiceLineAlias, JoinType.LeftOuterJoin) .WhereRestrictionOn(() => invoiceLineAlias.LineNumber) .IsIn(new List<int> { 1, 2, 3 }) .List(); 

Produced by sql:

 SELECT this_.Id as Id2_1_, invoicelin1_.Invoice_id as Invoice3_3_, invoicelin1_.Id as Id3_, invoicelin1_.Id as Id3_0_, invoicelin1_.LineNumber as LineNumber3_0_ FROM "Invoice" this_ left outer join "InvoiceLine" invoicelin1_ on this_.Id=invoicelin1_.Invoice_id WHERE this_.Id = @p0 and invoicelin1_.LineNumber in ( @p1, @p2, @p3 ); @p0 = 1 [Type: Int32 (0)], @p1 = 1 [Type: Int32 (0)], @p2 = 2 [Type: Int32 (0)], @p3 = 3 [Type: Int32 (0)] 

Answer 2

You can write

 var list = session.QueryOver<Invoice>() .WhereRestrictionOn(p => p.SomeColumn) .IsIn(someList) .List(); 

Answer 3

To use NHibernate Filters with arrays, put NHibernateUtil.Int32 in the AddParameter method, do this:

 public class OnlyLinesWithNumbersFilter : FilterDefinition { public OnlyLinesWithNumbersFilter() { WithName("OnlyLinesWithNumbers"); WithCondition("LineNumber IN (:LineNumbers)"); AddParameter("LineNumbers", NHibernateUtil.Int32); } } 

And when you turned on the filter, set the array to SetParameterList

 int[] lines = new int[] {1, 2, 3}; session.EnableFilter("OnlyLinesWithNumbers").SetParameterList("LineNumbers", lines); 

In my tests I use NHibernate 4.0.0.400

Answer 4

I think this is the correct syntax, I just wrote it from the head. :)

 InvoiceLine invoiceLine = null; var result = session.QueryOver<Invoice>() .Where(x => x.Id == 42) .JoinQueryOver(x => x.InvoiceLines, () => invoiceLine) .WhereRestrictionOn(x => x.ItemNumber) .IsIn(new[] {1, 2, 3}) .SingleOrDefault();