Dixin's Blog

My new car at Microsoft Building 35

Dixin — Mon, 06 Jun 2011 05:04:00 GMT

I am so excited to have it. These photos were taken when it is parked at Microsoft Building 35:

Understanding LINQ to SQL (11) Performance

Dixin — Mon, 31 Jan 2011 21:00:00 GMT

[LINQ via C# series]

LINQ to SQL has a lot of great features like

strong typing
query compilation
deferred execution
declarative paradigm

etc., which are very productive. Of course, these cannot be free, and one price is the performance.

O/R mapping overhead

Because LINQ to SQL is based on O/R mapping, one obvious overhead is, data changing usually requires data retrieving:

private static void UpdateProductUnitPrice(int id, decimal unitPrice)
{
    using (NorthwindDataContext database = new NorthwindDataContext())
    {
        Product product = database.Products.Single(item => item.ProductID == id); // SELECT...
        product.UnitPrice = unitPrice; // UPDATE...
        database.SubmitChanges();
    }
}

Before updating an entity, that entity has to be retrieved by an extra SELECT query. This is slower than direct data update via ADO.NET:

private static void UpdateProductUnitPrice(int id, decimal unitPrice)
{
    using (SqlConnection connection = new SqlConnection(
        "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True"))
    using (SqlCommand command = new SqlCommand(
        @"UPDATE [dbo].[Products] SET [UnitPrice] = @UnitPrice WHERE [ProductID] = @ProductID",
        connection))
    {
        command.Parameters.Add("@ProductID", SqlDbType.Int).Value = id;
        command.Parameters.Add("@UnitPrice", SqlDbType.Money).Value = unitPrice;

        connection.Open();
        command.Transaction = connection.BeginTransaction();
        command.ExecuteNonQuery(); // UPDATE...
        command.Transaction.Commit();
    }
}

The above imperative code specifies the “how to do” details with better performance.

For the same reason, some articles from Internet insist that, when updating data via LINQ to SQL, the above declarative code should be replaced by:

private static void UpdateProductUnitPrice(int id, decimal unitPrice)
{
    using (NorthwindDataContext database = new NorthwindDataContext())
    {
        database.ExecuteCommand(
            "UPDATE [dbo].[Products] SET [UnitPrice] = {0} WHERE [ProductID] = {1}",
            id, 
            unitPrice);
    }
}

Or just create a stored procedure:

CREATE PROCEDURE [dbo].[UpdateProductUnitPrice]
(
    @ProductID INT,
    @UnitPrice MONEY
)
AS
BEGIN
    BEGIN TRANSACTION 
    UPDATE [dbo].[Products] SET [UnitPrice] = @UnitPrice WHERE [ProductID] = @ProductID
    COMMIT TRANSACTION
END

and map it as a method of NorthwindDataContext (explained in this post):

private static void UpdateProductUnitPrice(int id, decimal unitPrice)
{
    using (NorthwindDataContext database = new NorthwindDataContext())
    {
        database.UpdateProductUnitPrice(id, unitPrice);
    }
}

As a normal trade off for O/R mapping, a decision has to be made between performance overhead and programming productivity according to the case. In a developer’s perspective, if O/R mapping is chosen, I consistently choose the declarative LINQ code, unless this kind of overhead is unacceptable.

Data retrieving overhead

After talking about the O/R mapping specific issue. Now look into the LINQ to SQL specific issues, for example, performance in the data retrieving process. The previous post has explained that the SQL translating and executing is complex. Actually, the LINQ to SQL pipeline is similar to the compiler pipeline. It consists of about 15 steps to translate an C# expression tree to SQL statement, which can be categorized as:

Convert: Invoke SqlProvider.BuildQuery() to convert the tree of Expression nodes into a tree of SqlNode nodes;

Bind: Used visitor pattern to figure out the meanings of names according to the mapping info, like a property for a column, etc.;

Flatten: Figure out the hierarchy of the query;

Rewrite: for SQL Server 2000, if needed

Reduce: Remove the unnecessary information from the tree.

Parameterize

Format: Generate the SQL statement string;

Parameterize: Figure out the parameters, for example, a reference to a local variable should be a parameter in SQL;

Materialize: Executes the reader and convert the result back into typed objects.

So for each data retrieving, even for data retrieving which looks simple:

private static Product[] RetrieveProducts(int productId)
{
    using (NorthwindDataContext database = new NorthwindDataContext())
    {
        return database.Products.Where(product => product.ProductID == productId)
                                .ToArray();
    }
}

LINQ to SQL goes through above steps to translate and execute the query. Fortunately, there is a built-in way to cache the translated query.

Compiled query

When such a LINQ to SQL query is executed repeatedly, The CompiledQuery can be used to translate query for one time, and execute for multiple times:

internal static class CompiledQueries
{
    private static readonly Func<NorthwindDataContext, int, Product[]> _retrieveProducts = 
        CompiledQuery.Compile((NorthwindDataContext database, int productId) =>
            database.Products.Where(product => product.ProductID == productId).ToArray());

    internal static Product[] RetrieveProducts(
        this NorthwindDataContext database, int productId)
    {
        return _retrieveProducts(database, productId);
    }
}

The new version of RetrieveProducts() gets better performance, because only when _retrieveProducts is first time invoked, it internally invokes SqlProvider.Compile() to translate the query expression. And it also uses lock to make sure translating once in multi-threading scenarios.

Static SQL / stored procedures without translating

Another way to avoid the translating overhead is to use static SQL or stored procedures, just as the above examples. Because this is a functional programming series, this article not dive into. For the details, Scott Guthrie already has some excellent articles:

Data changing overhead

By looking into the data updating process, it also needs a lot of work:

Begins transaction
Processes the changes (ChangeProcessor)
- Walks through the objects to identify the changes
- Determines the order of the changes
- Executes the changings
  - LINQ queries may be needed to execute the changings, like the first example in this article, an object needs to be retrieved before changed, then the above whole process of data retrieving will be went through
  - If there is user customization, it will be executed, for example, a table’s INSERT / UPDATE / DELETE can be customized in the O/R designer

It is important to keep these overhead in mind.

Bulk deleting / updating

Another thing to be aware is the bulk deleting:

private static void DeleteProducts(int categoryId)
{
    using (NorthwindDataContext database = new NorthwindDataContext())
    {
        database.Products.DeleteAllOnSubmit(
            database.Products.Where(product => product.CategoryID == categoryId));
        database.SubmitChanges();
    }
}

The expected SQL should be like:

BEGIN TRANSACTION 
exec sp_executesql N'DELETE FROM [dbo].[Products] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=9
COMMIT TRANSACTION

Hoverer, as fore mentioned, the actual SQL is to retrieving the entities, and then delete them one by one:

-- Retrieves the entities to be deleted:
exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=9

-- Deletes the retrieved entities one by one:
BEGIN TRANSACTION 
exec sp_executesql N'DELETE FROM [dbo].[Products] WHERE ([ProductID] = @p0) AND ([ProductName] = @p1) AND ([SupplierID] IS NULL) AND ([CategoryID] = @p2) AND ([QuantityPerUnit] IS NULL) AND ([UnitPrice] = @p3) AND ([UnitsInStock] = @p4) AND ([UnitsOnOrder] = @p5) AND ([ReorderLevel] = @p6) AND (NOT ([Discontinued] = 1))',N'@p0 int,@p1 nvarchar(4000),@p2 int,@p3 money,@p4 smallint,@p5 smallint,@p6 smallint',@p0=78,@p1=N'Optimus Prime',@p2=9,@p3=$0.0000,@p4=0,@p5=0,@p6=0
exec sp_executesql N'DELETE FROM [dbo].[Products] WHERE ([ProductID] = @p0) AND ([ProductName] = @p1) AND ([SupplierID] IS NULL) AND ([CategoryID] = @p2) AND ([QuantityPerUnit] IS NULL) AND ([UnitPrice] = @p3) AND ([UnitsInStock] = @p4) AND ([UnitsOnOrder] = @p5) AND ([ReorderLevel] = @p6) AND (NOT ([Discontinued] = 1))',N'@p0 int,@p1 nvarchar(4000),@p2 int,@p3 money,@p4 smallint,@p5 smallint,@p6 smallint',@p0=79,@p1=N'Bumble Bee',@p2=9,@p3=$0.0000,@p4=0,@p5=0,@p6=0
-- ...
COMMIT TRANSACTION

And the same to the bulk updating. This is really not effective and need to be aware. Here is already some solutions from the Internet, like this one. The idea is wrap the above SELECT statement into a INNER JOIN:

exec sp_executesql N'DELETE [dbo].[Products] FROM [dbo].[Products] AS [j0] 
INNER JOIN (   
SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[CategoryID] = @p0) AS [j1] 
ON ([j0].[ProductID] = [j1].[[Products])', -- The Primary Key
N'@p0 int',@p0=9

Query plan overhead

The last thing is about the SQL Server query plan. Before .NET 4.0, LINQ to SQL has an issue (not sure if it is a bug). LINQ to SQL internally uses ADO.NET, but it does not set the SqlParameter.Size for a variable-length argument, like argument of NVARCHAR type, etc. So for two queries with the same SQL but different argument length:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    database.Products.Where(product => product.ProductName == "A")
        .Select(product => product.ProductID).ToArray();

    // The same SQL and argument type, different argument length.
    database.Products.Where(product => product.ProductName == "AA")
        .Select(product => product.ProductID).ToArray();
}

Pay attention to the argument length in the translated SQL:

exec sp_executesql N'SELECT [t0].[ProductID]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductName] = @p0',N'@p0 nvarchar(1)',@p0=N'A'

exec sp_executesql N'SELECT [t0].[ProductID]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductName] = @p0',N'@p0 nvarchar(2)',@p0=N'AA'

Here is the overhead: The first query’s query plan cache is not reused by the second one:

SELECT sys.syscacheobjects.cacheobjtype, sys.dm_exec_cached_plans.usecounts, sys.syscacheobjects.[sql] FROM sys.syscacheobjects
INNER JOIN sys.dm_exec_cached_plans
ON sys.syscacheobjects.bucketid = sys.dm_exec_cached_plans.bucketid;

They actually use different query plans. Again, pay attention to the argument length in the [sql] column (@p0 nvarchar(2) / @p0 nvarchar(1)).

Fortunately, in .NET 4.0 this is fixed:

internal static class SqlTypeSystem
{
    private abstract class ProviderBase : TypeSystemProvider
    {
        protected int? GetLargestDeclarableSize(SqlType declaredType)
        {
            SqlDbType sqlDbType = declaredType.SqlDbType;
            if (sqlDbType <= SqlDbType.Image)
            {
                switch (sqlDbType)
                {
                    case SqlDbType.Binary:
                    case SqlDbType.Image:
                        return 8000;
                }

                return null;
            }

            if (sqlDbType == SqlDbType.NVarChar)
            {
                return 4000; // Max length for NVARCHAR.
            }

            if (sqlDbType != SqlDbType.VarChar)
            {
                return null;
            }

            return 8000;
        }
    }
}

In this above example, the translated SQL becomes:

exec sp_executesql N'SELECT [t0].[ProductID]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductName] = @p0',N'@p0 nvarchar(4000)',@p0=N'A'

exec sp_executesql N'SELECT [t0].[ProductID]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[ProductName] = @p0',N'@p0 nvarchar(4000)',@p0=N'AA'

So that they reuses the same query plan cache:

Now the [usecounts] column is 2.

A DirectoryCatalog class for Silverlight MEF (Managed Extensibility Framework)

Dixin — Sat, 29 Jan 2011 05:37:00 GMT

In the MEF (Managed Extension Framework) for .NET, there are useful ComposablePartCatalog implementations in System.ComponentModel.Composition.dll, like:

System.ComponentModel.Composition.Hosting.AggregateCatalog
System.ComponentModel.Composition.Hosting.AssemblyCatalog
System.ComponentModel.Composition.Hosting.DirectoryCatalog
System.ComponentModel.Composition.Hosting.TypeCatalog

While in Silverlight, there is a extra System.ComponentModel.Composition.Hosting.DeploymentCatalog. As a wrapper of AssemblyCatalog, it can load all assemblies in a XAP file in the web server side. Unfortunately, in silverlight there is no DirectoryCatalog to load a folder.

Background

There are scenarios that Silverlight application may need to load all XAP files in a folder in the web server side, for example:

If the Silverlight application is extensible and supports plug-ins, there would be something like a Plugins folder in the web server, and each pluin would be an individual XAP file in the folder. In this scenario, after the application is loaded and started up, it would like to load all XAP files in Plugins folder.
If the aplication supports themes, there would be something like a Themes folder, and each theme would be an individual XAP file too. The application would also need to load all XAP files in Themes.

It is useful if we have a DirectoryCatalog:

DirectoryCatalog catalog = new DirectoryCatalog("/Plugins");
catalog.DownloadCompleted += (sender, e) => { };
catalog.DownloadAsync();

Obviously, the implementation of DirectoryCatalog is easy. It is just a collection of DeploymentCatalog class.

Retrieve file list from a directory

Of course, to retrieve file list from a web folder, the folder’s “Directory Browsing” feature must be enabled:

So when the folder is requested, it responses a list of its files and folders:

This is nothing but a simple HTML page:

<html>
    <head>
        <title>localhost - /Folder/</title>
    </head>
    <body>
        <h1>localhost - /Folder/</h1>
        <hr>
        <pre>
            <a href="/">[To Parent Directory]</a><br>
            <br>
            1/3/2011  7:22 PM   185 <a href="/Folder/File.txt">File.txt</a><br>
            1/3/2011  7:22 PM   &lt;dir&gt; <a href="/Folder/Folder/">Folder</a><br>
        </pre>
        <hr>
    </body>
</html>

For the ASP.NET Deployment Server of Visual Studio, directory browsing is enabled by default:

The HTML <Body> is almost the same:

<body bgcolor="white">
    <h2><i>Directory Listing -- /ClientBin/</i></h2>
    <hr width="100%" size="1" color="silver">
    <pre>
        <a href="/">[To Parent Directory]</a>
        Thursday, January 27, 2011 11:51 PM 282,538 <a href="Test.xap">Test.xap</a>
        Tuesday, January 04, 2011 02:06 AM  &lt;dir&gt; <a href="TestFolder/">TestFolder</a>
    </pre>
    <hr width="100%" size="1" color="silver">
    <b>Version Information:</b>&nbsp;ASP.NET Development Server 10.0.0.0 
</body>

The only difference is, IIS’s links start with slash, but here the links do not.

Here one way to get the file list is read the href attributes of the links:

[Pure]
private IEnumerable<Uri> GetFilesFromDirectory(string html)
{
    Contract.Requires(html != null);
    Contract.Ensures(Contract.Result<IEnumerable<Uri>>() != null);

    return new Regex(
                    "<a href=\"(?<uriRelative>[^\"]*)\">[^<]*</a>",
                    RegexOptions.IgnoreCase | RegexOptions.CultureInvariant)
                .Matches(html)
                .OfType<Match>()
                .Where(match => match.Success)
                .Select(match => match.Groups["uriRelative"].Value)
                .Where(uriRelative => uriRelative.EndsWith(".xap", StringComparison.Ordinal))
                .Select(uriRelative =>
                    {
                        Uri baseUri = this.Uri.IsAbsoluteUri
                                            ? this.Uri
                                            : new Uri(Application.Current.Host.Source, this.Uri);
                        uriRelative = uriRelative.StartsWith("/", StringComparison.Ordinal)
                                            ? uriRelative
                                            : (baseUri.LocalPath.EndsWith("/", StringComparison.Ordinal)
                                                    ? baseUri.LocalPath + uriRelative
                                                    : baseUri.LocalPath + "/" + uriRelative);
                        return new Uri(baseUri, uriRelative);
                    });
}

Please notice the folders’ links end with a slash. They are filtered by the second Where() query.

The above method can find files’ URIs from the specified IIS folder, or ASP.NET Deployment Server folder while debugging. To support other formats of file list, a constructor is needed to pass into a customized method:

/// <summary>
/// Initializes a new instance of the <see cref="T:System.ComponentModel.Composition.Hosting.DirectoryCatalog" /> class with <see cref="T:System.ComponentModel.Composition.Primitives.ComposablePartDefinition" /> objects based on all the XAP files in the specified directory URI.
/// </summary>
/// <param name="uri">
/// URI to the directory to scan for XAPs to add to the catalog.
/// The URI must be absolute, or relative to <see cref="P:System.Windows.Interop.SilverlightHost.Source" />.
/// </param>
/// <param name="getFilesFromDirectory">
/// The method to find files' URIs in the specified directory.
/// </param>
public DirectoryCatalog(Uri uri, Func<string, IEnumerable<Uri>> getFilesFromDirectory)
{
    Contract.Requires(uri != null);

    this._uri = uri;
    this._getFilesFromDirectory = getFilesFromDirectory ?? this.GetFilesFromDirectory;
    this._webClient = new Lazy<WebClient>(() => new WebClient());

    // Initializes other members.
}

When the getFilesFromDirectory parameter is null, the above GetFilesFromDirectory() method will be used as default.

Download the directory’s XAP file list

Now a public method can be created to start the downloading:

/// <summary>
/// Begins downloading the XAP files in the directory.
/// </summary>
public void DownloadAsync()
{
    this.ThrowIfDisposed();

    if (Interlocked.CompareExchange(ref this._state, State.DownloadStarted, State.Created) == 0)
    {
        this._webClient.Value.OpenReadCompleted += this.HandleOpenReadCompleted;
        this._webClient.Value.OpenReadAsync(this.Uri, this);
    }
    else
    {
        this.MutateStateOrThrow(State.DownloadCompleted, State.Initialized);
        this.OnDownloadCompleted(new AsyncCompletedEventArgs(null, false, this));
    }
}

Here the HandleOpenReadCompleted() method is invoked when the file list HTML is downloaded.

Download all XAP files

After retrieving all files’ URIs, the next thing becomes even easier. HandleOpenReadCompleted() just uses built in DeploymentCatalog to download the XAPs, and aggregate them into one AggregateCatalog:

private void HandleOpenReadCompleted(object sender, OpenReadCompletedEventArgs e)
{
    Exception error = e.Error;
    bool cancelled = e.Cancelled;
    if (Interlocked.CompareExchange(ref this._state, State.DownloadCompleted, State.DownloadStarted) !=
        State.DownloadStarted)
    {
        cancelled = true;
    }

    if (error == null && !cancelled)
    {
        try
        {
            using (StreamReader reader = new StreamReader(e.Result))
            {
                string html = reader.ReadToEnd();
                IEnumerable<Uri> uris = this._getFilesFromDirectory(html);

                Contract.Assume(uris != null);

                IEnumerable<DeploymentCatalog> deploymentCatalogs =
                    uris.Select(uri => new DeploymentCatalog(uri));
                deploymentCatalogs.ForEach(
                    deploymentCatalog =>
                    {
                        this._aggregateCatalog.Catalogs.Add(deploymentCatalog);
                        deploymentCatalog.DownloadCompleted += this.HandleDownloadCompleted;
                    });
                deploymentCatalogs.ForEach(deploymentCatalog => deploymentCatalog.DownloadAsync());
            }
        }
        catch (Exception exception)
        {
            error = new InvalidOperationException(Resources.InvalidOperationException_ErrorReadingDirectory, exception);
        }
    }

    // Exception handling.
}

In HandleDownloadCompleted(), if all XAPs are downloaded without exception, OnDownloadCompleted() callback method will be invoked.

private void HandleDownloadCompleted(object sender, AsyncCompletedEventArgs e)
{
    if (Interlocked.Increment(ref this._downloaded) == this._aggregateCatalog.Catalogs.Count)
    {
        this.OnDownloadCompleted(e);
    }
}

Exception handling

Whether this DirectoryCatelog can work only if the directory browsing feature is enabled. It is important to inform caller when directory cannot be browsed for XAP downloading.

private void HandleOpenReadCompleted(object sender, OpenReadCompletedEventArgs e)
{
    Exception error = e.Error;
    bool cancelled = e.Cancelled;
    if (Interlocked.CompareExchange(ref this._state, State.DownloadCompleted, State.DownloadStarted) !=
        State.DownloadStarted)
    {
        cancelled = true;
    }

    if (error == null && !cancelled)
    {
        try
        {
            // No exception thrown when browsing directory. Downloads the listed XAPs.
        }
        catch (Exception exception)
        {
            error = new InvalidOperationException(Resources.InvalidOperationException_ErrorReadingDirectory, exception);
        }
    }

    WebException webException = error as WebException;
    if (webException != null)
    {
        HttpWebResponse webResponse = webException.Response as HttpWebResponse;
        if (webResponse != null)
        {
            // Internally, WebClient uses WebRequest.Create() to create the WebRequest object. Here does the same thing.
            WebRequest request = WebRequest.Create(Application.Current.Host.Source);

            Contract.Assume(request != null);

            if (request.CreatorInstance == WebRequestCreator.ClientHttp &&
                // Silverlight is in client HTTP handling, all HTTP status codes are supported.
                webResponse.StatusCode == HttpStatusCode.Forbidden)
            {
                // When directory browsing is disabled, the HTTP status code is 403 (forbidden).
                error = new InvalidOperationException(
                    Resources.InvalidOperationException_ErrorListingDirectory_ClientHttp, webException);
            }
            else if (request.CreatorInstance == WebRequestCreator.BrowserHttp &&
                // Silverlight is in browser HTTP handling, only 200 and 404 are supported.
                webResponse.StatusCode == HttpStatusCode.NotFound)
            {
                // When directory browsing is disabled, the HTTP status code is 404 (not found).
                error = new InvalidOperationException(
                    Resources.InvalidOperationException_ErrorListingDirectory_BrowserHttp, webException);
            }
        }
    }

    this.OnDownloadCompleted(new AsyncCompletedEventArgs(error, cancelled, this));
}

Please notice Silverlight 3+ application can work in either client HTTP handling, or browser HTTP handling. One difference is:

In browser HTTP handling, only HTTP status code 200 (OK) and 404 (not OK, including 500, 403, etc.) are supported
In client HTTP handling, all HTTP status code are supported

So in above code, exceptions in 2 modes are handled differently.

Conclusion

Here is the whole DirectoryCatelog’s looking:

Please click here to download the source code, a simple unit test is included. This is a rough implementation. And, for convenience, some design and coding are just following the built in AggregateCatalog class and Deployment class. Please feel free to modify the code, and please kindly tell me if any issue is found.

Microsoft Chinese Spring Festival Celebration (2)

Dixin — Tue, 25 Jan 2011 08:59:00 GMT

Qi Lu, Microsoft President:

Made in Taiwan:

Amazing Microsoft Bing team:

Chinese Kungfu:

Microsoft rocks:

Rap with my soul: I grow up with Microsoft.

Microsoft Chinese Spring Festival Celebration (1)

Dixin — Mon, 24 Jan 2011 08:46:00 GMT

Last night there was a big party in Redmond: Microsoft Spring Festival Celebration. It is organized by Chime (an employee network group in Microsoft), and it is also the biggest Chinese new year celebration in Seattle area. Thousands of people attended the party, including Steve Ballmer. I also brought my powerful Nikon D700 there.

Before the grand celebration show:

Greetings from Microsoft:

The opening, Chinese movie medley:

Microsoft MCs:

Talk Show: Microsoft, Welcome to Beijing!

Bing!

Dancing, happy new year:

Greetings to everyone:

Harry Shum, Microsoft Vice President:

Next is Steve Baller, Microsoft CEO:

I have some lucky money for you:

You want this red envelope?

Come and get it!!!

Come on!!!

He was moving so fast, and the photo is blurred:

I have red envelopes for every one:

Happy Chinese new year!!!

That’s me, hiding in the front row :)

