FluentNHibernate滤波器的参数化IN子句 [英] FluentNHibernate filter with parameterized IN clause

问题描述

在功能NHibernate，是否有可能添加参数类型的过滤器名单，其中，INT＆GT; 这样的过滤条件生成一个 WHERE SomeColumn IN（@x，@y，@z）？

我的使用情形是取发票和它的线的子集，考虑到发票的ID和发票行号的列表。我想在同一个往返的发票预先抓取线。我想这是做这样的事情，但我不能找到正确的类型声明的参数类型：

域对象：

 公共类发票{
  公众诠释编号{获取;集;}
  公开名单＆LT; InvoiceLine＆GT;行{获取;集;}
}

公共类InvoiceLine {
  公众诠释编号{获取;集}
  公众诠释LINENUMBER {获取;集;}
}
 

映射：

 公共类InvoiceMap：ClassMap＆LT;发票＆GT; {
  公共InvoiceMap（）{
    ID（X =＆GT; x.Id）;
    的hasMany（X =＆GT; x.Lines）.ApplyFilter＆LT; OnlyLinesWithNumbersFilter＆GT;（）;
  }
}

公共类InvoiceLineMap：ClassMap＆LT; InvoiceLine＆GT; {
  公共InvoiceLineMap（）{
    ID（X =＆GT; x.Id）;
    地图（X =＆GT; x.LineNumber）;
  }
}
 

过滤器定义：

 公共类OnlyLinesWithNumbersFilter：FilterDefinition
{
    公共OnlyLinesWithNumbersFilter（）
    {
        WithName（OnlyLinesWithNumbers）;
        WithCondition（LINENUMBER IN（：LineNumbers））;
        AddParameter（LineNumbers，??怎么把这里？）;
    }
}
 

查询：

  VAR FILTERNAME =OnlyLinesWithNumbers;
session.EnableFilter（FILTERNAME）.SetParameterList（LineNumbers，新的[] {1,2,3}）;

VAR的查询= session.QueryOver＆LT;发票＆GT;（）
       。凡（I =＆GT; i.Id == 42）
       .Fetch（I =＆GT; i.Lines）.Eager
       .TransformUsing（新DistinctRootEntityResultTransformer（））;

VAR的结果= query.SingleOrDefault（）;
session.DisableFilter（FILTERNAME）;
 

解决方案

取-2

  InvoiceLine invoiceLineAlias​​ = NULL;
VAR列表= session.QueryOver＆LT;发票＆GT;（）
                  。凡（X =＆GT; x.Id == 1）
                  .JoinQueryOver（X =＆GT; x.Lines，（）=＆GT; invoiceLineAlias​​，JoinType.LeftOuterJoin）
                  .WhereRestrictionOn（（）=＆GT; invoiceLineAlias​​.LineNumber）
                  .IsIn（新列表与所述; INT＆GT; {1，2，3}）
                  的.List（）;
 

产生的SQL：

  SELECT
        this_.Id为Id2_1_，
        invoicelin1_.Invoice_id为Invoice3_3_，
        invoicelin1_.Id为Id3_，
        invoicelin1_.Id为Id3_0_，
        invoicelin1_.LineNumber为LineNumber3_0_
    从
        发票THIS_
    左外连接
        InvoiceLineinvoicelin1_
            在this_.Id = invoicelin1_.Invoice_id
    哪里
        this_.Id = @ P0
        和invoicelin1_.LineNumber在（
            @ P1，P2 @，@ P3
        ）;
    @ P0 = 1 [类型：的Int32（0）]，
    @ P1 = 1 [类型：的Int32（0）]，
    @ P2 = 2 [类型：的Int32（0）]，
    @ P3 = 3 [类型：的Int32（0）]
 

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

解决方案

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

这篇关于FluentNHibernate滤波器的参数化IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！