none
Trying to run a stored procedure which deletes records from a table. RRS feed

  • Question

  • I'm using Entity Framework 4.2, in a C# app.  We have a situation in which we need to keep data around for a while, and then after a certain length of time has gone by we'll delete all of the records associated with the logged in user, from a table.  I've written a stored procedure to do this, and have mapped that stored procedure to the delete function of the table.  It's at this point that I get a little stumped as to how to proceed in the C# code.  Here's what I've got so far:

    var purgeOldAsis = context.ASICheckOuts.Where(a => a.LogonID == logonID);

    the variable logonID is a string variable which I assign previously in the code, the value of the currently logged in user.  Anyway, I don't see something like a Delete() method that I could use on the purgeOldAsis object.  Nor do I see a Remove(), or anything else of that time.  I'm wondering if I should use the ExecuteFunction() method of the ObjectContext object?

    Rod

    Tuesday, November 6, 2012 5:53 PM

Answers

  • It seems to me that you don't really need to map this stored procedure to the delete of the table. If your stored proc accepts a user id and then does all the logic to find what needs to be deleted and deletes it, then you really just need to execute that function at an appropriate time.

    So you should be able to just add the function to your model and call it on your context, passing in the id of the logged in user (context.DeleteOldDate(logonID)).

    This solutions is making an assumption about how you have implemented your stored procedure, and I think this solution is also what Ajay was recommending.

    If your stored proc is implemented differently and this doesn't make sense then let me know, and maybe show me the code of the sproc and we might be able to give some better help.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Wednesday, November 7, 2012 7:46 PM
    Moderator
  • Hi,

    This link may help .

    Tuesday, November 13, 2012 8:14 PM

All replies

  • Hi....Rod

    Create stored procedure and call the SP method from c#code using ObjectContext, that's so simple right,

    why u should pass query multiple time to delete from db.

    e.g you have to delete 10 record, then entity framework will hit ur db 10 times to delete records. 

    so better to use SP pass parameter and call them.

    This is how we are doing right now.

    Wednesday, November 7, 2012 3:46 AM
  • The reason we'll hit it a lot is because we have to leave the data there for a long time, before deleting it.  Here's the scenario.  There's a column in the table, specifying when to delete the record.  Once a day, when the user first uses the app, it will check that column for that user and determine whether or not the date has arrived.  If it hasn't, then it leaves it alone.  If it has, then it deletes the record, and other records via cascading deletes.

    The reason why we're going to do that is because the older app would delete the data immediately.  However, over the course of 10+ years, sometimes the data deleted was vital and hadn't been saved elsewhere.  This has caused so much pain and aggrevation that users demand that we not loose any data.  Therefore, we're approaching this differently; we'll leave the data on the user's machine for "some length of time" (TBD) when we know that it is safely stored elsewhere and can therefore be removed from the user's machine.  That's the reason for doing what we're doing.


    Rod

    Wednesday, November 7, 2012 3:51 PM
  • It seems to me that you don't really need to map this stored procedure to the delete of the table. If your stored proc accepts a user id and then does all the logic to find what needs to be deleted and deletes it, then you really just need to execute that function at an appropriate time.

    So you should be able to just add the function to your model and call it on your context, passing in the id of the logged in user (context.DeleteOldDate(logonID)).

    This solutions is making an assumption about how you have implemented your stored procedure, and I think this solution is also what Ajay was recommending.

    If your stored proc is implemented differently and this doesn't make sense then let me know, and maybe show me the code of the sproc and we might be able to give some better help.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Wednesday, November 7, 2012 7:46 PM
    Moderator
  • That makes sense, Gordon. Right now I've added the stored procedure to the table's delete function, but I think I'd rather do it the way you've described. How do I add it to my model so that I can call it on my context?

    Rod

    Tuesday, November 13, 2012 4:22 PM
  • Hi,

    This link may help .

    Tuesday, November 13, 2012 8:14 PM