Click here to view Part 2.

The Humor of Silverlight

Dixin — Sat, 11 Dec 2010 04:35:00 GMT

Silverlight must be kidding.

Arrival to Redmond

Dixin — Thu, 28 Oct 2010 23:47:00 GMT

In this year, I was rewarded as an MVP on C# of Year 2010; at the same time, 3 different groups of Microsoft offered me 3 SDEII positions, and I made my choice. Now, with the help of Microsoft, I just relocated from China to Redmond.

2 years ago, when I left Redmond, I decided that I’ll be back. Now everything just looks the same as 2 years ago. What a familiar place!

As a great company, Microsoft also offers me very nice temp housing. It is an apartment 2-minutes-walk away from my office, and it is free for 60 days!

The apartment is on the 2nd floor:

The living room:

The dining room:

The balcony:

The master bedroom:

The storage room in the master bedroom:

The second bedroom:

The laundry room:

The left one is the dryer, and the right one is the washer.

The utility room:

The bathroom is very nice and clean:

The kitchen:

Microsoft Most Valuable Professional Kit Unboxing

Dixin — Thu, 28 Oct 2010 00:54:09 GMT

I am very happy to receive the Microsoft Most Valuable Professional Kit:

The box is mailed from Redmond:

Ellipses (…) In UI Command Text

