This post has been replaced by this one : http://www.softlion.com/blogs/post/2009/02/02/Useful-LINQ-extensions.aspx

Yesterday I found what can easily be named THE bug of the year. Even I can named it the biggest bug in the last 4 years. It affects nearly all of our past projects with different degrees of impact.

It is documented in MSDN as a feature in a small note in the description of the SqlConnection.BeginTransaction .NET method (see references below).

Let me explain the context first so you really understand the bug and how to fix it.

Imagine you have a web application created using ASP.NET, and a SQL Server data storage.

To construct a web page, your ASP.NET code will query 3 times data on the SQL server.

The 1st and 2nd queries are executed inside a transaction which runs under a non default isolation level  READ UNCOMMITTED.

The last query is executed alone, completely separated from the previous ones. This query is using the with(READPAST) option in one of its statement.

If you run  your application, you'll see a nice .NET error page telling you that READPAST can not be used in this transaction isolation level (it does not tell you which).

So first you debug your application and you check in which isolation level you are. This is easily done using this query:

select
      transaction_isolation_level,
      last_request_start_time,
      *
from sys.dm_exec_sessions

And you'll find what ? That instead of being the default READ COMMITED isolation level, the query is executed under READ UNCOMMITTED !

Have you guessed why already ? Well it is because of the "feature" of the transaction handling combined with the feature of the SQL connections pooling implemented in ADO.NET.

Connection pooling makes .NET reuse existing SQL connections which is an efficient way to use this network resource.

