LINQ-Join tables on nullable columns

how to JOIN tables on nullable columns?

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

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开发者_Go百科))

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?

Thank you in advance.

Solution:

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

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

While you can use LINQ to Datasets to solve this issue, you might find better performance using the predefined DataRelations instead of ad-hoc joins. See http://msdn.microsoft.com/en-us/library/dbwcse3d.aspx for information about data relations.

If you do use LINQ to Datasets, you might want to check out our free bonus chapter 14 on them at http://www.manning.com/marguerie/.