Dixin — Sat, 26 Jun 2010 15:44:09 GMT

Some times, command text is followed by ellipsis (…) or not:

For years, many developers told me that ellipses mean a new window or dialog will pop up. For example, here:

Clicking New / Save / Exit will not pop up new window or dialog;
Clicking Open… / Save As… / Page Setup… / Print… will pop up new window or dialog.

But this is not correct. Take a look at About Notepad.

Recently, someone argues about this old topic again. I think it is time to copy something from latest Windows User Experience Interaction Guidelines, and save to my blog. So next time when some other people are confused, I can just send a link to them.

Page 10, Top Guidelines Violations:

Ellipses mean incompleteness. Use ellipses in UI text as follows:

Commands. Indicate that a command needs additional information. Don’t use an ellipsis whenever an action displays another window—only when additional information is required. Commands whose implicit verb is to show another window don’t take an ellipsis, such as Advanced, Help, Options, Properties, or Settings.

Page 236, Menus:

Using ellipses

While menu commands are used for immediate actions, more information might be needed to perform the action. Indicate a command that needs additional information (including a confirmation) by adding an ellipsis at the end of the label.

In this example, the Print... command displays a Print dialog box to gather more information.

Proper use of ellipses is important to indicate that users can make further choices before performing the action, or even cancel the action entirely. The visual cue offered by an ellipsis allows users to explore your software without fear.

This doesn’t mean you should use an ellipsis whenever an action displays another window—only when additional information is required to perform the action. For example, the commands About, Advanced, Help, Options, Properties, and Settings must display another window when clicked, but don’t require additional information from the user. Therefore they don’t need ellipses.

In case of ambiguity (for example, the command label lacks a verb), decide based on the most likely user action. If simply viewing the window is a common action, don’t use an ellipsis.

Correct:
More colors...
Version information

In the first example, users are most likely going to choose a color, so using an ellipses is correct. In the second example, users are most likely going to view the version information, making ellipses unnecessary.

Note: When determining if a menu command needs an ellipsis, don’t use the need to elevate privileges as a factor. Elevation isn’t information needed to perform a command (rather, it’s for permission) and the need to elevate is indicated with the security shield.
Labels

There are similar description in the other sections, like Buttons, Toolbars, etc.

A Snapshot Of ASP.NET Homepage

Dixin — Thu, 10 Jun 2010 16:30:00 GMT

First time to appear on www.asp.net homepage as headline!

A ToDynamic() Extension Method For Fluent Reflection

Dixin — Mon, 07 Jun 2010 15:19:00 GMT

Recently I needed to demonstrate some code with reflection, but I felt it inconvenient and tedious. To simplify the reflection coding, I created a ToDynamic() extension method. The source code can be downloaded from here.

Problem

One example for complex reflection is in LINQ to SQL. The DataContext class has a property Privider, and this Provider has an Execute() method, which executes the query expression and returns the result. Assume this Execute() needs to be invoked to query SQL Server database, then the following code will be expected:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    // Constructs the query.
    IQueryable<Product> query = database.Products.Where(product => product.ProductID > 0)
                                                 .OrderBy(product => product.ProductName)
                                                 .Take(2);

    // Executes the query. Here reflection is required,
    // because Provider, Execute(), and ReturnValue are not public members. The following code cannot compile.
    IEnumerable<Product> results = database.Provider.Execute(query.Expression).ReturnValue;

    // Processes the results. 
    foreach (Product product in results)
    {
        Console.WriteLine("{0}, {1}", product.ProductID, product.ProductName);
    }
}

Of course, this code cannot compile. And, no one wants to write code like this. Again, this is just an example of complex reflection.

using (NorthwindDataContext database = new NorthwindDataContext())
{
    // Constructs the query.
    IQueryable<Product> query = database.Products.Where(product => product.ProductID > 0)
                                                 .OrderBy(product => product.ProductName)
                                                 .Take(2);

    // database.Provider
    PropertyInfo providerProperty = database.GetType().GetProperty(
        "Provider", BindingFlags.NonPublic | BindingFlags.GetProperty | BindingFlags.Instance);
    object provider = providerProperty.GetValue(database, null);

    // database.Provider.Execute(query.Expression)
    // Here GetMethod() cannot be directly used,
    // because Execute() is a explicitly implemented interface method.
    Assembly assembly = Assembly.Load("System.Data.Linq");
    Type providerType = assembly.GetTypes().SingleOrDefault(
        type => type.FullName == "System.Data.Linq.Provider.IProvider");
    InterfaceMapping mapping = provider.GetType().GetInterfaceMap(providerType);
    MethodInfo executeMethod = mapping.InterfaceMethods.Single(method => method.Name == "Execute");
    IExecuteResult executeResult = 
        executeMethod.Invoke(provider, new object[] { query.Expression }) as IExecuteResult;

    // database.Provider.Execute(query.Expression).ReturnValue
    IEnumerable<Product> results = executeResult.ReturnValue as IEnumerable<Product>;

    // Processes the results.
    foreach (Product product in results)
    {
        Console.WriteLine("{0}, {1}", product.ProductID, product.ProductName);
    }
}

This may be not straight forward enough. So here is a solution implementing fluent reflection with a ToDynamic() extension method:

IEnumerable<Product> results = database.ToDynamic() // Starts fluent reflection. 
                                       .Provider.Execute(query.Expression).ReturnValue;

C# 4.0 dynamic

In this kind of scenarios, it is easy to have dynamic in mind, which enables developer to write whatever code after a dot:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    // Constructs the query.
    IQueryable<Product> query = database.Products.Where(product => product.ProductID > 0)
                                                 .OrderBy(product => product.ProductName)
                                                 .Take(2);

    // database.Provider
    dynamic dynamicDatabase = database;
    dynamic results = dynamicDatabase.Provider.Execute(query).ReturnValue;
}

This throws a RuntimeBinderException at runtime:

'System.Data.Linq.DataContext.Provider' is inaccessible due to its protection level.

Here dynamic is able find the specified member. So the next thing is just writing some custom code to access the found member.

.NET 4.0 DynamicObject, and DynamicWrapper<T>

Where to put the custom code for dynamic? The answer is DynamicObject’s derived class. I first heard of DynamicObject from Anders Hejlsberg's video in PDC2008. It is very powerful, providing useful virtual methods to be overridden, like:

TryGetMember()
TrySetMember()
TryInvokeMember()

etc. (In 2008 they are called GetMember, SetMember, etc., with different signature.)

For example, if dynamicDatabase is a DynamicObject, then the following code:

dynamicDatabase.Provider

will invoke dynamicDatabase.TryGetMember() to do the actual work, where custom code can be put into.

