Call Stored Procedure from Entity Framework

Question

I've done a lot of Googling re this subject, but can't seem to find any clear answer that works. This maybe because of the way the different EF versions has worked (or not) in the past.

I'm currently using Asp.Net 4.0 with EF 6.1. My model is database first. Additionally, I'm using my own UOW, repository pattern but this is just for information.

Firstly, the reason I want to call a stored procedure is because I need to incorporate a 'counter' into my table - put simply each time anyone visits a particular partner, I need to increment the counter. Of course, the main issue using EF is concurrency.

The articles I've read, tell me that EF isn't good at this type of update, however if this is now deemed easier to achieve in later EF versions, I'd be interested to hear more. Otherwise, I'm left with a native stored procedure - 2 options I guess

call from EF, and
call directly

Since I've been using primarily EF, my knowledge of SQL is fairly sparse, but I've created the following stored procedure:

ALTER PROCEDURE dbo.Popularity_Update
   @TermID smallint
AS
   SET NOCOUNT ON

   DECLARE @Now date = SYSDATETIME()

   BEGIN TRY
      MERGE Popularity AS t
      USING (SELECT @TermID AS TermID, @Now AS VisitDate) AS s ON t.TermID = s.TermID 
                                                               AND t.VisitDate = s.VisitDate

      WHEN MATCHED THEN
          UPDATE 
              SET VisitCount += 1

      WHEN NOT MATCHED BY TARGET THEN
          INSERT (TermID, VisitDate, VisitCount)
          VALUES (s.TermID, s.VisitDate, 1);

      END TRY
      BEGIN CATCH
      END CATCH

That's were I get lost. I noticed within the EF designer that the stored procedure could be referenced, so I added the table to my model and then mapped the stored procedure.

enter image description here

But I also noticed that I can reference a stored procedure from code using the following code:

var name = new SqlParameter("TermID", typeof(short));
uow.Context.Database.ExecuteSqlCommand("Popularity_Update", name);

At the moment, I'm just confused and have lots of questions.

Can this be done in EF without a stored procedure?
Should it be done in EF without a stored procedure?
If I use a stored procedure - whats the best way to do this?

I'd appreciate any help/guidance available.

Edward Tejada · Accepted Answer · 2014-11-21 12:37:26Z

1

you cant use this in EF without a stored procedure.

the best way to do this is using the dynamically mapping.

answered Nov 21, 2014 at 12:37

Edward Tejada

213 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Neil · Accepted Answer · 2014-11-21 17:08:02Z

1

Heres How I usually execute my stored procedures.

internal static Database Db = DatabaseFactory.CreateDatabase();//stated in my dataaccess layer

DbCommand dbCommand = Db.GetStoredProcCommand("Yourstoreprocturename");
Db.AddInParameter(dbCommand, "TermID", DbType.Int32, parameterofTermID);
Db.ExecuteNonQuery(dbCommand);

answered Nov 21, 2014 at 17:08

Neil

323 bronze badges

Comments

dotnetnoob · Accepted Answer · 2014-11-28 11:17:31Z

0

Took on board what the previous answers offered. Came up with this solution that caters specifically for the UOW/ Repository environment I'm working with.

    public static void Increment(short termID)
    {
        termID.ThrowDefault("termID");

        using (var uow = new UnitOfWork(Connection.Products))
        {
            var sql = "Popularity_Update @TermID";
            var parameters = new SqlParameter("TermID", termID);

            uow.Execute(sql, parameters);
        }
    }

answered Nov 28, 2014 at 11:17

dotnetnoob

11.5k20 gold badges60 silver badges108 bronze badges

Collectives™ on Stack Overflow

Call Stored Procedure from Entity Framework

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related