How to implement inner join using LINQ to SQL in ASP.NET MVC application?

Data and Databases
1

I’m working on an ASP.NET MVC project and need help converting a SQL inner join query to LINQ to SQL syntax. Here’s my original SQL query:

select P.Product_id, P.Product_Name, U.UnitName as Unit, P.Product_Category as Details
from Products as P inner join UnitTypes as U
on P.UnitTypeId = U.Id where P.Is_Active=1

Currently my repository class looks like this:

public class ProductRepository
{
    private AppDataContext context = new AppDataContext();
    public IQueryable<Product> GetAllProducts()
    {
        return context.Products;
    }
}

The issue is that context.Products only returns data from one table. I need to join it with another table and return the combined results. Here’s what I want to achieve:

public class ProductRepository
{
    private AppDataContext context = new AppDataContext();
    public IQueryable<Product> GetAllProducts()
    {
        // need inner join implementation here
    }
}

What’s the correct way to write this inner join query in LINQ to SQL?

2

interesting approach! are you planning to use anonymous types for the projection or make a specific dto class? also, do you have navigation properties set up between products and unittypes in your datacontext? that would make the join syntax way simpler.

3

Here’s a cleaner method-based approach: context.Products.Join(context.UnitTypes, p => p.UnitTypeId, u => u.Id, (p, u) => new { p.Product_id, p.Product_Name, Unit = u.UnitName, Details = p.Product_Category }).Where(x => x.Is_Active == 1). I like this better for complex queries - it’s more readable and easier to debug. Just watch your return type since you’re joining tables. You’ll probably need a custom DTO class instead of IQueryable<Product> because the result has fields from both tables.

4

you can use join syntax like this: from p in context.Products join u in context.UnitTypes on p.UnitTypeId equals u.Id where p.Is_Active == 1 select new { p.Product_id, p.Product_Name, Unit = u.UnitName, Details = p.Product_Category } - works great for me in similar situations