linq to sql and retrieving only specific rows from joined tables

Question

I have two tables 1) Product 2) Categories

Product table has a field called "CategoryID" and the Category table has a field called "CategoryName", ID, DateCreated, and a few others.

I want to use LINQ to SQL to query all the Product rows as well as JUST the Category.CategoryName. This query will be used in a datasource and be bound to a ListView.

I know I can use the LoadWith option with the DataContext and a join. But the problem is that I end up with all the rows from Product and Category. What I really want is all rows from Product and just the Category.CategoryName

The perfect solution would be if the LoadWith option would let you specify the fields to return but as answered here, that's not possible.

I did find one way to do it, create a custom class which has the same exact fields that I want returned. So for example

public class ProductInfo
{
   public string ProdName { get; set; }
   public int ProdID { get; set; }
   public int CategoryID { get; set; }
   public string CategoryName { get; set; } 
}

but the problem is that the select statement ends up really long. for example...

var products = from p in db.Products
               join c in db.Categories on p.CategoryID = c.ID
               select new ProductInfo { ProdName = p.ProdName, ProdID = p.ID,
 CategoryID = p.CategoryID, CategoryName = c.CategoryName }

this becomes really error prone.

So my question - is there a better way to accomplish this?

Edit/Clarification: i should have mentioned that the REASON i need the intermediary class is because i need to be able to return the result set as a specific type, in this case ProductInfo

Answer 1

I think you're pretty much on the right track, but I wouldn't really worry about the intermediery class:

var products = from p in db.Products
           join c in db.Categories on p.CategoryID = c.ID
           select new { p.ProdName, ProdId = p.ID, p.CategoryID, 
              CategoryName = c.CategoryName }

You can then wire this up in the Selecting event of the LinqDataSource. Scott Guthrie talks about in the section "Performing Custom Query Projections with the Selecting Event" in his post "Using a Custom Linq Expression with the asp:LinqDataSource" control".

Answer 2

oh gosh, i should have mentioned that the REASON i need the intermediary class is because i need to be able to return the result set as a specific type, in this case ProductInfo

Answer 3

List<ProductInfo> products = (from p in db.Products
               join c in db.Categories on p.CategoryID = c.ID
               select new ProductInfo { ProdName = p.ProdName, ProdID = p.ID,
 CategoryID = p.CategoryID, CategoryName = c.CategoryName }).ToList<ProductInfo>();