linq-to-sql - LINQ To SQL 可以与使用 sp_executeSQL 的存储过程一起使用吗？如果没有，你如何处理？

Question

LINQToSQL 不喜欢我的存储过程。它说 sproc 的返回类型为“无”，这可能是因为我正在使用 sp_ExecuteSQL 语句来生成结果。

SQL Server 存储过程代码

我有一个类似于下面的存储过程

CREATE PROCEDURE Foo<br/>
@BarName varchar(50)<br/>
AS<br/>
BEGIN<br/>
DECLARE @SQL NVARCHAR(1024)<br/>
SET @SQL = 'SELECT tbFoo.FooID, tbFoo.Name FROM tbFOO ';<br/>
IF @BarName IS NOT NULL<br/>
BEGIN;<br/>
SET @SQL = @SQL<br/>
+ ' JOIN tbBar '<br/>
+ ' ON tbFoo.FooID = tbBar.FooID '<br/>
+ ' AND tbBar.BarName = ''' + @BarName + ''''<br/>
END;<br/>
EXEC sp_executeSQL @SQL<br/>
END<br/>


返回

这个存储过程返回一组 FooID | FooName 元组。

• 12345 |探戈
• 98765 |现金

目标

此存储过程将用于在搜索页面上返回搜索结果。这是一种相当常见的搜索模式。我想找到满足条件的 Foos，但是该条件正在应用于单独的表。我本可以选择不使用 sp_executeSQL 直接编写此查询，但是这种方法所做的是创建仅包含实际查询的表的 SQL。在现实世界的场景中，我可以有 12 个连接，而不是 1 个，这种方法允许我只将实际用作条件的连接串在一起。

问题

LINQ to SQL 不喜欢它。它说这个查询返回类型“无”并且不允许我指定返回类型。我不确定其他 ORM，例如 NHibernate、Entity Framework 或 LLBLGen 是否能够处理这个问题。到目前为止，LINQToSQL 在该项目上运行良好，我已完成该项目的 95%，并且不想对单个方法使用不同的 ORM。如果我进行进一步的更改，它可能需要重构，但就目前而言，我还没有准备好为此切换到不同的 ORM。

我真的很想找到一种方法在 LinqToSql 中完成这项工作!我不确定它是否可以。我还没有找到关于这个明显限制的任何官方文件。

目前我正在考虑的替代方案

到目前为止，我已经提出了一些替代方案。我不喜欢他们中的任何一个，所以我真的希望有人有好的“技巧”来解决这个问题。这些是我到目前为止所得到的:

• 重写存储过程。摆脱 sp_executeSQL。对所有表执行 LEFT JOIN。
• 使用 ADO.Net 并手动滚动该方法。
• 不要使用存储过程，而是尝试在 LINQ 中进行所有过滤。

Answer 1

您可以使用 Linq2SQL 调用存储过程，但实体建模器可能无法为您生成调用包装器，因为它无法确定存储过程返回的内容，因此您必须自己创建调用包装器.

为此，创建一个实体模型“非设计者”模块，其部分类定义与您的实体模型数据上下文(以及必要时的实体)相匹配，并像这样定义调用包装器。

namespace bar.Context
{
    public partial class EntityModelDataContext
    {
        /// <summary>
        /// LINQ to SQL class mapper for Foo StoredProcedure
        /// </summary>
        /// <remarks>
        /// This one is too tough for the LINQ to SQL modeler tool to auto-generate
        /// </remarks>
        /// <returns></returns>
        [Function(Name = "dbo.Foo")]
        [ResultType(typeof(bar.Entity.tbFoo))]
        public ISingleresult<bar.Entity.tbFoo> Foo([Parameter(Name = "BarName", DbType = "varchar")] string barname)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), barname);
            return ((ISingleResult<bar.Entity.tbFoo>)(result.ReturnValue));
        }
    }
}

namespace bar.Entity
{
    /// <summary>
    /// Data Modeler couldn't figure out how to generate this from the sproc
    /// hopefully your entity model generated this and you don't need to replicate it
    /// </summary>
    [Table(Name = "dbo.tbFoo")]
    public partial class tbFoo        {
       ....
    }
}