Linq to SQL: Queries don't look at pending changes

Question

Follow up to this question. I have the following code:

string[] names = new[] { "Bob", "bob", "BoB" };
using (MyDataContext dataContext = new MyDataContext())
{
    foreach (var name in names)
    {
        string s = name;
        if (dataContext.Users.SingleOrDefault(u => u.Name.ToUpper() == s.ToUpper()) == null)
            dataContext.Users.InsertOnSubmit(new User { Name = name });
    }

    dataContext.SubmitChanges();
}

...and it inserts all three names ("Bob", "bob" and "BoB"). If this was Linq-to-Objects, it wouldn't.

Can I make it look at the pending changes as well as what's already in the table?

Answer 1

Why dont you try something like this

foreach (var name in names)
{
    string s = name;
    if (dataContext.Users.SingleOrDefault(u => u.Name.ToUpper() == s.ToUpper()) == null)
    {
        dataContext.Users.InsertOnSubmit(new User { Name = name });
        break;
    }
}

Answer 2

I am sorry, I don't understand LINQ to SQL as much.

But, when I look at the code, it seems you are telling it to insert all the records at once (similar to a transaction) using SubmitChanges and you are trying to check the existence of it from the DB, when the records are not inserted at all.

EDIT: Try putting the SubmitChanges inside the loop and see that the code will run as per your expectation.

Answer 3

I don't think that would be possible in general. Imagine you made a query like this:

dataContext.Users.InsertOnSubmit(new User { GroupId = 1 });
var groups = dataContext.Groups.Where(grp => grp.Users.Any());

The database knows nothing about the new user (yet) because the insert wasn't commited yet, so the generated SQL query might not return the Group with Id = 1. The only way the DataContext could take into account the not-yet-submitted insert in cases like this would be to get the whole Groups-Table (and possibly more tables, if they are affected by the query) and perform the query on the client, which is of course undesirable. I guess the L2S designers decided that it would be counterintuitive if some queries took not-yet-committed inserts into account while others wouldn't, so they chose to never take them into account.

Why don't you use something like

foreach (var name in names.Distinct(StringComparer.InvariantCultureIgnoreCase))

to filter out duplicate names before hitting the database?