Lambda two tables and three tables inner join code samples

In this blog post you will learn inner join using lambda queries. There will be two samples, in first sample you will see how you can join two tables and in second sample you will see how you can extend even further to join three tables and so on.

I will sample in-memory data, but the same implementation will work with IQueryable (lazy query) too.

Here's the code snippet:-

namespace ConsoleApp1
{
    class Program
    {
        static void Main()
        {
            var table1 = new List<Table1>();
            table1.Add(new Table1 { Id = 1, Name = "Name 1", Address = "Address 1" });
            table1.Add(new Table1 { Id = 2, Name = "Name 2", Address = "Address 2" });
            table1.Add(new Table1 { Id = 3, Name = "Name 3", Address = "Address 3" });
            table1.Add(new Table1 { Id = 4, Name = "Name 4", Address = "Address 4" });

            var table2 = new List<Table2>();
            table2.Add(new Table2 { Id = 1, Email = "[email protected]", PhoneNumber = "1111111111" });
            table2.Add(new Table2 { Id = 2, Email = "[email protected]", PhoneNumber = "2222222222" });
            table2.Add(new Table2 { Id = 3, Email = "[email protected]", PhoneNumber = "3333333333" });
            table2.Add(new Table2 { Id = 4, Email = "[email protected]", PhoneNumber = "4444444444" });

            var table3 = new List<Table3>();
            table3.Add(new Table3 { Id = 1, Details = "Details 1" });
            table3.Add(new Table3 { Id = 2, Details = "Details 2" });
            table3.Add(new Table3 { Id = 3, Details = "Details 3" });
            table3.Add(new Table3 { Id = 4, Details = "Details 4" });

            // Two table joining lambda expression
            var output1 = table1.Join(table2, e => e.Id, d => d.Id,
                (tbl1, tbl2) => new
                {
                    Id = tbl1.Id,
                    Name = tbl1.Name,
                    Address = tbl1.Address,
                    Email = tbl2.Email,
                    PhoneNumber = tbl2.PhoneNumber
                }).ToList();

            // print output1
            Console.WriteLine("Output1 printing:-");
            foreach (var item in output1)
            {
                Console.WriteLine(item.Id + " | " + item.Name + " | " + item.Address + " | " + item.Email + " | " + item.PhoneNumber);
            }

            // Three table joining lambda expression
            var output2 = table1.Join(table2, e => e.Id, d => d.Id,
                (tbl1, tbl2) => new
                {
                    Table1 = tbl1,
                    Table2 = tbl2
                }).Join(table3, ee => ee.Table1.Id, dd => dd.Id,
                (tbl1, tbl2) => new
                {
                    Id = tbl1.Table1.Id,
                    Name = tbl1.Table1.Name,
                    Address = tbl1.Table1.Address,
                    Email = tbl1.Table2.Email,
                    PhoneNumber = tbl1.Table2.PhoneNumber,
                    Details = tbl2.Details
                }).ToList();

            // print output2
            Console.WriteLine("Output2 printing:-");
            foreach (var item in output2)
            {
                Console.WriteLine(item.Id + " | " + item.Name + " | " + item.Address + " | " + item.Email + " | " + item.PhoneNumber + " | " + item.Details);
            }

            Console.ReadKey();
        }
    }

    public class Table1
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
    }

    public class Table2
    {
        public int Id { get; set; }
        public string Email { get; set; }
        public string PhoneNumber { get; set; }
    }

    public class Table3
    {
        public int Id { get; set; }
        public string Details { get; set; }
    }

}

And the output of above query is:

Output1 printing:-
1 | Name 1 | Address 1 | [email protected] | 1111111111
2 | Name 2 | Address 2 | [email protected] | 2222222222
3 | Name 3 | Address 3 | [email protected] | 3333333333
4 | Name 4 | Address 4 | [email protected] | 4444444444
Output2 printing:-
1 | Name 1 | Address 1 | [email protected] | 1111111111 | Details 1
2 | Name 2 | Address 2 | [email protected] | 2222222222 | Details 2
3 | Name 3 | Address 3 | [email protected] | 3333333333 | Details 3
4 | Name 4 | Address 4 | [email protected] | 4444444444 | Details 4

Hope this helps.

Comments

Popular posts from this blog

Migrating database from ASP.NET Identity to ASP.NET Core Identity