CODEWITHSUNDEEP
asp.netsql-serverentity-frameworkstored-procedures
dotnetnoob
dotnetnoob

Reputation: 11350

Call Stored Procedure from Entity Framework

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

  1. call from EF, and
  2. 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.

I'd appreciate any help/guidance available.

Upvotes: 0

Views: 200

Answers (3)

dotnetnoob
dotnetnoob

Reputation: 11350

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);
        }
    }

Upvotes: 0

Neil
Neil

Reputation: 32

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);

Upvotes: 1

Edward Tejada
Edward Tejada

Reputation: 21

you cant use this in EF without a stored procedure.

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

Upvotes: 1

Related Questions