In our case,  a connection is created by the first query and is used by the whole transaction. When closed by code, the connection is not closed but moved to the connections pool (with a TTL so it won't last forever if not used).

For the third query the code creates a new connection, which the library converts to a 'get the opened connection from my connections pool'.

Now read the note from MSDN :

After a transaction is committed or rolled back, the isolation level of the transaction persists for all subsequent commands that are in autocommit mode (the SQL Server default). This can produce unexpected results, such as an isolation level of REPEATABLE READ persisting and locking other users out of a row. To reset the isolation level to the default (READ COMMITTED), execute the Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED statement, or call SqlConnection..::.BeginTransaction followed immediately by SqlTransaction..::.Commit. For more information on SQL Server isolation levels, see "Isolation Levels in the Database Engine" in SQL Server 2005 Books Online.

What happens here is that the first connection is returned to the pool but the transaction isolation level is not reset to its default value. So our 3rd query will execute and fail unexpectedly because the with(READPAST) option is not compatible with an isolation level equals to read uncommited.

How to fix this ?

Before closing your connection, after a transaction where you know you change the default isolation level, just execute this code wich resets it to the default READ COMMITED or wathever you set to be the default:

myConnection.BeginTransaction().Commit();

References:

SqlConnection.BeginTransaction .NET method and the note explaining the "feature".

.NET SQL connection pooling (ADO.NET 2.0)

A modern way to put a list of IDs in a stored procedure parameter is to type the parameter as xml (instead of the conventional CSV like varchar). With the nodes() method you can inner join directly the xml 'table' with one of your database table without first filling a temporary table. And building the xml fragment with LINQ TO XML (ie: XDocument class) is a piece of cake.

If these ids are C# Guids (or tsql uniqueidentifier) and you optimize your query (like msdn tells you), you may fall in the uppercase/lowercase trap.

-- Optimized query (working)
select * from MyGames a
left join MyUsers b on a.Winners.exist('//winner[upper-case(@userId)=sql:column("b.UserId")]')=1

In this XQuery optimized comparison, T-SQL converts a uniqueidentifier to an upper case string. The Winners xml column has been filled using C# XDocument, which uses by default the C# method Guid.ToString() to convert a guid ... to lowercase.

So we have SQL converting by default uniqueidentifier to uppercase, and C# converting by default Guid to lowercase.
If you rely on defaults any comparison done between uniqueidentifier in this cutting edge xml/sql technology then it will not work.

Notes:
The xml type is a new native type available since SQL Server 2005. I use it essentially to simplify the model for 1-N relations by storing ids of the N table in a field of the 1 table instead of creating this dummy relationship table I always hated. This without giving up performance, as long as you setup the new special xml indexes for the xml typed field and provide the XSD schema to SQL. See references below for the full documentation.

A uniqueidentifier is a 16 bytes (128 bits) value which is used by the default implementation of User/Membership provider of the ASP.NET framework for all unique IDs.

With the xml data type, SQL 2005 introduced "methods" that can be used to efficiently query the xml data and use it as if it was a normal table : exists(..), value(..), ...
A common parameter for these methods is an XQuery string, where XQuery is a SQL Server specific language based on XPath, with at least 2 useful efficients extensions in the sql namespace:
sql:variable(..) which is replaced by the value of an existing variable
sql:column(..) which is replaced by the value of any table row available in the t-sql query

References:
SQL Server xml data type
XML indexes
XML type query methods
XQuery language reference

La conférence pour développeurs professionnels de Microsoft s'est terminée le 30 octobre dernier, avec des informations sur le C#4 qui va bentôt sortir et même la version suivante (session de Anders Hejlsberg).

Résumé:

C#1 a été initié en 1998, avec la création du MSIL et de la gestion de la mémoire automatique. La version 1.1 a apporté des modifications majeures pour rendre le language utilisable.

C#2 a corrigé toutes les erreurs du C#1 et du MSIL1 en apportant le plus qui existait encore dans le C++ à savoir les generics (templates en C++).

C#3 (et 3.5) a amélioré le compilateur pour supporter LINQ, une syntaxe ressemblant au SQL pour manipuler facilement des listes. C'est la première fois qu'un langage réussit cette prouesse et je crois pour ma part que ca a été une avancée majeure dans les languages de développements qui met à la traine Java et C++. LINQ intervient également dans une stratégie d'optimisation automatique du logiciel pour le multicore. Le 3.5 intègre également les fonctions anonymes qui ont fait la force du javascript et du PHP, et le type delegate qui représente une fonction typée (très utilisé en C++ sous forme de pointeur typé).

C#4, avec Visual Studo 2010, pompe encore quelques goodies à Javascript avec le nouveau type "dynamic" (pratique pour COM) qui représente un objet non typé (eh oui ca peux faire rire !) sur lequel on peux appeler des propriétés ou méthodes non connues à la compilation. Ecrire des macros Excel ou Word en C# ou .NET va peux être devenir une réalité et enfin remplacer le très vieux VBA. Dommage que cela arrive si tard ! C#4 ajoute également les paramètres de fonction optionnels (merci C++) et nommés (extension pratique qui manque à C++), ainsi qu'une amélioration des generics et des delegates utile au compilateur.

Enfin la version suivante (C# 4.5 ?) devrait intégrer le "Compiler as a Service" - une API pour utiliser le compilateur dans le code. Ca permet d'exécuter des bouts de code dynamiquement comme pour la fonction Eval du javascript. Ca va aussi permettre de poubelliser/remplacer le language bizarre de PowerShell. Bon ok c'était déjà possible avec la version 1.1, mais la ca devrait être encore plus facile.

Ex d'objet dynamic:
dynamic monObjet1 = { Code=1, Name="Parapluie" };
dynamic monObjet2 = { Code=2, Name="Casquette" };
DisplayObject( monObjet1 );
DisplayObject( monObjet2 );
...
void DisplayObject( dynamic o )
{
  Console.WriteLine( String.Format( "Code: {0}, Name: {1}", o.Code, o.Name ) );
}

Ex de fonction avec paramètres optionnels : (existe depuis ... 15ans dans C++ ?)

public void SaveAs( string fileName, Encoding encoding = Encoding.UTF8 ) { ... }

Ex de fonctions avec paramètres nommés: (n'existe pas dans C++)

SaveAs( fileName: "monFichier.xml", encoding: Encoding.ASCII );

Référence:

La vidéo de la session sur C#4

Visual Studio 2010 et .NET Framework 4 CTP

Un résumé des sessions du PDC par Thomas Lebrun