Inner Joins in LINQ to SQL

In this tutorial, we will explore how to perform inner joins using Language Integrated Query (LINQ) to SQL. We will cover the basic syntax and provide examples of how to join two tables based on a common column.

Introduction to Inner Joins

An inner join is a type of join that returns only the rows that have a match in both tables. In other words, it returns the intersection of the two tables based on the join condition. In LINQ to SQL, we can perform an inner join using the join keyword or the Join method.

Using the Join Keyword

The join keyword is used to specify the join condition and the tables to be joined. The basic syntax is as follows:

from table1 in db.Table1
join table2 in db.Table2 on table1.field equals table2.field
select new { table1.field2, table2.field3 }

In this example, we are joining Table1 and Table2 based on the field column. The on keyword specifies the join condition, and the equals keyword is used to compare the values of the columns.

Using the Join Method

The Join method is another way to perform an inner join in LINQ to SQL. The basic syntax is as follows:

var results = db.Table1.Join(db.Table2,
    table1 => table1.field,
    table2 => table2.field,
    (table1, table2) => new { table1.field2, table2.field3 });

In this example, we are joining Table1 and Table2 based on the field column. The first two arguments to the Join method specify the tables to be joined, and the third argument specifies the join condition.

Example: Joining Dealer and DealerContact Tables

Suppose we have two tables, Dealer and DealerContact, with a common column DealerID. We can join these tables using the following code:

var dealerContacts = from contact in db.DealerContact
                     join dealer in db.Dealer on contact.DealerId equals dealer.ID
                     select contact;

This will return all the rows from the DealerContact table where there is a matching row in the Dealer table based on the DealerID column.

Using Navigation Properties

If we have defined foreign keys between tables, LINQ to SQL can create navigation properties for us. These navigation properties allow us to access related data without having to write explicit joins. For example:

from contact in dealer.DealerContacts select contact

This will return all the contacts associated with a particular dealer.

Conclusion

In this tutorial, we have covered how to perform inner joins using LINQ to SQL. We have seen two ways of performing an inner join: using the join keyword and the Join method. We have also discussed how to use navigation properties to access related data without explicit joins.