为什么选择N + 1没有外键和LINQ？ [英] Why SELECT N + 1 with no foreign keys and LINQ?
问题描述
我有一个数据库，不幸的是没有真正的外键（我计划稍后添加，但是现在宁愿不要这样做，使迁移更容易）。我已经手动编写了映射到数据库的域对象来建立关系（下面的教程 http://www.codeproject.com/Articles/43025/A-LINQ-Tutorial-Mapping-Tables-to-Objects ），我终于得到了代码正常运行。但是，我注意到我现在有SELECT N + 1的问题。
SELECT [t0]。[id] AS [产品ID]，[t0]。[Name]，[t0]。[info] AS [说明]
FROM [产品] AS [t0]
WHERE [t0]。[id] = @ p0
- @ p0：输入Int（大小= -1; Prec = 0; Scale = 0）
Controller： 由于我不确定我的LINQ表达是否正确，使用这个，但我仍然有N + 1： 域对象：
$ b $ pre $ public ViewResult List（string category，int page = 1）
{$ （lc => lc.CountryID == 1）.First（lc => lc.Name ==类别）.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）;
$ b
var cat = categoriesRepository.Categories.First（）;
[Table（Name =products）]
public class Product
{
[Column（Name =id，IsPrimaryKey = true，IsDbGenerated = true，AutoSync = AutoSync.OnInsert）]
public int ProductID {get;组; }
[Column]
public string Name {get;组; }
$ b $ [列（Name =info）]
public string描述{get;组; }
$ b $ 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>类别
{
get {return（从ProductCategories中的pc选择pc.Category）.ToList（）; }（b
$ b $ {
$ { ，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; }
$ b $ [$ {$（$ {
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; } {b
$ {
[列表（名称=产品类型）] id，IsPrimaryKey = true，IsDbGenerated = true，AutoSync = AutoSync.OnInsert）]
public int CategoryID {get;组; }
$ b $ 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>产品
{
得到{返回（从产品分类选择pc.Product pc）.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）; }}
public class LocalizedCategory
{
[Column（Name =product_types_localized）]
$ b $ id，IsPrimaryKey = true，IsDbGenerated = true，AutoSync = AutoSync.OnInsert）]
public int LocalizedCategoryID {get;组; }
$ b $ [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;组; }
[Column]
public string Name {get;组;我试图从我的视图中评论一切，所以没有任何东西似乎影响了这一点。 ViewModel就像看起来一样简单，所以不应该有任何东西。
当阅读这个（ http://www.hookedonlinq.com/LinqToSQL5MinuteOVerview.ashx ）我开始怀疑这可能是因为我没有真正的外键在数据库中，我可能需要在我的代码中使用手动连接。那是对的吗？我将如何去做呢？我应该从我的域模型中删除我的映射代码，还是我需要添加/更改它？
注意：我已经剥离了部分代码我认为这个问题并不重要。
编辑：Gert Arnold解决了所有产品从 Category 被逐个查询。不过，我仍然有问题，所有产品显示在页面上被逐个查询。
发生在我的视图代码：
List.cshtml：
模型MaxFPS.WebUI.Models.ProductsListViewModel
@foreach（Model.Products中的var产品）{
Html.RenderPartial（ProductSummary，product）;
$ / code> ProductSummary.cshtml：
@model MaxFPS.Domain.Entities.Product
< div class =item>
< h3> @ Model.Name< / h3>
@ Model.Description
@if（Model.ProductSubs.Count == 1）
{
using（Html.BeginForm（AddToCart，Cart））{
@ Html.HiddenFor（x => x.ProductSubs.First（）。ProductSubID）;
@ Html.Hidden（returnUrl，Request.Url.PathAndQuery）;
< input type =submitvalue =+ Add to cart/>
p podo
}
< h4> @ Model.LowestPrice.ToString（c）< / h4>
< / div>
再次使用.First（）我尝试了.Take（1），但我无法选择ID ... $ /
$ b编辑：我试着添加一些代码到我的资源库来访问DataContext和这段代码创建一个DataLoadOptions。但它仍然会为每个ProductSub生成一个查询。
var dlo = new System.Data.Linq.DataLoadOptions（）;
dlo.LoadWith< Product>（p => p.ProductSubs）;
localizedCategoriesRepository.DataContext.LoadOptions = dlo;
var productsInCategory = localizedCategoriesRepository.LocalizedCategories.Where（lc => lc.CountryID == 1&& lc.Name == category）
.Take（1）
.SelectMany lc => lc.Category.ProductCategories）
.Select（pc => pc.Product）;
生成的SQL略有不同，查询顺序也不同。
对于选择ProductSub的查询，DataLoadOptions代码会生成名为 @ x1 的变量，如果没有它们，变量将被命名为
$ b $ p $ SELECT $ [code $]。 [t0]。[id] AS [ProductSubID]，[t0]。[Name]，[t0]。[Price]
FROM [products_sub] AS [t0]
WHERE [t0]。[products_id ] = @ x1
查询的差异表明DataLoadOptions实际上在做某些事情，但不是我所期望的。我期望的是它会产生这样的东西：
$ p $选择[t0]。[products_id] AS [ProductID] [t0]。[id] AS [ProductSubID]，[t0]。[Name]，[t0]。[Price]
FROM [products_sub] AS [t0]
WHERE [t0]。[ products_id] = @ x1 OR [t0]。[products_id] = @ x2 OR [t0]。[products_id] = @ x3 ...等等
它是 First（）。它会触发它之前的部分的执行，并且在它之后的部分通过延迟加载在不同的查询中被提取。狡猾，难以发现。
这是你可以做的，以防止它，并获取一切：
LocalizedCategories.Where（lc => lc.CountryID == 1&& lc.Name == category）
.Take（1）
。 SelectMany（lc => lc.Category.ProductCategories）
.Select（pc => pc.Product）
您应该使成员 ProductCategories public。我认为最好删除派生的属性 Category.Products 和 Product.Categories ，因为我认为他们会触发一个查询，只要其所有者物化或处理。
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.
EDIT: Gert Arnold solved the issue of all Products from the Category being queried one by one. However I'm still having the issue that all Products displayed on the page gets queried one by one.
This happens from my view code:
List.cshtml:
@model MaxFPS.WebUI.Models.ProductsListViewModel
@foreach(var product in Model.Products) {
Html.RenderPartial("ProductSummary", product);
}
ProductSummary.cshtml:
@model MaxFPS.Domain.Entities.Product
<div class="item">
<h3>@Model.Name</h3>
@Model.Description
@if (Model.ProductSubs.Count == 1)
{
using(Html.BeginForm("AddToCart", "Cart")) {
@Html.HiddenFor(x => x.ProductSubs.First().ProductSubID);
@Html.Hidden("returnUrl", Request.Url.PathAndQuery);
<input type="submit" value="+ Add to cart" />
}
}
else
{
<p>TODO: länk eller dropdown för produkter med varianter</p>
}
<h4>@Model.LowestPrice.ToString("c")</h4>
</div>
Is it something with .First() again? I tried .Take(1) but then I couldn't select the ID anyway...
EDIT: I tried adding some code to my repository to access the DataContext and this code to create a DataLoadOptions. But it still generates a query for each ProductSub.
var dlo = new System.Data.Linq.DataLoadOptions();
dlo.LoadWith<Product>(p => p.ProductSubs);
localizedCategoriesRepository.DataContext.LoadOptions = dlo;
var productsInCategory = localizedCategoriesRepository.LocalizedCategories.Where(lc => lc.CountryID == 1 && lc.Name == category)
.Take(1)
.SelectMany(lc => lc.Category.ProductCategories)
.Select(pc => pc.Product);
The SQL generated is slightly different though, and the order of the queries is also different.
For the queries that select ProductSub the DataLoadOptions-code generates variables named @x1 and without them the variables are named @p0.
SELECT [t0].[products_id] AS [ProductID], [t0].[id] AS [ProductSubID], [t0].[Name], [t0].[Price]
FROM [products_sub] AS [t0]
WHERE [t0].[products_id] = @x1
The difference in order for queries to me indicate that DataLoadOptions is in fact doing something, but not what I expect. What I'd expect is for it to generate something like this:
SELECT [t0].[products_id] AS [ProductID], [t0].[id] AS [ProductSubID], [t0].[Name], [t0].[Price]
FROM [products_sub] AS [t0]
WHERE [t0].[products_id] = @x1 OR [t0].[products_id] = @x2 OR [t0].[products_id] = @x3 ... and so on
It is the First(). It triggers execution of the part before it and the part following it is fetched by lazy loading in separate queries. Tricky, hard to spot.
This is what you can do to prevent it and fetch everything in one shot:
LocalizedCategories.Where(lc => lc.CountryID == 1 && lc.Name == category)
.Take(1)
.SelectMany(lc => lc.Category.ProductCategories)
.Select (pc => pc.Product)
You should make the member ProductCategories public. I think it is also better to remove the derived properties Category.Products and Product.Categories, because I think they will trigger a query whenever their owner is materialized or addressed.
这篇关于为什么选择N + 1没有外键和LINQ？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
