LINQ options.loadwith问题 [英] LINQ options.loadwith problem

问题描述

我写一个基于标签的ASP.net系统。使用下面的DB计划：

主题<多对多> TagTopicMap<多对多>标签

基本上它是一个3NF办法（TOXI），我从以下发现：的 http://www.pui.ch/phred/archives/2005/04/tags-database-schemas。 HTML

下面是代码片段我有：

  DataLoadOptions选项=新DataLoadOptions（）; 
 options.LoadWith<&主题GT（T => t.TagTopicMaps）; 
 options.LoadWith< TagTopicMap>（TT = GT; tt.Tag）; 
变种DB =新lcDbDataContext（）; 
 db.LoadOptions =选择; 
 db.Log = W; 
 
变种X =从db.Topics 
排序依据的话题topic.dateAdded降
选择的话题; 
 
计算机[题目列表] = x.Take（10）; 
  

当我执行此，结果是好的，但它有11个单独的SQL查询一上来获取的十大主题列表：

  SELECT TOP（10）[T0] [ID]，[T0]。 [标题]，[T0] [dateAdded] 
从[DBO] [专题] AS [T0] ORDER BY [T0] [dateAdded] DESC 
  - 语境。sqlProvider的（SQL2005）型号：AttributedMetaModel构建：3.5.30729.1 
  

和其他用于单独获取标签的细节10

我试图打开和关闭两个loadwith语句，发现下面的事情发生了：

  loadwith<主题> ：用于开启或关闭没有区别。 
 loadwith< tagtopicmap计算值：11查询时，当多了。 
  

在短，只有第二loadwith选项按预期工作。第一个没有任何效果！

我也试图使该结果了ToList（）。但更重要的问题来了：对于标签细节部分，只检索这些独特的项目，所有这些重复的标记（！相同的标签可能会出现一些话题，当然）通过查询被丢弃

最后一件事，下面是我的aspx用于检索的数据，使结果了ToList（），我改（IQueryable的）到（IList的）的情况下，代码：

 <％的foreach（VAR吨（IQueryable的）计算机[题目列表]）
 {
变种主题=（主题）吨; 
 
％GT; 
<立GT; 
<％= topic.title％GT; || 
<％的foreach（在（topic.TagTopicMaps变种TT））
 {％GT; 
<％= tt.Tag.Name％>中
<％}％GT; 
< /李> 
<％
} 
％GT; 
  

解决方案

简短的回答是：LinqToSql有几个怪癖喜欢这个，有时你必须使用变通...

该LINQ2SQL LoadWith选项只是导致数据库表之间的内部连接，因此您可以通过rewritting强制相似的行为您的LINQ语句来像（请原谅任何错别字，我已经习惯了在VB中的语法书面方式LINQ的...）：

  VAR X =从db.Topics 
主题topic.TagTopicMaps 
排序依据加入topicMap topic.dateAdded降由topicMap.topic 
组topicMap到标签=组; 
  

这语法可能是可怕的错误，但基本的想法是，你强迫LINQ2SQL评估之间的连接主题和TagTopicMaps，然后用分组（或集团加入，让等）保存在结果集中的对象层次结构。

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>
    <%
        }
    %>

解决方案

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.

这篇关于LINQ options.loadwith问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！