Gregory Kornblum - Tensai Labs

Well, lately as I have been converting my projects over to Visual Studio 2008 and .NET 3.5 framework I have been putting LINQ to SQL to use. It is a great ORM (object relational mapping) compared to its competition, nHibernate and ORM.NET. However the use of it in Visual Studio 2008 proves that Microsoft jumped the gun and released an incomplete ORM solution.

I have two reasons for this. The first and most annoying is the lack of support for temporary tables. So if you have a stored procedure which returns its results from a temporary table it is unable to decipher the type of result output. Instead of getting a ISingleResult you simply get an integer set to zero.

The method for dealing with this situation it to manually correct the mapping by right clicking the LINQ to SQL class file, choose “open with…” and select “XML Editor”. Now you are able to make the changes I describe below.

After dragging your stored procedure which uses temporary tables from the server explorer to the the LINQ to SQL class and opening it as XML as described above you will see the mapping is configured like the snippet below.

<Function Name=“dbo.spUsesTempTables“ Method=“spUsesTempTables“>

<Parameter Name=“uid“ Parameter=“uid“ Type=“System.Int32“ DbType=“Int“ /><

Return Type=“System.Int32“ />

</Function>

You will need to replace <Return Type=“System.Int32” /> as shown in the snippet below.

<Function Name=“dbo.spUsesTempTables“ Method=“spUsesTempTables“>

<Parameter Name=“uid“ Parameter=“uid“ Type=“System.Int32“ DbType=“Int“ /><

ElementType Name=“spUsesTempTables“>

<Column Name=“FieldName1“ Type=“System.String“ DbType=“VarChar(200) NOT NULL“ CanBeNull=“false“ /><

Column Name=“FieldName2“ Type=“System.String“ DbType=“VarChar(200) NOT NULL“ CanBeNull=“false“ />

<Column Name=“FieldName3“ Type=“System.String“ DbType=“VarChar(7000) NULL“ CanBeNull=“true“ /></

ElementType>

</Function>

Not extremly difficult yet it’s enough to be annoying depending on scenarios which result in causing this issue.

The other problem is that, say you are adding another field to be returned from a stored procedure that is mapped via LINQ to SQL, there is no way to automatically refresh the mapping to account for the changed. You must remove the mapping and recreate it. Again not extremly difficult, however far from elegant.

Sadly, even Visual Studio 2008 SP1 deas not address either of these issues. To me LINQ to SQL was the only redeeming feature of LINQ that made me even consider its use. However it shows that they did not fully build the architecture for this properly and aspects such as the ones I mention fell through the cracks.

If you have any input on this topic please comment below.