代码之家 › 专栏 › 技术社区 › dev9

亚音速一对多关系子对象

subsonic c#

dev9 · 技术社区 · 16 年前

我有3个表,分别是Articles、ArticleCategories和ArticleComments,其中的文章与其他表之间有一对多的关系。我创建了以下类

  public partial class Article
{
    private string _CategoryName;
    public string CategoryName
    {
        get { return _CategoryName; }
        set
        {
            _CategoryName = value;
        }

    }
    public ArticleCategory Category { get;set;}
    private List<System.Linq.IQueryable<ArticleComment>> _Comments;
    public List<System.Linq.IQueryable<ArticleComment>> Comments
    {
        get
        {
            if (_Comments == null)
                _Comments = new List<IQueryable<ArticleComment>>();
            return _Comments;
        }
        set
        {
            _Comments = value;
        }
    }

}

我用这个片段收集了一系列文章

var list = new IMBDB().Select.From<Article>()
             .InnerJoin<ArticleCategory>(ArticlesTable.CategoryIDColumn, ArticleCategoriesTable.IDColumn)
             .InnerJoin<ArticleComment>(ArticlesTable.ArticleIDColumn,ArticleCommentsTable.ArticleIDColumn)
             .Where(ArticleCategoriesTable.DescriptionColumn).IsEqualTo(category).ExecuteTypedList<Article>();

            list.ForEach(x=>x.CategoryName=category);

            list.ForEach(y => y.Comments.AddRange(list.Select(z => z.ArticleComments)));

我得到了集合,但是当我尝试使用

  foreach (IMB.Data.Article item in Model)

{

%>

  <%
      foreach (IMB.Data.ArticleComment comment in item.Comments)
      {

          %>
         ***<%=comment.Comment %>*** 
      <%}
   } %>

无法将类型为'SubSonic.Linq.Structure.Query'1[IMB.Data.ArticleComment]'的对象强制转换为类型为'IMB.Data.ArticleComment'。

我基本上是尽量避免每次需要评论时都访问数据库。谢谢

2 回复 | 直到 16 年前

Dave Jellison 16 年前

好的-我并不是在做你想做的事情,但希望这个例子能让情况变得更清楚。这更像是我将如何使用亚音速连接,如果我必须这样做的话。我将考虑这种方法的唯一方式是,如果我受限于对象和/或数据库模式的第三方实现……

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using SubSonic.Repository;

namespace SubsonicOneToManyRelationshipChildObjects
{
    public static class Program
    {
        private static readonly SimpleRepository Repository;

        static Program()
        {
            try
            {
                Repository = new SimpleRepository("SubsonicOneToManyRelationshipChildObjects.Properties.Settings.StackOverflow", SimpleRepositoryOptions.RunMigrations);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                Console.ReadLine();
            }
        }

        public class Article
        {

            public int Id { get; set; }
            public string Name { get; set; }

            private ArticleCategory category;
            public ArticleCategory Category
            {
                get { return category ?? (category = Repository.Single<ArticleCategory>(single => single.Id == ArticleCategoryId)); }
            }

            public int ArticleCategoryId { get; set; }

            private List<ArticleComment> comments;
            public List<ArticleComment> Comments
            {
                get { return comments ?? (comments = Repository.Find<ArticleComment>(comment => comment.ArticleId == Id).ToList()); }
            }
        }

        public class ArticleCategory
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }

        public class ArticleComment
        {
            public int Id { get; set; }
            public string Name { get; set; }

            public string Body { get; set; }

            public int ArticleId { get; set; }
        }

