Query Microsoft Access MDB Database using LINQ and C#

Question

I have a *.MDB database file, and I am wondering if it is possible or recommended to work against it using LINQ in C#. I am also wondering what some simple examples would look like.

I don't know a lot about LINQ, but my requirements for this task are pretty simple (I believe). The user will be passing me a file path to Microsoft Access MDB database and I would like to use LINQ to add rows to one of the tables within the database.

Thanks.

Answer 1

What you want is a LINQ to ODBC provider, or a LINQ to JET/OLEDB provider.

Out of the box, MS doesn't make one. There may be a 3rd party who does.

Answer 2

LINQ to SQL only works for SQL Server databases. What you need is the Microsoft Entity Framework. This makes object oriented access to your mdb. From this you can run LINQ queries.

http://msdn.microsoft.com/en-us/library/aa697427(vs.80).aspx

Answer 3

Here is an earlier thread on the subject: http://stackoverflow.com/questions/194528/linq-aspnet-page-against-ms-access

Answer 4

Why not try ALinq, the bestest database linq provider,it supports MS Access, SQLite, MySQL,Oracl and firebird database.

Answer 5

Actually I recently (today) discovered that you can access an Access database with LinqToSql. It must be in the 2002 or newer format, you will not be able to drag and drop the tables to your datacontext so either manually create the objects in your dbml or you can use SQL Server Migration for Access to move it to a sql server and then drag and drop all you want. When you want to actually create the context pass it an OleDbConnection. Use your standard Jet.OLEDB.4.0 connection string on the OleDbConnection and you are good to go. Not sure of the limitation this may incurr though. I just did a quick sample and did an OrderBy without issue.

Answer 6

You can use a DataSet. There are linq extensions that will allow you to query the data with all that LINQ goodness we have become use to :)

        eICATDataSet.ICSWSbuDataTable tbl = new eICATDataSet.ICSWSbuDataTable();

        ICSWSbuTableAdapter ta = new ICSWSbuTableAdapter();
        ta.Fill(tbl);

        var res = tbl.Select(x => x.ProcedureDate.Year == 2010);