- Article
- 7 minutes to read
In relational database terms, 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 has no matching elements, it does not appear in the result set. The Join method, which is called by the join clause in C#, implements an inner join.
This article shows you how to perform four variations of an inner join:
A simple inner join that correlates elements from two data sources based on a simple key.
(Video) (#32) Inner join in linq | LINQ tutorial for beginnersAn inner join that correlates elements from two data sources based on a composite key. A composite key, which is a key that consists of more than one value, enables you to correlate elements based on more than one property.
A multiple join in which successive join operations are appended to each other.
An inner join that is implemented by using a group join.
Note
The examples in this topic use the following data classes:
record Person(string FirstName, string LastName);record Pet(string Name, Person Owner);record Employee(string FirstName, string LastName, int EmployeeID);record Cat(string Name, Person Owner) : Pet(Name, Owner);record Dog(string Name, Person Owner) : Pet(Name, Owner);as well as the Student class from Query a collection of objects.
Example - Simple key join
The following example creates two collections that contain objects of two user-defined types, Person and Pet. The query uses the join clause in C# to match Person objects with Pet objects whose Owner is that Person. The select clause in C# defines how the resulting objects will look. In this example the resulting objects are anonymous types that consist of the owner's first name and the pet's name.
Person magnus = new(FirstName: "Magnus", LastName: "Hedlund");Person terry = new("Terry", "Adams");Person charlotte = new("Charlotte", "Weiss");Person arlene = new("Arlene", "Huff");Person rui = new("Rui", "Raposo");List<Person> people = new() { magnus, terry, charlotte, arlene, rui };List<Pet> pets = new(){ new(Name: "Barley", Owner: terry), new("Boots", terry), new("Whiskers", charlotte), new("Blue Moon", rui), new("Daisy", magnus),};// Create a collection of person-pet pairs. Each element in the collection// is an anonymous type containing both the person's name and their pet's name.var query = from person in people join pet in pets on person equals pet.Owner select new { OwnerName = person.FirstName, PetName = pet.Name };foreach (var ownerAndPet in query){ Console.WriteLine($"\"{ownerAndPet.PetName}\" is owned by {ownerAndPet.OwnerName}");}/* Output: "Daisy" is owned by Magnus "Barley" is owned by Terry "Boots" is owned by Terry "Whiskers" is owned by Charlotte "Blue Moon" is owned by Rui*/Note that the Person object whose LastName is "Huff" does not appear in the result set because there is no Pet object that has Pet.Owner equal to that Person.
Example - Composite key join
Instead of correlating elements based on just one property, you can use a composite key to compare elements based on multiple properties. To do this, specify the key selector function for each collection to return an anonymous type that consists of the properties you want to compare. If you label the properties, they must have the same label in each key's anonymous type. The properties must also appear in the same order.
The following example uses a list of Employee objects and a list of Student objects to determine which employees are also students. Both of these types have a FirstName and a LastName property of type String. The functions that create the join keys from each list's elements return an anonymous type that consists of the FirstName and LastName properties of each element. The join operation compares these composite keys for equality and returns pairs of objects from each list where both the first name and the last name match.
List<Employee> employees = new(){ new(FirstName: "Terry", LastName: "Adams", EmployeeID: 522459), new("Charlotte", "Weiss", 204467), new("Magnus", "Hedland", 866200), new("Vernette", "Price", 437139)};List<Student> students = new(){ new(FirstName: "Vernette", LastName: "Price", StudentID: 9562), new("Terry", "Earls", 9870), new("Terry", "Adams", 9913)};// Join the two data sources based on a composite key consisting of first and last name,// to determine which employees are also students.var query = from employee in employees join student in students on new { employee.FirstName, employee.LastName } equals new { student.FirstName, student.LastName } select employee.FirstName + " " + employee.LastName;Console.WriteLine("The following people are both employees and students:");foreach (string name in query){ Console.WriteLine(name);}/* Output: The following people are both employees and students: Terry Adams Vernette Price */Example - Multiple join
Any number of join operations can be appended to each other to perform a multiple join. Each join clause in C# correlates a specified data source with the results of the previous join.
The following example creates three collections: a list of Person objects, a list of Cat objects, and a list of Dog objects.
The first join clause in C# matches people and cats based on a Person object matching Cat.Owner. It returns a sequence of anonymous types that contain the Person object and Cat.Name.
The second join clause in C# correlates the anonymous types returned by the first join with Dog objects in the supplied list of dogs, based on a composite key that consists of the Owner property of type Person, and the first letter of the animal's name. It returns a sequence of anonymous types that contain the Cat.Name and Dog.Name properties from each matching pair. Because this is an inner join, only those objects from the first data source that have a match in the second data source are returned.
Person magnus = new(FirstName: "Magnus", LastName: "Hedlund");Person terry = new("Terry", "Adams");Person charlotte = new("Charlotte", "Weiss");Person arlene = new("Arlene", "Huff");Person rui = new("Rui", "Raposo");Person phyllis = new("Phyllis", "Harris");List<Person> people = new() { magnus, terry, charlotte, arlene, rui, phyllis };List<Cat> cats = new(){ new(Name: "Barley", Owner: terry), new("Boots", terry), new("Whiskers", charlotte), new("Blue Moon", rui), new("Daisy", magnus),};List<Dog> dogs = new(){ new(Name: "Four Wheel Drive", Owner: phyllis), new("Duke", magnus), new("Denim", terry), new("Wiley", charlotte), new("Snoopy", rui), new("Snickers", arlene),};// The first join matches Person and Cat.Owner from the list of people and// cats, based on a common Person. The second join matches dogs whose names start// with the same letter as the cats that have the same owner.var query = from person in people join cat in cats on person equals cat.Owner join dog in dogs on new { Owner = person, Letter = cat.Name.Substring(0, 1) } equals new { dog.Owner, Letter = dog.Name.Substring(0, 1) } select new { CatName = cat.Name, DogName = dog.Name };foreach (var obj in query){ Console.WriteLine( $"The cat \"{obj.CatName}\" shares a house, and the first letter of their name, with \"{obj.DogName}\"." );}/* Output: The cat "Daisy" shares a house, and the first letter of their name, with "Duke". The cat "Whiskers" shares a house, and the first letter of their name, with "Wiley". */Example - Inner join by using grouped join
The following example shows you how to implement an inner join by using a group join.
In query1, the list of Person objects is group-joined to the list of Pet objects based on the Person matching the Pet.Owner property. The group join creates a collection of intermediate groups, where each group consists of a Person object and a sequence of matching Pet objects.
By adding a second from clause to the query, this sequence of sequences is combined (or flattened) into one longer sequence. The type of the elements of the final sequence is specified by the select clause. In this example, that type is an anonymous type that consists of the Person.FirstName and Pet.Name properties for each matching pair.
The result of query1 is equivalent to the result set that would have been obtained by using the join clause without the into clause to perform an inner join. The query2 variable demonstrates this equivalent query.
Person magnus = new(FirstName: "Magnus", LastName: "Hedlund");Person terry = new("Terry", "Adams");Person charlotte = new("Charlotte", "Weiss");Person arlene = new("Arlene", "Huff");List<Person> people = new() { magnus, terry, charlotte, arlene };List<Pet> pets = new(){ new(Name: "Barley", Owner: terry), new("Boots", terry), new("Whiskers", charlotte), new("Blue Moon", terry), new("Daisy", magnus),};var query1 = from person in people join pet in pets on person equals pet.Owner into gj from subpet in gj select new { OwnerName = person.FirstName, PetName = subpet.Name };Console.WriteLine("Inner join using GroupJoin():");foreach (var v in query1){ Console.WriteLine($"{v.OwnerName} - {v.PetName}");}var query2 = from person in people join pet in pets on person equals pet.Owner select new { OwnerName = person.FirstName, PetName = pet.Name };Console.WriteLine();Console.WriteLine("The equivalent operation using Join():");foreach (var v in query2){ Console.WriteLine($"{v.OwnerName} - {v.PetName}");}/* Output: Inner join using GroupJoin(): Magnus - Daisy Terry - Barley Terry - Boots Terry - Blue Moon Charlotte - Whiskers The equivalent operation using Join(): Magnus - Daisy Terry - Barley Terry - Boots Terry - Blue Moon Charlotte - Whiskers*/See also
- Join
- GroupJoin
- Perform grouped joins
- Perform left outer joins
- Anonymous types
FAQs
How use inner join in LINQ query? ›
A simple inner join that correlates elements from two data sources based on a simple key. An inner join that correlates elements from two data sources based on a composite key. A composite key, which is a key that consists of more than one value, enables you to correlate elements based on more than one property.
How to write inner join in C#? ›For that we can create a query using joins. It returns only the matching rows from both tables. To join one table with another, we say Join or Inner Join. Whenever we use joins in a query, we use an ON clause to give a condition, to filter the records we use a WHERE clause and if the match is found, we get the output.
What is inner join in LINQ? ›In LINQ, an inner join is used to serve a result which contains only those elements from the first data source that appears only one time in the second data source. And if an element of the first data source does not have matching elements, then it will not appear in the result data set.
How you will perform inner joins? ›SQL INNER JOIN Example
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!
There are Different Types of SQL Joins which are used to query data from more than one database tables. In this article, you will learn about how to write SQL joins queries in LINQ using C#. LINQ has a JOIN query operator that gives you SQL JOIN like behavior and syntax.
What is inner join with example? ›Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same for both the students and courses tables.
What is LINQ in C# with example? ›LINQ is the basic C#. It is utilized to recover information from various kinds of sources, for example, XML, docs, collections, ADO.Net DataSet, Web Service, MS SQL Server, and different database servers.
How to join two tables in C#? ›Provide the Project name such as "JoinDataTableUsingLINQ" or another as you wish and specify the location. Then right-click on Solution Explorer and select "Add New Item" then select Default. aspx page. Drag and drop three Grid view to bind the records after Joining the two data table .
Where is LINQ in C#? ›In a LINQ query, the first step is to specify the data source. In C# as in most programming languages a variable must be declared before it can be used. In a LINQ query, the from clause comes first in order to introduce the data source ( customers ) and the range variable ( cust ).
How do you write a join in LINQ? ›Join in Query Syntax
Join operator in query syntax works slightly different than method syntax. It requires outer sequence, inner sequence, key selector and result selector. 'on' keyword is used for key selector where left side of 'equals' operator is outerKeySelector and right side of 'equals' is innerKeySelector.
When should I use inner join? ›
You'll use INNER JOIN when you want to return only records having pair on both sides, and you'll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not.
Is LINQ join inner or outer? ›When you use the LINQ join clause in the query expression syntax to combine two sets of related information, you perform an inner join.
Is inner join the same as join? ›'Inner Join' is a SQL syntax that is functionally the same as the 'Join' syntax. When you replace 'Join' with 'Inner Join' in the above SQL query, you will get the same result!
Is LINQ faster than for loop C#? ›No, LINQ iterators are not and will never be faster than foreach .
How to implement left join in LINQ C#? ›In order to implement the Linq Left Join in C#, it's mandatory to use the “INTO” keyword along with the “DefaultIfEmpty()” method.
What type of join is LINQ join? ›A LINQ JOIN keyword is used to combine rows from two or more tables, based on a common field between them. Like SQL Joins, the Linq is also provided some keywords to achieve Inner Join and Outer Join. As we know from SQL outer join is divided into 2 groups that is Left Outer Join and Right Outer Join.
How many types of inner joins are there? ›Inner Join is further divided into three subtypes: 1) Theta join 2) Natural join 3) EQUI join.
Does inner join remove duplicates? ›if join two tables using inner join method will it return duplicate values ? The answer is yes, if there are any. If there are duplicate keys in the tables being joined.
What is the symbol for inner join? ›Inner join, includes only those tuples that satisfy the matching criteria. The general case of JOIN operation is called a Theta join. It is denoted by symbol θ.
How to write a LINQ query in C#? ›List<int> numbers = new() { 5, 4, 1, 3, 9, 8, 6, 7, 2, 0 }; // The query variables can also be implicitly typed by using var // Query #1. IEnumerable<int> filteringQuery = from num in numbers where num < 3 || num > 7 select num; // Query #2.
How use LINQ method in C#? ›
- Step 1: First add System. Linq namespace in your code. ...
- Step 2: Next, create a data source on which you want to perform operations. For example: ...
- Step 3: Now create the query using the methods provided by the Enumerable or Queryable static classes. ...
- Step 4: Last step is to execute the query by using a foreach loop.
- MyDbContext db = new MyDbContext();
- List < SelectListItem > selectedItems = new List < SelectListItem > ();
- if (type == null) return selectedItems;
- if (type == typeof(TestDemo))
- selectedItems = db. TestDemo. ...
- {
- Text = i.Name, Value = i. Id. ...
- }). ToList();
Provide the Project name such as "ConvertLinqResultToDataTable" or another as you wish and specify the location. Then right-click on Solution Explorer and select "Add New Item" then select Default. aspx page. Drag and drop One Button and Grid view to bind the records after creating the data table from LINQ query.
How can I merge two tables without Joining? ›- Using a comma between the table names in the FROM clause and specifying the joining condition in a WHERE.
- Using UNION / UNION ALL .
- DataSet ds = LoadDataSetUsingDataAdapter();
- DataTable orders = ds.Tables["Orders"];DataTable orderDetails = ds.Tables["OrderDetails"];
- var query = from o in orders.AsEnumerable()
- where o.Field < DateTime > ("OrderDate"). ...
- orderby o.Field < DateTime > ("OrderDate") descending.
- select o;
Language-Integrated Query (LINQ) is the name for a set of technologies based on the integration of query capabilities directly into the C# language. Traditionally, queries against data are expressed as simple strings without type checking at compile time or IntelliSense support.
Why do we need LINQ? ›LINQ supports filtering, sorting, ordering, grouping. It makes easy debugging because it is integrated with C# language. It provides easy transformation means you can easily convert one data type into another data type like transforming SQL data into XML data.
Why use LINQ instead of SQL? ›Compared to SQL, LINQ is simpler, tidier, and higher-level. It's rather like comparing C# to C++. Sure, there are times when it's still best to use C++ (as is the case with SQL), but in most situations, working in a modern tidy language and not having to worry about lower-level details is a big win.
How to join two tables in SQL using LINQ? ›- SELECT [t1].[OrderId], [t1].[OrderNumber], [tsql-join-query-with-linq].[ProductName], [t1].[Quantity], [t1].[TotalAmount], [t1].[OrderDate]
- FROM [Product] AS [tsql-join-query-with-linq]
- INNER JOIN [Orders] AS [t1]
The syntax for the SQL FULL OUTER JOIN is: SELECT columns FROM table1 FULL [OUTER] JOIN table2 ON table1. column = table2.
How to combine two result sets in LINQ? ›
- Use the Concat method. CORRECT ANSWER : Use the Concat method.
Is inner join better than subquery? ›The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can minimize the calculation burden on the database i.e., instead of multiple queries using one join query.
Why inner join is faster? ›In case there are a large number of rows in the tables and there is an index to use, INNER JOIN is generally faster than OUTER JOIN. Generally, an OUTER JOIN is slower than an INNER JOIN as it needs to return more number of records when compared to INNER JOIN.
Is inner join faster than exists? ›If you do an inner join on a UNIQUE column, they exhibit same performance. If you do an inner join on a recordset with DISTINCT applied (to get rid of the duplicates), EXISTS is usually faster.
How many types of joins are there in LINQ C#? ›The following are three most common join types: Inner join. Group join. Left outer join.
Is LINQ deprecated? ›LINQ to SQL was the first object-relational mapping technology released by Microsoft. It works well in basic scenarios and continues to be supported in Visual Studio, but it's no longer under active development.
Is Linq join inner or outer? ›When you use the LINQ join clause in the query expression syntax to combine two sets of related information, you perform an inner join.
Can we use inner join in subquery? ›A subquery can be used with JOIN operation.
How to use group join in LINQ? ›In Linq, we can apply the Group Join on two or more data sources based on a common key (the key must exist in both the data sources) and then it produces the result set in the form of groups. In simple words, we can say that Linq Group Join is used to group the result sets based on a common key.
Can I use a subquery for an inner join? ›A subquery is a nested query (inner query) that's used to filter the results of the outer query. Subqueries can be used as an alternative to joins.
Which join is faster inner or outer? ›
In case there are a large number of rows in the tables and there is an index to use, INNER JOIN is generally faster than OUTER JOIN. Generally, an OUTER JOIN is slower than an INNER JOIN as it needs to return more number of records when compared to INNER JOIN.
Is LINQ faster than loops? ›No, LINQ iterators are not and will never be faster than foreach .
Is inner join faster than subquery? ›The advantage of a join includes that it executes faster. The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can minimize the calculation burden on the database i.e., instead of multiple queries using one join query.
Is inner query better than join? ›I won't leave you in suspense, between Joins and Subqueries, joins tend to execute faster. In fact, query retrieval time using joins will almost always outperform one that employs a subquery. The reason is that joins mitigate the processing burden on the database by replacing multiple queries with one join query.
When should we use inner join? ›You can use an INNER JOIN operation in any FROM clause. This is the most common type of join. Inner joins combine records from two tables whenever there are matching values in a field common to both tables. You can use INNER JOIN with the Departments and Employees tables to select all the employees in each department.
How do you make a link join a group? ›Alternatively, tap and hold the group in the CHATS tab. Then, tap More options > Group info. Tap Invite via link. Choose to Send link via WhatsApp, Copy link, Share link through another app, or QR code.
What is the difference between inner query and subquery? ›When a query is included inside another query, the Outer query is known as Main Query, and Inner query is known as Subquery. In Nested Query, Inner query runs first, and only once. Outer query is executed with result from Inner query. Hence, Inner query is used in execution of Outer query.
Does subquery reduce performance? ›A Sub-Query Does Not Hurt Performance.
How do you replace left join with inner join? ›You can replace a LEFT OUTER JOIN with an INNER JOIN if you add the missing values in the related table. It has not worked for you because you have added a -1 value. But the not matching value on your INNER JOIN is a 3, not a null or a -1. Save this answer.