I have a database that unfortunately have no real foreign keys (I plan to add this later, but prefer not to do it right now to make migration easier). I have manually written domain objects that map to the database to set up relationships (following this tutorial http://www.codeproject.com/Articles/43025/A-LINQ-Tutorial-Mapping-Tables-to-Objects), and I've finally gotten the code to run properly. However, I've noticed I now have the SELECT N + 1 problem. Instead of selecting all Product's they're selected one by one with this SQL:
SELECT [t0].[id] AS [ProductID], [t0].[Name], [t0].[info] AS [Description]
FROM [products] AS [t0]
WHERE [t0].[id] = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [65]
Controller:
public ViewResult List(string category, int page = 1)
{
var cat = categoriesRepository.Categories.SelectMany(c => c.LocalizedCategories).Where(lc => lc.CountryID == 1).First(lc => lc.Name == category).Category;
var productsToShow = cat.Products;
var viewModel = new ProductsListViewModel
{
Products = productsToShow.Skip((page - 1) * PageSize).Take(PageSize).ToList(),
PagingInfo = new PagingInfo
{
CurrentPage = page,
ItemsPerPage = PageSize,
TotalItems = productsToShow.Count()
},
CurrentCategory = cat
};
return View("List", viewModel);
}
Since I wasn'
t sure if my LINQ expression was correct I tried to just use this but I still got N+1:
var cat = categoriesRepository.Categories.First();
Domain objects:
[Table(Name = "products")]
public class Product
{
[Column(Name = "id", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
public int ProductID { get; set; }
[Column]
public
string Name { get; set; }
[Column(Name = "info")]
public
string Description { get; set; }
private EntitySet<ProductCategory> _productCategories = new EntitySet<ProductCategory>();
[System.Data.Linq.Mapping.Association(Storage = "_productCategories", OtherKey = "productId", ThisKey = "ProductID")]
private ICollection<ProductCategory> ProductCategories
{
get { return _productCategories; }
set { _productCategories.Assign(value); }
}
public ICollection<Category> Categories
{
get { return (from pc in ProductCategories select pc.Category).ToList(); }
}
}
[Table(Name = "products_menu")]
class ProductCategory
{
[Column(IsPrimaryKey = true, Name = "products_id")]
private int productId;
private EntityRef<Product> _product = new EntityRef<Product>();
[System.Data.Linq.Mapping.Association(Storage = "_product", ThisKey = "productId")]
public Product Product
{
get { return _product.Entity; }
set { _product.Entity = value; }
}
[Column(IsPrimaryKey = true, Name = "products_types_id")]
private int categoryId;
private EntityRef<Category> _category = new EntityRef<Category>();
[System.Data.Linq.Mapping.Association(Storage = "_category", ThisKey = "categoryId")]
public Category Category
{
get { return _category.Entity; }
set { _category.Entity = value; }
}
}
[Table(Name = "products_types")]
public class Category
{
[Column(Name = "id", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
public int CategoryID { get; set; }
private EntitySet<ProductCategory> _productCategories = new EntitySet<ProductCategory>();
[System.Data.Linq.Mapping.Association(Storage = "_productCategories", OtherKey = "categoryId", ThisKey = "CategoryID")]
private ICollection<ProductCategory> ProductCategories
{
get { return _productCategories; }
set { _productCategories.Assign(value); }
}
public ICollection<Product> Products
{
get { return (from pc in ProductCategories select pc.Product).ToList(); }
}
private EntitySet<LocalizedCategory> _LocalizedCategories = new EntitySet<LocalizedCategory>();
[System.Data.Linq.Mapping.Association(Storage = "_LocalizedCategories", OtherKey = "CategoryID")]
public ICollection<LocalizedCategory> LocalizedCategories
{
get { return _LocalizedCategories; }
set { _LocalizedCategories.Assign(value); }
}
}
[Table(Name = "products_types_localized")]
public class LocalizedCategory
{
[Column(Name = "id", IsPrimaryKey = true, IsDbGenerated = true, AutoSync = AutoSync.OnInsert)]
public int LocalizedCategoryID { get; set; }
[Column(Name = "products_types_id")]
private int CategoryID;
private EntityRef<Category> _Category = new EntityRef<Category>();
[System.Data.Linq.Mapping.Association(Storage = "_Category", ThisKey = "CategoryID")]
public Category Category
{
get { return _Category.Entity; }
set { _Category.Entity = value; }
}
[Column(Name = "country_id")]
public int CountryID { get; set; }
[Column]
public
string Name { get; set; }
}
I've tried to comment out everything from my View, so nothing there seems to influence this. The ViewModel is as simple as it looks, so shouldn'
t be anything there.
When reading this ( http://www.hookedonlinq.com/LinqToSQL5MinuteOVerview.ashx) I started suspecting it might be because I have no real foreign keys in the database and that I might need to use manual joins in my code. Is that correct? How would I go about it? Should I remove my mapping code from my domain model or is it something that I need to add/change to it?
Note: I've stripped parts of the code out that I don'
t think is relevant to make it cleaner for this question. Please let me know if something is missing.
