How to map LINQ To SQL to enable eager loading, re

This is related (but fairly independent) to my question here: Why SELECT N + 1 with no foreign keys and LINQ?

I've tried using DataLoadOptions to force eager loading, but I'm not getting it to work.

I'm manually writing my LinqToSQL mappings and was first following this tutorial: http://www.codeproject.com/Articles/43025/A-LINQ-Tutorial-Mapping-Tables-to-Objects

Now I've found this tutorial: http://msdn.microsoft.com/en-us/library/bb386950.aspx

There's at least one major difference that I can spot. The first tutorial suggest returning ICollection's and the second EntitySet's. Since I'm having issues I tried to switch my code to return EntitySet's, but then I got issue with needing to reference System.Data.Linq in my Views and Controllers. I tried to do that, but didn't get it to work. I'm also not sure it's a good idea.

At this point, I just want to know which return type I'm supposed to use for a good design? Can I have a good design and still be able to force eager loading in specific cases?

A lot of trial and error finally lead to the solution. It's fine to return ICollection or IList, or in some cases IEnumerable. Some think returning EntitySet or IQueryable is a bad idea, and I agree because it exposes to much of the datasource/technology. Some thing returning IEnumerable is a bad idea and it seems like it depends. The problem beeing that it can be used for lazy loading, which may or may not be a good thing.

One reoccuring issue is that of returning paged results with a count for the total items outside the page. This can be solved by creating a CollectionPage<T> ( http://www.codetunnel.com/blog/post/104/how-to-properly-return-a-paged-result-set-from-your-repository )

More on what to return from repositories here:

http://www.codetunnel.com/blog/post/103/should-you-return-iqueryablet-from-your-repositories

http://www.shawnmclean.com/blog/2011/06/iqueryable-vs-ienumerable-in-the-repository-pattern/

IEnumerable vs IQueryable for Business Logic or DAL return Types

List, IList, IEnumerable, IQueryable, ICollection, which is most flexible return type?

Even more important, DataLoadOptions can do the eager loading! I've now restructured my code so much I'm not 100% sure what I did wrong to cause DataLoadOptions not to work. As far as I've gathered I should get an exception if I tried to add it to the DataContext after the DataContext has been used, which it didn't. What I've found out though is to think in the Unit of Work-pattern. However, for my needs (and because I don't want to return EntitySet or IQueryable from my repositories) I'm not going to implement a cross-repository Unit of Work. Instead I'm just thinking about my repository methods as their own small Unit of Work. I'm sure there's bad things about this (for instance it might cause more round-trips to the database in some update scenarios), and in the future I might reconcider. However it's a simple clean solution.

More info here:

https://stackoverflow.com/a/7941017/1312533

http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

This is what I ended up with in my repository:

public class SqlLocalizedCategoriesRepository : ILocalizedCategoriesRepository
{
    private string connectionString;
    private HttpContextBase httpContext;
    public SqlLocalizedCategoriesRepository(string connectionString, HttpContextBase httpContext) // Injected with Inversion of Control
    {
        this.connectionString = connectionString;
        this.httpContext = httpContext;
    }

    public CollectionPage<Product> GetProductsByLocalizedCategory(string category, int countryId, int page, int pageSize)
    {
        // Setup a DataContext
        using (var context = new DataContext(connectionString)) // Because DataContext implements IDisposable it should be disposed of
        {
            var dlo = new System.Data.Linq.DataLoadOptions();
            dlo.LoadWith<Product>(p => p.ProductSubs); // In this case I want all ProductSubs for the Products, so I eager load them with LoadWith. There's also AssociateWith which can filter what is eager loaded.
            context.LoadOptions = dlo;
            context.Log = (StringWriter)httpContext.Items["linqToSqlLog"]; // For logging queries, a must so you can see what LINQ to SQL generates

            // Query the DataContext
            var cat = (from lc in context.GetTable<LocalizedCategory>()
                       where lc.CountryID == countryId && lc.Name == category
                       select lc.Category).First(); // Gets the category into memory. Might be some way to not get it into memory by combining with the next query, but in my case my next step is that I'm also going to need the Category anyway so it's not worth doing because I'm going to restructure this code to take a categoryId parameter instead of the category parameter.

            var products = (from p in context.GetTable<Product>()
                            where p.ProductCategories.Any(pm => pm.Category.CategoryID == cat.CategoryID)
                            select p); // Generates a single query to get the the relevant products, which with DataLoadOptions loads related ProductSubs. It's important that this is just a query and not loaded into memory since we're going to split it into pages.

            // Return the results
            var pageOfItems = new CollectionPage<Product>
            {
                Items = products.Skip(pageSize * (page - 1)).Take(pageSize).ToList(), // Gets the page of products into memory
                TotalItems = products.Count(), // Get to total count of items belonging to the Category
                CurrentPage = page
            };
            return pageOfItems;
        }
    }
}