LINQ to SQL SubmitChanges Order of Operations

I did not realize the following is true in LINQ to SQL: Calling SubmitChanges fires ChangeSet changes in the following order:

• Inserts
• Updates
• Deletes

Normally, this might not be a big deal, and you might never notice it, but it was the source of some pain on my current project.

Consider an order-entry screen that allows the user to add/edit/delete line items on an order.  The order-detail records are keyed by order-number/line-number, and this is the source of the problem.  If the user deletes line #1 in the order, what was originally line #2 gets re-ordered as line #1.  When the user decides they are done editing the order, they will then hit the save button and the queued up add/edit/delete operations are committed via the LINQ to SQL SubmitChanges command.

Fortunately, we were able to change the primary-key on our order-detail table, but we effectively lost some automatic data-integrity since we were previously able to ensure uniqueness on order-number/line-number.  For teams that don’t have the luxury of changing their database keys, this design decision by the LINQ to SQL team may be cause to move away from LINQ to SQL. 

Why the LINQ to SQL team decided to not submit changes in the order of operations, or allow the user to specify a non-default order to submit changes is beyond me.  I’m sure they had valid reasons for this decision, but it’s a bit short-sighted in that this interaction pattern is fairly common in order-entry system. 

This could perhaps been avoided by stamping various LINQ class members as virtual and letting us developers (consumers) override behaviors as we need – and assume the risks that come with overriding the framework code.

Linq to SQL Object Equality Issue

I started having some issues where Linq to SQL objects are "different” instances between calls to db.GetTable(type) and comparing objects returned.  In the majority of my types, object equality was working just fine, but in a few types, the hash-codes for otherwise “equal” objects was different, while the remainder of the object data was as expected.

Deleted the designer-objects and re-added to my DBML and the issue is resolved. 

A compare of my DBML file shows that the “IsPrimaryKey” attribute on my Identity columns was missing.  I had probably added the table to the DBML and realized later that I hadn’t specified a primary key and later added said key, without re-synchronizing my DBML.

Mocking IQueryable

On my current project, we’ve been pretty successful in mocking out all of our LINQ to SQL repository operations, with one exception. One of our new methods returns an IQueryable object. No problem I think, I’ll just set an expectation on that call and return a list of the property entity type. But that gives me a build-error that I can’t convert a List<T> to IQueryable<T>.

A quick google yields the simplest of solutions to mocking IQueryable: the “AsQueryable” extension method. So now, my mock expectation returns something like “List<T>.AsQueryable” and all is well. This finding also clued me into a whole slew of interesting looking extension methods in the System.Linq namespace…..

LINQ to SQL and Overriding .Equals = not a good idea.

Short version: don’t override .Equals in your LINQ TO SQL entities, unless you really know what “equals” means to your objects!

Long version: on my current project, we’re using LINQ to SQL for our ORM needs, and it’s been working pretty well for us so far.  Pretty well, except for late last week when we realized that we weren’t properly persisting a number of records in our object graph.

We have a fairly complex object model, with some deep graphs of related objects, and have been using the .Add method to add child and grandchild objects into our “Order” object’s graph.  There was one particular “Child” record that was only being created one time per order, when we expected several variations of this child, yet only the first was being saved.

Our object graphs were build correctly, SubmitChanges was doing its job, we weren’t getting any errors, and yet these records just weren’t making it to the database.  After a few conversations with other people that have some LINQ to SQL experience and me trying some of the ugliest hacks I could think of, it dawned on me to try testing another theory I had – create another order-detail that looked similar to an existing order-detail.

Lo and behold, this similar order-detail also did not persist, and it was behaving like the other “exceptional” order-details – they were all in the object-graph, but not in the Insert Changes of my data-context!  Once I was able to see this, I took a closer look at the order-detail partial class we had written, and there I saw an overridden .Equals implementation.  Unfortunately, this .Equals was testing for property values within the order-detail to determine if a detail might match another detail instance, not testing for reference equality, which is evidently important to LINQ to SQL.

So there you have it – when working with LINQ to SQL entities, take care in how you decide to utilize the .Equals method and how you decide to override it.

Managing Environment Specific Configurations Settings at Install Time

I’ve been asked a few times in the past few weeks about approaches for managing environment specific settings in config files.  Over the years, I’ve seen this solved a few different ways, and I tend to like the following approach:

• Create a Setup project in your solution.
• Open the User Interface Editor, and add a 3-button dialog to the setup (right-click the Start folder and choose “Add Dialog” and pick the 3-button dialog option)
• Right-Click the newly added entry in the “Start” windows tree and select “Move Up”, so that this window is somewhere before the “Conform Install” dialog.
• Edit the Properties Window for the 3-buttons window to be something like the following:

• In your main project (UI, Console, etc), add an XML file for the environment specific settings.  The schema should be something like the following:

<environments>
  <environment name="Development">
    <setting settingKeyPath="<your xpath here..>" propertyName="<your property here..>" propertyValue="<your value here…>" />
  </environment>
…….repeat the environment section for each targeted environment
</environments>

• In your main project, add a new class and have it implement the “Installer” class (found in System.Configuration.Install – so you’ll probably need to add a project reference to that assembly).
• Override OnAfterInstall, but make sure you first call base.OnAfterInstall(), then start the process of applying your settings from the XML file created earlier to the config file of your installing application.
• Your environment setting can be pulled from Context.Parameters[“ENVIRONMENT”]
• Write yourself some code to read in the data in the XML file, and overwrite the appropriate settings in the config file.

That’s the basic steps I take to manage environment specific config file settings in my VisualStudio projects.