Complex Insert Stored Proc unsuitable for EF?
I'm trying to build a new app on top of an existing DB.
User Credentials are retrieved via a separate system and return a GUID to the client app identifying the user, however t开发者_如何学JAVAhis database uses abigInt
for user identification, to get around the problem that each SP in the DB uses a Mapping function to discover the local UserIdentity
based on a GUID (userID) passed into the SP.
I'd like to use EF (.Net4.0) but I can't see a way to have the GUID passed into an SP to allow the DB mapping function to determine the local UserIdentity.
Mapping the SP for returning sets works OK, taking in a GUID, deriving the local UserIdentity and returning a recordset of 'pulse'. Updates and deletes are fine because they can use the Entities own Id value.
I guess my real question is "Is there a way to send a value to a stored procedure if the entity the SP is mapped to doesn't contain a property with that value?"
Here's a typical Table (the EF Entity has the same properties) and it's corresponding Insert SP.
CREATE TABLE [dbo].[Pulse](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserIdentity] [bigint] NOT NULL,
[Recorded] [datetime] NOT NULL,
[Value] [int] NOT NULL,
The insert SP looks like this
ALTER PROCEDURE [dbo].[Pulse_Insert] @userId uniqueIdentifier, @recorded datetime, @pulse int AS
BEGIN
SET NOCOUNT ON;
declare @userIdentity bigint
select @userIdentity = dbo.GUIDUserMapping(@userId)
insert into dbo.Pulse (UserIdentity, recorded, value)
values(@userIdentity,@recorded,@pulse)
END
It's not "unsuitable for EF," but if you use a proc for one operation (INSERT / UPDATE / DELETE / SELECT," the EF expects you to use procs for the other.
Wouldn't it be easier to use an INSTEAD OF trigger, here? Then the EF won't need to know about it at all.
精彩评论