可为空的列上的LINQ联接表 [英] LINQ-Join tables on nullable columns

问题描述

如何在可为空的列上联接表?

how to JOIN tables on nullable columns?

我有以下LINQ查询，RMA.fiCharge可以为NULL:

I have following LINQ-query, RMA.fiCharge can be NULL:

Dim query = From charge In Services.dsERP.ERP_Charge _
                     Join rma In Services.dsRMA.RMA _
                     On charge.idCharge Equals rma.fiCharge _
                     Where rma.IMEI = imei
               Select charge.idCharge

在query.ToArray()中，我收到从类型'DBNull'到类型'Integer'的转换无效":

I get a "Conversion from type 'DBNull' to type 'Integer' is not valid" in query.ToArray():

Dim filter = _
       String.Format(Services.dsERP.ERP_Charge.idChargeColumn.ColumnName & " IN({0})", String.Join(",", query.ToArray))

所以我可以在查询中附加一个WHERE RMA.fiCharge IS NOT NULL.但是，如何在LINQ中做到这一点?还是有另一种选择?

So i could append a WHERE RMA.fiCharge IS NOT NULL in the query. But how to do that in LINQ or is there another option?

谢谢.

解决方案:

问题在于，如果您查询整数列上的任何NULL值，则DataSet不支持Nullable-Type，但是会生成InvalidCastException(感谢Martinho). 来自dahlbyk的修改后的LINQ查询仅需少量修改即可工作.数据集使用AllowDbNull = True(在本例中为IsfiChargeNull)为每个列生成布尔属性.

The problem was that the DataSet does not support Nullable-Types but generates an InvalidCastException if you query any NULL-Values on an integer-column(thanks Martinho). The modified LINQ-query from dahlbyk works with little modification. The DataSet generates a boolean-property for every column with AllowDbNull=True, in this case IsfiChargeNull.

Dim query = From charge In Services.dsERP.ERP_Charge _
            Join rma In (From rma In Services.dsRMA.RMA _
                         Where Not rma.IsfiChargeNull
                         Select rma)
              On charge.idCharge Equals rma.fiCharge _
            Where rma.IMEI = imei
            Select charge.idCharge

推荐答案

您是否尝试过将空检查添加到where子句中?

Have you tried adding the null check to your where clause?

Dim query = From charge In Services.dsERP.ERP_Charge _
            Join rma In Services.dsRMA.RMA _
              On charge.idCharge Equals rma.fiCharge _
            Where rma.fiCharge <> Nothing AndAlso rma.IMEI = imei
            Select charge.idCharge

如果这不起作用，您可以尝试执行以下操作:

If that doesn't work, you could try something like this:

Dim query = From charge In Services.dsERP.ERP_Charge _
            Join rma In (From rma in Services.dsRMA.RMA _
                         Where rma.fiCharge IsNot Nothing
                         Select rma)
              On charge.idCharge Equals rma.fiCharge _
            Where rma.IMEI = imei
            Select charge.idCharge

这篇关于可为空的列上的LINQ联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！