Creating Custom Classes for LINQ to SQL Join Results

Data and Databases
1

I’m working with two database tables called Product and Category in my application. Right now, I can write LINQ queries that join these tables together, but the results come back as anonymous types. I want to be able to map the joined data to a custom strongly typed class instead.

Should I create a new class like ProductWithCategoryInfo to hold the combined data? I already have my Product and Category entity classes, but I need something that can hold fields from both tables when I do joins.

Here’s what my current query looks like:

from product in productTable
join cat in categoryTable on product.CategoryId equals cat.CategoryId
where (cat.CategoryId == selectedCategory)
select new
{
    product.ProductId,
    product.Title,
    product.Details,
    product.Cost,
    CategoryTitle = cat.Title
}

Is there a better way to handle this situation? I want to pass strongly typed objects to my MVC views instead of anonymous types.

2

yeah, custom class is def the way to go! but y not just add a CategoryTitle property directly to your existing Product class using projection? what’s pushing you towards keeping them separate instead of extending what u already have?

3

Totally agree on making a separate class. Just remember you’ll need a constructor that matches the properties you’re selecting for that select new ProductWithCategoryInfo syntax. Otherwise LINQ throws runtime errors that are a pain to debug.

4

Yes, creating a dedicated ProductWithCategoryInfo class is definitely the right move here. I ran into this exact same thing on a recent project where I needed product data with category info in my views. Using a custom class beats modifying your existing entities for a few reasons. It keeps your database entities clean and focused on what they’re supposed to do. Plus you can pick exactly which properties you need for each view. I ended up making classes like ProductDetailViewModel that pulled properties from multiple tables. Way more maintainable and the IntelliSense worked great in views. Just update your select statement to create your custom class instead of those anonymous types. This pattern really pays off when you’ve got different join scenarios throughout your app. You can make specific classes for each use case without messing up your core entity models.