Entity Framework with OleDB connection - am I just plain nuts?

Question

I'm experimenting with the Entity Framework and I want to connect to an Access 2007 database.

The following code is inspired by http://msdn.microsoft.com/en-us/library/system.data.entityclient.entityconnection.connectionstring.aspx

I suspect that I've got the wrong end of the stick...

OleDbConnectionStringBuilder oledbConn = new OleDbConnectionStringBuilder();

oledbConn.DataSource = @"..\..\..\..\Pruebas.accdb"; //yep Access 2007!

EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder ();
entityBuilder.Provider = "Microsoft.ACE.OLEDB.12.0";
entityBuilder.ConnectionString = oledbConn.ToString();
EntityConnection ec = new EntityConnection(entityBuilder.ToString());
ec.Open();
ec.Close();

The EntityConnectionStringBuilder tells me that it doesn't support the DataSource property. I can connect fine with ADO.net so I know that the path and the provider are correct.

Is this just the complete wrong approach?

Answer 1

The approach you are using to build the EF connection string is correct.

BUT...

The Entity Framework only works with Providers (i.e. SqlClient) that support something called provider services.

The OleDB provider doesn't support 'Provider Services' so you can't use EF with the OleDb (unless you can find a third party OleDb provider with support for EF).

Hope this helps

Alex

(Entity Framework Team, Microsoft)

Answer 2

I'm not sure you have either end of the stick. :)

Check out this example instead. There might be other issues with your code, but it looks like you're setting the entity builder's ConnectionString property when you need to be setting its ProviderConnectionString property (among other properties).

It seems to me that for something called a "connection string builder", the ConnectionString property should be read-only (it's not). I guess it's intended to also double as a connection string parser.

Edit: I just looked at your code again, and I think all you have to do is change ConnectionString to ProviderConnectionString. You may have the stick after all!

Answer 3

To build your connection string create a file on your desktop called a.udl

Double click on it, should open a UI. Follow the wizard, test the connection.

Then close the UI, open the file with notepad, and you have your connection string.

EDIT It may be that you are getting this error because your are missing a refernce. Entity Framework uses extension methods. Therefore, it might compile but still not work.

Answer 4

Probably OleDb does not work with EF but the initialization works fine if you set the entityBuilder and the oledbConn in this way.

        OleDbConnectionStringBuilder oledbConn = new OleDbConnectionStringBuilder();
        oledbConn.Provider = "Microsoft.Jet.OLEDB.4.0";
        oledbConn.DataSource = @"C:\Users\Utente\Documents\visual studio 2013\Projects\How to implement the1\Debug\Test.mdb";

        EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
        entityBuilder.Provider = "System.Data.EntityClient";
        string connectionString = string.Format("metadata=res://*/School.csdl|res://*/School.ssdl|res://*/School.msl;provider=System.Data.OleDb;provider connection string='{0}'", oledbConn);
        entityBuilder.ConnectionString = connectionString;
        EntityConnection ec = new EntityConnection(entityBuilder.ToString());
        ec.Open();
        ec.Close();