LINQ: How to write multiple conditions for "on" caluse?

Question

I have difficulty in translating SQL to LINQ statement.

Here is SQL statement:

SELECT * FROM dataTableA INNER JOIN dataTableB ON dataTableA.ID =dataTableB.SNo OR 
         dataTableA.Address = dataTableB.Address 
         WHERE (dataTableA.Name = dataTableB.UserName)

It is OR part in On clause where I don't know how to write LINQ syntax. I have tried with below quote without OR condition. Kindly advise.

var matches = from rowA  in dataTableA.AsEnumerable()
                join rowB in dataTableB.AsEnumerable()
                on  rowA["ID"].ToString().Trim() equals rowB["SNo"].ToString().Trim()  
                where  rowA["Name"].ToString().Trim() == rowB["UserName"].ToString().Trim() 
                select new { rowA, rowB } ;

possible duplicate of [Linq to Entity Join table with multiple OR conditions](http://stackoverflow.com/questions/15887223/linq-to-entity-join-table-with-multiple-or-conditions) — Taher Rahgooy, Aug 14 '15 at 08:42

Mukesh Kalgude · Accepted Answer · 2015-08-14T09:34:08.110

1

Try this way

var matches = from rowA  in dataTableA.AsEnumerable()
              from  rowB in dataTableB.AsEnumerable().Where(x=>x.SNo==rowA.ID || x.Address ==rowA.Address) .AsEnumerable()              
               where  rowA["Name"].ToString().Trim() == rowB["UserName"].ToString().Trim() 
                    select new { rowA, rowB } ;

edited Aug 14 '15 at 09:34

answered Aug 14 '15 at 08:49

Mukesh Kalgude

4,814
2
17
32

Thanks for the answer. But this showing error message " System.Data.DataTable does not contain definition for 'Where' and no extension method 'Where' accepting a first argument of type 'System.Data.DataTable' coould be found" at dataTableB.Where(x=>...) part. – Htet Thuzar Aug 14 '15 at 09:32
I have made a little changes and it worked well. Greatly appreciated your help. – Htet Thuzar Aug 14 '15 at 09:56
@HtetThuzar Not say thanks if it is help out then given vot and right mark – Mukesh Kalgude Aug 14 '15 at 09:58
we can also use a more straight forward `join` – tafia Aug 14 '15 at 10:13

score 1 · Answer 2 · edited May 23 '17 at 12:23

You can use composite keys to join on both ID and Adress https://msdn.microsoft.com/en-us/library/bb907099.aspx

dataTableA.AsEnumerable().join(dataTableB.AsEnumerable(),
  ta => new {ta.ID, ta.Adress}, tb => new {tb.SNo, tb.Adress}, 
  (ta, tb) => new {ta, tb})

If you want to join on ID or Adress, you can union 2 joins, which I think should still be faster than n wheres: Why is LINQ JOIN so much faster than linking with WHERE?

dataTableA.AsEnumerable().join(dataTableB.AsEnumerable(),
  ta => ta.ID, tb => tb.SNo, (ta, tb) => new {ta, tb})
.union(dataTableA.AsEnumerable().join(dataTableB.AsEnumerable(),
  ta => ta.Adress, tb => tb.Adress, (ta, tb) => new {ta, tb}))

Thanks. But my requirement is to join two tables on either ID or Address if Name in both tables are same. The composite keys idea will join two tables only on condition that all Name, ID and Address are same. — Htet Thuzar, Aug 17 '15 at 02:01

Fidel · Answer 3 · 2015-08-14T09:40:10.010

0

    var matches = dataTableA.AsEnumerable()
        .Select(a => new {
                    RowA = a,
                    RowB = dataTableB.AsEnumerable().FirstOrDefault(b => ((b["SNo"] == a["ID"]) || (b["Address"].Equals(a["Address"]))))
                })
        .Where(pair => pair.RowB != null) //to replicate the inner-join
        .Where(pair => pair.RowA["Name"].Equals(pair.RowB["Username"]));

edited Aug 14 '15 at 09:40

answered Aug 14 '15 at 08:49

Fidel

7,027
11
57
81

score 0 · Answer 4 · answered Aug 14 '15 at 10:02

After minor changes to Mukesh Kalgude's answer, problem solved. Below here is finalized working codes. Thanks to all for your kind suggestions.

var matches = from rowA  in dataTableA.AsEnumerable()
              from  rowB in    dataTableB.AsEnumerable().Where(x=>x["SNo"]==rowA["ID"] || x["Address"]==rowA["Address"]) .AsEnumerable()              
             where  rowA["Name"].ToString().Trim() == rowB["UserName"].ToString().Trim() 
             select new { rowA, rowB } ;

LINQ: How to write multiple conditions for "on" caluse?

4 Answers4