具有多个结果的 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. 使用 Join 选择查询

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

但是在上面的第二个选择查询中，显示的数据来自表之一，即来自类别表.但是我们使用了 join 并希望显示数据表，其中包含来自两个表的结果，即来自 Categories 和 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 实现上述方法相比，性能权衡是什么

推荐答案

Scott Guthrie(在 MS 运行 .Net 开发团队的人)几个月前在他的博客上介绍了如何做到这一点，比我以往任何时候都好，此处链接.在该页面上有一个标题为处理来自 SPROC 的多个结果形状"的部分.这解释了如何处理来自不同形状(或相同形状)的存储过程的多个结果.

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 提要.他几乎是所有 .Net 内容的THE权威来源.

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

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