LINQ Left Join And Right Join

Question

I need a help,

I have two dataTable called A and B , i need all rows from A and matching row of B

Ex:

A:                                           B:  User | age| Data                            ID  | age|Growth                                 1    |2   |43.5                             1   |2   |46.5 2    |3   |44.5                             1   |5   |49.5 3    |4   |45.6                             1   |6   |48.5 

I need Out Put:

User | age| Data |Growth ------------------------                            1    |2   |43.5  |46.5                            2    |3   |44.5  |                           3    |4   |45.6  | 

Answer 1

The example data and output you've provided does not demonstrate a left join. If it was a left join your output would look like this (notice how we have 3 results for user 1, i.e. once for each Growth record that user 1 has):

User | age| Data |Growth ------------------------                            1    |2   |43.5  |46.5                            1    |2   |43.5  |49.5      1    |2   |43.5  |48.5      2    |3   |44.5  |                           3    |4   |45.6  | 

Assuming that you still require a left join; here's how you do a left join in Linq:

var results = from data in userData               join growth in userGrowth               on data.User equals growth.User into joined               from j in joined.DefaultIfEmpty()               select new                {                   UserData = data,                   UserGrowth = j               }; 

If you want to do a right join, just swap the tables that you're selecting from over, like so:

var results = from growth in userGrowth               join data in userData               on growth.User equals data.User into joined               from j in joined.DefaultIfEmpty()               select new                {                   UserData = j,                   UserGrowth = growth               }; 

The important part of the code is the into statement, followed by the DefaultIfEmpty. This tells Linq that we want to have the default value (i.e. null) if there isn't a matching result in the other table.

Answer 2

Doctor Jones showed left outer join, but correct answer would be slightly different - because in the original question two tables linked on age field so to get result exactly as needed following code should be used.

.... //ctx = dataContext class - not shown here. var user1 = new UserData() { User = 1, Age = 2, Data = 43.5 }; var user2 = new UserData() { User = 2, Age = 3, Data = 44.5 }; var user3 = new UserData() { User = 3, Age = 4, Data = 45.6 };  ctx.UserData.AddRange(new List<UserData> { user1, user2, user3 });  var growth1 = new UserGrowth() { Id = 1, Age = 2, Growth = 46.5 }; var growth2 = new UserGrowth() { Id = 1, Age = 5, Growth = 49.5 }; var growth3 = new UserGrowth() { Id = 1, Age = 6, Growth = 48.5 };  ctx.UserGrowth.AddRange(new List<UserGrowth> { growth1, growth2, growth3 });  var query = from userData in ctx.UserData                         join userGrowth in ctx.UserGrowth on userData.Age equals userGrowth.Age                             into joinGroup                         from gr in joinGroup.DefaultIfEmpty()                         select new                         {                             User = userData.User,                             age = userData.Age,                             Data = (double?)userData.Data,                             Growth = (double?)gr.Growth                         };  Console.WriteLine("{0} | {1} | {2} | {3}", "User", "age", "Data", "Growth");             foreach (var x in query)             {                 Console.WriteLine("{0} | {1} | {2} | {3}", x.User, x.age, x.Data, x.Growth);             }   .... with following entity classes:  public class UserData     {         [Key]         public int User { get; set; }         public int Age { get; set; }         public double Data { get; set; }     }      public class UserGrowth     {         public int Id { get; set; }         public int Age { get; set; }         public double Growth { get; set; }     }