我有许多存储过程,所有存储过程都具有许多参数,并执行一个复杂的select语句,其结果与表或视图完全匹配。我想将结果集映射到实体。在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,@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编写SQL。
我目前唯一的解决方法是返回到SqlCommand
/ SqlDataReader
方法,但这需要编写大量其他代码才能将结果映射回实体。
在EF Core 3.0中有更好的方法吗?