I’ve tried an interesting experiment today trying to see if I can get LINQ to SQL to run with a custom ADO.NET Data Provider I’ve created some time ago. Basically I implemented a provider that is a proxy to a remote Web  ‘service’ that can marshal SQL commands via Web Requests on a remote server. I implemented a custom provider for this interface that ships XML across the wire and marshals raw SQL commands to the Web server to process. It works well using plain ADO.NET and my own internal business layer.

The provider uses SQL Server syntax since all it’s really doing is marshalling commands to the server via XML, but the raw syntax of commands is still full SQL Server T-SQL dialect. So I started thinking why shouldn’t I be able to use this provider with LINQ to SQL since the SQL it generates should still work with this provider – no dialect problems here. 

The dialect is important though since LINQ to SQL is SQL Server specific. I’m not sure what logic it actually uses to figure out which provider it’s dealing with (it officially supports SQL 2005, Sql 2008 and Sql Compact), but I suspect the default is SQL 2005, which is likely what’s being used with my provider. Either way – the following will only work if you have a provider that’s highly SQL Server T-SQL compatible . If the provider is some other database that isn’t closely TSQL compatible (like Oracle, MySql etc.) this approach won’t work, so this is not a general purpose solution.

After an evening of mucking around with this I was able to get LINQ to SQL to work with my custom provider. The process involved is actually quite simple, although it’s not real obvious to discover. LINQ to SQL doesn’t have any explicit support for plugging in new providers (unfortunately) however, you CAN pass it an instance of a Connection object like this:

WebRequestConnection conn = new WebRequestConnection();  // my custom provider’s Connection class
conn.ConnectionString = "Data Source=http://rasnote/PraWeb/DataService.ashx;uid=ricks;pwd=secret";   // this.connectionString         
TimeTrakkerDataContext context = new TimeTrakkerDataContext(conn);  

Here I create an instance of my custom provider’s  WebRequestConnection() class, set its connection string and pass it to the constructor of the DataContext. And lo and behold – assuming your provider is compatible with SQL syntax and implements the DbProvider classes properly LINQ to SQL works using the custom provider.

I haven’t done any extensive checking, but running through a number of my small apps I’ve been able to simply plug in the Web ADO.NET provider and the apps work so far. My ADO.NET provider implementation is pretty bare bones. Because it’s disconnected for example it doesn’t support Transactions, but for the applications that I will be using this for this won’t be a problem since the app deals with simple atomic updates.

This post probably falls into the stupid pet tricks category since there’s not much need to use an alternate provider that uses SQL Server dialect, but I’m pretty stoked because this solves a very specific problem I’ve been agonizing over. I could not  LINQ to SQL for this particular app because of the remote provider aspect that should be switchable with local and SQL operation. Now it looks I will be able to. This gives me Sql Server, Sql Compact (local data) and remote Web access.

I haven’t had enough time to try this out with other providers, but I wonder how compatible the SQL driver actually has to be to work with LINQ to SQL. For example, there are several other tools out there that supposedly are very compatible SQL Server syntax. VistaDb springs to mind since they claim a very high level of SQL Server compatibility. I’d be curious if something like VistaDb could actually be made to work with LINQ to SQL using this approach.