Now create a type to inherit DynamicObject:

public class DynamicWrapper<T> : DynamicObject
{
    private readonly bool _isValueType;

    private readonly Type _type;

    private T _value; // Not readonly, for value type scenarios.

    public DynamicWrapper(ref T value) // Uses ref in case of value type.
    {
        if (value == null)
        {
            throw new ArgumentNullException("value");
        }

        this._value = value;
        this._type = value.GetType();
        this._isValueType = this._type.IsValueType;
    }

    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        // Searches in current type's public and non-public properties.
        PropertyInfo property = this._type.GetTypeProperty(binder.Name);
        if (property != null)
        {
            result = property.GetValue(this._value, null).ToDynamic();
            return true;
        }

        // Searches in explicitly implemented properties for interface.
        MethodInfo method = this._type.GetInterfaceMethod(string.Concat("get_", binder.Name), null);
        if (method != null)
        {
            result = method.Invoke(this._value, null).ToDynamic();
            return true;
        }

        // Searches in current type's public and non-public fields.
        FieldInfo field = this._type.GetTypeField(binder.Name);
        if (field != null)
        {
            result = field.GetValue(this._value).ToDynamic();
            return true;
        }

        // Searches in base type's public and non-public properties.
        property = this._type.GetBaseProperty(binder.Name);
        if (property != null)
        {
            result = property.GetValue(this._value, null).ToDynamic();
            return true;
        }

        // Searches in base type's public and non-public fields.
        field = this._type.GetBaseField(binder.Name);
        if (field != null)
        {
            result = field.GetValue(this._value).ToDynamic();
            return true;
        }

        // The specified member is not found.
        result = null;
        return false;
    }

    // Other overridden methods are not listed.
}

In the above code, GetTypeProperty(), GetInterfaceMethod(), GetTypeField(), GetBaseProperty(), and GetBaseField() are extension methods for Type class. For example:

internal static class TypeExtensions
{
    internal static FieldInfo GetBaseField(this Type type, string name)
    {
        Type @base = type.BaseType;
        if (@base == null)
        {
            return null;
        }

        return @base.GetTypeField(name) ?? @base.GetBaseField(name);
    }

    internal static PropertyInfo GetBaseProperty(this Type type, string name)
    {
        Type @base = type.BaseType;
        if (@base == null)
        {
            return null;
        }

        return @base.GetTypeProperty(name) ?? @base.GetBaseProperty(name);
    }

    internal static MethodInfo GetInterfaceMethod(this Type type, string name, params object[] args)
    {
        return
            type.GetInterfaces().Select(type.GetInterfaceMap).SelectMany(mapping => mapping.TargetMethods)
                .FirstOrDefault(
                    method =>
                    method.Name.Split('.').Last().Equals(name, StringComparison.Ordinal) &&
                    method.GetParameters().Count() == args.Length &&
                    method.GetParameters().Select(
                        (parameter, index) =>
                        parameter.ParameterType.IsAssignableFrom(args[index].GetType())).Aggregate(
                            true, (a, b) => a && b));
    }

    internal static FieldInfo GetTypeField(this Type type, string name)
    {
        return
            type.GetFields(
                BindingFlags.GetField | BindingFlags.Instance | BindingFlags.Static | BindingFlags.Public |
                BindingFlags.NonPublic).FirstOrDefault(
                    field => field.Name.Equals(name, StringComparison.Ordinal));
    }

    internal static PropertyInfo GetTypeProperty(this Type type, string name)
    {
        return
            type.GetProperties(
                BindingFlags.GetProperty | BindingFlags.Instance | BindingFlags.Static |
                BindingFlags.Public | BindingFlags.NonPublic).FirstOrDefault(
                    property => property.Name.Equals(name, StringComparison.Ordinal));
    }

    // Other extension methods are not listed.
}

So now, when invoked, TryGetMember() searches the specified member and invoke it. The code can be written like this:

dynamic dynamicDatabase = new DynamicWrapper<NorthwindDataContext>(ref database);
dynamic dynamicReturnValue = dynamicDatabase.Provider.Execute(query.Expression).ReturnValue;

This greatly simplified reflection.

ToDynamic() and fluent reflection

To make it even more straight forward, A ToDynamic() method is provided:

public static class DynamicWrapperExtensions
{
    public static dynamic ToDynamic<T>(this T value)
    {
        return new DynamicWrapper<T>(ref value);
    }
}

and a ToStatic() method is provided to unwrap the value:

public class DynamicWrapper<T> : DynamicObject
{
    public T ToStatic()
    {
        return this._value;
    }
}

In the above TryGetMember() method, please notice it does not output the member’s value, but output a wrapped member value (that is, memberValue.ToDynamic()). This is very important to make the reflection fluent.

Now the code becomes:

IEnumerable<Product> results = database.ToDynamic() // Here starts fluent reflection. 
                                       .Provider.Execute(query.Expression).ReturnValue
                                       .ToStatic(); // Unwraps to get the static value.

With the help of TryConvert():

public class DynamicWrapper<T> : DynamicObject
{
    public override bool TryConvert(ConvertBinder binder, out object result)
    {
        result = this._value;
        return true;
    }
}

ToStatic() can be omitted:

IEnumerable<Product> results = database.ToDynamic() 
                                       .Provider.Execute(query.Expression).ReturnValue;
                                       // Automatically converts to expected static value.

Take a look at the reflection code at the beginning of this post again. Now it is much much simplified!

Special scenarios

In 90% of the scenarios ToDynamic() is enough. But there are some special scenarios.

Access static members

Using extension method ToDynamic() for accessing static members does not make sense. Instead, DynamicWrapper<T> has a parameterless constructor to handle these scenarios:

public class DynamicWrapper<T> : DynamicObject
{
    public DynamicWrapper() // For static.
    {
        this._type = typeof(T);
        this._isValueType = this._type.IsValueType;
    }
}

The reflection code should be like this:

dynamic wrapper = new DynamicWrapper<StaticClass>();
int value = wrapper._value;
int result = wrapper.PrivateMethod();

So accessing static member is also simple, and fluent of course.

Change instances of value types

Value type is much more complex. The main problem is, value type is copied when passing to a method as a parameter.

This is why ref keyword is used for the constructor. That is, if a value type instance is passed to DynamicWrapper<T>, the instance itself will be stored in this._value of DynamicWrapper<T>. Without the ref keyword, when this._value is changed, the value type instance itself does not change.

Consider FieldInfo.SetValue(). In the value type scenarios, invoking FieldInfo.SetValue(this._value, value) does not change this._value, because it changes the copy of this._value.

I searched the Web and found a solution for setting the value of field:

internal static class FieldInfoExtensions
{
    internal static void SetValue<T>(this FieldInfo field, ref T obj, object value)
    {
        if (typeof(T).IsValueType)
        {
            field.SetValueDirect(__makeref(obj), value); // For value type. 
        }
        else
        {
            field.SetValue(obj, value); // For reference type.
        }
    }
}

Here __makeref is a undocumented keyword of C#.

But method invocation has problem. This is the source code of TryInvokeMember():

public override bool TryInvokeMember(InvokeMemberBinder binder, object[] args, out object result)
{
    if (binder == null)
    {
        throw new ArgumentNullException("binder");
    }

    MethodInfo method = this._type.GetTypeMethod(binder.Name, args) ??
                        this._type.GetInterfaceMethod(binder.Name, args) ??
                        this._type.GetBaseMethod(binder.Name, args);
    if (method != null)
    {
        // Oops!
        // If the returnValue is a struct, it is copied to heap.
        object resultValue = method.Invoke(this._value, args);
        // And result is a wrapper of that copied struct.
        result = new DynamicWrapper<object>(ref resultValue);
        return true;
    }

    result = null;
    return false;
}

If the returned value is of value type, it will definitely copied, because MethodInfo.Invoke() does return object. If changing the value of the result, the copied struct is changed instead of the original struct. And so is the property and index accessing. They are both actually method invocation. For less confusion, setting property and index are not allowed on struct.

Conclusions

The DynamicWrapper<T> provides a simplified solution for reflection programming. It works for normal classes (reference types), accessing both instance and static members.

In most of the scenarios, just remember to invoke ToDynamic() method, and access whatever you want:

StaticType result = someValue.ToDynamic()._field.Method().Property[index];

In some special scenarios which requires changing the value of a struct (value type), this DynamicWrapper<T> does not work perfectly. Only changing struct’s field value is supported.

The source code can be downloaded from here, including a few unit test code.

Back From Microsoft Web Camps Beijing

Dixin — Tue, 25 May 2010 01:12:00 GMT

I am just back from Microsoft Web Camps, where Web developers in Beijing had a good time for 2 days with 2 fantastic speakers, Scott Hanselman and James Senior.

On day 1, Scott and James talked about Web Platform Installer, ASP.NET core runtime, ASP.NET MVC, Entity Framework, Visual Studio 2010, … They were humorous and smart, and everyone was excited!

On day 2, developers were organized into teams to build Web applications. At the end of day 2, each team had a chance of presentation. Before ending, I also demonstrated my so-called “WebOS”, a tiny but funny Web website developed with ASP.NET MVC and jQuery, which looks like an operating system, to show the power of ASP.NET MVC and jQuery. Scott, James and me were joking there, and people cannot help laughing and applauding…

You can play with it here: http://www.coolwebos.com/, if interested.

I discussed with Scott and James about Web and ASP.NET, like some junior ideas on ASP.NET MVC anti-forgery request, etc. I also helped on some English / Chinese translation. At the end Scott gave me a fabulous gift, which I will post to blog later.

Hope Microsoft can have more and more events like this!

Anti-Forgery Request Recipes For ASP.NET MVC And AJAX

Dixin — Sat, 22 May 2010 08:34:00 GMT

This post discusses solutions for anti-forgery request scenarios in ASP.NET MVC and AJAX:

How to enable validation on controller, instead of on each action;
How to specify non-constant token salt in runtime;
How to work with the server side validation in AJAX scenarios.

Background (Normal scenario of form submitting)

To secure websites from cross-site request forgery (CSRF, or XSRF) attack, ASP.NET MVC provides an excellent mechanism:

The server prints tokens to cookie and inside the form;
When the form is submitted to server, token in cookie and token inside the form are sent in the HTTP request;
Server validates the tokens.

To print tokens to browser, just invoke HtmlHelper.AntiForgeryToken():

<% using (Html.BeginForm())
   { %>
    <%: this.Html.AntiForgeryToken(Constants.AntiForgeryTokenSalt)%>

    <%-- Other fields. --%>

    <input type="submit" value="Submit" />
<% } %>

This invocation generates a token and writes it inside the form:

