Querying Newly Added Entities in LINQ to SQL

In LINQ to SQL, when you want to add a new record to the database, you have to follow these steps:

1. Create the object in memory.
2. Attach it to the current context, using either InsertOnSubmit() or InsertAllOnSubmit().
3. Submit the context, using SubmitChanges().

The other day, I was working on some complicated logic and as part of debugging I tried to check if a certain object had already been attached to the context (step #2). Unfortunately, this doesn’t work. Let’s say I had a Customer table in the database, represented in LINQ as follows:

Then I add a new Customer using this code:

1:             using (var ctx = new MyDataContext(connectionString))
2:             {
3:                 Customer newCustomer = new Customer
4:                 {
5:                     Id = Guid.NewGuid(),
6:                     Name = "James Bond",
7:                     AccountNumber = "007",
8:                     JoinDate = new DateTime(1953, 3, 15)
9:                 };
10:
11:                 ctx.Customers.InsertOnSubmit(newCustomer);
12:
13:                 int count = (from c in ctx.Customers
14:                              select c).Count();
15:             }
16:

Assuming that there were 10 records in the table prior to executing this code, the value of count in line 13 will still return 10. This is because even though newCustomer is attached to the context, LINQ will not “see” it when you query until it has been submitted.


Instead of adding it directly to the context, if you add it to the Entity collection of another Entity, like in the following example, you would have the exact same results.

1: using (var ctx = new MyDataContext(connectionString))

2: {

3:      Customer newCustomer = new Customer

4:      {

5:           Id = Guid.NewGuid(),

6:           Name = "James Bond",

7:           AccountNumber = "007",

8:           JoinDate = new DateTime(1953, 3, 15)

9:      };

10: 

11:      var myOrder = (from o in ctx.Orders

12:                     where o.Id == orderId

13:                     select o).Single();

14: 

15:      order.Customers.Add(newCustomer);

16: 

17:      int count = (from c in ctx.Customers

18:                   select c).Count();

19: }

If you need it to return in the results, you have to submit the context first:

1:             using (var ctx = new MyDataContext(connectionString))
2:             {
3:                 Customer newCustomer = new Customer
4:                 {
5:                     Id = Guid.NewGuid(),
6:                     Name = "James Bond",
7:                     AccountNumber = "007",
8:                     JoinDate = new DateTime(1953, 3, 15)
9:                 };
10:
11:                 ctx.Customers.InsertOnSubmit(newCustomer);
12:                 ctx.SubmitChanges();
13:
14:                 int count = (from c in ctx.Customers
15:                              select c).Count();
16:             }
17:

Now, the count in line 14 will return 11 instead of 10.


It’s a fairly simple concept, but when you’re writing complicated code sometimes that’s the exact type of thing you can overlook. Unfortunately, I haven’t found a way to actually query what LINQ has pending for submission. If anyone has ideas, please let me know!