Can I use LINQ2SQL with ODBC?

Question

I am writing and ASP.NET MVC application with SQL Server 2008. Unfortunately, the only way I can connect to that database is through a dsn or ODBC in my production environment. In development I have used a Linq2SQL layer to access data in the database. Is there a way that I can force Linq2SQL to use ODBC for connectivity rather than a SQL Client connection?

Answer 1

Darin, while you could use the solution suggested by Peder, it would defeat the purpose of Linq, as you would be querying the server directly instead of mapping. Tom is right about the "hard coded" but there is a way around, you could create your own interface, a great explanation on the concepts and how to do it is here: http://blogs.msdn.com/b/mattwar/archive/2008/11/18/linq-links.aspx

Answer 2

The code generated by LINQ to SQL has hardcoded references to SQL server connections all over the place, so no. However, you may be able to use DBLinq.

Answer 3

Yes, there is a way to use ODBC with LINQ to SQL, but some extra code is required and it will not be nearly as feature-rich. I know that you can get ORM for SELECT statements, but I'm unsure if it will work for UPDATE or DELETE statements.

In the following code snippet, I first defined my model in an empty Linq2Sql .dbml file (I manually created a PurchaseOrder object with the fields that I need). But then note how I'm able to create a new data context by passing in an ODBC connection to it. Finally, there's one last trick: I have to use the data context's ExecuteQuery method along with a SELECT statement to get my purchase orders.

using (var connection = new OdbcConnection("Driver={iSeries Access ODBC Driver};System=serverName;UID=yourUid;PWD=yourPwd"))
{
    var purchaseOrderContext = new Models.PurchaseOrdersDataContext(connection);

    var purchaseOrders = purchaseOrderContext.ExecuteQuery<Models.PurchaseOrder>("SELECT * FROM mySchema.myTable");
}