Why SELECT N + 1 with no foreign keys and LINQ?
- by Daniel Flöijer
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.
