FluentNHibernate filter with parameterized IN clau

问题:

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

My use case is to fetch an invoice and a subset of its lines, given the ID of the invoice and a list of invoice line numbers. I want to eager fetch the lines in the same roundtrip as the invoice. I assume it 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;}
}

Mappings:

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);

回答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 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)]

回答2:

you can write

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

回答3:

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

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

And when you enabled filter, set an array in SetParameterList

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

In my tests I use NHibernate 4.0.0.400

回答4:

I think this is the correct syntax, I just wrote this off the top of my 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();