Linq to SQL in web services

Quite a while ago, I investigated the use of Linq to SQL in our one web service component that feeds a CMS that we package with out software. In my initial investigations, every time I would send an entity through the web service, I would get a circular reference error. This made sense since each relationship is bidirectional. If I have a family entity that has a relationship to a person entity, that person entity also has a reference to the family entity. This goes on infinitely. Those relationships are nice because you can grab one single person and then get data out of the family entity without having to do any joins or write a separate query. So what do you do?

There are a couple of options.

1. Make the data context serialization unidirectional. This will turn off all relationships. You will have to manually do your joins using Linq when the data reaches the other side of the transaction.

2. Change the parent access modifier of the relationship to Friend (VB) or Internal (C#). This leaves the child relationships intact but removes the parent relationships. There would no longer be a family relationship in the person entity.

I prefer option 2 simply because then I don’t have to rejoin my data every time I fetch it. I don’t need those child to parent relationships because I can build around it by making my web service functions return the family instead of just returning a person, even if I just request the person.

The next issue that I ran into was the updating existing records. Since adding new records takes a detached object and just inserts it as new, that isn’t an issue. The problem comes in the concurrency tracking that Linq to SQL uses. You either time stamp your record, or you allow it to check previous fields for concurrency. We don’t use the time stamp method. The way that most things work in the SQL world is that when you save, you either overwrite what is currently in the table, or you get an concurrency error that makes you input everything over again after getting a refresh from the database. This is not the way we like it. We like a merge scenario where I can make a couple changes to the record and someone else can make some other changes and we the save is made, both our changes make it to the database.

So, how do you fix this? It’s easy with Linq to SQL. It already has the merge saving built in, unlike ADO.Net. (The Entity Framework will do this as well if you set it up right.) Even though Linq to SQL already supports the saving, this won’t work in web services. The problem is that during serialization, the entity loses it’s data context. It’s the data context that actually tracks the changes in the record, not the entity itself. So once the data context loses it’s entity (gets detached), it has to be reattached in order to do the save. The problem with this is that the data context no longer knows what has changed in this entity, so it cannot do any concurrency validation. The key is to use the Attach method and fill in two of it’s parameters, one for current entity and one for the original entity. There are a couple ways you can do this, but I choose to add a base class where I could store the serialized entity (complete with children) and ship it back and forth.

No matter how you slice it, you are going to have the overhead of change tracking eating up bandwidth to and from the web service to the calling application. This can either be incredibly apparent by holding your original values in the session state and just passing it back to the web service, or you can serialize it in the entity object and just pull it out later. I use this method for simplicity. I actually just make a business class (actually, I already had one because all my validation is stored there in a generic, fun sort of way that allows it to just interact with IDataError) and in that business class, I just add a object holder and some functions to set the original value.

Here is a small sample of the base business class:

Imports System.Xml.Serialization

Public Class BaseBusiness

  Private original As Object

  Public Sub SetOriginalValue(ByVal _OriginalValue As Object, ByVal originalType As System.Type)
    Dim sb As New StringBuilder()
    Using strw As New System.IO.StringWriter(sb)
      Dim SXO As New XmlSerializer(originalType)
      SXO.Serialize(strw, _OriginalValue)
      original = sb.ToString()
    End Using
  End Sub

  Public Function HasOriginalValue() As Boolean
    Dim bOriginal As Boolean = False
    If original IsNot Nothing Then
      bOriginal = True
    End If
    Return bOriginal
  End Function

  Public Function GetOriginalValue(ByVal originalType As System.Type) As Object
    If HasOriginalValue() Then
      Dim sXml As String = CStr(original)
      Dim fam As Object
      Using strr As New System.IO.StringReader(sXml)
        Dim SXO As New XmlSerializer(originalType)
        fam = SXO.Deserialize(strr)
        Return (fam)
      End Using
      Return (Nothing)
    End If
  End Function
End Class

Notice the use of System.Type, this allows me to pass in any data type and serialize it on the fly without having to have special code in my entity class. This makes it appropriate for any class I need to track changes in, even in the entity framework if I so desire.

Here is the top level entity class where I use it:

Imports System.Xml.Serialization

Partial Class Family
  Inherits BaseBusiness

  Private Sub OnLoaded()
    OriginalValue = Me
  End Sub

  Public Property OriginalValue() As Family
      Return CType((Me.GetOriginalValue(GetType(Family))), Family)
    End Get
    Set(ByVal value As Family)
      Me.SetOriginalValue(value, GetType(Family))
    End Set
  End Property
End Class

That is the only code that will need to be in the entity class in order for it to work. The best part of this is when the data gets shipped back and forth, it’s always accessible as the original entity, not a serialized XML string.

This was just a quick test I did when investigating this stuff today. Eventually, I am going to try to minimize the code in both classes some more and see if I can even strip out the XML Serialization to string and just store it as binary data.

I <3 Linq

One of the latest technologies to hit .Net recently is Linq. Language Integrated Query allows you to perform a more SQL like syntax on object collections in code. It has been out for quite some time, but I just recently was able to use it in a project. It’s pretty cool stuff and has already saved me a few lines of code. Places where loops would have been neccessary have easily been replaced with a simple where.

One of the concerns most developers had, especially when working with Linq to SQL data classes, was the relative speed at retrieving data from the SQL server. They were pretty right about that. It’s slow. Much slower than even using datasets with table adapters. However, using compiled queries can help.

One thing some people never took into consideration was the relative ease that Developer’s Express users can now use server mode without having to use the proprietary XPO classes. Their XtraGrid’s now support ServerMode which will page SQL queries in windows forms applications. One issue we had were slow VPN clients trying to use a windows form application installed locally and connecting to a remote SQL Server. Until recently we have had to tell users to use terminal services instead. Some forms have to show a relatively large amount of data in a single grid (finding customers, paging through certain data, etc). Over a VPN connection it was just painfully slow. Now combining XtraGrid’s and the Linq Server Mode, everything works as expected over a slow (1.5 MB DSL even) VPN connection.