Linq-To-SQL select, update and commit results in empty Table

Given:

using (DataContext ctx = new DataContext(props.ConnectionString))
{
    IQueryable<Email> emails = null;
    try
    {
       emails = ctx.Emails.Where(e => !(e.IsLocked || e.IsSent));
       foreach (var e in emails)
       {
           e.IsLocked = true;
       }
       ctx.SubmitChanges();
    }
}

// do something with emails here

Why is emails em开发者_开发知识库pty after SubmitChanges()? Is there any way to avoid emptying the Table after IsLocked is set to true?

The table ctx.Emails is probably not empty. The collection emails is evaluated every time you call it.

You could do this if you want to keep the emails return on the initial call:

emails = ctx.Emails.Where(e => !(e.IsLocked || e.IsSent)).ToList().AsQueryable();

Thomas's answer is correct but I'll have a go at explaining it as well.

The emails collection is an enumeration that is reevaluated when you call it. Picture it like this.

// another method...
IEnumerable<Email> GetEmails()
{
    return ctx.Emails.Where(e => !(e.IsLocked || e.IsSent));
}

// & inside your main method
foreach (var e in GetEmails())
{
    e.IsLocked = true;
}
ctx.SubmitChanges(); 

// now if you check the result it will be reevaluated
GetEmails();

This is intended behaviour.

If you want to 'get' the emails, do something to them and keep that collection, you should put them in a list / array. The enumeration isn't a real collection, it's more like a function that returns a collection when you query it.