        public static void Main(string[] args)
        {
            try
            {
                // generate database schema
                Repository.Single<ArticleCategory>(entity => entity.Name == "Schema Update");
                Repository.Single<ArticleComment>(entity => entity.Name == "Schema Update");
                Repository.Single<Article>(entity => entity.Name == "Schema Update");

                var category1 = new ArticleCategory { Name = "ArticleCategory 1"};
                var category2 = new ArticleCategory { Name = "ArticleCategory 2"};
                var category3 = new ArticleCategory { Name = "ArticleCategory 3"};

                // clear/populate the database
                Repository.DeleteMany((ArticleCategory entity) => true);
                var cat1Id = Convert.ToInt32(Repository.Add(category1));
                var cat2Id = Convert.ToInt32(Repository.Add(category2));
                var cat3Id = Convert.ToInt32(Repository.Add(category3));

                Repository.DeleteMany((Article entity) => true);
                var article1 = new Article { Name = "Article 1", ArticleCategoryId = cat1Id };
                var article2 = new Article { Name = "Article 2", ArticleCategoryId = cat2Id };
                var article3 = new Article { Name = "Article 3", ArticleCategoryId = cat3Id };

                var art1Id = Convert.ToInt32(Repository.Add(article1));
                var art2Id = Convert.ToInt32(Repository.Add(article2));
                var art3Id = Convert.ToInt32(Repository.Add(article3));

                Repository.DeleteMany((ArticleComment entity) => true);
                var comment1 = new ArticleComment { Body = "This is comment 1", Name = "Comment1", ArticleId = art1Id };
                var comment2 = new ArticleComment { Body = "This is comment 2", Name = "Comment2", ArticleId = art1Id };
                var comment3 = new ArticleComment { Body = "This is comment 3", Name = "Comment3", ArticleId = art1Id };
                var comment4 = new ArticleComment { Body = "This is comment 4", Name = "Comment4", ArticleId = art2Id };
                var comment5 = new ArticleComment { Body = "This is comment 5", Name = "Comment5", ArticleId = art2Id };
                var comment6 = new ArticleComment { Body = "This is comment 6", Name = "Comment6", ArticleId = art2Id };
                var comment7 = new ArticleComment { Body = "This is comment 7", Name = "Comment7", ArticleId = art3Id };
                var comment8 = new ArticleComment { Body = "This is comment 8", Name = "Comment8", ArticleId = art3Id };
                var comment9 = new ArticleComment { Body = "This is comment 9", Name = "Comment9", ArticleId = art3Id };
                Repository.Add(comment1);
                Repository.Add(comment2);
                Repository.Add(comment3);
                Repository.Add(comment4);
                Repository.Add(comment5);
                Repository.Add(comment6);
                Repository.Add(comment7);
                Repository.Add(comment8);
                Repository.Add(comment9);

                // verify the database generation
                Debug.Assert(Repository.All<Article>().Count() == 3);
                Debug.Assert(Repository.All<ArticleCategory>().Count() == 3);
                Debug.Assert(Repository.All<ArticleComment>().Count() == 9);

                // fetch a master list of articles from the database
                var articles = 
                    (from article in Repository.All<Article>() 
                    join category in Repository.All<ArticleCategory>() 
                        on article.ArticleCategoryId equals category.Id 
                    join comment in Repository.All<ArticleComment>() 
                        on article.Id equals comment.ArticleId 
                    select article)
                    .Distinct()
                    .ToList();

                foreach (var article in articles)
                {
                    Console.WriteLine(article.Name  + " ID " + article.Id);
                    Console.WriteLine("\t" + article.Category.Name + " ID " + article.Category.Id);

                    foreach (var articleComment in article.Comments)
                    {
                        Console.WriteLine("\t\t" + articleComment.Name + " ID " + articleComment.Id);
                        Console.WriteLine("\t\t\t" + articleComment.Body);
                    }
                }

                // OUTPUT (ID will vary as autoincrement SQL index

                //Article 1 ID 28
                //        ArticleCategory 1 ID 41
                //                Comment1 ID 100
                //                        This is comment 1
                //                Comment2 ID 101
                //                        This is comment 2
                //                Comment3 ID 102
                //                        This is comment 3
                //Article 2 ID 29
                //        ArticleCategory 2 ID 42
                //                Comment4 ID 103
                //                        This is comment 4
                //                Comment5 ID 104
                //                        This is comment 5
                //                Comment6 ID 105
                //                        This is comment 6
                //Article 3 ID 30
                //        ArticleCategory 3 ID 43
                //                Comment7 ID 106
                //                        This is comment 7
                //                Comment8 ID 107
                //                        This is comment 8
                //                Comment9 ID 108
                //                        This is comment 9         

                Console.ReadLine();

                // BETTER WAY (imho)...(joins aren't needed thus freeing up SQL overhead)

                // fetch a master list of articles from the database
                articles = Repository.All<Article>().ToList();

                foreach (var article in articles)
                {
                    Console.WriteLine(article.Name + " ID " + article.Id);
                    Console.WriteLine("\t" + article.Category.Name + " ID " + article.Category.Id);

                    foreach (var articleComment in article.Comments)
                    {
                        Console.WriteLine("\t\t" + articleComment.Name + " ID " + articleComment.Id);
                        Console.WriteLine("\t\t\t" + articleComment.Body);
                    }
                }

                Console.ReadLine();

                // OUTPUT should be identicle
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                Console.ReadLine();
            }
        }
    }
}

以下是我个人的观点和猜测,你认为合适的话可以抛掷/使用。。。

如果你看一下“更好的方式”的例子,这更接近于我实际使用亚音速的方式。

每个表都是一个对象(类)
每个对象实例都有一个Id(例如Article.Id)
每个对象都应有一个名称(或类似名称)

现在,如果您以一种在使用亚音速时有意义的方式编写数据实体(表示表的类),那么您将作为一个团队很好地协作。当我使用亚音速时,我实际上不做任何连接,因为我通常不需要这样做,也不需要额外的开销。您开始展示在您的文章对象上“延迟加载”注释列表属性的良好实践。这很好,这意味着如果我们需要消费者代码中的注释,就去找他们。如果我们不需要注释,就不要花时间&钱去从数据库里取。我重组了您的ArticleCategory-to-Article关系,这种方式对我来说很有意义,但可能不适合您的需要。看起来你在概念上同意Rob(我也同意他的观点)。

注意:如果您看一下Linq2SQL的工作方式,这种方法在最基本的层上非常相似。Linq2SQL通常在每次加载依赖关系时都会加载,无论您是否希望这样做。我更喜欢亚音速的“明显性”,如果你愿意的话,真实发生的事情。

user1151 user1151 16 年前

首先,类别关联不应该是另一种方式吗?类别有很多文章吗?我不知道这是否有帮助,这是我的第一次观察。

能否打开Article类并查看“Comments”属性的返回,以确保它返回的是类型,而不是查询?