Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs
Contact   •   Articles   •   Products   •   Support   •   Search
Ad-free experience sponsored by:
ASPOSE - the market leader of .NET and Java APIs for file formats – natively work with DOCX, XLSX, PPT, PDF, images and more

LINQ to SQL and alternate Providers


:P
On this page:

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.

Posted in LINQ  

The Voices of Reason


 

Matt
May 18, 2009

# re: LINQ to SQL and alternate Providers

Rick,

You have probably read the post below. Matt Warren talks about plugging in a new provider when the feature in LINQ to SQL was specifically disabled for RTM.

Mocks Nix - An Extensible LINQ to SQL DataContext
http://blogs.msdn.com/mattwar/archive/2008/05/04/mocks-nix-an-extensible-linq-to-sql-datacontext.aspx

"LINQ to SQL was actually designed to be host to more types of back-ends than just SQL server. It had a provider model targeted for RTM, but was disabled before the release. Don’t ask me why. Be satisfied to know that is was not a technical reason. Internally, it still behaves that way. The trick is to find out how to swap in a new one when everything from the language to the runtime wants to keep you from doing it."

Darrell
May 18, 2009

# re: LINQ to SQL and alternate Providers

Is Linq to Sql being enhanced for .NET 4.0? I read that somewhere the other day and it surprised me since I thought MS was focusing on the Entity Framework and just leaving Linq to Sql as-is. Maybe it's just bug fixes.

Matt
May 18, 2009

# re: LINQ to SQL and alternate Providers

Darrel,

I think we can only expect to see bug fixes and very minor enhancements. EF vNext will see the major investment.

DotNetShoutout
May 18, 2009

# LINQ to SQL and alternate Providers - Rick Strahl

Thank you for submitting this cool story - Trackback from DotNetShoutout

Jason Haley
May 18, 2009

# Interesting Finds: May 18, 2009

Interesting Finds: May 18, 2009

Rick Strahl
May 18, 2009

# re: LINQ to SQL and alternate Providers

@Matt - Matt Warren's extensions are interesting but I think the goal of his approach was for testing and providing mock behavior. Plus it requires private reflection to work so it won't work in Web apps running in Medium trust, so probably not a solution that works. I wonder though if anybody has tried to just replace the "provider" property on the DataContext with another provider using the first few lines of code?

The main problem I see is one of dialect. While we may be able to get a custom provider in there in some way getting the syntax across different SQL dialects probably destroys any easy workarounds in this space. This would take skills that I don't have :-}.

However for T-SQL dialect I think what I used here might just work. I have to run this through its paces some more, but in early testing and plugging into existing applications I think this might just be workable.

Matt Vanderhoof
May 18, 2009

# re: LINQ to SQL and alternate Providers

Thanks for the info, Rick!

In case anyone is curious, this trick also works (with some caveats) to trick LINQ to SQL into connecting to SQL Server 2000 (and possibly earlier) databases. If I recall correctly, I had to roll my own classes (no designer support for unsupported SQL Server versions), but just creating a SqlConnection and passing it to the DataContext did the trick.

There are definitely some things that break, since LINQ to SQL relies on functionality not present in earlier versions of SQL Server, but for simple select queries it works fine. And, like Rick's example, it doesn't require use of private reflection or anything like that.

Jason Short
May 22, 2009

# re: LINQ to SQL and alternate Providers

Interesting. I am not sure if that would work with VistaDB or not. I guess it would depend upon the SQL statements being generated under the hood and whether they work. How did you handle transactions? I don't remember in L2S, but EF uses transactions for everything on the planet (even select statements sometimes).

DotNetKicks.com
May 22, 2009

# LINQ to SQL and alternate Providers

You've been kicked (a good thing) - Trackback from DotNetKicks.com

Rick Strahl
May 22, 2009

# re: LINQ to SQL and alternate Providers

@Jason - it all depends on how compatible the driver is I suppose. I remember some time ago I used VistaDb on a project as a drop in replacement for local SQL Server and it just worked with a different provider hooked up.

Transactions should be handled by the plain ADO.NET Data Provider implementation, so if your provider supports this it should just work. Since you guys have a full provider (unlike mine which is built from scratch and only has minimal feature support) this shouldn't be a problem. On my driver I have no transaction support because it's stateless Web data access mechanism. For what I'm doing with this this (mainly a flat file table) this is not really a problem.

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2019