Geek Answers Handbook

LINQ-Join Tables for Zero Columns

how to connect to tables in columns with zero value?

I have the following LINQ query, RMA.fiCharge may 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 "Convert from type" DBNull "to enter" Integer "is invalid" in query.ToArray() :

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

So, I can add WHERE RMA.fiCharge IS NOT NULL to the request. But how to do it in LINQ or is there another option?

Thanks in advance.

Decision:

The problem was that the DataSet does not support Nullable-Types, but throws an InvalidCastException if you request any NULL values ​​for an integer column (thanks Martinho). The modified LINQ query from dahlbyk works with minor changes. The DataSet generates a boolean property for each 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
+4
source share
2 answers

Have you tried adding null checking to the 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 this does not work, you can 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
+3
source

Although you can use LINQ to Datasets to solve this problem, you can find better performance using predefined DataRelations instead of ad-hoc connections. See http://msdn.microsoft.com/en-us/library/dbwcse3d.aspx for information on data relationships.

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

0
source

More articles:


All Articles