CODEWITHSUNDEEP
c#mysqlsql-serverpostgresqllinq
sudheer
sudheer

Reputation: 21

INNER JOIN 3 Data Tables using LINQ

Trying to use LINQ to join three datatables table1, table2, table3. Table1 and table2 joined on 2 condition and table 1 and table 3 joined on 1 condition. Following is my code. But I am getting an exception saying that "Unable to cast object of type 'System.DBNull' to type 'System.String'."

Below is my code:

var result = from cust in tblCust.AsEnumerable()
             join mat in tblMat.AsEnumerable()
             new { coil_id = (string)cust["coil_id"], order_id = (string)cust["order_id"] }
             equals
             new { coil_id = (string)mat["PIECE_ID"], order_id = (string)mat["PRODUCTION_ORDER_ID"] }
                                 join parts in tblParts.AsEnumerable() on (string)mat["PIECE_ID"] equals (string)parts["ProdCoilNo"]
             select new
             {
                 coil_id = mat["PIECE_ID"],
                 order_id = mat["PRODUCTION_ORDER_ID"],
                 part = parts["PartNumber"],
                 gauge = mat["THICKNESS"],
                 width = mat["WIDTH"],
                 weight = mat["WEIGHT"],
                 code = mat["MATERIAL_BLOCK_STATE"],
                 requestor_comment = cust["requestor_comment"],
                 requestor = cust["requestor"],
                 updated_by_comment = cust["updated_by_comment"],
                 updated_by_user = cust["updated_by_user"]                                     
             };

I an getting an exception near (string)parts["ProdCoilNo"]

Upvotes: 1

Views: 340

Answers (2)

Noor A Shuvo
Noor A Shuvo

Reputation: 2807

Use the coalesce operator

var result = from cust in tblCust.AsEnumerable()
         join mat in tblMat.AsEnumerable()
         new { coil_id = (string)cust["coil_id"], order_id = (string)cust["order_id"] }
         equals
         new { coil_id = (string)mat["PIECE_ID"], order_id = (string)mat["PRODUCTION_ORDER_ID"] }
                             join parts in tblParts.AsEnumerable() on (string)mat["PIECE_ID"] equals (string)parts["ProdCoilNo"]
         select new
         {
             coil_id = mat["PIECE_ID"]?? String.Empty,
             order_id = mat["PRODUCTION_ORDER_ID"]?? String.Empty,
             part = parts["PartNumber"]?? String.Empty,
             gauge = mat["THICKNESS"]?? String.Empty,
             width = mat["WIDTH"]?? String.Empty,
             weight = mat["WEIGHT"]?? String.Empty,
             code = mat["MATERIAL_BLOCK_STATE"]?? String.Empty,
             requestor_comment = cust["requestor_comment"]?? String.Empty,
             requestor = cust["requestor"]?? String.Empty,
             updated_by_comment = cust["updated_by_comment"]?? String.Empty,
             updated_by_user = cust["updated_by_user"]?? String.Empty                                     
         };

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use the coalesce operator for the field containing null to replace with empty string

Like this:

coil_id = mat["PIECE_ID"] ?? String.Empty,

Upvotes: 0

Related Questions