我有一个数据库，不幸的是有没有真正的外键（我计划稍后添加这一点，但不希望现在就做，使移民更容易）。 我已经写手动映射到数据库中建立关系（按照本教程域对象http://www.codeproject.com/Articles/43025/A-LINQ-Tutorial-Mapping-Tables-to-Objects ），和我“VE最后得到的代码才能正常运行。 然而，我发现我现在SELECT N + 1点的问题。 相反，选择所有产品的，他们会选择一个而与此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]
控制器：
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);
}
因为我不知道如果我的LINQ表达是正确的我试着只是用这个，但我仍然有N + 1：
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; 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; }
}
我试图从我的浏览注释掉一切，所以什么也没有，似乎影响这一点。 该视图模型是因为它看起来那么简单，所以不应该是任何东西在那里。
当读这个（ http://www.hookedonlinq.com/LinqToSQL5MinuteOVerview.ashx ），我开始怀疑这可能是因为我在数据库中没有真正的外键，而我可能需要使用手动在我的代码加入。 那是对的吗？ 我会怎么做呢？ 我应该从我的域模型中删除我的映射代码或者是它，我需要添加/修改它的东西吗？
注：我已经剥离的部分代码的是，我不认为是相关的，使它更清洁了这个问题。 请让我知道，如果缺少了什么。
编辑：格特·阿诺德解决了所有问题Products从Category中查询一个接一个。 但是我仍然有所有的问题Products页面上显示得到由一个查询之一。
这种情况从我的视图代码：
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>
它是用的东西。首先（）再一次？ 我试过。取（1），但是我不能选择ID反正...
编辑：我尝试添加一些代码到我的仓库访问的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 ，没有他们的变量命名为@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
为了查询我的差异表明，DataLoadOptions实际上是在做的事情，但不是我所期望的。 什么我期望的是它产生这样的：
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
