时间戳

首页 > 解决方案 > 使用存储过程但不返回表数据在 EF Core 中获取输出参数值

stored-procedures - 使用存储过程但不返回表数据在 EF Core 中获取输出参数值

问题描述

我有一个应该通过 id 返回登录的存储过程：

CREATE PROCEDURE MyProcedure
(
    @id INT,
    @login NVARCHAR(50) OUTPUT
)
AS
BEGIN
    SELECT
            ID
        ,
            Login
FROM
    Users
WHERE
    ID = @id
END
GO

在端点中使用此代码可以正常工作：

            var id = new SqlParameter
            {
                ParameterName = "id",
                SqlDbType = SqlDbType.Int,
                Value = 15,
            };
            var login = new SqlParameter
            {
                ParameterName = "login",
                SqlDbType = SqlDbType.NVarChar,
                Direction = ParameterDirection.Output,
                Size = 50,
            };
            var query = $"EXEC MyProcedure @{id}, @{login} OUTPUT";
            var result = _context.Set<User>()
                .FromSqlRaw(query, id, login).ToList();
            foreach (var item in result) Console.WriteLine(item.Login);

和类：

    public class User
    {
        public string Login { get; set; }
    }

和属性：

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>().HasNoKey();
        }

我得到一个包含一个搜索元素的列表，它的Login属性就是我想要的。

但是如果我更改SELECT为（原始程序如下所示，我无法更改）：

    SELECT
        @id = 
            ID
        ,
        @login = 
            Login

我收到一个错误The required column 'Login' was not present in the results of a 'FromSql' operation.

那么获得a的正确方法是Login什么？

标签： stored-proceduresentity-framework-core

解决方案

解决此问题的一种简单方法是使用context.Database.ExecuteSqlInterpolated()orcontext.Database.ExecuteSqlRaw()代替context.Set<T>().FromSqlRaw()：

这是一个完全工作的示例控制台程序，它演示了该方法：

using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class User
    {
        public int UserId { get; set; }
        public string Login { get; set; }
    }
    
    public class Context : DbContext
    {
        public DbSet<User> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer(
                    @"Data Source=.\MSSQL14;Integrated Security=SSPI;Initial Catalog=So63991939")
                .UseLoggerFactory(LoggerFactory.Create(b => b
                    .AddConsole()
                    .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .HasData(
                    new User {UserId = 1, Login = "John"},
                    new User {UserId = 2, Login = "Jane"});
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            var connection = context.Database.GetDbConnection();
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = @"CREATE PROCEDURE uspGetUsers (
    @userCount INT OUTPUT
) AS
BEGIN
    SELECT 
        UserId,
        Login
    FROM
        Users;

    SELECT @userCount = @@ROWCOUNT;
END;";
                command.ExecuteNonQuery();
            }

            var userCountParameter = new SqlParameter("@myUserCountParam", SqlDbType.Int) {Direction = ParameterDirection.Output};
            context.Database.ExecuteSqlInterpolated($"EXEC uspGetUsers @userCount={userCountParameter} OUTPUT");
            
            Debug.Assert((int)userCountParameter.Value == 2);
        }
    }
}

您可以使用此.NET Fiddle运行示例代码。

推荐阅读