手动选择相关的表数据(SELECT N + 1问题)LINQ to SQL [英] Manually select related table data (SELECT N + 1 problem) LINQ to SQL

问题描述

数据库示例:

图像-ImageTag-标签

图像可以具有多个标签.关系建立得很好，但我遇到了性能问题.

Images can have multiple tags. The relationships are set up fine and stuff but I am running into performance issues.

我有许多不同的查询，这些查询根据不同的条件选择图像".它们工作正常，但是未在这些查询中选择标签"的数据.

I have many different queries which select Images according to different criteria. They work fine, however the data for the Tags are not selected with these queries.

这意味着如果我遍历10张图像的列表并尝试访问它们的标签对象(通过ImageTag)，则会在数据库中针对每个图像执行一个新查询.

This means if I iterate through a list of 10 images and try to access their tags objects (via ImageTag), then a new query is executed on my database for every image.

<%foreach (LINQRepositories.Image i in Model)
  { %>

   <li><%=i.title%>
    <ul>
        <%foreach(ImageTag t in i.ImageTags){ %>
            <li><%=t.Tag.name%></li>
        <%} %>
    </ul>
   </li> 

<%} %>

这显然不是理想的.有没有一种方法可以强制LINQ to SQL查询某些数据?

This is obviously not ideal. Is there a way to force LINQ to SQL to query for certain data?

这是我的一个查询示例

public static IQueryable<Image> WithTags(this IQueryable<Image> qry, IEnumerable<Tag> tags)
{
    return
        from i in qry
        from iTags in i.ImageTags
        where tags.Contains(iTags.Tag)
        select i;
}

---

编辑

尝试数据加载选项后，这是一个正在生成的示例查询

---

Edit

After trying dataload options, this is an example query being generated

{SELECT [t0].[id]，[t0].[title]， [t0].[legend]，[t0].[dateAdded]， [t0].[deleted]，[t0].[averageRating]， [t0].[numberOfVotes]， [t0].[imageOfTheWeek]， [t0].[版权]， [t0].[copyrightText]， [t0].[areaOfInterest]，[t0].[typeId]， [t0].[authorId]， [t0].[editorialStatusId]， [t0].[comments]来自[dbo].[Image] AS [t0]交叉加入([dbo].[ImageTag] AS [t1] 内部联接[dbo].[标签] AS [t2]开[t2].[id] = [t1].[TagId])在哪里 ([t2].[id] = @ p0)和(不是 ([t0].[deleted] = 1))AND(不 ([t0].[deleted] = 1))和 ([t1].[ImageId] = [t0].[id])}

{SELECT [t0].[id], [t0].[title], [t0].[legend], [t0].[dateAdded], [t0].[deleted], [t0].[averageRating], [t0].[numberOfVotes], [t0].[imageOfTheWeek], [t0].[copyright], [t0].[copyrightText], [t0].[areaOfInterest], [t0].[typeId], [t0].[authorId], [t0].[editorialStatusId], [t0].[comments] FROM [dbo].[Image] AS [t0] CROSS JOIN ([dbo].[ImageTag] AS [t1] INNER JOIN [dbo].[Tag] AS [t2] ON [t2].[id] = [t1].[TagId]) WHERE ([t2].[id] = @p0) AND (NOT ([t0].[deleted] = 1)) AND (NOT ([t0].[deleted] = 1)) AND ([t1].[ImageId] = [t0].[id]) }

推荐答案

您可以使用

You can use the DataLoadOptions class to load related objects with a query.

DataLoadOptions dlo = new DataLoadOptions();

dlo.LoadWith<Image>(image => image.ImageTags);
dlo.LoadWith<ImageTag>(imageTag => imageTag.Tags);

context.DataLoadOptions = dlo;

只需提一下-这就是"SELECT N + 1问题" .

更新

我通常使用LINQ to Entities，对LINQ to SQL经验不足.可能需要禁用 DeferredLoadingEnabled 也要明确.

I am usually using LINQ to Entities and have not much experience with LINQ to SQL. It might be required to disable DeferredLoadingEnabled explicitly, too.

context.DeferredLoadingEnabled = false;

这篇关于手动选择相关的表数据(SELECT N + 1问题)LINQ to SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！