Linq to SQL存储过程具有多个结果 [英] Linq to SQL Stored Procedures with Multiple Results

问题描述

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

We have followed the approach below to get the data from multiple results using 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

从DataContext继承的类中的调用方法应类似于:

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

[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);
    }
}

请注意，该方法不仅用映射到存储过程名称的Function属性装饰，而且还用带有存储过程返回的结果集类型的ReturnType属性装饰.另外，该方法返回IMultipleResults的无类型接口:

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:

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>();

在上面的存储过程中，我们有两个选择查询 1.选择不加入查询 2.选择加入"查询

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

但是在上述第二个select查询中，显示的数据来自表之一，即来自Categories表.但是我们使用了join并希望显示数据表以及两个表(即Category和PostCategories)的结果.

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.

1. 如果有人可以让我知道如何使用LINQ to SQL实现这一目标
2. 如果相对于通过简单的SQL实现上述方法，使用上述方法，性能的取舍是什么?

推荐答案

斯科特·格思里 (在MS中运行.Net开发团队的人)几个月前在他的博客上介绍了如何做到这一点，这比我以前做的要好得多，

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).

我强烈建议您订阅他的RSS feed.他几乎是.net上所有东西的权威来源.

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

这篇关于Linq to SQL存储过程具有多个结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！