How to perform an inner join using LINQ to SQL in C#?

Data and Databases
1

I’m trying to figure out how to do a basic inner join with LINQ to SQL in C#. I know how to write it in regular SQL, but I’m not sure about the LINQ syntax.

Here’s what I want to do:

I have two tables, Products and Categories. I want to join them based on their IDs and get all the product details along with their category names.

In SQL, it would look something like this:

SELECT Products.*, Categories.CategoryName
FROM Products
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID

How can I write this using LINQ to SQL? I’m especially confused about how to specify the ON part of the join.

Any help or examples would be great. Thanks!

2

ooh, interesting question! have u tried using the join keyword in LINQ? it’s pretty similar to SQL syntax. something like:

from p in Products
join c in Categories on p.CategoryID equals c.CategoryID
select new { p, CategoryName = c.CategoryName }

wht do u think? does that help or am i way off? :thinking:

3

In my experience, using the query syntax for LINQ to SQL can be more readable for complex joins. Here’s how you can achieve the inner join you’re looking for:

var query = from p in dataContext.Products
join c in dataContext.Categories on p.CategoryID equals c.CategoryID
select new
{
p.ProductID,
p.ProductName,
p.UnitPrice,
c.CategoryName
};

This approach mirrors the SQL syntax you’re familiar with. The ‘on’ clause is represented by the ‘equals’ keyword in LINQ. You can then execute this query and iterate over the results as needed.

Remember to import the necessary namespaces and ensure your data context is properly set up. This method has served me well in various projects.

4

hey there! i’ve done this before. try using a lambda expression, it’s cleaner:

var result = Products.Join(Categories,
p => p.CategoryID,
c => c.CategoryID,
(p, c) => new { p, c.CategoryName });

hope this helps! let me know if u need more info :slight_smile: