Best way to periodically remove a set of records with LINQ to SQL

This is my first crack at a method that is run periodically during the lifetime of my ASP.NET application to clean up expired sessions stored in my database. It seems to work pretty well, but the software engineer in me doesn't feel "right" about this code. I've been working with LINQ to SQL for a few months now, but I'm not very confident in the following code. I'm worried about a few things:

  1. Is the following code safe to run in a situation where the database is being accessed by different threads in my application? I have a decent understanding of the idea of transactions, but I want to make sure I'm using them properly.

  2. Is my query going to cause performance issues? Or is it appropriate in this case to select all of the records in this particular table? This method only runs every 15 minutes, so it's not like that query will be made over and over again in a short period of time.

  3. Is there a better way that I could do this? I have a nagging feeling that there is.

Code:

/// <summary>
/// Method, run periodically, to remove all sign in records that correspond to expired sessions.
/// </summary>
/// <param name="connectionString">Database connection string</param>
/// <returns>Number of expired sign in records removed</returns>
public static int Clean(String connectionString)
{
    MyDatabaseDataContext db = new MyDatabaseDataContext(connectionString);

    var signIns = db.SignIns.Select(x => x);
    int removeCount = 0;

    using (TransactionScope scope = new TransactionScope())
    {
        foreach (SignIn signIn in signIns)
        {
            DateTime currentTime = DateTime.Now;
            TimeSpan span = currentTime.Subtract(signIn.LastActivityTime);

            if (span.Minutes > 10)
            {
                db.SignIns.DeleteOnSubmit(signIn);
                ++removeCount;
            }
        }

        db.SubmitChanges();
        scope.Complete();
    }

    return removeCount;
}

Answers


This sounds like something you could easily do in a sproc. SQLServer gives you a GETDATE() method that returns the current time... I don't see why you can't just

 DELETE * FROM tblSignIns 
 WHERE LastActivityTime < DATEADD("minute", -10, GETDATE());

Wouldn't that do the same thing?


Need Your Help

How to improve UIWebView scrolling performance?

iphone uiwebview

It seems to be really hard to improve the performance of a UIWebView, especially for websites like Mashable or Ars Technica, where tons of scripts are loaded and long, multi-page articles are commo...

Curl w3c linkckecker

curl hyperlink

I would like to use w3c link checker from commandline

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.