nHibernate 3.0.0.4000在查询中对bool的处理方式不同? [英] nHibernate 3.0.0.4000 handles bools differently in queries?

问题描述

我刚刚将nHibernate从版本3.0.0.1002更新到了3.0.0.4000.一旦我这样做，我的许多查询就会开始失败.一个这样的查询是这样的:

I just updated nHibernate from version 3.0.0.1002 to 3.0.0.4000. As soon as I did that many of my queries started failing. One such query is this:

var items = (from b in session.Query<InvoiceDetail>() 
                            where b.Customer == AddressedToCustomer && b.IsCreditNote == !addInvoices 
                            orderby b.DueDate , b.InvoiceNumber 
                            select b).ToList(); 

在3.0.0.1002中生成的SQL:

SQL Generated in 3.0.0.1002:

2011-02-17 15:55:43,612调试选择 invoicedet0_.Id为Id3_， invoicedet0_.InvoiceNumber为 发票N2_3_， invoicedet0_.DocumentNumber为 Document3_3_， invoicedet0_.DocumentDate为 Document4_3_，发票金额为0_.DueDate，格式为 DueDate3_， invoicedet0_.DivisionDetails作为 发票6_3_，发票0_.Is发票 作为IsInvoice3_， invoicedet0_.IsCreditNote为 IsCredit8_3_， invoicedet0_.OriginalAmount as Original9_3_，invoicedet0_.金额为 Amount3_，invoicedet0_.LRNo作为LRNo3_， invoicedet0_.LRDate作为LRDate3_， invoicedet0_.DispatchedBy as Dispatc13_3_，发票金额0_.信用天数 作为CreditDays3_， invoicedet0_.CustomerId为 来自InvoiceDetails的CustomerId3_ invoicedet0_其中 ((invoicedet0_.CustomerId为null)和 (@ p0为空)或 invoicedet0_.CustomerId=@p0)和大小写 当invoicedet0_.IsCreditNote = 1时 1 else 0 end = case @ p1 = 1则为1 否则0个结束订单 invoicedet0_.DueDate asc， invoicedet0_.InvoiceNumber asc; @ p0 = 101790 [Type:Int32(0)]，@ p1 = False [Type:Int32(0)]

2011-02-17 15:55:43,612 DEBUG select invoicedet0_.Id as Id3_, invoicedet0_.InvoiceNumber as InvoiceN2_3_, invoicedet0_.DocumentNumber as Document3_3_, invoicedet0_.DocumentDate as Document4_3_, invoicedet0_.DueDate as DueDate3_, invoicedet0_.DivisionDetails as Division6_3_, invoicedet0_.IsInvoice as IsInvoice3_, invoicedet0_.IsCreditNote as IsCredit8_3_, invoicedet0_.OriginalAmount as Original9_3_, invoicedet0_.Amount as Amount3_, invoicedet0_.LRNo as LRNo3_, invoicedet0_.LRDate as LRDate3_, invoicedet0_.DispatchedBy as Dispatc13_3_, invoicedet0_.CreditDays as CreditDays3_, invoicedet0_.CustomerId as CustomerId3_ from InvoiceDetails invoicedet0_ where ((invoicedet0_.CustomerId is null) and (@p0 is null) or invoicedet0_.CustomerId=@p0) and case when invoicedet0_.IsCreditNote=1 then 1 else 0 end=case when @p1=1 then 1 else 0 end order by invoicedet0_.DueDate asc, invoicedet0_.InvoiceNumber asc;@p0 = 101790 [Type: Int32 (0)], @p1 = False [Type: Int32 (0)]

在3.0.0.4000中生成的SQL:

SQL Generated in 3.0.0.4000:

2011-02-17 16:22:15,275调试选择 invoicedet0_.Id为Id3_， invoicedet0_.InvoiceNumber为 发票N2_3_， invoicedet0_.DocumentNumber为 Document3_3_， invoicedet0_.DocumentDate为 Document4_3_，发票金额为0_.DueDate，格式为 DueDate3_， invoicedet0_.DivisionDetails作为 发票6_3_，发票0_.Is发票 作为IsInvoice3_， invoicedet0_.IsCreditNote为 IsCredit8_3_， invoicedet0_.OriginalAmount as Original9_3_，invoicedet0_.金额为 Amount3_，invoicedet0_.LRNo作为LRNo3_， invoicedet0_.LRDate作为LRDate3_， invoicedet0_.DispatchedBy as Dispatc13_3_，发票金额0_.信用天数 作为CreditDays3_， invoicedet0_.CustomerId为 来自InvoiceDetails的CustomerId3_ invoicedet0_其中 invoicedet0_.CustomerId=@p0和大小写 当invoicedet0_.IsCreditNote = 1时 'true'else'false'end = case when @ p1 ='true'然后是'true'否则是'false' 通过invoicedet0_.DueDate asc结束订单， invoicedet0_.InvoiceNumber asc; @ p0 = 101790 [Type:Int32(0)]，@ p1 = 'False'[Type:字符串(0)]