<form action="..." method="post">
    <input name="__RequestVerificationToken" type="hidden" value="J56khgCvbE3bVcsCSZkNVuH9Cclm9SSIT/ywruFsXEgmV8CL2eW5C/gGsQUf/YuP" />
 
    <!-- Other fields. -->
 
    <input type="submit" value="Submit" />
</form>

and also writes it into the cookie:

__RequestVerificationToken_Lw__= J56khgCvbE3bVcsCSZkNVuH9Cclm9SSIT/ywruFsXEgmV8CL2eW5C/gGsQUf/YuP

When the above form is submitted, they are both sent to server.

In the server side, [ValidateAntiForgeryToken] attribute is used to specify the controllers or actions to validate them:

[HttpPost]
[ValidateAntiForgeryToken(Salt = Constants.AntiForgeryTokenSalt)]
public ActionResult Action(/* ... */)
{
    // ...
}

This is very productive for form scenarios. But recently, when resolving security vulnerabilities for Web products, problems are encountered.

Turn on validation on controller (not on each action)

The server side problem is, one single [ValidateAntiForgeryToken] attribute is expected to declare on controller, but actually a lot of attributes have be to declared on controller's each POST actions. Because POST actions are usually much more then controllers, the work would be a little crazy.

Problem

Usually a controller contains both actions for HTTP GET requests and actions for POST, and, usually validations are expected for only HTTP POST requests. So, if the [ValidateAntiForgeryToken] is declared on the controller, the HTTP GET requests become invalid:

[ValidateAntiForgeryToken(Salt = Constants.AntiForgeryTokenSalt)]
public class ProductController : Controller // One [ValidateAntiForgeryToken] attribute. 
{
    [HttpGet]
    public ActionResult Index() // Index() cannot work.
    {
        // ...
    }

    [HttpPost]
    public ActionResult PostAction1(/* ... */)
    { 
        // ...
    }

    [HttpPost]
    public ActionResult PostAction2(/* ... */)
    {
        // ...
    }

    // Other actions.
}

If browser sends an HTTP GET request by clicking a link: http://Site/Product/Index, validation definitely fails, because no token is provided (by http://Site/Product/Index?__RequestVerificationToken=???, for example).

As a result, many [ValidateAntiForgeryToken] attributes have be distributed to each POST action:

public class ProductController : Controller // Many [ValidateAntiForgeryToken] attributes.
{
    [HttpGet]
    public ActionResult Index() // Works.
    {
        // ...
    }

    [HttpPost]
    [ValidateAntiForgeryToken(Salt = Constants.AntiForgeryTokenSalt)]
    public ActionResult PostAction1(/* ... */)
    { 
        // ...
    }

    [HttpPost]
    [ValidateAntiForgeryToken(Salt = Constants.AntiForgeryTokenSalt)]
    public ActionResult PostAction2(/* ... */)
    {
        // ...
    }

    // Other actions.
}

This would be a little bit crazy, because one Web product can have a lot of POST actions.

Solution

To avoid a large number of [ValidateAntiForgeryToken] attributes (one for each POST action), the following ValidateAntiForgeryTokenWrapperAttribute wrapper class can be helpful, where HTTP verbs can be specified:

[AttributeUsage(AttributeTargets.Class | AttributeTargets.Method,
    AllowMultiple = false, Inherited = true)]
public class ValidateAntiForgeryTokenWrapperAttribute : FilterAttribute, IAuthorizationFilter
{
    private readonly ValidateAntiForgeryTokenAttribute _validator;

    private readonly AcceptVerbsAttribute _verbs;

    public ValidateAntiForgeryTokenWrapperAttribute(HttpVerbs verbs)
        : this(verbs, null)
    {
    }

    public ValidateAntiForgeryTokenWrapperAttribute(HttpVerbs verbs, string salt)
    {
        this._verbs = new AcceptVerbsAttribute(verbs);
        this._validator = new ValidateAntiForgeryTokenAttribute()
            {
                Salt = salt
            };
    }

    public void OnAuthorization(AuthorizationContext filterContext)
    {
        string httpMethodOverride = filterContext.HttpContext.Request.GetHttpMethodOverride();
        if (this._verbs.Verbs.Contains(httpMethodOverride, StringComparer.OrdinalIgnoreCase))
        {
            this._validator.OnAuthorization(filterContext);
        }
    }
}

Here only HTTP requests of the specified verbs are validated:

[ValidateAntiForgeryTokenWrapper(HttpVerbs.Post, Constants.AntiForgeryTokenSalt)]
public class ProductController : Controller
{
    // GET actions are not affected.
    // Only HTTP POST requests are validated.
}

Now one single attribute on a controller turns on validation for all POST actions in that controller.

It would be nice if HTTP verbs can be specified on the built-in [ValidateAntiForgeryToken] attribute. And, this is very easy to implement.

Specify non-constant salt in runtime

By default, the salt should be a compile time constant, so it can be used for the [ValidateAntiForgeryToken] or [ValidateAntiForgeryTokenWrapper] attribute.

Problem

One Web product might be sold to many clients. If a constant salt is evaluated in compile time, after the product is built and deployed to many clients, they all have the same salt. Of course, clients do not like this. Even some clients might expect a configurable custom salt. In these scenarios, salt is required to be a runtime value.

Solution

In the above [ValidateAntiForgeryToken] and [ValidateAntiForgeryTokenWrapper] attributes, the salt is passed through constructor. So one solution is to remove that parameter:

public class ValidateAntiForgeryTokenWrapperAttribute : FilterAttribute, IAuthorizationFilter
{
    public ValidateAntiForgeryTokenWrapperAttribute(HttpVerbs verbs)
    {
        this._verbs = new AcceptVerbsAttribute(verbs);
        this._validator = new ValidateAntiForgeryTokenAttribute()
            {
                Salt = Configurations.AntiForgeryTokenSalt
            };
    }

    // Other members.
}

But this smells bad because the injected dependency becomes a hard dependency. So the other solution to work around the limitation of attributes, is moving validation code into controller:

public abstract class AntiForgeryControllerBase : Controller
{
    private readonly ValidateAntiForgeryTokenAttribute _validator;

    private readonly AcceptVerbsAttribute _verbs;

    protected AntiForgeryControllerBase(HttpVerbs verbs, string salt)
    {
        this._verbs = new AcceptVerbsAttribute(verbs);
        this._validator = new ValidateAntiForgeryTokenAttribute()
            {
                Salt = salt
            };
    }

    protected override void OnAuthorization(AuthorizationContext filterContext)
    {
        base.OnAuthorization(filterContext);

        string httpMethodOverride = filterContext.HttpContext.Request.GetHttpMethodOverride();
        if (this._verbs.Verbs.Contains(httpMethodOverride, StringComparer.OrdinalIgnoreCase))
        {
            this._validator.OnAuthorization(filterContext);
        }
    }
}

Then just make controller classes inheriting from this AntiForgeryControllerBase class. Now the salt is no long required to be a compile time constant.

Submit token via AJAX

For browser side, once server side turns on anti-forgery validation for HTTP POST, all AJAX POST requests will fail by default.

Problem

In AJAX scenarios, the HTTP POST request is not sent by form. Take jQuery as an example:

$.post(url, {
    productName: "Tofu",
    categoryId: 1 // Token is not posted.
}, callback);

This kind of AJAX POST requests will always be invalid, because server side code cannot see the token in the posted data.

Solution

Basically, the tokens must be printed to browser then sent back to server. So first of all, HtmlHelper.AntiForgeryToken() need to be called somewhere. Now the browser has token in both HTML and cookie.

Then jQuery must find the printed token in the HTML, and append token to the data before sending:

$.post(url, {
    productName: "Tofu",
    categoryId: 1,
    __RequestVerificationToken: getToken() // Token is posted.
}, callback);

To be reusable, this can be encapsulated into a tiny jQuery plugin:

/// <reference path="jquery-1.4.2.js" />

(function ($) {
    $.getAntiForgeryToken = function (tokenWindow, appPath) {
        // HtmlHelper.AntiForgeryToken() must be invoked to print the token.
        tokenWindow = tokenWindow && typeof tokenWindow === typeof window ? tokenWindow : window;

        appPath = appPath && typeof appPath === "string" ? "_" + appPath.toString() : "";
        // The name attribute is either __RequestVerificationToken,
        // or __RequestVerificationToken_{appPath}.
        var tokenName = "__RequestVerificationToken" + appPath;

        // Finds the <input type="hidden" name={tokenName} value="..." /> from the specified window.
        // var inputElements = tokenWindow.$("input[type='hidden'][name=' + tokenName + "']");
        var inputElements = tokenWindow.document.getElementsByTagName("input");
        for (var i = 0; i < inputElements.length; i++) {
            var inputElement = inputElements[i];
            if (inputElement.type === "hidden" && inputElement.name === tokenName) {
                return {
                    name: tokenName,
                    value: inputElement.value
                };
            }
        }
    };

    $.appendAntiForgeryToken = function (data, token) {
        // Converts data if not already a string.
        if (data && typeof data !== "string") {
            data = $.param(data);
        }

        // Gets token from current window by default.
        token = token ? token : $.getAntiForgeryToken(); // $.getAntiForgeryToken(window).

        data = data ? data + "&" : "";
        // If token exists, appends {token.name}={token.value} to data.
        return token ? data + encodeURIComponent(token.name) + "=" + encodeURIComponent(token.value) : data;
    };

    // Wraps $.post(url, data, callback, type) for most common scenarios.
    $.postAntiForgery = function (url, data, callback, type) {
        return $.post(url, $.appendAntiForgeryToken(data), callback, type);
    };

    // Wraps $.ajax(settings).
    $.ajaxAntiForgery = function (settings) {
        // Supports more options than $.ajax(): 
        // settings.token, settings.tokenWindow, settings.appPath.
        var token = settings.token ? settings.token : $.getAntiForgeryToken(settings.tokenWindow, settings.appPath);
        settings.data = $.appendAntiForgeryToken(settings.data, token);
        return $.ajax(settings);
    };
})(jQuery);

In most of the scenarios, it is Ok to just replace $.post() invocation with $.postAntiForgery(), and replace $.ajax() with $.ajaxAntiForgery():

$.postAntiForgery(url, {
    productName: "Tofu",
    categoryId: 1
}, callback); // The same usage as $.post(), but token is posted.

There might be some scenarios of custom token, where $.appendAntiForgeryToken() is useful:

data = $.appendAntiForgeryToken(data, token);
// Token is already in data. No need to invoke $.postAntiForgery().
$.post(url, data, callback);

or $.ajaxAntiForgery() can be used:

