与 LINQ2SQL 和 POCO 对象的父子关系

发布于 2024-07-14 04:42:01 字数 1873 浏览 8 评论 0原文

我刚刚开始学习 LINQ2SQL,我想尝试的第一件事就是一个简单的父子层次结构,但我似乎找不到一个好的方法来做到这一点。 我在这里看到了一些例子,我已经用谷歌搜索过,但我无法直接应用它们,所以我将准确解释我想要完成的任务。

让我们使用带有标签的常见示例。

数据库表:Post--Post_Tags--标签

我创建了一个简单的Post类,因此我避免传递Linq2Sql类:

public class Post
{
    public int Id {get; set;}
    public int Title {get; set;}
    public IEnumerable<string> Tags {get; set;}
}

我想从Posts表中选择5条最新记录,获取它们的相关标签并返回每个Post所在的IList已填充其 Tags 属性。

你能给我看一个具体的 Linq2Sql 代码吗?我该怎么做?

我尝试过:

      IList<Post> GetLatest()
      {
            return (from p in _db.Posts
                   orderby p.DateCreated descending
                   select new Post
                   {
                       Id = p.Id,
                       Title = p.Title,
                       Tags = p.Post_Tags.Select(pt => pt.Tag.Name)
                   }).Take(5).ToList();
       }

这有效,但会重复每个标签记录的发布记录,并且我必须在我使用的每个方法中重复属性映射(Id = p.Id,...)。 然后我尝试了这种方法,但在这种情况下,每个标签都有一个到数据库的往返:

      IQueryable<Post> GetList()
      {
            return (from p in _db.Posts
                   select new Post
                   {
                       Id = p.Id,
                       Title = p.Title,
                       Tags = p.Post_Tags.Select(pt => pt.Tag.Name)
                   });
       }

      IList<Post> GetLatest()
      {
            return (from p in GetList()
                   orderby p.DateCreated descending
                   select p).Take(5).ToList();
       }

如果我在经典的 ADO.NET 中执行此操作,我将创建一个返回两个结果集的存储过程。 第一个包含发布记录,第二个包含相关标签记录。 然后我会将它们映射到代码中(手动、通过 DataRelation、ORM 等)。 我可以对 LINQ2SQL 做同样的事情吗?

我真的很好奇看到一些关于你们如何处理如此简单的层次结构的代码示例。

是的,我真的很想返回 IList<> 对象和我的自定义类,而不是可查询的 Linq to Sql 对象,因为如果我决定放弃 Linq2Sql,我希望对数据访问代码保持灵活。

谢谢。

I just started learning LINQ2SQL and one of the first things I wanted to try is a simple parent-child hierarchy, but I can't seem to find a good way to do it. I saw some examples here on SO and i've googled, but I couldn't apply them directly, so I'll explain exactly what i'm trying to accomplish.

Lets use the common example with tags.

Database tables: Post-- Post_Tags -- Tags

I've created a simple Post class so I avoid passing Linq2Sql classes around:

public class Post
{
    public int Id {get; set;}
    public int Title {get; set;}
    public IEnumerable<string> Tags {get; set;}
}

I would like to select 5 latest records from the Posts table, get their related tags and return the IList where each Post has their Tags property filled.

Can you show me a concrete Linq2Sql code how could I do that?

I tried:

      IList<Post> GetLatest()
      {
            return (from p in _db.Posts
                   orderby p.DateCreated descending
                   select new Post
                   {
                       Id = p.Id,
                       Title = p.Title,
                       Tags = p.Post_Tags.Select(pt => pt.Tag.Name)
                   }).Take(5).ToList();
       }

This works but duplicates Post records for each Tag record and I have to duplicate property mapping (Id=p.Id, ...) in every method I user. I then tried this approach, but in this case, I have a roundtrip to DB for every tag:

      IQueryable<Post> GetList()
      {
            return (from p in _db.Posts
                   select new Post
                   {
                       Id = p.Id,
                       Title = p.Title,
                       Tags = p.Post_Tags.Select(pt => pt.Tag.Name)
                   });
       }

      IList<Post> GetLatest()
      {
            return (from p in GetList()
                   orderby p.DateCreated descending
                   select p).Take(5).ToList();
       }

If I were doing it in classic ADO.NET, I would create a stored procedure that returns two resultsets. One with Post records and second with related Tag records. I would then map them in the code (by hand, by DataRelation, ORM, etc.). Could I do the same with LINQ2SQL?

I'm really curious to see some code samples on how do you guys handle such simple hierarchies.

And yes, I would really like to return IList<> objects and my custom classes and not queryable Linq to Sql objects, because I would like to be flexible about the data access code if I for example decide to abandon Linq2Sql.

Thanks.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

时光沙漏 2024-07-21 04:42:01

如果您创建 DataContext,则会自动为您维护父子关系。

即,如果您在 Linq2Sql DataContext 中对帖子和标签及其关系进行建模,则可以像这样获取帖子:

var allPosts = from p in _db.Posts
               orderby p.DateCreated descending
               select p;

