Linq ile inner Join Kullanımı


October 2022 (0) Iptv 3/28/2024


Join, iki ya da daha fazla sonuç kümesini, bir ya da birden fazla kritere göre birbirine bağlayan ve geriye bir sonuç kümesi döndüren bir yapıdır. SQL Server' da olduğu gibi LINQ to SQL' de bize JOIN yazmamıza imkan vermektedir. Şimdi LINQ ile nasıl iki veri kümesini birleştireceğiz bunu örnek üzerinde inceleyelim.

Örneğimizde Northwind veritabanı kullanılacaktır. Veritabanı içerisinde bulunan Products ve Categories tabloları için bir INNER JOIN sorgusu oluşturacağız. LINQ öncesinde bu işlemi SQL sorgusu ile yapmak isteseydik, aşağıdaki gibi bir sorgu oluşturmamız gerekecekti.

  1. SELECT P.ProductName, C.CategoryName   
  2. FROM dbo.Products P  
  3. INNER JOIN dbo.Categories C ON P.CategoryID = C.CategoryID  

Şimdi benzer örneği LINQ ile yazalım ve iki tabloyu INNER JOIN ile birleştirelim.

  1. using (var context = new NorthwindContext())  
  2. {  
  3.     var result = from p in context.Products  
  4.                  join c in context.Categories on p.CategoryID equals c.CategoryID  
  5.                  select new  
  6.                  {  
  7.                      ProductName = p.ProductName,  
  8.                      CategoryName = c.CategoryName  
  9.                  };  
  10.   
  11.     result.ToList().ForEach(x =>  
  12.     {  
  13.         Console.WriteLine($"Product Name: {x.ProductName}, Category Name: {x.CategoryName}");  
  14.     });  
  15. }  

İlk örneğimizde, sonucu doğrudan SQL sorgusu yazarak görmüştük. Şimdi LINQ ile yazdığımız kodun, bizim için arka tarafta oluşturmuş olduğu SQL sorgusuna bakalım.


  • SELECT   
  • [Extent2].[CategoryID] AS [CategoryID],   
  • [Extent1].[ProductName] AS [ProductName],   
  • [Extent2].[CategoryName] AS [CategoryName]  
  • FROM  [dbo].[Products] AS [Extent1]  
  • INNER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] = [Extent2].[CategoryID]  

LINQ tarafından oluşturulan sql sorgusuna baktığımız zaman, ilk örnekte yazılan sorgu ile benzer bir sql ifadesinin oluştuğu görülmektedir. JOIN ifadesinde kullanılan equals anahtarı, eşitliği karşılaştırmak için kullanılmıştır. equals yerine "==" şeklinde bir eşitlik kontrolü kullanamayız. LINQ, JOIN işlemi için bu şekilde bir syntax' ı desteklememektedir. Eğer işlemimiz gereği equals yerine not equals şeklinde bir sorgulama yapılacaksa, bu tarz bir syntax' ı da LINQ desteklememektedir. Eşit değil gibi bir kontrol için farklı yöntemler kullanılabilmektedir.

Linq Inner Join in C#

Linq Inner Join in C# with Examples

In this article, I am going to discuss the Linq Inner Join in C# with examples. Please read our previous article before proceeding to this article where we discussed the basics of Linq Join in C#. This is the most common join used in real-time applications. At the end of this article, you will learn what is Linq Inner Join and when and how to use Inner Join in Linq.

What is Linq Inner Join in C#?

As per the Microsoft documentation “An inner join produces a result set in which each element of the first collection appears one time for every matching element in the second collection. If an element in the first collection does not have any matching element in the second collection, then it does not appear in the result set”.

In simple words, we can say that the Linq Inner join is used to return only the matching elements from both the data sources while the non-matching elements are removed from the result set. So, if you have two data sources, and when you perform the LINQ inner join, then only the matching elements which exist in both the data sources are included in the result set. Let’s have a look at the following diagram.

Inner Join in Linq

Note: While performing the Linq inner join then there should exist a common element or property in both the data sources.

What is Linq Join Method in C#?

The Linq Join Method in C# operates on two data sources or you can say two collections such as inner collection and outer collection. This operator returns a new collection which contains the data from both the collections and it is the same as the SQL join operator. Let us have a look at the signature of the Linq Join Methods.

Linq Join Method in C#

As you can see there are two overloaded versions available in Linq to perform inner join operations. The second overloaded version takes a comparer as an extra parameter.

So, while working with Linq Join you need to understand the following five things.

  1. Outer data source
  2. Inner data source
  3. Outer Key selector (common key in the outer data source)
  4. Inner Key selector (Common key in the inner data source)
  5. Result selector (project the data into a result set)

Let us understand the Linq Inner Join with some examples.

Creating the Model classes:

In this demo, we are going to use the following two model classes i.e. Employee and Address. So, create a class file and then copy and paste the following code in it.

