Geek Answers Handbook

Manually select related table data (SELECT N + 1 issue) LINQ to SQL

Example database:

Image - ImageTag - tag

Images can have multiple tags. These relationships are fine tuned, but I run into performance issues.

I have many different requests that select Images according to different criteria. They work fine, however, the data for the tags is not selected with these queries.

This means that if I iterate over a list of 10 images and try to access their tag objects (via ImageTag), then a new query is executed in my database for each image.

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

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

<%} %>

This is obviously not perfect. Is there a way to get LINQ to SQL to query certain data?

Here is an example of one of my queries

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

dataload

{SELECT [t0]. [id], [t0]. [title], [t0]. [legend], [t0]. [dateAdded], [t0]. [], [t0]. [averageRating], [T0]. [NumberOfVotes], [T0]. [ImageOfTheWeek], [T0]. [ ], [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]) ([t2]. [id] = @p0) ( ([t0]. [deleted] = 1)) AND (NOT ([t0]. [deleted] = 1)) AND ([t1]. [ImageId] = [t0]. [id])}

+5
2

DataLoadOptions .

DataLoadOptions dlo = new DataLoadOptions();

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

context.DataLoadOptions = dlo;

- "SELECT N + 1".

UPDATE

LINQ Entities LINQ to SQL. DeferredLoadingEnabled.

context.DeferredLoadingEnabled = false;
+4

- :

return
        from i in qry
        from iTags in i.ImageTags
        where tags.Contains(iTags.Tag)
        select new { TagName = i.Tag.name };

. , . , .

0

More articles:


All Articles