6

I created an ODBC DSN for my Asp.Net MVC application's database access. One of the main reasons is it makes it easy to keep database credentials (such as server address, port, username, and password) out of source control without hindering my publishing abilities.

So I changed my connection to be DSN=MyDSN.

Unfortunately, when I run my Entity Framework queries I get Exception Details: System.ArgumentException: Keyword not supported: 'dsn'.

Does anyone know what I am doing wrong?

Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
KallDrexx
  • 27,229
  • 33
  • 143
  • 254

1 Answers1

10

If you want to use ODBC DSN your connection string must use System.Data.Odbc native provider instead of managed SQL client.

Edit:

So now from theory to practice. It doesn't work because of internal EF implementation. EF internally calls some method which tries to get DbProviderFactory from the created connection. The problem is that this property is defined in DbConnection and it returns null. Only SqlConnection overrides property and returns correct factory. So EF doesn't work with default ODBC provider and here is very clearly described why.

Community
  • 1
  • 1
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • Changing my provider name to `providerName="System.Data.Odbc"` gives me `System.Data.ProviderIncompatibleException: A null was returned after calling the 'get_ProviderFactory' method on a store provider instance of type 'System.Data.Odbc.OdbcConnection'. The store provider might not be functioning correctly.` – KallDrexx Jul 04 '11 at 14:58
  • ` ` – KallDrexx Jul 04 '11 at 15:27
  • Go back to SqlClient. It will not work with Odbc because EF is not able to generate SQL for Odbc driver. – Ladislav Mrnka Jul 07 '11 at 12:26
  • Any idea how to do a DSN with SqlClient, that just brings me back to the original error. – KallDrexx Jul 07 '11 at 12:35
  • 1
    No idea. As I understand it ODBC is not supported by EF at all. If you want to use ODBC, don't use entity framework. If you want to use EF don't use ODBC. Especially if the only reason why you moved to ODBC is keeping connection details outside of your configuration file. – Ladislav Mrnka Jul 07 '11 at 12:47