那么您根本不必担心任何标签,因为它们可以作为变量 p 的成员进行访问然后,

var allPostsList = allPosts.ToList();

var someTags = allPostsList[0].Post_Tags;
var moreTags = allPostsList[1].Post_Tags;

任何重复的实例都会在整个 DataContext 中自动更新,直到您要求它 SubmitChanges();

IMO,这就是 ORM 的要点,您不需要重新创建模型类并维护多个位置的映射,因为您希望 ORM 为您管理所有这些关系。

至于往返,如果您避免使用任何显式请求访问数据库的代码,则所有查询都将存储在中间查询表示中,并且仅当实际需要继续数据时,才会执行查询转换为 sql 并分派到数据库以获取结果。

即以下代码仅访问数据库一次

 // these 3 variables are all in query form until otherwise needed
 var allPosts = Posts.All();
 var somePosts = allPosts.Where(p => p.Name.Contains("hello"));
 var lesserPosts = somePosts.Where(p => p.Name.Contains("World"));

 // calling "ToList" will force the query to be sent to the db
 var result = lesserPosts.ToList();

If you create a DataContext, the parent-child relationship is maintained automatically for you.

i.e. If you model the Posts and Tags and their relationship inside a Linq2Sql DataContext, you can then fetch posts like this:

var allPosts = from p in _db.Posts
               orderby p.DateCreated descending
               select p;

Then you won't have to worry about any tags at all, because they are accessible as a member of the variable p as in:

var allPostsList = allPosts.ToList();

var someTags = allPostsList[0].Post_Tags;
var moreTags = allPostsList[1].Post_Tags;

And then any repeated instance is then automatically updated across entire DataContext until you ask it to SubmitChanges();

IMO, That's the point of an ORM, you don't re-create the model class and maintain the mapping across many places because you want all those relationships managed for you by the ORM.

As for the roundtrip, if you refrain from any code that explicitly requests a trip to the database, all queries will be stored in an intermediate query representation and only when the data is actually needed to continue, is when the query will be translated to sql and dispatched to the database to fetch results.

i.e. the following code only access the database once

 // these 3 variables are all in query form until otherwise needed
 var allPosts = Posts.All();
 var somePosts = allPosts.Where(p => p.Name.Contains("hello"));
 var lesserPosts = somePosts.Where(p => p.Name.Contains("World"));

 // calling "ToList" will force the query to be sent to the db
 var result = lesserPosts.ToList();
白馒头 2024-07-21 04:42:01

如果您首先设置 DataLoadOptions 以显式加载带有帖子的标签,效果如何? 就像是:

IList<Post> GetLatest()
{
     DataLoadOptions options = new DataLoadOptions();
     options.LoadWith<Post>(post => post.Tags);
     _db.LoadOptions = options;

     return (from p in _db.Posts
             orderby p.DateCreated descending)
             Take(5).ToList();
   }

How about if you set your DataLoadOptions first to explicitly load tags with posts? Something like:

IList<Post> GetLatest()
{
     DataLoadOptions options = new DataLoadOptions();
     options.LoadWith<Post>(post => post.Tags);
     _db.LoadOptions = options;

     return (from p in _db.Posts
             orderby p.DateCreated descending)
             Take(5).ToList();
   }
罪歌 2024-07-21 04:42:01
List<Post> latestPosts = db.Posts
  .OrderByDescending( p => p.DateCreated )
  .Take(5)
  .ToList();

  // project the Posts to a List of IDs to send back in
List<int> postIDs = latestPosts
  .Select(p => p.Id)
  .ToList();

// fetch the strings and the ints used to connect 
ILookup<int, string> tagNameLookup = db.Posts
  .Where(p => postIDs.Contains(p.Id))
  .SelectMany(p => p.Post_Tags)
  .Select(pt => new {PostID = pt.PostID, TagName = pt.Tag.Name } )
  .ToLookup(x => x.PostID, x => x.TagName);
//now form results
List<Post> results = latestPosts
  .Select(p => new Post()
  {
    Id = p.Id,
    Title = p.Title,
    Tags = tagNameLookup[p.Id]
  })
  .ToList();
List<Post> latestPosts = db.Posts
  .OrderByDescending( p => p.DateCreated )
  .Take(5)
  .ToList();

  // project the Posts to a List of IDs to send back in
List<int> postIDs = latestPosts
  .Select(p => p.Id)
  .ToList();

// fetch the strings and the ints used to connect 
ILookup<int, string> tagNameLookup = db.Posts
  .Where(p => postIDs.Contains(p.Id))
  .SelectMany(p => p.Post_Tags)
  .Select(pt => new {PostID = pt.PostID, TagName = pt.Tag.Name } )
  .ToLookup(x => x.PostID, x => x.TagName);
//now form results
List<Post> results = latestPosts
  .Select(p => new Post()
  {
    Id = p.Id,
    Title = p.Title,
    Tags = tagNameLookup[p.Id]
  })
  .ToList();
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文