FluentNHibernate filter with parameterized IN clause

Question

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

Just a guess, try putting `AddParameter("LineNumbers", NHibernateUtil.GuessType(typeof(int[])));` — Phill, Jul 14 '11 at 07:56
Thanks @Phill, but that fails with an "Incorrect type for parameter" (just as it does if I put any other type there) — Jonas Høgh, Jul 14 '11 at 08:03
Hmm, I think you might have to implement your own UserType which converts it to a comma separated string or something. — Phill, Jul 14 '11 at 08:05

score 6 · Accepted Answer · answered Jul 14 '11 at 15:26

6

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

answered Jul 14 '11 at 15:26

VahidN

18,457
8
73
117

1

Thanks, the JoinType.LeftOuterJoin actually allows the eager fetch to work correctly. Does not seem logical to me why, though. And the produced SQL joins back and forth between invoice and lines and back to invoice ... but the result is correct. – Jonas Høgh Jul 14 '11 at 17:43
1

+1 - I don't really understand why it works because giving it a test with and without the join type, produces the same query. But without it causes it to not keep the results. The question I asked on NHUsers suggested the same solution. – Phill Jul 29 '11 at 02:39
took me a while to figure out what the hell was going on, but it worked! Thanks!!! – viggity Dec 16 '11 at 20:52
The question is about how to solve a problem in a correct way and that problem is how to generate `WHERE SomeColumn IN` (first line of the question). – VahidN May 20 '16 at 08:43

score 2 · Answer 2 · answered May 20 '16 at 17:41

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

score 1 · Answer 3 · answered Jul 14 '11 at 09:24

1

you can write

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

answered Jul 14 '11 at 09:24

VahidN

18,457
8
73
117

That is not, as far as I know, possible when "SomeColumn" is a collection (here the invoice lines) – Jonas Høgh Jul 14 '11 at 09:29
@VahidH - That answer is completely unrelated to the question. He's asking about Table Filters, not a normal Query with an In-Clause. – Phill Jul 14 '11 at 09:39
@Jonas - If you want to write the filter at Query level, it is possible. I'll write up an answer for that if you like. – Phill Jul 14 '11 at 09:42
@Phill - that would be great, probably a nicer solution than the custom type. – Jonas Høgh Jul 14 '11 at 09:47

score 0 · Answer 4 · answered Jul 14 '11 at 09:54

0

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

answered Jul 14 '11 at 09:54

Phill

18,398
7
62
102

This does not work as desired. It generates SQL similar to the following: SELECT * FROM Invoice i INNER JOIN InvoiceLine il ON i.Id=il.InvoiceId LEFT OUTER JOIN Invoice i2 ON i.Id=i2.Id WHERE i.Id = 42 and il.LineNumber in (1,2,3) . But does not correctly eager-fetch the lines, which means that ALL invoice lines are lazy loaded when the collection property is accessed. – Jonas Høgh Jul 14 '11 at 10:55
Hmm I just got home, wrote a quick example and your right. I would consider that a bug. – Phill Jul 14 '11 at 12:16
I just posted a question on NHUsers with an example. http://groups.google.com/group/nhusers/browse_thread/thread/707be98a32891c64 – Phill Jul 14 '11 at 12:25

FluentNHibernate filter with parameterized IN clause

4 Answers4

Linked