LINQ-Join tables on nullable columns

Question

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

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

Are you using nullable ints? Why did you tag this with linq-to-objects? LINQ-to-Objects has **no SQL**. — R. Martinho Fernandes, Apr 17 '11 at 22:29
@Martinho: Thank you, i will look at it tomorrow. You mean the dataset itself is throwing the exception when LINQ iterates the rows? I've tagged it LINQ-To-Objects because it's a query on a typed dataset. Ignore the SQL where the exception is thrown, that is a filter-string that i want to apply on a DataView later. What i don't understand is: if i join to tables via LINQ, why does result.ToArry() throws an exception that could only occur if `fiCharge IS NULL`, what i've assumed that i would have prevented with the JOIN? — Tim Schmelter, Apr 17 '11 at 22:50
I'm not sure (I haven't used typed datasets ever), but I guess that's because the raw data from the DB is stored, and only when you materialize the query with `ToArray()` do the objects get created. Maybe the client-side join does not work exactly like an SQL JOIN and includes nulls as well. Since you can't set an int to null (unless its nullable, of course), it fails. Or not. I'm just guessing here. — R. Martinho Fernandes, Apr 17 '11 at 23:00

dahlbyk · Accepted Answer · 2011-04-18T12:48:33.467

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

Thank you, the second query works after replacing `rma.fiCharge <> Nothing` with `Not rma.IsfiChargeNull` :) — Tim Schmelter, Apr 18 '11 at 07:49

score 0 · Answer 2 · answered Oct 19 '11 at 18:20

0

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/.

answered Oct 19 '11 at 18:20

Jim Wooley

10,169
1
25
43

I don't think that DataRelations are much faster than [LINQ-Joins since their also using hash algorithms to link the tables](http://stackoverflow.com/questions/5551264/why-is-linq-join-so-much-faster-than-linking-with-where) but LINQ is much more flexible. – Tim Schmelter Oct 19 '11 at 18:46
I think those optimizations with the hash algorithm apply only if you are using .Net 4+. If using 3.5, the Dataset DataRelation is the better option. – Jim Wooley Oct 20 '11 at 16:39

LINQ-Join tables on nullable columns

2 Answers2

Linked