LINQ to SQL Table Dependency

Question

If I have two tables... Category and Pet.

Is there a way in LINQ to SQL to make the result of the joined query map to a another strongly typed class (such as: PetWithCategoryName) so that I can strongly pass it to a MVC View?

I currently have Category and Pet classes... should I make another one?

Maybe I missing something here. Can any of you enlighten me?

from p in petTable
join c in categoryTable on p.CategoryId equals c.Id
where (c.Id == categoryId.Value)
select new
{
    p.Id, 
    p.Name,
    p.Description,
    p.Price,
    CategoryName = c.Name
}

<?xml version="1.0" encoding="utf-8" ?>
<Database Name="PetShop" xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007"&gt;
  <Table Name="Category" Member="PetShop.Models.Category">
    <Type Name="PetShop.Models.Category">
      <Column Name="Id" Member="Id" IsDbGenerated="true" IsPrimaryKey="true" />
      <Column Name="Name" Member="Name" />
      <Column Name="Description" Member="Description" />
    </Type>
  </Table>
  <Table Name="Pet" Member="PetShop.Models.Pet">
    <Type Name="PetShop.Models.Pet">
      <Column Name="Id" Member="Id" IsDbGenerated="true" IsPrimaryKey="true" />
      <Column Name="Name" Member="Name" />
      <Column Name="Description" Member="Description" />
      <Column Name="ImageUrl" Member="ImageUrl" />
      <Column Name="Price" Member="Price" />
      <Column Name="CategoryId" Member="CategoryId" />
      <Association Name="FK_Pet_Category" Member="Category" ThisKey="CategoryId" OtherKey="Id" IsForeignKey="true" />
    </Type>
  </Table>
</Database>

Answer 1

If you use the LoadWith LoadOption then your Pet query will do an eager load on categories, so that you will be able to do

MyPet.Category.Name without incurring an extra query, so you'll have the data joined and strongly typed without the risk of running multiple queries for Categories as you loop or bind on the Pet collection.

Or you can use stored procedures in Linq To SQL,the result is strongly typed.

Answer 2

How would I go about using LoadWith? I'm not finding much help online. Any good resources?

I found this online: http://blogs.msdn.com/wriju/archive/2007/10/04/linq-to-sql-change-in-datacontext-from-beta-1-to-beta-2.aspx

You would do something like:

var loadOption = new DataLoadOptions();           
loadOption.LoadWith<Pets>(p => p.Category);
db.LoadOptions = loadOption; 

var pets = from p in PetStoreContext.Pets
           select p;

And then your pets query will already include category, so no trip to the database happens when you try to access category.