How to inject parameter with EF6 / Code First?

Question

I'm trying to figure out how to inject a parameter into Entity Framework 6 when using MapToStoredProcedures. Is this even possible?

I just want to pass my currently logged in username from the application to the stored procedure, but I can't seem to figure out WHERE EF6 does the actual call.

EDIT : A bit more information

Ok, so WITHOUT MapToStoredProcedures (aka letting EF6 just use tables directly) I can do the following in my overridden SaveChangesAsync method:

For Each Entry As DbEntityEntry In Me.ChangeTracker.Entries().Where(Function(o) o.State = EntityState.Deleted)
    If TypeOf Entry.Entity Is ISoftDelete Then
        'Implements Soft Delete interface, so let's do what needs doing.

        Select Case Entry.Entity.GetType()
             Case GetType(OS)
                 Dim _thisOS As OS = TryCast(Entry.Entity, OS)

                 Using db As New AppRegistrationContext
                     _thisOS = Await db.OSSet.Include("OSType").FirstOrDefaultAsync(Function(o) o.ID = _thisOS.ID)
                 End Using

                        If Not _thisOS Is Nothing Then
                            Try
                                Entry.Reference("OSType").CurrentValue = _thisOS.OSType
                            Catch ex As Exception
                                Debug.Print(ex.ToString)
                            End Try

                        End If
                    Case GetType(Server)

                    Case Else
                        'Do nothing - only filling in extra information for those that we need to
                End Select

                'Set the archival bits
                Entry.Property("Archive").CurrentValue = True
                Entry.Property("ArchiveDate").CurrentValue = Date.Now
                Entry.Property("ArchiveBy").CurrentValue = HttpContext.Current.User.Identity.Name.ToString()

                'Mark it modified
                Entry.State = EntityState.Modified

            End If
        Next

Return Await MyBase.SaveChangesAsync()

Alright, that works great with direct-table manipulation on EF's behalf.

What I want to do instead, is handle all of this in stored procedures - but I need to pass HttpContext.Current.User.Identity.Name.ToString() WITH my delete stored procedure to set the ArchiveBy parameter.

Hopefully this better illustrates what I am attempting to do.

MHOOS · Answer 1 · 2014-10-17T16:25:11.473

Life could not be easier for you. Run something like the following:

In your repository add the following:

    public void ExecuteSqlCommand(string sql, params object[] parameters)
    {
        DbContext.Database.ExecuteSqlCommand(sql, parameters);
    }

and use it just like the following:

    public void DoSomething(int officeId)
    {
        var sqlParam = new SqlParameter("p0", officeId);

        var parameters = new object[] { sqlParam };

        ((GenericRepository)Repository).ExecuteSqlCommand("EXEC dbo.myProc @p0", parameters);
    }

Or simply just call

DbContext.Database.ExecuteSqlCommand

as I showed above based on your needs.

Update 1 : You want a stored procedure to take care of the CRUD business :

Suppose your context is called : MyDbContext

Then declare something like the following in a partial MyDbContext class:

public partial class MyDbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder
        .Entity<SomeCustomEntity>()
        .MapToStoredProcedures(agent =>
        {
            agent.Insert(i => i.HasName("spr_MyInsert"));
            agent.Update(u => u.HasName("spr_MyUpdate"));
            agent.Delete(d => d.HasName("spr_MyDelete"));
        });
    }
}

Now every time you want to do some CRUD procedure you operation will be running through the stored procedure [The one you have mapped] and you don't need to worry about passing anything to the stored procedure :

using (var context = new MyDbContext())
{
    context.SomeCustomEntity.Add(new SomeCustomEntity
    {
        Name = "Jack Something",
        Phone = "999"
    });

    context.SaveChanges();
}

I'm trying to work with EF's ChangeTracker... my thought was that I'd override EF's Entity.Delete, as that's where I thought EF would call the stored procedure, but I can't seem to figure out how to override it... — John, Oct 16 '14 at 16:42

How to inject parameter with EF6 / Code First?

1 Answers1