c# - 从 Entity Framework Core 3.0 调用存储过程
问题描述
我有许多存储过程,它们都有许多参数并执行复杂的选择语句,其中结果与表或视图完全匹配。我想将结果集映射到实体。这适用于 2.2 版,但我无法让它与 EFCore 3.0 一起使用。
我的代码是这样的:
SqlParameter regionId = p.RegionId.HasValue && p.RegionId.Value != 0 ? new SqlParameter( "@RegionId", p.RegionId.Value ) : new SqlParameter( "@RegionId", System.DBNull.Value );
… (other parameters omitted for clarity, but all SqlParameters)
var contacts = await _context.PersonSearchViewSearch.FromSqlRaw( "EXECUTE [dbo].[PersonSearchView_Search] @RegionId, @RecordId, @Surname, @Firstname, @PostCode, @HomePhone, @WorkPhone, @MobilePhone, @EMail, @IncludePatients, @IncludeContacts, @IncludeEnquiries, @AllowedRegions, @IncludeOtherRegions, @IncludeDisallowedRegions, @FirstNameMP, @FirstNameAltMP, @SurnameMP, @SurnameAltMP", regionId, id, surname, forename, postcode, homePhone, workPhone, mobilePhone, email,includePatients, includeContacts, includeEnquiries, allowedRegions, includeOtherRegions,includeDisallowedRegions, forenameMP, forenameAltMP, surnameMP, surnameAltMP ).ToListAsync();
在 EF Core 3.0 中,这会生成以下 SQL(无效):
exec sp_executesql N'SELECT [p].[Address1], [p].[Address2], [p].[Address3], [p].[Address4], [p].[CarerOrProfessionalId], [p].[City], [p].[ContactId], [p].[Discriminator], [p].[EMail], [p].[EnquiryId], [p].[FirstName], [p].[FirstNameAltMP], [p].[FirstNameMP], [p].[HomePhone], [p].[MobilePhone], [p].[PatientId], [p].[PostCode], [p].[PreferredPhone], [p].[PreferredPhoneNo], [p].[RegionId], [p].[RegionName], [p].[SalutationId], [p].[SearchId], [p].[Surname], [p].[SurnameAltMP], [p].[SurnameMP], [p].[Title], [p].[WorkPhone], [p].[IsAllowed], [p].[IsPrimaryRegion]
FROM (
EXECUTE PersonSearchView_Search @RegionId,@RecordId,@Surname,@Firstname,@PostCode,@HomePhone,@WorkPhone,@MobilePhone,@EMail,@IncludePatients,@IncludeContacts,@IncludeEnquiries,@AllowedRegions,@IncludeOtherRegions,@IncludeDisallowedRegions,@FirstNameMP,@FirstNameAltMP,@SurnameMP,@SurnameAltMP
) AS [p]
WHERE [p].[Discriminator] = N''PersonSearchViewSearch''',N'@RegionId int,@RecordId nvarchar(4000),@Surname nvarchar(6),@Firstname nvarchar(4000),@PostCode nvarchar(4000),@HomePhone nvarchar(4000),@WorkPhone nvarchar(4000),@MobilePhone nvarchar(4000),@EMail nvarchar(4000),@IncludePatients bit,@IncludeContacts bit,@IncludeEnquiries bit,@AllowedRegions nvarchar(8),@IncludeOtherRegions bit,@IncludeDisallowedRegions bit,@FirstNameMP nvarchar(4000),@FirstNameAltMP nvarchar(4000),@SurnameMP nvarchar(3),@SurnameAltMP nvarchar(3)',@RegionId=1,@RecordId=NULL,@Surname=N'smith%',@Firstname=NULL,@PostCode=NULL,@HomePhone=NULL,@WorkPhone=NULL,@MobilePhone=NULL,@EMail=NULL,@IncludePatients=1,@IncludeContacts=1,@IncludeEnquiries=1,@AllowedRegions=N'1,2,6,11',@IncludeOtherRegions=0,@IncludeDisallowedRegions=0,@FirstNameMP=NULL,@FirstNameAltMP=NULL,@SurnameMP=N'SM0',@SurnameAltMP=N'XMT'
存储过程过于复杂,无法尝试使用 Linq to SQL 编写。
我目前唯一的解决方法是返回SqlCommand
/SqlDataReader
方法,但这需要编写大量额外的代码来将结果映射回实体。
在 EF Core 3.0 中是否有更好的方法来执行此操作?
解决方案
对于遇到此问题的其他人,我将其追溯到我对“PersonSearchViewSearch”的定义是来自另一个视图的派生类。我通过复制基类中的所有字段将定义更改为独立类,这解决了问题。
这将生成的 SQL 更改为简单的 Execute 语句,而不是 Select from Execute。
(EF Core 2.2 对我使用派生类很满意)。
推荐阅读
- android - 如何在 Android Studio 中使用概述的 Material 图标?
- node.js - Node.js 和 CPU 缓存利用率
- sql - SQL中的UPDATE语句一半工作一半不做任何事情
- javascript - Jquery 可排序/可滚动标签跳跃
- c# - IBackgroundJobManager 使用“ContinueWith”的顺序作业
- javascript - 为放置区中的选择创建唯一 ID
- php - merge array values - PHP
- c - 错误 - 接受()失败:错误的文件描述符
- go - ssh 客户端显示服务器支持的算法
- sql - 如何按团队按月查询总销售额?