using System.Collections.Generic;
namespace LINQJoin
{
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public int AddressId { get; set; }
public static List<Employee> GetAllEmployees()
{
return new List<Employee>()
{
new Employee { ID = 1, Name = "Preety", AddressId = 1 },
new Employee { ID = 2, Name = "Priyanka", AddressId = 2 },
new Employee { ID = 3, Name = "Anurag", AddressId = 3 },
new Employee { ID = 4, Name = "Pranaya", AddressId = 4 },
new Employee { ID = 5, Name = "Hina", AddressId = 5 },
new Employee { ID = 6, Name = "Sambit", AddressId = 6 },
new Employee { ID = 7, Name = "Happy", AddressId = 7},
new Employee { ID = 8, Name = "Tarun", AddressId = 8 },
new Employee { ID = 9, Name = "Santosh", AddressId = 9 },
new Employee { ID = 10, Name = "Raja", AddressId = 10},
new Employee { ID = 11, Name = "Sudhanshu", AddressId = 11}
};
}
}
public class Address
{
public int ID { get; set; }
public string AddressLine { get; set; }
public static List<Address> GetAllAddresses()
{
return new List<Address>()
{
new Address { ID = 1, AddressLine = "AddressLine1"},
new Address { ID = 2, AddressLine = "AddressLine2"},
new Address { ID = 3, AddressLine = "AddressLine3"},
new Address { ID = 4, AddressLine = "AddressLine4"},
new Address { ID = 5, AddressLine = "AddressLine5"},
new Address { ID = 9, AddressLine = "AddressLine9"},
new Address { ID = 10, AddressLine = "AddressLine10"},
new Address { ID = 11, AddressLine = "AddressLine11"},
};
}
}
}
using System.Collections.Generic; namespace LINQJoin { public class Employee { public int ID { get; set; } public string Name { get; set; } public int AddressId { get; set; } public static List<Employee> GetAllEmployees() { return new List<Employee>() { new Employee { ID = 1, Name = "Preety", AddressId = 1 }, new Employee { ID = 2, Name = "Priyanka", AddressId = 2 }, new Employee { ID = 3, Name = "Anurag", AddressId = 3 }, new Employee { ID = 4, Name = "Pranaya", AddressId = 4 }, new Employee { ID = 5, Name = "Hina", AddressId = 5 }, new Employee { ID = 6, Name = "Sambit", AddressId = 6 }, new Employee { ID = 7, Name = "Happy", AddressId = 7}, new Employee { ID = 8, Name = "Tarun", AddressId = 8 }, new Employee { ID = 9, Name = "Santosh", AddressId = 9 }, new Employee { ID = 10, Name = "Raja", AddressId = 10}, new Employee { ID = 11, Name = "Sudhanshu", AddressId = 11} }; } } public class Address { public int ID { get; set; } public string AddressLine { get; set; } public static List<Address> GetAllAddresses() { return new List<Address>() { new Address { ID = 1, AddressLine = "AddressLine1"}, new Address { ID = 2, AddressLine = "AddressLine2"}, new Address { ID = 3, AddressLine = "AddressLine3"}, new Address { ID = 4, AddressLine = "AddressLine4"}, new Address { ID = 5, AddressLine = "AddressLine5"}, new Address { ID = 9, AddressLine = "AddressLine9"}, new Address { ID = 10, AddressLine = "AddressLine10"}, new Address { ID = 11, AddressLine = "AddressLine11"}, }; } } }
using System.Collections.Generic;

namespace LINQJoin
{
    public class Employee
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public int AddressId { get; set; }

        public static List<Employee> GetAllEmployees()
        {
            return new List<Employee>()
            {
                new Employee { ID = 1, Name = "Preety", AddressId = 1 },
                new Employee { ID = 2, Name = "Priyanka", AddressId = 2 },
                new Employee { ID = 3, Name = "Anurag", AddressId = 3 },
                new Employee { ID = 4, Name = "Pranaya", AddressId = 4 },
                new Employee { ID = 5, Name = "Hina", AddressId = 5 },
                new Employee { ID = 6, Name = "Sambit", AddressId = 6 },
                new Employee { ID = 7, Name = "Happy", AddressId = 7},
                new Employee { ID = 8, Name = "Tarun", AddressId = 8 },
                new Employee { ID = 9, Name = "Santosh", AddressId = 9 },
                new Employee { ID = 10, Name = "Raja", AddressId = 10},
                new Employee { ID = 11, Name = "Sudhanshu", AddressId = 11}
            };
        }
    }

    public class Address
    {
        public int ID { get; set; }
        public string AddressLine { get; set; }

        public static List<Address> GetAllAddresses()
        {
            return new List<Address>()
            {
                new Address { ID = 1, AddressLine = "AddressLine1"},
                new Address { ID = 2, AddressLine = "AddressLine2"},
                new Address { ID = 3, AddressLine = "AddressLine3"},
                new Address { ID = 4, AddressLine = "AddressLine4"},
                new Address { ID = 5, AddressLine = "AddressLine5"},
                new Address { ID = 9, AddressLine = "AddressLine9"},
                new Address { ID = 10, AddressLine = "AddressLine10"},
                new Address { ID = 11, AddressLine = "AddressLine11"},
            };
        }
    }
}

