With many web 2.0 applications there’s a basic three-tier architecture..   In our case the client is a Flex 3/Caringorm application, the Services are WCF/ASP.NET Web Services, and the Database SQL 2005.

One of the typical approaches to creating Web Services for this type of system is to use a CRUD type pattern. That is: all methods are based around either Creating, Retrieving, Updating, or Deleting records.  In most usually done on a per-table basis, and means that you’re effectively making the Web Services a HTTP enabled SQL client.

For our situation, this wasn’t really appropriate for a number of reasons, including complex relationships between tables, and a need to reduce the amount of network traffic.

Another concern, although relatively minor, is to reduce the amount of work needed by the Flex team to implement the Web Services. 

Ideally, we wanted to be able to share business objects as widely as possible, to reduce the amount of rework needed by everyone involved in implementing the interfaces.

Therefore we chose to go with task, or semantic based methods, and using the objects as needed by the Flex front-end.  The work of validation, and mapping to appropriate tables would be done by the Web services.

An example of this might be that a Document had many properties, such as Media Items (pictures, video, etc), Tags, Authors, etc.  However, within the database there might be a necessity to track Document Versions, What versions are Live, the relationships between Documents, Document Versions and Media Items. 

Because the objects that I needed to send/receive didn’t match the objects that needed to be saved in the database, I needed to write a lot of “left hand/right hand code”: ServiceDocument.Property =   SQLDocument.Property.  Most of this was fairly simple code to write, but tracking the places where this takes place can be grow to become quite a challenge when the solution grows to dozens of tables.

This is an approximate list of what I need to do to add a property to one table:

• Add the Property to the Service Types
• Add conversion pieces to transpose the Service Type to/from the LINQ to SQL Object equivalents.
• Add the column to the Table in the Database Model for LINQ to SQL
• Add the column to all Stored Procedures in the Database Model which reference this, removing and re-adding them if this means new properties too.  Don’t forget to ensure the return types on the re-added Stored Procedures are set correctly.
• Add the columns to the actual Stored Procedures, update parameters, etc
• Add the column to the actual Table

I can only imagine the Version Control conflict chaos that would ensue if you had several people making these changes concurrently.

I highly recommend grouping changes into a per-table basis, because it can take a while to go through all the additional pieces you have referencing the LINQ to SQL and Service Type object equivilents.

With databases in many organisations, there’s a rule that’s carved in stone:

Thou shalt use Stored Procedures for all table access.

Regardless of your position on this, some clients just don’t give us any other option. The application is never given direct table access, and anything that doesn’t go through a stored procedure just generates errors.  This could be because they’re enforcing access control on the data to a finer level than just table level, or because they need to ensure records are kept of all confidential information.

The picture for LINQ to SQL with this situation appears at first to be rather rosy, through two things:

1. You can assign sprocs that handle inserts, updates, and deletes against your tables.
2. You can directly execute sprocs as methods in your application. So, you can select data out of tables via this method.

Both of these things, are true. However, what doesn’t get mentioned is that LINQ to SQL will still perform reads (using dynamic SQL) from your database if you (for example) try to access children of a relationship, or insert/update rows**. That is: You can’t stop additional queries against the database.

So, as far as I can tell - this means LINQ to SQL, as it stands, cannot be used for these situations. Ofcourse, this doesn’t prevent me from using LINQ to XML, LINQ to Entities, etc - it just means that our ways of executing SQL still have not advanced.

There’s a possible way I think this could be fixed, but it really requires a bit of a rethink as to how LINQ to SQL works - perhaps this could be done as a third party LINQ provider, but really it should be done by Microsoft in LINQ to SQL v.Next.

The way I’d like to see it fixed, would be for someone to create an analysis tool that runs at publish time, that generates Stored Procedures for all the LINQ statements in your code, and then forces the compiled LINQ commands to map through these sprocs.

This would have the benefit of rapid prototyping/development, and being able to still enforce access through sprocs. 

* = Well, there might be a way, but I’ve not yet found it.
** = It does select’s to get the updated column values from, for example, computed columns.

This is the first in (hopefully) a series of quick things I’ve picked up whilst tackling the previously mentioned project. 

So, I have a table something like this:

CREATE TABLE [dbo].[Product](
  [ProductID] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](100) NOT NULL,
   [Price] [int] NOT NULL,
    [LastSaveTimestamp] [datetime] NOT NULL CONSTRAINT [DF_Product_SaveTimestamp]  DEFAULT (getutcdate())
) ON [PRIMARY]

The key here is the default value on the column: LastSaveTimestamp.

If I then try to, say insert a new column into this table, for example using this code:

  DatabaseContext dc = new DatabaseContext();
  Product product = new Product();
  product.Name = “test product”;
  product.Price = 50;
  dc.Products.InsertOnSubmit(product);
  dc.SubmitChanges(System.Data.Linq.ConflictMode.FailOnFirstConflict);

Then I’d get an exception like:

System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM..

The fix is actually really simple - In the table designer / DBML, you need to tell it that the column is auto-generated. Unfortunately this doesn’t seem to be automatically detected. It’s one of a few ‘just plain weird’ situations. 

AzamSharp has the fix details, with a handy-dandy screenshot over on his blog.