Monday, May 24, 2010

Setting an EntityRef in LINQ to SQL

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.
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.

No comments:

Post a Comment