Archive

Posts Tagged ‘LINQ’

Querying Newly Added Entities in LINQ to SQL

June 12, 2010 Leave a comment

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:

Customer

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!

Share

Categories: C#, LINQ Tags: , , , ,

Setting an EntityRef in LINQ to SQL

May 24, 2010 Leave a comment

I was debugging a unit test today and came across a behavior in LINQ to SQL that I thought it would be beneficial to review. It has to do with setting the EntityRef of a LINQ to SQL object in memory. According to MSDN, EntityRef is a structure that:

Provides for deferred loading and relationship maintenance for the singleton side of a one-to-many relationship in a LINQ to SQL application. 

In the system we’re currently developing, most database records have temporary copies that we refer to as shadow records. A shadow record is a temporary copy of a database record that is used for editing until it is “approved”, at which point it gets copied to the real record. Shadow records are identical to the actual record, but with a different primary key. They are stored in the same database table as the real records. Those tables have a foreign key relationship to themselves from a shadow source column to the primary key column.

For example, one table is Address. In addition to all the columns that represent the address information, there is the AddressId column and a ShadowSourceId column. The ShadowSourceId is a foreign key to the AddressId in the same table. Rows with a null value for ShadowSourceId are approved records, while rows with a value for ShadowSourceId are shadow records.

Address Table

In LINQ, we create a shadow record by first duplicating the original record:

  1: Address duplicateAddress = DuplicateAddress(originalAddress);

 Then we modify the duplicate to turn it into a shadow record:

  1: duplicateAddress.AddressId = Guid.NewGuid();
  2: duplicateAddress.ShadowSourceId = originalAddress.Id;

 

In the DBML designer, I named the EntityRef as ShadowSource, so that writing duplicateAddress.ShadowSource will give you originalAddress. This is where the tricky behavior comes in. In the lines above, I set the ShadowSourceId directly. At this point, if you try to access the ShadowSource EntityRef, you will get a null value (even though ShadowSourceId has been set). Instead, if I were to create the shadow record as follows:

  1: duplicateAddress.AddressId = Guid.NewGuid();
  2: duplicateAddress.ShadowSource = originalAddress;

 

then the ShadowSource EntityRef will have the expected value. However, now the ShadowSourceId property will contain a null.

The reason for this is that the ID property and the EntityRef, even though they reference the same thing, are not “wired up” by LINQ to SQL until you call SubmitChanges(). Whichever one you set, the other will still be null until you submit your changes and LINQ to SQL creates all the proper connections. If for some reason you don’t want to submit your changes, you will have to set both the ID property and the EntityRef for them to have their proper values:

  1: duplicateAddress.AddressId = Guid.NewGuid();
  2: duplicateAddress.ShadowSource = originalAddress;
  3: duplicateAddress.ShadowSourceId = originalAddress.Id;

 

While this seems redundant, sometimes it’s necessary. In my situation, I didn’t need to submit my changes because this was a unit test for a small piece of the process. If you need both of these to be available, then you’ll need to decide which solution is the most appropriate in your situation – submitting your changes or simply setting both manually.

Share

Categories: C#, LINQ Tags: , , , ,