优化具有外键访问的LINQ to SQL语句 [英] Optimize LINQ to SQL statement that has foreign key access

问题描述

我有以下数据库图:

每个Product使用LanguageKey来确定其名称，并且每个LanguageKey都有多个LanguageValue记录，其中包含每种语言的值.

Each Product uses a LanguageKey to determine its Name, and each LanguageKey has multiple LanguageValue records containing value for each language.

现在在我的Controller中，我想将我的Product实体选择到ViewModel对象中，所以我的声明如下所示:

Now in my Controller, I want to Select my Product entity into the ViewModel object, so my statement look like this:

 var model = new ProductListViewModel()
        {
            Products = products
                .Select(q => new ProductViewModel()
                {
                    ID = q.ID,
                    Name = q.LanguageKey.LanguageValues.FirstOrDefault(p => p.LanguageID == this.CurrentLanguage.ID && p.Active).Value,
                    Code = q.Code,

                    // Other code

因此，简而言之，我已经具有所需的语言ID，并且需要获取确切的值.但是，在查看VS的诊断工具时，当执行查询树时(当我在View中调用foreach语句时)，我的应用程序执行了多个SELECT语句，每个SELECT语句都针对一个LanguageValue，如下所示:

So in short, I already have the language ID needed, and I need to get the exact value. However, when looking at the VS's Diagnostic Tools, when the query tree is executed (when I call a foreach statement in my View), there are multiple SELECT statement executed by my application, each for one LanguageValue, like this:

"SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Active] AS [Active]
    FROM [dbo].[LanguageKey] AS [Extent1]
    WHERE [Extent1].[ID] = @EntityKeyValue1"

"SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[LanguageKeyID] AS [LanguageKeyID], 
    [Extent1].[LanguageID] AS [LanguageID], 
    [Extent1].[Value] AS [Value], 
    [Extent1].[Active] AS [Active]
    FROM [dbo].[LanguageValue] AS [Extent1]
    WHERE [Extent1].[LanguageKeyID] = @EntityKeyValue1"

是因为我正在使用FirstOrDefault()吗?有什么更好的方法来解决这个问题，因为我的应用程序在过滤后将经常处理大约1k条记录.

Is it because I am using FirstOrDefault()? Is there any better way to deal with this, because my application will frequently process around 1k records after filtered.

P.s:我也想知道为什么在已编译的查询中没有Active谓词.

P.s: I am also wonder why there is no Active predicate in the compiled query.

这是我获取products的方式(上下文是我的DbContext，该函数位于ProductService类中，而productService是其实例):

This is how I get the products (Context is my DbContext, the function is inside ProductService class, and productService is its instance):

    public IEnumerable<Product> GetAllActiveProducts()
    {
        return this.Context.Products
            .Where(q => q.Active);
    }

    var products = productService.GetAllActiveProducts();

推荐答案

您的GetAllActiveProducts()应该返回IQueryable<Product>，而不是IEnumerable<Product>.通过返回IEnumerable<Product>，您可以有效地强制对该枚举进行任何进一步的操作，以使其在客户端进行.正是这样会导致您所看到的行为.

Your GetAllActiveProducts() should return IQueryable<Product>, not IEnumerable<Product>. By returning IEnumerable<Product>, you're effectively forcing any further operations on that enumerable to be done client-side. Which causes exactly the behaviour you're seeing.

这篇关于优化具有外键访问的LINQ to SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！