Creating Custom Classes for LINQ to SQL Join Operations

Data and Databases
1

I’m working with two database tables called Product and Brand. I want to join these tables using LINQ to SQL and map the results to a custom strongly typed class like ProductWithBrandInfo. This way I can pass it properly to my MVC view.

Right now I have separate Product and Brand entity classes. Should I create an additional class for the joined data? I feel like I might be missing something obvious here. Any guidance would be appreciated.

from item in productTable
join brand in brandTable on item.BrandId equals brand.BrandId
where (brand.BrandId == selectedBrandId.Value)
select new
{
    item.ProductId,
    item.Title,
    item.Details,
    item.Cost,
    BrandTitle = brand.Title
}

<Database Name="Store" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">
  <Table Name="Brand" Member="Store.Models.Brand">
    <Type Name="Store.Models.Brand">
      <Column Name="BrandId" Member="BrandId" IsDbGenerated="true" IsPrimaryKey="true" />
      <Column Name="Title" Member="Title" />
      <Column Name="Details" Member="Details" />
    </Type>
  </Table>
  <Table Name="Product" Member="Store.Models.Product">
    <Type Name="Store.Models.Product">
      <Column Name="ProductId" Member="ProductId" IsDbGenerated="true" IsPrimaryKey="true" />
      <Column Name="Title" Member="Title" />
      <Column Name="Details" Member="Details" />
      <Column Name="ImagePath" Member="ImagePath" />
      <Column Name="Cost" Member="Cost" />
      <Column Name="BrandId" Member="BrandId" />
      <Association Name="FK_Product_Brand" Member="Brand" ThisKey="BrandId" OtherKey="BrandId" IsForeignKey="true" />
    </Type>
  </Table>
</Database>
2

Yes, creating a dedicated class for your combined data is advisable. Custom classes, such as ProductWithBrandInfo, enhance maintainability and type safety, offering clear advantages over anonymous objects. While you could leverage the navigation property to access item.Brand.Title directly in your query, be cautious of potential additional SQL queries depending on your DataContext configuration. Ensure that your property names in the class align perfectly with those in your select statement to avoid any mapping issues. Utilizing strongly typed classes also simplifies unit testing, allowing for direct property mocking. I recommend organizing these composite classes within a ViewModels or DTOs folder to keep your domain entities uncluttered.

3

definitely go with the custom class! anonymous objects are a nightmare when you’re passing data to views. just make sure your ProductWithBrandInfo properties match exactly what you’re selecting - otherwise you’ll hit runtime errors. since you already have the association set up, you could also just use item.Brand.Title instead of doing the explicit join if that’s easier.

4

what’s your specific use case? planning to reuse ProductWithBrandInfo elsewhere? also, thought about lazy loading with those navigation properties? performance could be rough with bigger datasets.