views:

379

answers:

2

Database example:

Image - ImageTag - Tag

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.

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> 

<%} %>

This is obviously not ideal. Is there a way to force LINQ to SQL to query for 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

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].[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]) }

A: 

why not try something like:

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

That will return a Collection with only the Tag Names. I hope I'm understanding your question properly here. Hope it helps

FailBoy
No, not quite sorry. I still need to return a collection of Images, but I need it to have thier Tags already loaded, so the server doesnt have to query again for them
qui
+4  A: 

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;

Just to mention - this is called the "SELECT N + 1 problem".

UPDATE

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;
Daniel Brückner
I would have thought this would be it, it compiles fine, but still the unneeded queries are getting fired
qui
Edited question to show what query is..
qui
but +1 as this is surely the right track..
qui