$.ajaxAntiForgery({
    type: "POST",
    url: url,
    data: {
        productName: "Tofu",
        categoryId: 1
    },
    success: callback, // The same usage as $.ajax(), supporting more options.
    token: token // Custom token.
});

And there are special scenarios that the token is not in the current window. For example:

An HTTP POST request can be sent from an iframe, while the token is in the parent window or top window;
An HTTP POST request can be sent from an popup window or a dialog, while the token is in the opener window;

etc. Here, token's container window can be specified for $.getAntiForgeryToken():

data = $.appendAntiForgeryToken(data, $.getAntiForgeryToken(window.parent));
// Token is already in data. No need to invoke $.postAntiForgery().
$.post(url, data, callback);

or $.ajaxAntiForgery() can be used:

$.ajaxAntiForgery({
    type: "POST",
    url: url,
    data: {
        productName: "Tofu",
        categoryId: 1
    },
    success: callback, // The same usage as $.ajax(), supporting more options.
    tokenWindow: window.parent // Token is in another window.
});

If you have better solution, please do tell me.

Understanding LINQ to SQL (10) Implementing LINQ to SQL Provider

Dixin — Tue, 11 May 2010 18:43:00 GMT

[LINQ via C# series]

So far LINQ to SQL data CRUD (Creating / Retrieving / Updating / Deleting) has been explained. This post takes a deeper look at the internal implementation of LINQ to SQL query.

The provider model

Unlike IEnumerable / IEnumerable<T>, the IQueryable / IQueryable<T> need a query provider:

namespace System.Linq
{
    public interface IQueryable : IEnumerable
    {
        Type ElementType { get; }

        Expression Expression { get; }

        IQueryProvider Provider { get; }
    }

    public interface IQueryable<out T> : IEnumerable<T>, IQueryable, IEnumerable
    {
    }
}

And this is the definition of IQueryProvider:

namespace System.Linq
{
    public interface IQueryProvider
    {
        IQueryable CreateQuery(Expression expression);

        IQueryable<TElement> CreateQuery<TElement>(Expression expression);

        object Execute(Expression expression);

        TResult Execute<TResult>(Expression expression);
    }
}

Yes, IQueryable / IQueryable<T> are much more complex than IEnumerable / IEnumerable<T>, because they are supposed to work against non-.NET data source, like SQL Server database, etc.

Please also notice IOrderedQueryable and IOrderedQueryable<T>:

namespace System.Linq
{
    // The same as IQueryable.
    public interface IOrderedQueryable : IQueryable, IEnumerable
    {
    }
    
    // The same as IQueryable<T>.
    public interface IOrderedQueryable<out T> : IOrderedQueryable,
                                                IQueryable<T>, IQueryable,
                                                IEnumerable<T>, IEnumerable
    {
    }
}

They are the same as IQueryable and IQueryable<T>, and just used to represent an ordering query, like OrderBy(), etc.

Implement IQueryable<T> and IOrderedQueryable<T>

The best way to understand these interfaces is just creating IQueryable / IQueryable<T> objects, and examining how they work and query data from SQL Server.

This is one simple implementation:

public class Queryable<TSource> : IOrderedQueryable<TSource>
{
    public Queryable(IQueryProvider provider, IQueryable<TSource> innerSource)
    {
        this.Provider = provider;
        this.Expression = Expression.Constant(innerSource);
    }

    public Queryable(IQueryProvider provider, Expression expression)
    {
        this.Provider = provider;
        this.Expression = expression;
    }

    #region IEnumerable<TSource> Members

    public IEnumerator<TSource> GetEnumerator()
    {
        return this.Provider.Execute<IEnumerable<TSource>>(this.Expression).GetEnumerator();
    }

    #endregion

    #region IEnumerable Members

    IEnumerator IEnumerable.GetEnumerator()
    {
        return this.GetEnumerator();
    }

    #endregion

    #region IQueryable Members

    public Type ElementType
    {
        get
        {
            return typeof(TSource);
        }
    }

    public Expression Expression
    {
        get;
        private set;
    }

    public IQueryProvider Provider
    {
        get;
        private set;
    }

    #endregion
}

Since Queryable<TSource> implements IOrderedQueryable<T>, it also implements IQeryable<TSource>, IQeryable and IOrderedQueryable.

There is not too much things. The most important method is GetEnumerator(). When a Queryable<TSource> object is iterated to traverse the data items, it simply asks its query provider to execute its expression to retrieve an IEnumerable<TSource> object, and return that object’s iterator.

Implement IQueryProvider

So the actual SQL query implantation is in the query provider:

public class QueryProvider : IQueryProvider
{
    // Translates LINQ query to SQL.
    private readonly Func<IQueryable, DbCommand> _translator;

    // Executes the translated SQL and retrieves results.
    private readonly Func<Type, string, object[], IEnumerable> _executor;

    public QueryProvider(
        Func<IQueryable, DbCommand> translator,
        Func<Type, string, object[], IEnumerable> executor)
    {
        this._translator = translator;
        this._executor = executor;
    }

    #region IQueryProvider Members

    public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
    {
        return new Queryable<TElement>(this, expression);
    }

    public IQueryable CreateQuery(Expression expression)
    {
        throw new NotImplementedException();
    }

    public TResult Execute<TResult>(Expression expression)
    {
        bool isCollection = typeof(TResult).IsGenericType &&
            typeof(TResult).GetGenericTypeDefinition() == typeof(IEnumerable<>);
        Type itemType = isCollection
            // TResult is an IEnumerable`1 collection.
            ? typeof(TResult).GetGenericArguments().Single()
            // TResult is not an IEnumerable`1 collection, but a single item.
            : typeof(TResult);
        IQueryable queryable = Activator.CreateInstance(
            typeof(Queryable<>).MakeGenericType(itemType), this, expression) as IQueryable;

        IEnumerable queryResult;

        // Translates LINQ query to SQL.
        using (DbCommand command = this._translator(queryable))
        {
            // Executes the transalted SQL.
            queryResult = this._executor(
                itemType,
                command.CommandText,
                command.Parameters.OfType<DbParameter>()
                                  .Select(parameter => parameter.Value)
                                  .ToArray());
        }

        return isCollection
            ? (TResult)queryResult // Returns an IEnumerable`1 collection.
            : queryResult.OfType<TResult>()
                         .SingleOrDefault(); // Returns a single item.
    }

    public object Execute(Expression expression)
    {
        throw new NotImplementedException();
    }

    #endregion
}

QueryProvider must be initialized with a translator and executor, so that it is able to translate LINQ query to SQL, and execute the translated SQL.

And here the most important is the generic Execute() method, which is called by the above Queryable<TSource>.GetEnumerator(). It does the following work:

Checks whether it should return a collection of items (for the Where() scenarios, etc.), or should return a sinlge item (for the Single() query scenarios, etc.)
Invokes the translator to translate LINQ query to SQL.
Invokes the executor to execute the translated SQL and retrieves the result.
Returns result of a proper type (either a collection, or a single item).

Query method internals

Before running the query, take a look at the IQueryable<T> query methods.

Deferred execution methods

Take Where() as an example:

public static class Queryable
{
    public static IQueryable<TSource> Where<TSource>(
        this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
    {
        // Checks arguments.
        return source.Provider.CreateQuery<TSource>(
            Expression.Call(
                null,
                ((MethodInfo)MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[]
                    { 
                        typeof(TSource) 
                    }),
                new Expression[] 
                    { 
                        source.Expression, 
                        Expression.Quote(predicate) 
                    }));
    }
}

It is very very different from IEnumerable<T>’s Where() query method. It is not executing any thing, it just:

Constructs a new expression tree, which contains the following information:
- The original expression tree from the source IQueryable<T> object
- The predicate expression tree
- This Where() query method is invoked
Then invokes the query provider’s generic CreateQuery() method to construct a new IQueryable<TSource> object.

Obviously, the above constructed expression tree is used to contain the information which is prepared to be translated.

The ordering query method, like OrderBy(), is a little different, which converts the constructed IQueryable<TSource> object to an IOrderedQueryable<TSource> object:

public static IOrderedQueryable<TSource> OrderBy<TSource, TKey>(
    this IQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector)
{
    // Checks arguments.
    return (IOrderedQueryable<TSource>)source.Provider.CreateQuery<TSource>(
        Expression.Call(
            null, 
            ((MethodInfo)MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[] 
                { 
                    typeof(TSource), 
                    typeof(TKey) 
                }), 
            new Expression[] 
                { 
                    source.Expression, 
                    Expression.Quote(keySelector) 
                }));
}

And so is ThenBy():

public static IOrderedQueryable<TSource> ThenBy<TSource, TKey>(
    this IOrderedQueryable<TSource> source, Expression<Func<TSource, TKey>> keySelector)
{
    // Checks arguments.
    return (IOrderedQueryable<TSource>)source.Provider.CreateQuery<TSource>(
        Expression.Call(
            null, 
            ((MethodInfo)MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[] 
                { 
                    typeof(TSource), 
                    typeof(TKey) 
                }), 
            new Expression[] { 
                    source.Expression, 
                    Expression.Quote(keySelector) 
            }));
}

ThenBy() / ThenByDescending() are extension methods of IOrderedQueryable<TSource> instead of IQueryable<TSource>, which means, It must be invoked after invoking OrderBy() / OrderByDescending().

Eager execution methods

Single() is different:

public static TSource Single<TSource>(this IQueryable<TSource> source)
{
    // Checks arguments.
    return source.Provider.Execute<TSource>(
        Expression.Call(
            null, 
            ((MethodInfo)MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[] 
                { 
                    typeof(TSource) 
                }), 
            new Expression[] 
                { 
                    source.Expression 
                }));
}

Logically, Single() cannot be deferred. So after construction the expression tree, it invokes query provider’s generic Execute() method, and returns a TSource object instead of a IQueryable<TSource>.

Of course, the aggregate methods looks similar, invoking Execute() instead of CreateQuery():

public static decimal Average<TSource>(
    this IQueryable<TSource> source, Expression<Func<TSource, decimal>> selector)
{
    // Checks arguments.
    return source.Provider.Execute<decimal>(
        Expression.Call(
            null, 
            ((MethodInfo)MethodBase.GetCurrentMethod()).MakeGenericMethod(new Type[] 
                { 
                    typeof(TSource) 
                }), 
            new Expression[] 
                { 
                    source.Expression, 
                    Expression.Quote(selector) 
                }));
}

It cannot be deferred either.

Work together

Now it is ready to run all the stuff above.

Query a collection of items (deferred execution)

The following query expects a collection of Product objects:

using (NorthwindDataContext database = new NorthwindDataContext())
{
    IQueryProvider provider = new QueryProvider(database.GetCommand, database.ExecuteQuery);
    IQueryable<Product> source = new Queryable<Product>(provider, database.GetTable<Product>());
    IQueryable<string> results = source.Where(product => product.CategoryID == 2)
                                       .OrderBy(product => product.ProductName)
                                       .Select(product => product.ProductName)
                                       .Skip(5)
                                       .Take(10);

    using (IEnumerator<string> iterator = results.GetEnumerator())
    {
        while (iterator.MoveNext())
        {
            string item = iterator.Current;
            Console.WriteLine(item);
        }
    }
}

To initialize the provider, DataContext.GetCommand() and DataContext.ExecuteQuery() are passed as translator and executor.

When results.GetEnumerator() is invoked, provider.Execute() is invoked. The query is translated to:

exec sp_executesql N'SELECT [t1].[ProductName]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ProductName]) AS [ROW_NUMBER], [t0].[ProductName]
    FROM [dbo].[Products] AS [t0]
    WHERE [t0].[CategoryID] > @p0
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int',@p0=2,@p1=5,@p2=10

by the provider’s translator, then provider’s executor executes the above SQL in SQL Server, and return a collection of items.

This is the printed output:

Escargots de Bourgogne
Filo Mix
Flotemysost
Geitost
Gnocchi di nonna Alice
Gorgonzola Telino
Gravad lax
Gudbrandsdalsost
Gumbär Gummibärchen
Gustaf's Knäckebröd

Query a single item (eager execution)

The following sample is different:

IQueryProvider provider = new QueryProvider(database.GetCommand, database.ExecuteQuery);
IQueryable<Product> source = new Queryable<Product>(provider, database.GetTable<Product>());
string productName = source.Where(product => product.CategoryID > 2)
                           .Select(product => product.ProductName)
                           .First();

Without deferred execution and iterating, the First() invokes provider.Execute() directly.

This is the translated SQL:

exec sp_executesql N'SELECT TOP (1) [t0].[ProductName]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[CategoryID] > @p0',N'@p0 int',@p0=2

Aggregate (eager execution)

Aggregate query is also eager:

IQueryProvider provider = new QueryProvider(database.GetCommand, database.ExecuteQuery);
IQueryable<Product> source = new Queryable<Product>(provider, database.GetTable<Product>());
decimal averagePrice = source.Where(product => product.CategoryID == 2)
                             .Average(product => product.UnitPrice.GetValueOrDefault());

This is the translated SQL:

exec sp_executesql N'SELECT AVG([t1].[value]) AS [value]
FROM (
    SELECT COALESCE([t0].[UnitPrice],0) AS [value], [t0].[CategoryID]
    FROM [dbo].[Products] AS [t0]
    ) AS [t1]
WHERE [t1].[CategoryID] = @p0',N'@p0 int',@p0=2

SQL translating and executing

The above samples explained the implementation of LINQ to SQL query and query provider. Inside the QueryProvider class, it does not provide the detailed implementation of SQL translating and executing, but pass the work to DataContext.GetCommand() and DataContext.ExecuteQuery().

This post has demonstrated the simplest SQL translating and executing. But the realistic work is very very complex. Since this is not a SQL series but a LINQ / functional programming series, to develop a full featured SQL “compiler” is far beyond this series’ scope. For SQL executing, it is also complex to convert the retrieved data back to strong-typed objects in LINQ to SQL. To understand the entire translating and executing process, please follow the source code of Table<T>, which implements IQueryProvider.

Internally, Table<T> uses several internal classes, like SqlProvider, QueryConverter, etc., to accomplish the translating. For example, one of the core APIs is the QueryConverter.VisitSequenceOperatorCall():

internal class QueryConverter
{
    private SqlNode VisitSequenceOperatorCall(MethodCallExpression mc)
    {
        Type declaringType = mc.Method.DeclaringType;
        if (!(declaringType == typeof(Enumerable)) && !(declaringType == typeof(Queryable)))
        {
            throw new InvalidOperationException(string.Format(
                CultureInfo.InvariantCulture,
                "Sequence operator call is only valid for Sequence, Queryable, or DataQueryExtensions not for '{0}'",
                declaringType));
        }

        bool isNotSupported = false;
        switch (mc.Method.Name)
        {
            case "Where":
                isNotSupported = true;

                // The overload:
                // IQueryable<TSource> Where<TSource>(
                // this IQueryable<TSource> source, Expression<Func<TSource, int, bool>> predicate)
                // is not supported.

                // The MethodCallExpression object mc should have 2 arguments.
                // The first argument should be null.
                // The second argument should be Expression.Quote(predicate).
                if (mc.Arguments.Count != 2 ||
                    // IsLambda() removes the quote to get the predicate object,
                    // and checks predicate.NodeType ==  ExpressionType.Lambda.
                    !this.IsLambda(mc.Arguments[1]) ||
                    // precicate should have 1 TSource argument.
                    this.GetLambda(mc.Arguments[1]).Parameters.Count != 1)
                {
                    break; // The overload is not supported.
                }

                // The overload:
                // IQueryable<TSource> Where<TSource>(
                // this IQueryable<TSource> source, Expression<Func<TSource, bool>> predicate)
                // is supported.
                return this.VisitWhere(mc.Arguments[0], this.GetLambda(mc.Arguments[1]));

            case "OrderBy":
                isNotSupported = true;

                if (mc.Arguments.Count != 2 || !this.IsLambda(mc.Arguments[1]) ||
                    this.GetLambda(mc.Arguments[1]).Parameters.Count != 1)
                {
                    break; // The overload is not supported.
                }

                return this.VisitOrderBy(
                    mc.Arguments[0], this.GetLambda(mc.Arguments[1]), SqlOrderType.Ascending);

            case "ThenBy":
                isNotSupported = true;

                if (mc.Arguments.Count != 2 || !this.IsLambda(mc.Arguments[1]) ||
                    this.GetLambda(mc.Arguments[1]).Parameters.Count != 1)
                {
                    break; // The overload is not supported.
                }

                return this.VisitThenBy(
                    mc.Arguments[0], this.GetLambda(mc.Arguments[1]), SqlOrderType.Ascending);

            case "Single":
            case "SingleOrDefault":
                isNotSupported = true;

                if (mc.Arguments.Count != 1)
                {
                    if (mc.Arguments.Count != 2 || !this.IsLambda(mc.Arguments[1]) ||
                        this.GetLambda(mc.Arguments[1]).Parameters.Count != 1)
                    {
                        break; // The overload is not supported.
                    }

                    return this.VisitFirst(
                        mc.Arguments[0], this.GetLambda(mc.Arguments[1]), false);
                }

                return this.VisitFirst(mc.Arguments[0], null, false);

            case "Average":
                isNotSupported = true;

                if (mc.Arguments.Count != 1)
                {
                    if (mc.Arguments.Count != 2 || !this.IsLambda(mc.Arguments[1]) ||
                        this.GetLambda(mc.Arguments[1]).Parameters.Count != 1)
                    {
                        break; // The overload is not supported.
                    }

                    return this.VisitAggregate(
                        mc.Arguments[0], this.GetLambda(mc.Arguments[1]), SqlNodeType.Avg, mc.Type);
                }

                return this.VisitAggregate(mc.Arguments[0], null, SqlNodeType.Avg, mc.Type);

            // Other cases, like "Take", "Skip", "Distinct", etc.                
        }

        if (isNotSupported)
        {
            throw new NotSupportedException(string.Format(
                CultureInfo.InvariantCulture,
                "Unsupported overload used for query operator '{0}'.",
                mc.Method.Name));
        }

        throw new NotSupportedException(string.Format(
            CultureInfo.InvariantCulture,
            "The query operator '{0}' is not supported.",
            mc.Method.Name));
    }
}

Please compare this with the fore mentioned IQueryable<T> query methods, Where(), OrderBy(), Single(), Average(), etc.

There is also an excellent tutorial from MSDN.

LINQ Providers

There are several kinds of built-in LINQ in .NET 4.0:

Built-in IQueryable LINQ Providers

LINQ to Objects and LINQ to XML are IEnumerable based, and the 3 kinds of LINQ to ADO.NET are IQueryable-based, which have their specific IQueryProvider.

For example, in LINQ to SQL, the IQueryable, IQueryable<T> and IQueryProvider are implemented by Table<T> class and an internal DataQuery<T> class. DataQuery<T> also implements IOrderedQueryable and IOrderedQueryable<T>. These classes and all the other related classes (like SqlProvider, ) can be considered the provider of LINQ to SQL.

LINQ to Everything

To implement any other LINQ query against a specific data source, the specific LINQ provider should be provided. That is, classes which implements the above IQueryable, IQueryable<T>, IQueryProvider, IOrderedQueryable and IOrderedQueryable<T> interfaces. The LINQ to Wikipedia provider at the beginning of the series is one example. This post lists a lot of custom LINQ providers, like:

etc.

This tutorial teaches how to create a IQueryable LINQ provider against the TerraServer-USA Web service.

LINQ to Objects provider

LINQ to Objects is IEnumerable based, but the interesting thing is, IEnumerble<T> has an AsQueryable() extension method, which turns IEnumerble-based query into IQueryable-based query:

public static class Queryable
{
    public static IQueryable<TElement> AsQueryable<TElement>(
        this IEnumerable<TElement> source)
    {
        // Checks arguments.
        if (source is IQueryable<TElement>)
        {
            return (IQueryable<TElement>)source;
        }

        return new EnumerableQuery<TElement>(source);
    }
}

Here the EnumerableQuery<T> class implements IQueryable<T>, as well as the IQueryProvider:

namespace System.Linq
{
    public abstract class EnumerableQuery
    {
        // ...
    }

    public class EnumerableQuery<T> : EnumerableQuery, IQueryProvider,
                                      IQueryable<T>, IQueryable,
                                      IOrderedQueryable<T>, IOrderedQueryable,
                                      IEnumerable<T>, IEnumerable
    {
        // ...
    }
}

Internally, EnumerableQuery<T>.Execute() invokes Expression<TDelegate>.Compile() to execute the expression representing the query.

Understanding LINQ to SQL (9) Concurrent Conflict

Dixin — Mon, 26 Apr 2010 13:53:00 GMT

[LINQ via C# series]

Conflicts are very common when concurrently accessing the same data.

Conflicts in concurrent data access

The following code presents the concurrent conflict scenario:

Action<int, Action<Category>> updateCategory = (id, updater) =>
    {
        using (NorthwindDataContext database = new NorthwindDataContext())
        {
            Category category = database.Categories
                                        .Single(item => item.CategoryID == id);

            Thread.Sleep(4000);

            updater(category);
            // database.SubmitChanges() invokes:
            database.SubmitChanges(ConflictMode.FailOnFirstConflict);
        }
    };

new Thread(() => updateCategory(1, category => category.CategoryName = "Thread 1")).Start();

Thread.Sleep(2000);

new Thread(() => updateCategory(1, category => category.CategoryName = "Thread 2")).Start();

Here 2 threads are accessing the same category. This is the order of the executions:

Time (second)	Thread 1	Thread 2	[CategoryName] database value
0 (Thread 1 reads)	Retrieves “Beverages”		“Beverages”
2 (Thread 2 reads)		Retrieves “Beverages”	“Beverages”
4 (Thread 1 writes)	updates “Beverages” to “Thread 1”		“Thread 1”
6 (Thread 2 writes)		Should update “Beverages” to “Thread 2”	[CategoryName] is no longer “Beverages”

When the later started thread (thread 2) tries to submit the change, the conflict occurs, and DataContext.SubmitChanges() throws a ChangeConflictException:

Row not found or changed.

Optimistic concurrency control

The concurrency control tactic of LINQ to SQL is optimistic, which means LINQ to SQL checks the status of data instead of locking the data (pessimistic concurrency control).

This is the translated SQL from 2 threads:

-- Thread 1 reads.
exec sp_executesql N'SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1

-- Thread 2 reads.
exec sp_executesql N'SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1

-- Thread 1 writes.
BEGIN TRANSACTION 
exec sp_executesql N'UPDATE [dbo].[Categories]
SET [CategoryName] = @p2
WHERE ([CategoryID] = @p0) AND ([CategoryName] = @p1)',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=1,@p1=N'Beverages',@p2=N'Thread 1' -- CategoryName has an [Column(UpdateCheck = UpdateCheck.Always)] attribute.
COMMIT TRANSACTION -- Updating successes.

-- Thread 2 writes.
BEGIN TRANSACTION 
exec sp_executesql N'UPDATE [dbo].[Categories]
SET [CategoryName] = @p2
WHERE ([CategoryID] = @p0) AND ([CategoryName] = @p1)',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=1,@p1=N'Beverages',@p2=N'Thread 2' -- CategoryName has an [Column(UpdateCheck = UpdateCheck.Always)] attribute.
ROLLBACK TRANSACTION -- Updating fails.

When submitting data changes, LINQ to SQL not only uses primary key to identify the data, but also checks the original state of the column which is expected to be updated.

Update check

This original state check is specified by the [Column] attribute of entity property:

If ColumnAttribute.UpdateCheck is not specified:

[Column(Storage = "_CategoryName", DbType = "NVarChar(15) NOT NULL", CanBeNull = false)]
public string CategoryName
{
}

then it will have a default value: UpdateCheck.Always:

[AttributeUsage(AttributeTargets.Field | AttributeTargets.Property, AllowMultiple = false)]
public sealed class ColumnAttribute : DataAttribute
{
    private UpdateCheck _updateCheck = UpdateCheck.Always;

    public UpdateCheck UpdateCheck
    {
        get
        {
            return this._updateCheck;
        }
        set
        {
            this._updateCheck = value;
        }
    }
}

Time stamp

In the above screenshot, there is a [Time Stamp] option in the O/R designer, which can be used when this column is of type timestamp (rowversion). To demonstrating this, add a timestamp column [Version] to the [Categories] table:

And recreate the model in O/R designer. Now this is the generated [Column] attribute:

[Column(Storage = "_Version", AutoSync = AutoSync.Always, DbType = "rowversion NOT NULL", 
    CanBeNull = false, IsDbGenerated = true, IsVersion = true, UpdateCheck = UpdateCheck.Never)]
public Binary Version
{
}

Now LINQ to SQL always checks the [Version] column instead of [CategoryName] column. So when rerunning the above code, the translated SQL is different:

-- Thread 1 reads.
exec sp_executesql N'SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture], [t0].[Version]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1

-- Thread 2 reads.
exec sp_executesql N'SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture], [t0].[Version]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1

-- Thread 1 writes.
BEGIN TRANSACTION 
-- Checks time stamp.
exec sp_executesql N'UPDATE [dbo].[Categories]
SET [CategoryName] = @p2
WHERE ([CategoryID] = @p0) AND ([Version] = @p1)

SELECT [t1].[Version]
FROM [dbo].[Categories] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CategoryID] = @p3)',N'@p0 int,@p1 timestamp,@p2 nvarchar(4000),@p3 int',@p0=1,@p1=0x0000000000000479,@p2=N'Thread 1',@p3=1
-- SELECT for [Column(AutoSync = AutoSync.Always)]
COMMIT TRANSACTION -- Updating successes.

-- Thread 2 writes.
BEGIN TRANSACTION 
-- Checks time stamp.
exec sp_executesql N'UPDATE [dbo].[Categories]
SET [CategoryName] = @p2
WHERE ([CategoryID] = @p0) AND ([Version] = @p1)

SELECT [t1].[Version]
FROM [dbo].[Categories] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[CategoryID] = @p3)',N'@p0 int,@p1 timestamp,@p2 nvarchar(4000),@p3 int',@p0=1,@p1=0x0000000000000479,@p2=N'Thread 2',@p3=1
-- SELECT for [Column(AutoSync = AutoSync.Always)]
ROLLBACK TRANSACTION -- Updating fails.

