How can I populate an object's child List property with a LINQ To SQL query

I'm creating a product listing for an online store. It's pretty standard stuff, a page of product thumbnails with brief details, price and a link through to full details.

I'm using a repository pattern, so I have a central data repository which gives me back tables from a SQL server. I've cut a lot of the code out for the sake of b开发者_Go百科revity, but just so you get the idea:

public class SqlProductsRepository : IProductsRepository
{
    private Table<Product> productsTable;

    public SqlProductsRepository(string connectionString)
    {
        var context = new DataContext(connectionString);

        productsTable = context.GetTable<Product>();
        // More tables set up here
    }

    public IQueryable<Product> Products
    {
        get { return productsTable; }
    }

    // More properties here
}

I have the following objects mapped to tables:

[Table(Name = "Products")]
public class Product
{
    [Column(IsPrimaryKey = true)]
    public string ProductCode { get; set; }
    [Column]
    public string Name { get; set; }
    [Column]
    public decimal Price { get; set; }

    public List<ShopImage> Images = new List<ShopImage>();
}

[Table(Name = "Images_Products")]
public class Image_Product
{
    [Column] 
    public int ImageID { get; set; }
    [Column] 
    public string ProductCode { get; set; }
    [Column] 
    public int DisplayOrder { get; set; }
}

[Table(Name = "Images")]
public class Image
{
    [Column(Name = "ImageID")]
    public int ImageID { get; set; }
    [Column]
    public bool Caption { get; set; }
}

If I perform the following query:

// 'db' is the repository (member variable of the controller class)

IQueryable<Product> products = from p in db.Products
                               join ip in db.Image_Product on p.ProductCode equals ip.ProductCode
                               where ip.DisplayOrder == 0
                               select p;

I get a nice IQueryable full of Product objects. However, what I want to do is populate each object's Images list property with a single Image object, with its ID set from the joined Image_Product table.

So I end up with a list of Products, each with one Image in its Images property, which has the ID of the image for that product in the database where DisplayOrder is 0.

I tried this projection, which I thought made sense:

IQueryable<Product> products = from p in db.Products
                               join ip in db.Image_Product on p.ProductCode equals ip.ProductCode
                               where ip.DisplayOrder == 0
                               select new Product { 
                                    ProductCode = p.ProductCode,
                                    Price = p.Price,
                                    Images = new List<Image> { 
                                        new Image { ImageID = ip.ImageID } 
                                    }
                               };

Which compiles, but throws a runtime error: Explicit construction of entity type 'xxx.Product' in query is not allowed.

Yet elsewhere in the project I do this:

var pages = from i in db.TemplatePageNavigationItems
            orderby i.DisplayOrder
            select new NavigationItem {
                ID = i.PageID,
                ParentID = i.ParentID,
                Text = i.Name,
                Title = i.Name,
                Url = (i.Folder == null) ? "" : i.Folder
            };

And get no complaints! I assume it's something to do with the first query returning an IQueryable<Product> but I'm not sure why.

Two questions really: why is this not allowed in the first situation, and what should I be doing in order to get my desired result?

As the error says, you can't construct explicit entity types (Product is just that) in your query which should return IQueryable<Product>. Your pages query will return IEnumerable<NavigationItem> and NavigationItem does not seem to be an entity type defined in the database.

You could try returning IEnumerable<Product> in your first query or define a separate type and return IEnumerable of that instead, if you need to project explicit, custom tailored instances of an object.