Почему ВЫБИРАЕТ N + 1 без внешних ключей и LINQ?

Question

У меня есть база данных, которая, к сожалению, не имеет реальных внешних ключей (я планирую добавить это позже, но предпочитаю не делать это прямо сейчас, чтобы облегчить миграцию). Я вручную написал доменные объекты, которые сопоставляются с базой данных, чтобы установить отношения (следуя этому руководству http://www.codeproject.com/Articles/43025/A-LINQ-Tutorial-Mapping-Tables-to-Objects), и я наконец-то получил код для правильной работы. Тем не менее, я заметил, что теперь у меня есть проблема 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; }
}

Я пытался закомментировать все из моего просмотра, так что, кажется, ничто не влияет на это. ViewModel так же прост, как и выглядит, поэтому ничего не должно быть там.

Когда я читал это ( 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>

Это опять что-то с.First()? Я попробовал.Take(1), но потом все равно не смог выбрать идентификатор...

РЕДАКТИРОВАТЬ: я попытался добавить некоторый код в свой репозиторий для доступа к 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

Answer 1

Это First(), Он запускает выполнение части перед ней, а часть, которая следует за ней, выбирается отложенной загрузкой в ​​отдельных запросах. Хитрый, трудно заметить.

Вот что вы можете сделать, чтобы предотвратить это и извлечь все за один выстрел:

LocalizedCategories.Where(lc => lc.CountryID == 1 && lc.Name == category)
    .Take(1)
    .SelectMany(lc => lc.Category.ProductCategories)
    .Select (pc => pc.Product)

Вы должны сделать член ProductCategories общественности. Я думаю, что также лучше удалить производные свойства Category.Products а также Product.Categoriesпотому что я думаю, что они будут запускать запрос всякий раз, когда их владелец материализуется или обращается.