How to use Entity framework for MS Access database

Question

I have to develop a desktop windows application in C#.Net 4.0 that will run in three different databases ie. MS Access 2007 onwards, Oracle 11G, and SQL Server 2008. Now I want to use Entity Framework in my application. Is this a best choice to use EF for my application ? If yes, then how can I use entity framework for Access database? Thanks in Advance.

Viand

Answer 1

You can use the EF with Microsoft Access using an Entity Framework provider for Microsoft Access. You can find an EF provider for Access

https://github.com/bubibubi/JetEntityFrameworkProvider

Ciprian

Hi - I followed the instructions for db first but when I define ADO entity framework -I don't get any option of data provider other than SQL server for connection. I have changed the machine config (I am running on 64 bit) – Parcel

To use DBFirst you need to compile from the source code (during compile the provider is registered in Visual Studio 2013). Could you open a specific question about this please? – Ciprian

Codeplex is shutting down so if Codeplex does not exist then go to bubibubi/JetEntityFrameworkProvider or search NuGet for JetEntityFrameworkProvider. – Neale

Answer 2

See this answer. Ask yourself why you need Access, could you use SQL Express instead? If you absolutely need Access then it would seem EF is not the right choice in this instance.

Putting Access to one side for the moment, it's perfectly possible to change data providers with EF (particularly code-first) as EF abstracts a lot of the database goo away from your code; depending on the database design it could be as simple as changing the connection string dynamically. The practical implications of this really do depend on your database design and the nature and complexity of the application you are building. See this question for example, here's another example.

Chattel

Then what should be advisable for the above scenario? Using Access database is must. – Viand

Then what is the right solution for above scenario. Any suggestion will be appreciated. – Viand

The next level down is ADO.NET. It's not ORM, but simply what you've done before ORM came. You can't do everything with Access that you can do with all other DBMS (like DDL), but otherwise it's working just fine here. – Centiliter

You could need Access because the host company doesn't support MS SQL. Strange for a hosting company in the year 2014. – Conchoidal

@Chattel Maybe you could link the tables from the Access database to a copy in SQL Server. Correct me if I'm wrong, but I think it should work, right? – Pitiable

My use case, when I came across this post... is that I want to hook up EF to both SQL and Access, and use EF to project all of the data from the old access database into the new SQL structure. Doing it via SQL is a hassle. – Lorica

@LonelyPixel You can use DDL with Access. (I'm doing it right now to setup test cases for an Access compatible ORM.) – Jer

Answer 3

We use Dapper as our ORM with repositories when working with MS Access. It's fantastic.

Stutsman