2011-02-17 16:22:15,275 DEBUG select invoicedet0_.Id as Id3_, invoicedet0_.InvoiceNumber as InvoiceN2_3_, invoicedet0_.DocumentNumber as Document3_3_, invoicedet0_.DocumentDate as Document4_3_, invoicedet0_.DueDate as DueDate3_, invoicedet0_.DivisionDetails as Division6_3_, invoicedet0_.IsInvoice as IsInvoice3_, invoicedet0_.IsCreditNote as IsCredit8_3_, invoicedet0_.OriginalAmount as Original9_3_, invoicedet0_.Amount as Amount3_, invoicedet0_.LRNo as LRNo3_, invoicedet0_.LRDate as LRDate3_, invoicedet0_.DispatchedBy as Dispatc13_3_, invoicedet0_.CreditDays as CreditDays3_, invoicedet0_.CustomerId as CustomerId3_ from InvoiceDetails invoicedet0_ where invoicedet0_.CustomerId=@p0 and case when invoicedet0_.IsCreditNote=1 then 'true' else 'false' end=case when @p1='true' then 'true' else 'false' end order by invoicedet0_.DueDate asc, invoicedet0_.InvoiceNumber asc;@p0 = 101790 [Type: Int32 (0)], @p1 = 'False' [Type: String (0)]

我正在使用约定将布尔值转换为整数(true = 1，false = 0).以前的版本正在进行此转换，而新版本则没有. bool在较新的版本中将转换为字符串，但应根据约定将其转换为int.

I am using a convention to convert bools into ints (true=1, false=0). The previous version is doing this conversion, the newer version isn't. The bool is converted to a string in the newer version, but it should be converted to int according to the convention.

从生成的SQL:
！addInvoices"作为字符串在sql查询中传递，并在先前版本中以int形式传递:
3.0.0.1002:@p1 = True [Type:Int32(0)]
3.0.0.4000:@ p1 ='True'[Type:字符串(0)]

From generated SQL:
"!addInvoices" is passed as a string in the sql query and int in the previous version:
3.0.0.1002: @p1 = True [Type: Int32 (0)]
3.0.0.4000: @p1 = 'True' [Type: String (0)]

还将IsCreditNote数据库字段在新版本中与"true"和"false"进行比较，并在先前版本中将其与1和0进行比较.

Also, the IsCreditNote database field is compared to 'true' and 'false' in the new version, and with 1 and 0 in the previous version.

第二，即使我删除了约定，这也是3.0.0.4000中两个不同命令的输出:
插入:

Secondly, even if I remove the convention, this is the output for two different commands in 3.0.0.4000:
Insert:

2011-02-20 10:18:22,977调试插入 INTO发票明细(发票编号， DocumentNumber，DocumentDate，DueDate， DivisionDetails，IsInvoice， IsCreditNote，OriginAmount，Amount， LRNo，LRDate，DispatchedBy， CreditDays，CustomerId)VALUES(@ p0， @ p1，@ p2，@ p3，@ p4，@ p5，@ p6，@ p7， @ p8，@ p9，@ p10，@ p11，@ p12，@ p13)； 选择last_insert_rowid(); @ p0 = 9070183358 [Type:Int64(0)]，@ p1 = 28592879 [Type:Int64(0)]，@ p2 = '20110210'[Type:字符串(0)]，@ p3 = '20110303'[Type:字符串(0)]，@ p4 = NULL [类型:字符串(0)]，@ p5 =真 [类型:布尔值(0)]，@ p6 =否 [类型:布尔值(0)]，@ p7 = 2685 [类型: 十进制(0)]，@ p8 = 2685 [类型: 十进制(0)]，@ p9 = NULL [类型:字符串 (0)]，@ p10 = NULL [类型:字符串(0)]， @ p11 = NULL [类型:字符串(0)]，@ p12 = 21 [类型:Int32(0)]，@ p13 = 101760 [Type:Int32(0)]

2011-02-20 10:18:22,977 DEBUG INSERT INTO InvoiceDetails (InvoiceNumber, DocumentNumber, DocumentDate, DueDate, DivisionDetails, IsInvoice, IsCreditNote, OriginalAmount, Amount, LRNo, LRDate, DispatchedBy, CreditDays, CustomerId) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13); select last_insert_rowid();@p0 = 9070183358 [Type: Int64 (0)], @p1 = 28592879 [Type: Int64 (0)], @p2 = '20110210' [Type: String (0)], @p3 = '20110303' [Type: String (0)], @p4 = NULL [Type: String (0)], @p5 = True [Type: Boolean (0)], @p6 = False [Type: Boolean (0)], @p7 = 2685 [Type: Decimal (0)], @p8 = 2685 [Type: Decimal (0)], @p9 = NULL [Type: String (0)], @p10 = NULL [Type: String (0)], @p11 = NULL [Type: String (0)], @p12 = 21 [Type: Int32 (0)], @p13 = 101760 [Type: Int32 (0)]

