ServiceStack/ORM Lite - 外键关系 [英] ServiceStack / ORM Lite - Foreign Key Relationships
问题描述
我有以下 POCO:
[Alias("Posts")]
public class Post : IReturn<Post>
{
[AutoIncrement]
[PrimaryKey]
public int PostId { get; set; }
public DateTime CreatedDate { get; set; }
[StringLength(50)]
public string CreatedBy { get; set; }
[StringLength(75)]
public string Title { get; set; }
public string Body { get; set; }
public int UpVote { get; set; }
public int DownVote { get; set; }
public bool IsPublished { get; set; }
public List<Comment> Comments { get; set; }
public List<Tag> Tags { get; set; }
}
它在我的 Comment 和 Tag 实体上有一个 FK.所以我想在我的服务的响应中返回那些，但它说 'Invalid Column name 'Comments'' 和 'Invalid Column name 'Tags'' .如何使用 ORM Lite 查看我的帖子附加了哪些评论和标签?在 EF 中，我会简单地使用 Include 来延迟加载我的相关表信息，什么是等效的?
It has a FK on my Comment and Tag entities. So I'd like to return those in my response from my service, but it says 'Invalid Column name 'Comments'' and 'Invalid Column name 'Tags'' . How do I see which Comments and Tags are attached to my Post, with ORM Lite? In EF I would simply use Include to lazy load my related table information, whats the equivalent?
为了回应这些答案，我已经这样做了:
In response to the answers, I've done this:
public class PostFull
{
public Post Post { get; set; }
public List<Comment> Comments { get; set; }
public List<Tag> Tags { get; set; }
}
然后在我的服务中，我返回这个，我的实体 PostTag 是一个交集实体，因为我的 Post 和 Tag 实体是一个 M:M关系:
Then in my service, I return this, my entity PostTag is an intersection entity as my Post and Tag entities are a M:M relationship:
var posts = Db.Select<Post>().ToList();
var fullPosts = new List<PostFull>();
posts.ForEach(delegate(Post post)
{
var postTags = Db.Select<PostTag>(x => x.Where(y => y.PostId ==
post.PostId)).ToList();
fullPosts.Add(new PostFull()
{
Post = post,
Tags = Db.Select<Tag>(x => x.Where(y => postTags.Select(z =>
z.TagId).Contains(y.TagId))).ToList(),
Comments = Db.Select<Comment>(x => x.Where(y => y.PostId ==
post.PostId)).ToList()
});
});
return fullPosts;
不确定它是否是一个好的设计模式?
Not sure whether its a good design pattern or not?
这是我的实体:
[Alias("Tags")]
public class Tag
{
[AutoIncrement]
[PrimaryKey]
public int TagId { get; set; }
[StringLength(50)]
public string Name { get; set; }
}
[Alias("Posts")]
public class Post
{
[AutoIncrement]
[PrimaryKey]
public int PostId { get; set; }
public DateTime CreatedDate { get; set; }
[StringLength(50)]
public string CreatedBy { get; set; }
[StringLength(75)]
public string Title { get; set; }
public string Body { get; set; }
}
[Alias("PostTags")]
public class PostTag
{
[AutoIncrement]
[PrimaryKey]
public int PostTagId { get; set; }
[References(typeof(Post))]
public int PostId { get; set; }
[References(typeof(Tag))]
public int TagId { get; set; }
}
推荐答案
OrmLite 中的表是与底层数据库表严格的 1:1 映射.
Tables in OrmLite are strictly a 1:1 mapping with the underlying db tables.
这意味着所有复杂类型的属性都blobbed到带有属性名称的数据库文本字段中，它们从未像您在这里期望的那样用于自动映射到子关系.
This means all complex type properties are blobbed into a db text field with the property name, they're never used to auto-map to child relations as you're expecting to do here.
这是一个早期答案，展示了如何使用 OrmLite 映射多对多关系.
Here's an early answer that shows how you could map many to many relations with OrmLite.
尽量避免 N+1 查询，记住每次对 Db.x 的调用都是远程数据库查询，所以理想情况下你应该尽量避免任何数据库调用环形.
Try to avoid N+1 queries, remember that every call to Db.x is a remote DB query so you should ideally try to avoid any Database calls in a loop.
您可以使用 OrmLite 对 JOIN 的支持像在普通 SQL 中一样构造一个 Typed 查询，以通过多对多表进行查询并找到具有指定标签的所有帖子:
You can use OrmLite's support for JOINs to construct a Typed query as you would in normal SQL to query by the Many to Many table and find all posts with the specified Tag:
db.CreateTable<Post>();
db.CreateTable<Tag>();
db.CreateTable<PostTag>();
var post1Id = db.Insert(new Post {
CreatedBy = "gistlyn", Title = "Post 1", Body = "Body 1" }, selectIdentity:true);
var post2Id = db.Insert(new Post {
CreatedBy = "gistlyn", Title = "Post 2", Body = "Body 2" }, selectIdentity:true);
db.Insert(new Tag { Id = 1, Name = "A" },
new Tag { Id = 2, Name = "B" });
db.Insert(new PostTag { PostId = post1Id, TagId = 1 },
new PostTag { PostId = post1Id, TagId = 2 });
db.Insert(new PostTag { PostId = post2Id, TagId = 1 });
创建一个连接所有相关表的 SQL 表达式:
当遵循上面 OrmLite 的正常命名约定时，OrmLite 可以推断每个表之间的关系使您无需为每个查询指定 JOIN 表达式，例如:
Create a SQL Expression Joining all related tables:
When following OrmLite's normal naming conventions above, OrmLite can infer the relationship between each table saving you from specifying the JOIN expression for each query, e.g:
var postsWithTagB = db.Select(db.From<Post>()
.Join<PostTag>()
.Join<PostTag,Tag>()
.Where<Tag>(x => x.Name == "B"));
postsWithTagB.PrintDump();
此查询仅返回标签 B 的第一个帖子和标签 A 的两个帖子.
Where this Query returns just the first Post for Tag B and both Posts for Tag A.
您可以通过在 Gistlyn 上实时运行来进一步在线探索这个独立示例.
You can further explore this stand-alone example online by running it Live on Gistlyn.
如果这是一个小博客，并且您想加载所有带有相关标签和评论的帖子，例如在主页或 RSS 提要中，您可以使用 Linq2Objects 将整个数据集加载到内存中，并使用 4 个查询将它们连接起来，例如:
If this is a small blog and you want to load all the posts with their related tags and comments e.g. in a home page or RSS feed you can load the entire dataset in memory with 4 queries using Linq2Objects to join them with something like:
//Only 4 DB calls to read all table data
var posts = Db.Select<Post>();
var postTags = Db.Select<PostTag>();
var tags = Db.Select<Tag>();
var comments = Db.Select<Comment>();
//using Linq2Objects to stitch the data together
var fullPosts = posts.ConvertAll(post =>
{
var postTagIds = postTags
.Where(x => x.PostId == post.PostId)
.Select(x => x.PostTagId).ToList();
return new PostFull {
Post = post,
Tags = tags.Where(x => postTagIds.Contains(x.TagId)).ToList(),
Comments = comments.Where(x => x.PostId == post.PostId).ToList(),
};
});
这篇关于ServiceStack/ORM Lite - 外键关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