Note: In real-time applications, you need to fetch the data from a database. Here we are not going to focus on how to fetch the data from a database rather we are going to focus on how to perform the inner join. So, here we created the required data sources (i.e. list of employees and addresses) with the hard-coded data.

Here in both the data sources the common property is the Address id i.e. the AddressId property of the Employee data source and the ID property of the Address data source is the common property. As you can see we have 11 records in the employee data source and 8 records in the addresses data source. Further, if you notice some of the data are present in both the data sources.

Example1:

Our requirement is to fetch the employee name and their address into an anonymous type. But here we need to fetch only the elements which are present in both the data sources.

Using Method Syntax:

Linq Inner Join Using Method Syntax

The Complete example is given below.

using System.Linq;
using System;
namespace LINQJoin
{
class Program
{
static void Main(string[] args)
{
var JoinUsingMS = Employee.GetAllEmployees() //Outer Data Source
.Join(
Address.GetAllAddresses(), //Inner Data Source
employee => employee.AddressId, //Inner Key Selector
address => address.ID, //Outer Key selector
(employee, address) => new //Projecting the data into a result set
{
EmployeeName = employee.Name,
AddressLine = address.AddressLine
}).ToList();
foreach (var employee in JoinUsingMS)
{
Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
}
Console.ReadLine();
}
}
}
using System.Linq; using System; namespace LINQJoin { class Program { static void Main(string[] args) { var JoinUsingMS = Employee.GetAllEmployees() //Outer Data Source .Join( Address.GetAllAddresses(), //Inner Data Source employee => employee.AddressId, //Inner Key Selector address => address.ID, //Outer Key selector (employee, address) => new //Projecting the data into a result set { EmployeeName = employee.Name, AddressLine = address.AddressLine }).ToList(); foreach (var employee in JoinUsingMS) { Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}"); } Console.ReadLine(); } } }
using System.Linq;
using System;

namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            var JoinUsingMS = Employee.GetAllEmployees() //Outer Data Source
                           .Join(
                           Address.GetAllAddresses(),  //Inner Data Source
                           employee => employee.AddressId, //Inner Key Selector
                           address => address.ID, //Outer Key selector
                           (employee, address) => new //Projecting the data into a result set
                           {
                               EmployeeName = employee.Name,
                               AddressLine = address.AddressLine
                           }).ToList();

            foreach (var employee in JoinUsingMS)
            {
                Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
            }

            Console.ReadLine();
        }
    }
}

Output:

Inner Join Output

As you can see in the above output, it only fetches the matching records from both the data sources. Let’s rewrite the same example using Linq Query Syntax.

Using Linq Query Syntax to Perform Inner Join:

The Linq provides the join operator to perform the joins using Query syntax. Performing the join using query syntax is very much similar to performing the join in SQL.

Using Linq Query Syntax to Perform Inner Join

The complete example is given below.

using System.Linq;
using System;
namespace LINQJoin
{
class Program
{
static void Main(string[] args)
{
var JoinUsingQS = (from emp in Employee.GetAllEmployees()
join address in Address.GetAllAddresses()
on emp.AddressId equals address.ID
select new
{
EmployeeName = emp.Name,
AddressLine = address.AddressLine
}).ToList();
foreach (var employee in JoinUsingQS)
{
Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
}
Console.ReadLine();
}
}
}
using System.Linq; using System; namespace LINQJoin { class Program { static void Main(string[] args) { var JoinUsingQS = (from emp in Employee.GetAllEmployees() join address in Address.GetAllAddresses() on emp.AddressId equals address.ID select new { EmployeeName = emp.Name, AddressLine = address.AddressLine }).ToList(); foreach (var employee in JoinUsingQS) { Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}"); } Console.ReadLine(); } } }
using System.Linq;
using System;

namespace LINQJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            var JoinUsingQS = (from emp in Employee.GetAllEmployees()
                               join address in Address.GetAllAddresses()
                               on emp.AddressId equals address.ID
                               select new
                               {
                                   EmployeeName = emp.Name,
                                   AddressLine = address.AddressLine
                               }).ToList();

            foreach (var employee in JoinUsingQS)
            {
                Console.WriteLine($"Name :{employee.EmployeeName}, Address : {employee.AddressLine}");
            }

            Console.ReadLine();
        }
    }
}


Yorum yapabilmek için giriş yapınız

Giriş Yap

Sitede Ara

ralfiSoft.com Download

DOWNLOADS