Handle ChangeConflictException

When concurrent conflict occurs, SubmitChanges() rollbacks the TRANSACTION, then throws a ChangeConflictException exception.

So if the caller of DataContext.SubmitChanges() knows how to resolve the conflict, it can detects conflict by handling ChangeConflictException .

Merge changes to resolve conflict

For example, a common tactic is to merge the changes into database:

Action<int, Action<Category>> updateCategory = (id, updater) =>
    {
        using (NorthwindDataContext database = new NorthwindDataContext())
        {
            Category category = database.Categories
                                        .Single(item => item.CategoryID == id);

            Thread.Sleep(4000);

            updater(category);
            try
            {
                // All data changes will be tried before rollback.
                database.SubmitChanges(ConflictMode.ContinueOnConflict);
                // Now all conflicts are stored in DataContext.ChangeConflicts.
            }
            catch (ChangeConflictException)
            {
                foreach (ObjectChangeConflict conflict in database.ChangeConflicts)
                {
                    Console.WriteLine(
                        "Conflicted row: ID = {0}.",
                        (conflict.Object as Category).CategoryID);

                    foreach (MemberChangeConflict member in conflict.MemberConflicts)
                    {
                        Console.WriteLine(
                            "[{0}] column is expected to be '{1}' in database, but it is not.",
                            member.Member.Name,
                            member.CurrentValue);
                    }

                    conflict.Resolve(RefreshMode.KeepChanges); // Queries row to merge changes.
                    Console.WriteLine("Merged changes to row: {0}.", conflict.IsResolved);
                }

                // Submits again by merging changes.
                database.SubmitChanges();
            }
        }
    };

