CODEWITHSUNDEEP
c#linq
Nilesh Barai
Nilesh Barai

Reputation: 1322

Left outer join of 2 datatables

I want to left join 2 data tables AllTimesTxnTable and resolvedDt in C#, Where I want every row from AllTimesTxnTable and only matching rows from resolvedDt.

The join condition should be based on mid and txndate and finally while selecting I want mid and txndate from AllTimesTxnTable and txncount from resolvedDt, if no record in resolvedDt then a 0.

I tried the following LINQ query:

 var results = from table2 in AllTimesTxnTable.AsEnumerable()
               join table1 in resolvedDt.AsEnumerable()
               on new { mid = table2.Field<int>("mid"), txndate = table2.Field<string>("txndate") } equals new { mid = table1.Field<int>("mid"), txndate = table1.Field<string>("txndate") }
               into temp
               from leftTable in temp.DefaultIfEmpty()
               select new
               {
                    MId = (int)table2["mid"],
                    TxnDate = (string)table2["txndate"],
                    TxnCount = leftTable["txncount"] == null ? 0M : (decimal)leftTable["txncount"]
                };

But it gives me an Error: Object reference not set to an instance of an object. while selecting.

Not getting whats going wrong here.

Upvotes: 0

Views: 2137

Answers (1)

Nilesh Barai
Nilesh Barai

Reputation: 1322

Updated the code. Its working now.

var results = from table2 in AllTimesTxnTable.AsEnumerable()
              join table1 in resolvedDt.AsEnumerable()
              on new { mid = table2.Field<int>("mid"), txndate = table2.Field<string>("txndate") } equals new { mid = table1.Field<int>("mid"), txndate = table1.Field<string>("txndate") }
              into temp
              from row in temp.DefaultIfEmpty()
              select new
              {
                   MId = (int)table2["mid"],
                   TxnDate = (string)table2["txndate"],
                   TxnCount = row == null ? 0M : (decimal)row["txncount"]
              };

Upvotes: 1

Related Questions