LINQ options.loadwith problem

问题:

I am writing a tag-based ASP.net system. Using the following db scheme:

Topic <many-many> TagTopicMap <many-many> Tag

Basically it is a 3NF approach (toxi) that I found from the following: http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html

Here is the code snippet I have:

DataLoadOptions options = new DataLoadOptions();
        options.LoadWith<Topic>(t => t.TagTopicMaps);
        options.LoadWith<TagTopicMap>(tt => tt.Tag);
        var db = new lcDbDataContext();
        db.LoadOptions = options;
        db.Log = w;

        var x = from topic in db.Topics
                orderby topic.dateAdded descending
                select topic;

        ViewData["TopicList"] = x.Take(10);

When I execute this, the result is fine, but it comes up with 11 single SQL queries, one for getting the list of top 10 topics:

    SELECT TOP (10) [t0].[Id], [t0].[title], [t0].[dateAdded]
FROM [dbo].[Topics] AS [t0] ORDER BY [t0].[dateAdded] DESC
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.30729.1 

And 10 of those other for getting details of the tags individually.

I tried to switch the two loadwith statements on and off, and found the following things happen:

loadwith<topic> : no difference for on or off.
loadwith<tagtopicmap>: 11 Queries when on, much more when off.

In short, ONLY the second loadwith option is working as expected. The first one doesn't have any effect!

I also tried to make the resultset ToList(). But even more problem coming out: for the tags detail part, it only retrieve those UNIQUE items, all those repeating tags (that same tag might appear in a number of topic, of course!) are dropped by the query.

One last thing, following is the code I used in aspx to retrieve the data, in case of making the result tolist(), I change (IQueryable) to (IList):

<% foreach (var t in (IQueryable)ViewData["TopicList"])
       {
           var topic = (Topic)t;

    %>
    <li>
        <%=topic.title %> || 
        <% foreach (var tt in (topic.TagTopicMaps))
           { %>
                <%=tt.Tag.Name%>, 
                <%} %>
    </li>
    <%
        }
    %>

回答1:

The short answer is: LinqToSql has several quirks like this, and sometimes you have to use work-arounds...

The Linq2Sql LoadWith option simply causes an inner join between the database tables, so you can force similar behavior by rewritting your Linq statement to something like (please forgive any typos, I'm used to writting Linq in VB syntax...):

var x = from topic in db.Topics
        join topicMap in topic.TagTopicMaps
        orderby topic.dateAdded descending
        group topicMap by topicMap.topic into tags = Group;

This syntax may be horribly wrong, but the basic idea is that you force Linq2Sql to evaluate the join between Topics and TagTopicMaps, and then use grouping (or "group join", "let", etc.) to preserve the object heirarchy in the result set.

回答2:

Set the EnabledDefferedLoad on your datacontext class to false.

回答3:

The problem in your case is Take(10). Here's from the horse's mouth:

https://connect.microsoft.com/VisualStudio/feedback/details/473333/linq-to-sql-loadoptions-ignored-when-using-take-in-the-query

The suggested workaround is to add Skip(0). That did not work for me, but Skip(1) did work. Useless as it may be, at least I know where my problem is.