CODEWITHSUNDEEP
entity-frameworkstored-proceduresrepository-pattern
Marin Takanov
Marin Takanov

Reputation: 1138

Call stored procedure in EF 6 Repository Pattern without output parameters

I'm working with Repository Pattern and EF6. What I need to do is to call a SP that delete records for mapping table that have nothing in common within the repository I'm calling the SP.

I've tried with

this.dbSet.SqlQuery("exec mySP @Param1, @Param2", param1, param2);

but this returns all the records from the DB of the current repository I'm in. It's like I've wrote "SELECT * FROM Group" (if I'm in GroupRepository). My SP returns nothing since it's deleting some records and I already wasted 2 days of searching how to call the SP. I can give additional info if needed. Can anybody help?

Upvotes: 0

Views: 609

Answers (1)

Marin Takanov
Marin Takanov

Reputation: 1138

Finally I manage to execute my stored procedure, which was accepting a List<int> and an Id. The code looks like this:

SQL SERVER:

CREATE TYPE [dbo].[IntListType] AS TABLE
(   
    [Item] INT 
);
GO

CREATE PROCEDURE [removeEventUsersFromEvents]
(
    @EventIds [dbo].[IntListType] READONLY,
    @UserId INT
)
AS
BEGIN
    BEGIN Tran T1
        DECLARE @query NVARCHAR(MAX)
        SET NOCOUNT ON;
        SET @query = 'DELETE FROM [EventUser]
                      WHERE [EventID] IN (SELECT Item from @EventIds) AND [UserID] = @UserId';
        EXECUTE sp_executesql @query,
            N'@EventIds [dbo].[IntListType] READONLY, @UserId INT',
            @EventIds,
            @UserId;
    COMMIT Tran T1
END;

C#:

    List<int> eventIdsToBeDeleted = this.dbSet...(geting some Ids from the db);

    DataTable dt = new DataTable("IntListType");
    dt.Columns.Add("Item", typeof(Int32));

    eventIdsToBeDeleted.ForEach(id => dt.Rows.Add(id));

    var eventIds = new SqlParameter("EventIds", SqlDbType.Structured);
    eventIds.TypeName = "dbo.IntListType";
    eventIds.Value = dt;

    foreach (var user in usersToBeDeletedFromEvents)
    {
        var userId = new SqlParameter("UserId", SqlDbType.Int);
        userId.Value = user.UserID;

        this.Context.Database.SqlQuery(typeof(List<int>), 
        "EXEC removeCalendarEventUsersFromSpecificGroupAndEvents
        @CalendarEventIds,
        @UserProfileDetailId", 
        eventIds, 
        userId);
   }

This will eventually return an empty List<int>.

Upvotes: 2

Related Questions