new Thread(() => updateCategory(1, category => category.CategoryName = "Thread 1")).Start();

Thread.Sleep(2000);

new Thread(() => updateCategory(1, category => category.Description = "Thread 2")).Start();

Running this refined code will print:

Conflicted row: ID = 1.
[CategoryName] column is expected to be 'Beverages' in database, but it is not.
Merged changes to row: True.

This is the order of the executions:

Time (second)	Thread 1	Thread 2	[CategoryName]	[Description]
0	Retrieves “Beverages” for [CategoryName].		“Beverages”	“Soft drinks, coffees, teas, beers, and ales”
2		Retrieves “Beverages” for [CategoryName].	“Beverages”	“Soft drinks, coffees, teas, beers, and ales”
4	Checks whether [CategoryName] is “Beverages”, and updates [CategoryName].		“Thread 1”	“Soft drinks, coffees, teas, beers, and ales”
6		Checks whether [CategoryName] is “Beverages”.	“Thread 1”	“Soft drinks, coffees, teas, beers, and ales”
		Retrieves “Thread1” for [CategoryName]	“Thread 1”	“Soft drinks, coffees, teas, beers, and ales”
		Checks whether [CategoryName] is “Thread 1”., and updates [Description].	“Thread 1”	“Thread 2”

Please notice that, to merge the changes, database must be queried.

This is the entire translated SQL:

-- Thread 1 reads.
exec sp_executesql N'SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1

-- Thread 2 reads.
exec sp_executesql N'SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1

-- Thread 1 writes.
BEGIN TRANSACTION 
exec sp_executesql N'UPDATE [dbo].[Categories]
SET [CategoryName] = @p2
WHERE ([CategoryID] = @p0) AND ([CategoryName] = @p1)',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000)',@p0=1,@p1=N'Beverages',@p2=N'Thread 1' -- CategoryName has an [Column(UpdateCheck = UpdateCheck.Always)] attribute.
COMMIT TRANSACTION -- Updating successes.

-- Thread 2 writes.
BEGIN TRANSACTION 
exec sp_executesql N'UPDATE [dbo].[Categories]
SET [Description] = @p2
WHERE ([CategoryID] = @p0) AND ([CategoryName] = @p1)',N'@p0 int,@p1 nvarchar(4000),@p2 ntext',@p0=1,@p1=N'Beverages',@p2=N'Thread 2' -- CategoryName has an [Column(UpdateCheck = UpdateCheck.Always)] attribute.
ROLLBACK TRANSACTION -- Updating fails.

-- Thread 2 reads data to merge changes.
exec sp_executesql N'SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
FROM [dbo].[Categories] AS [t0]
WHERE [t0].[CategoryID] = @p0',N'@p0 int',@p0=1

-- Thread 2 writes again.
BEGIN TRANSACTION 
exec sp_executesql N'UPDATE [dbo].[Categories]
SET [CategoryName] = @p2, [Description] = @p3
WHERE ([CategoryID] = @p0) AND ([CategoryName] = @p1)',N'@p0 int,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 ntext',@p0=1,@p1=N'Thread 1',@p2=N'Thread 1',@p3=N'Thread 2'
COMMIT TRANSACTION -- Updating successes.

To resolve conflicts, an easier way is just invoking ChangeConflictCollection.ResolveAll():

catch (ChangeConflictException)
{
    database.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
    database.SubmitChanges();
}

More about concurrency

Because this is a LINQ / functional programming series, not a SQL / database series, this post only gives a brief explanation about how LINQ to SQL controls concurrent conflict. please check MSDN and Wikipedia for further topics, like concurrency, concurrency control, optimistic concurrency control, timestamp-based concurrency control, SQL Server transactions, SQL Server locking, SQL Server isolation levels, SQL Server row level versioning, etc.