更新:

2011-02-20 10:10:13,941调试选择 invoicedet0_.Id为Id3_， invoicedet0_.InvoiceNumber为 发票N2_3_， invoicedet0_.DocumentNumber为 Document3_3_， invoicedet0_.DocumentDate为 Document4_3_，发票金额为0_.DueDate，格式为 DueDate3_， invoicedet0_.DivisionDetails作为 发票6_3_，发票0_.Is发票 作为IsInvoice3_， invoicedet0_.IsCreditNote为 IsCredit8_3_， invoicedet0_.OriginalAmount as Original9_3_，invoicedet0_.金额为 Amount3_，invoicedet0_.LRNo作为LRNo3_， invoicedet0_.LRDate作为LRDate3_， invoicedet0_.DispatchedBy as Dispatc13_3_，发票金额0_.信用天数 作为CreditDays3_， invoicedet0_.CustomerId为 来自InvoiceDetails的CustomerId3_ invoicedet0_其中 invoicedet0_.CustomerId=@p0和大小写 当invoicedet0_.IsCreditNote = 1时 'true'else'false'end = case when @ p1 ='true'然后是'true'否则是'false' 通过invoicedet0_.DueDate asc结束订单， invoicedet0_.InvoiceNumber asc; @ p0 = 107233 [Type:Int32(0)]，@ p1 ='True' [类型:字符串(0)]

2011-02-20 10:10:13,941 DEBUG select invoicedet0_.Id as Id3_, invoicedet0_.InvoiceNumber as InvoiceN2_3_, invoicedet0_.DocumentNumber as Document3_3_, invoicedet0_.DocumentDate as Document4_3_, invoicedet0_.DueDate as DueDate3_, invoicedet0_.DivisionDetails as Division6_3_, invoicedet0_.IsInvoice as IsInvoice3_, invoicedet0_.IsCreditNote as IsCredit8_3_, invoicedet0_.OriginalAmount as Original9_3_, invoicedet0_.Amount as Amount3_, invoicedet0_.LRNo as LRNo3_, invoicedet0_.LRDate as LRDate3_, invoicedet0_.DispatchedBy as Dispatc13_3_, invoicedet0_.CreditDays as CreditDays3_, invoicedet0_.CustomerId as CustomerId3_ from InvoiceDetails invoicedet0_ where invoicedet0_.CustomerId=@p0 and case when invoicedet0_.IsCreditNote=1 then 'true' else 'false' end=case when @p1='true' then 'true' else 'false' end order by invoicedet0_.DueDate asc, invoicedet0_.InvoiceNumber asc;@p0 = 107233 [Type: Int32 (0)], @p1 = 'True' [Type: String (0)]

在插入"中，传递的参数是布尔值(@ p6)，在选择"中，参数是字符串(@ p1).

In Inserts, the passed parameter is a Boolean (@p6), and in Selects, the parameter is a String (@p1).

问题是，我在nHibernate JIRA上将此问题发布为问题(实际上大部分是是从该问题线程复制的)，但Patrick Earl作为非问题而关闭了该问题.他说这是标准行为.

The thing is, I posted this as an issue on nHibernate JIRA (actually most of this is copied from that issue thread), but Patrick Earl closed the issue as a non-issue. He said this is the standard behavior.

nHibernate之前的所有版本均运行正常.只有这一行为有所不同.有人可以评论这确实是一个错误，还是Patrick正确，我需要在数据库中解决此问题?

All the versions of nHibernate before this version behaved correctly. Only this one behaves differently. Can anybody comment is this really a bug, or is Patrick correct and I need to workaround this problem in my database?

我的数据库是SQLite，正在使用Fluent nHibernate.

My database is SQLite and I am using Fluent nHibernate.

推荐答案

Phill是正确的. Query/LINQ提供程序有问题.

Phill is right. The Query/LINQ provider has issues.

此Criteria/QueryOver API可以完美运行:

This Criteria/QueryOver API works perfectly:

var items = session.QueryOver<InvoiceDetail>()
        .Where(i => i.Customer == AddressedToCustomer)
        .And(i => i.IsCreditNote != addInvoices)
        .OrderBy(i => i.DueDate).Asc
        .ThenBy(i => i.InvoiceNumber).Asc
        .List();

更令人惊讶的是，提供程序的开发人员只是将问题/错误视为非问题而关闭，甚至没有适当地对其进行检查.为了确保，我打算将所有Query<>调用替换为QueryOver<>调用.对于像nHibernate这样的项目，确实很可惜，特别是考虑到喜欢linq的像我这样的开发人员.

And even more surprising is that the developers of the provider just close issues/bugs as non-issues without even looking into them properly. I am planning to replace all Query<> calls to QueryOver<> calls just to be sure. For a project like nHibernate, this is indeed a pity specially considering developers like me who love linq.

这篇关于nHibernate 3.0.0.4000在查询中对bool的处理方式不同?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！