EF Core 7+MariaDb调用存储过程获取数据失败求助

我正在使用EF Core 7.0.9、Pomelo.EntityFrameworkCore.MySql 7.0.0搭配MariaDb，执行代码中testResult2行调用存储过程AddressSelectLatestId时出现错误。

相关代码片段：

var testResult2 = await _dbContext.Address
                                  .FromSqlRaw("Call AddressSelectLatestId()")
                                  .Select(x => x.AddressId)
                                  .FirstOrDefaultAsync();

完整仓库代码：

using AutoMapper;
using KadaiS.Dal.Models;
using KadaiS.Domain.Interfaces;
using Microsoft.EntityFrameworkCore;
using System.Data;

namespace KadaiS.Dal.Repository
{
    public class AddressRepository : IAddressRepository
    {
        private readonly KadaiSDbContext _dbContext;
        private readonly IMapper _mapper;

        public AddressRepository(KadaiSDbContext kadaiSDbContext, IMapper mapper)
        {
            _dbContext = kadaiSDbContext;
            _mapper = mapper;
        }
        
        public async Task<int> InsertAddressAsync(Domain.Models.Address address)
        {
            try
            {
                var addressInsert = _mapper.Map<Domain.Models.Address, Models.Address>(address);
                var results = await _dbContext.Database.ExecuteSqlAsync($"Call AddressInsert({addressInsert.Street}, {addressInsert.City}, {addressInsert.Province}, {addressInsert.PostalCode})");

                if(results == 1)
                {
                    var testResult1 = await _dbContext.Address.FromSqlRaw("SELECT MAX(addressId) AS addressId FROM address").Select(x => x.AddressId).FirstOrDefaultAsync();
                    var testResult2 = await _dbContext.Address.FromSqlRaw("Call AddressSelectLatestId()").Select(x => x.AddressId).FirstOrDefaultAsync();
                }

                return 10;
            }
            catch(Exception e)
            {

                throw e;
            }
        }
    }
}

存储过程AddressSelectLatestId定义：

BEGIN
SELECT MAX(addressId) AS addressId FROM address;
END

错误信息：

MySqlConnector.MySqlException: '你的SQL语法有误；请查看对应MariaDB服务器版本的手册，了解在'Call AddressSelectLatestId() ) AS k
LIMIT 1'附近使用的正确语法'

EF Core的FromSqlRaw在配合Select、FirstOrDefaultAsync等LINQ方法时，会自动将传入的SQL语句包装成子查询（生成类似SELECT k.AddressId FROM (Call AddressSelectLatestId()) AS k LIMIT 1的SQL），但MariaDB不支持在子查询中直接调用存储过程，因此触发语法错误。

直接使用Database.SqlQueryRaw方法执行存储过程，该方法不会对原始SQL进行额外包装，可直接获取结果：

var testResult2 = await _dbContext.Database
    .SqlQueryRaw<int>("Call AddressSelectLatestId()")
    .FirstOrDefaultAsync();

代码中ExecuteSqlAsync使用字符串拼接传递参数存在SQL注入风险，建议改用参数化查询：

var results = await _dbContext.Database.ExecuteSqlAsync(
    "Call AddressInsert(@Street, @City, @Province, @PostalCode)",
    new MySqlParameter("@Street", addressInsert.Street),
    new MySqlParameter("@City", addressInsert.City),
    new MySqlParameter("@Province", addressInsert.Province),
    new MySqlParameter("@PostalCode", addressInsert.PostalCode));

内容的提问来源于stack exchange，提问作者Ameer