在 EF Core 中执行存储过程,从 JOIN 返回 int。 blazor(客户端)
•浏览 1
Execute stored procedure in EF Core returning int from JOIN. blazor(client-side)
如何在 EF Core 中获取从 SQL Server 中的存储过程返回的 int 值?
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);
在 SQL Server 测试中返回正确的 Employee.Id
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);
现在在 Blazor(客户端)控制器中,我正在尝试执行存储过程。
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);
我得到了这个例外:
FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it.
Consider calling AsEnumerable after the FromSqlRaw or FromSqlInterpolated method to perform the composition on the client side.
如果尝试
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);
它总是返回-1,因为没有行受到影响。
我看到了很多东西,但没有成功...在此先感谢
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);
- https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/break-changes#linq-queries-are-no-longer-evaluated-on-the-客户
- https://github.com/dotnet/efcore/issues/17558
- 在 EF Core 3.1 中包含 FromSqlRaw 和存储过程
- 如何通过 FromSqlRaw 在 EF Core 3.0 中调用存储过程
- EF core 3.1 无法运行复杂的原始 sql 查询
- https://www.sqlservertutorial.net/sql-server-stored-procedures/stored-procedure-output-parameters/
您有不可组合的 SQL。关键是实体框架会写类似
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);
它这样做是为了确定能够实现(嵌套)结果、分页等的格式。
如果你没有从你的存储过程中选择任何东西,它就不能组成这个查询。它无论如何都不会返回结果集,因此您也无法从 dbContext.Employee 中进行选择。那将是两个不同的查询。一个获取您已经拥有的 ID:
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);
这确实返回 -1,因为您的查询中有错误。您提供了一个名为 @count 的参数,但提供了一个名为 @idemp.
的已配置参数
所以请确保你正确地传递参数:
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);
然后使用ID参数查询:
--Two tables, Employee and AspNetUsers--
CREATE PROCEDURE SP_GetIdEmployeeByUserName
(@username varchar(100), @idemployee int output)
AS
BEGIN
SELECT @idemployee = (SELECT e.Id
FROM Employee e
JOIN AspNetUsers a ON e.IdUser = a.Id
WHERE a.Email = @username)
END
--Executing in database--
DECLARE @idemp INT;
EXEC SP_GetIdEmployeeByUserName 'someinsertedemail@gmail.com', @idemp output
SELECT @idemp;
idemp `Id = 40`
[HttpGet]
[Route("getbyname")]
public int GetByName([FromQuery] string username)
{
try
{
if (!string.IsNullOrEmpty(username))
{
SqlParameter paramUsername = new SqlParameter("@username", username);
var paramOut = new SqlParameter
{
ParameterName ="@idemp",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output,
};
var result = _context
.Employee.FromSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut)
.FirstOrDefault();
Console.WriteLine("result = >>>>>>>>>" + result);
return result.Id;
}
else
{
return 0;
}
}
catch (Exception ex)
{
Console.WriteLine("ex ->" + ex);
throw;
}
}
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
_context.Database.ExecuteSqlCommand if obsolete
SELECT [Generated Column Names] FROM
(
@YOURQUERY
)
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @count OUTPUT", paramUsername, paramOut);
var result = _context
.Database.ExecuteSqlRaw
($"EXEC SP_GetIdFuncByUserName @username, @idemp OUTPUT", paramUsername, paramOut);
var employeeId = paramOut.Value;
var employee = _context.Employee.FirstOrDefault(e => e.Id == employeeId);