具有多个结果的Linq到SQL存储过程

We have followed the approach below to get the data from multiple results using LINQ To SQL

我们遵循以下方法使用LINQ To SQL从多个结果中获取数据

CREATE PROCEDURE dbo.GetPostByID
(
    @PostID int
)
AS
    SELECT    *
    FROM      Posts AS p
    WHERE     p.PostID = @PostID

    SELECT    c.*
    FROM      Categories AS c
    JOIN      PostCategories AS pc
    ON        (pc.CategoryID = c.CategoryID)
    WHERE     pc.PostID = @PostID

The calling method in the class the inherits from DataContext should look like:

从DataContext继承的类中的调用方法应如下所示:

[Database(Name = "Blog")]
public class BlogContext : DataContext
{
    ... 

    [Function(Name = "dbo.GetPostByID")]
    [ResultType(typeof(Post))]
    [ResultType(typeof(Category))]
    public IMultipleResults GetPostByID(int postID)
    {
        IExecuteResult result = 
            this.ExecuteMethodCall(this, 
                  ((MethodInfo)(MethodInfo.GetCurrentMethod())), 
                  postID);

        return (IMultipleResults)(result.ReturnValue);
    }
}

Notice that the method is decorated not only with the Function attribute that maps to the stored procedure name, but also with the ReturnType attributes with the types of the result sets that the stored procedure returns. Additionally, the method returns an untyped interface of IMultipleResults:

请注意,该方法不仅使用映射到存储过程名称的Function属性进行修饰,还使用带有存储过程返回的结果集类型的ReturnType属性进行修饰。此外,该方法返回IMultipleResults的无类型接口:

public interface IMultipleResults : IFunctionResult, IDisposable
{
    IEnumerable<TElement> GetResult<TElement>();
}

so the program can use this interface in order to retrieve the results:

所以程序可以使用这个接口来检索结果:

BlogContext ctx = new BlogContext(...);

IMultipleResults results = ctx.GetPostByID(...);

IEnumerable<Post> posts = results.GetResult<Post>();

IEnumerable<Category> categories = results.GetResult<Category>();

In the above stored procedures we had two select queries 1. Select query without join 2. Select query with Join

在上面的存储过程中,我们有两个选择查询1.选择不带连接的查询2.选择带有Join的查询

But in the above second select query the data which is displayed is from one of the table i.e. from Categories table. But we have used join and want to display the data table with the results from both the tables i.e. from Categories as well as PostCategories.

但是在上面的第二个选择查询中,显示的数据来自表中的一个,即来自Categories表。但是我们使用了join并希望显示数据表和两个表的结果,即来自Categories和PostCategories。

1. Please if anybody can let me know how to achieve this using LINQ to SQL

如果有人可以让我知道如何使用LINQ to SQL实现这一目标

2. What is the performance trade-off if we use the above approach vis-à-vis implement the above approach with simple SQL

如果我们使用上述方法与使用简单SQL实现上述方法相比,性能权衡是什么

3 个解决方案

#1

Scott Guthrie (the guy who runs the .Net dev teams at MS) covered how to do this on his blog some months ago much better than I ever could, link here. On that page there is a section titled "Handling Multiple Result Shapes from SPROCs". That explains how to handle multiple results from stored procs of different shapes (or the same shape).

斯科特格思里(曾经在MS开设.Net开发团队的人)在几个月前在他的博客上介绍了如何做到这一点,这比以往任何时候都要好得多,链接在这里。在该页面上有一个标题为“处理来自SPROC的多个结果形状”的部分。这解释了如何处理来自不同形状(或相同形状)的存储过程的多个结果。

I highly recommend subscribing to his RSS feed. He is pretty much THE authoritative source on all things .Net.

我强烈建议订阅他的RSS提要。他几乎是所有事物.Net的权威来源。

#2

Heya dude - does this work?

Heya dude - 这有用吗?

IEnumerable<Post> posts;
IEnumerable<Category> categories;

using (BlogContext ctx = new BlogContext(...))
{
    ctx.DeferredLoadingEnabled = false; // THIS IS IMPORTANT.
    IMultipleResults results = ctx.GetPostByID(...);
    posts = results.GetResult<Post>().ToList();
    categories = results.GetResult<Category>().ToList();
}
// Now we need to associate each category to the post.
// ASSUMPTION: Each post has only one category (1-1 mapping).
if (posts != null)
{
    foreach(var post in posts)
    {
        int postId = post.PostId;
        post.Category = categories
            .Where(p => p.PostId == postId)
            .SingleOrDefault();
    }
}

Ok. lets break this down.

好。让我们打破这个。

First up, a nice connection inside a using block (so it's disposed of nicely).

首先,在一个使用块内部建立一个很好的连接(因此处理得很好)。

Next, we make sure DEFERRED LOADING is off. Otherwise, when u try and do the set (eg. post.Category == blah) it will see that it's null, lazy-load the data (eg. do a rountrip the database) set the data and THEN override the what was just dragged down from the db, with the result of there Where(..) method. phew! Summary: make sure deferred loading is off for the scope of the query.

接下来,我们确保DEFERRED LOADING已关闭。否则,当你尝试做集合(例如post.Category == blah)时,它会看到它是空的,懒惰加载数据(例如,做数据库的rountrip)设置数据然后重写那些只是从db中拖出来,结果是Where(..)方法。唷!摘要:确保查询范围的延迟加载已关闭。

Last, for each post, iterate and set the category from the second list.

最后,对于每个帖子,迭代并设置第二个列表中的类别。

does that help?

这有帮助吗?

EDIT

Fixed it so that it doesn't throw an enumeration error by calling the ToList() methods.

修复它,以便它不会通过调用ToList()方法抛出枚举错误。

#3

Just curious, if a Post have have one or many Categories, is it possible to instead of using the for loop, to load the Post.PostCategories with the list of Categories (one to many), all in one shot, using a JOIN?

只是好奇,如果一个帖子有一个或多个类别,是否有可能不使用for循环,使用JOIN一次性加载带有类别列表(一对多)的Post.PostCategories?

var rslt = from p in results.GetResult<Post>()
           join c in results.GetResult<Category>() on p.PostId = c.PostID
           ...
           p.Categories.Add(c)