Looking for online help (Remote TeamViewer session) a Linq expert to convert a stored procedure into a Linq Query to perform a single EF query to the Database.
Here is the original procedure:
I dont have a sample Model - but basically, it will require updating 1 parent entity, 2 related child entities
CREATE PROCEDURE [dbo].[CreateScanOutActivity]
@SerNoIdTable IDTable READONLY,
@StationId INTEGER,
@PersonId INTEGER,
@ActivityType INTEGER,
@Duration_s INTEGER,
@ActivityTime DATETIME,
@TargetTime_s Decimal,
@MRPCodeId INTEGER,
@ScanningOUT BIT,
@SequenceNumber INTEGER
AS
BEGIN
CREATE TABLE #TempTable( ActivityID int);
-- GET DATE
DECLARE @TOD DATETIME;
IF @ActivityTime IS NULL
SET @TOD = CURRENT_TIMESTAMP;
ELSE
SET @TOD = @ActivityTime;
DECLARE @SerNoID INTEGER;
DECLARE @ActivityID INTEGER
DECLARE @SerNoProcessActivityId INTEGER
DECLARE SerNo_Cursor CURSOR FOR SELECT * FROM @SerNoIdTable
OPEN SerNo_Cursor
FETCH NEXT FROM SerNo_Cursor INTO @SerNoID
WHILE @@FETCH_STATUS = 0
BEGIN
-- FOR EACH SERIAL NUMBER
-- Check if Activity exists
SET @ActivityID = (SELECT TOP(1) ActivityID FROM Activity
WHERE activitySernoIDFK = @SerNoID AND activityStationIDFK = @StationID
AND activityTime = @ActivityTime AND activityUserIDFK = @PersonID
AND activityType = @ActivityType AND (MRPCode = @MRPCodeId OR @MRPCodeId = NULL));
IF(@ActivityID IS NULL)
BEGIN
-- If Activity does NOT exist - Create it
INSERT INTO Activity (activitySernoIDFK, activityStationIDFK, activityTime, activityUserIDFK, activityType, Duration_s, TargetTime_s, MRPCode)
VALUES (@SerNoID, @StationID, @TOD, @PersonID, @ActivityType, @Duration_s, @TargetTime_s, @MRPCodeId);
SET @ActivityID = SCOPE_IDENTITY();
-- Create SerNoProcessActivity
SET @SerNoProcessActivityId =
(SELECT TOP(1) SerNoProcessID FROM SerNoProcess snp
JOIN SerNoRoute snr ON [login to view URL] = [login to view URL]
WHERE [login to view URL] = @SerNoID AND [login to view URL] = 1 AND [login to view URL] = 0 AND [login to view URL] = @MRPCodeId AND [login to view URL] = @SequenceNumber);
IF (@SerNoProcessActivityId IS NOT NULL)
BEGIN
INSERT INTO SerNoProcessActivity (ActivityID, SerNoProcessID) VALUES (@ActivityID, @SerNoProcessActivityId);
END
END
-- SCAN OUT
IF @ScanningOUT = 1
BEGIN
UPDATE SerNos SET ScannedOutActivityID = @ActivityID WHERE SerNoID = @SerNoID;
END
INSERT INTO #TempTable (ActivityID) VALUES (@ActivityID)
FETCH NEXT FROM SerNo_Cursor INTO @SerNoID;
-- FOR EACH SERIAL NUMBER -- END
END
CLOSE SerNo_Cursor;
DEALLOCATE SerNo_Cursor;
SELECT * FROM #TempTable;
DROP TABLE #TempTable;
END
GO
Hi,
Have you created model? Or you have to create a model? Are you using Database first approach? I am sure I can convert your SPs to linq quries and it would be in best budget and time frame.
Let's talk.
Cheers,
Afnan
Hi,
My name Saurabh. I have total 7 Year Experiences in .Net Technology. Last 4 Year I am working on EF 6 and EF core and I have code knowledge about Code First approach.
I checked you stored procedure and I am thinking I will help you on this project .