1

I would like to ask a question about using Entity Framework with Access. I am working on a project where I need to connect an Access database to my .NET application using Entity Framework. I have experience with Entity Framework and have used it with other databases like SQL Server, but I'm not sure how to set it up for Access. Can someone provide me with guidance on how to use Entity Framework with Access? Specifically, I would like to know how to create a connection string, generate entity classes, and perform CRUD operations with Access. Any help would be greatly appreciated.

??????????????????????????????????????????????????????????????????

Arian Amiri
  • 21
  • 1
  • 4
    Entity Framework 1.0 through 6.x **does not suport MS Access** - EF Core supports Access via a third-party NuGet package: https://www.nuget.org/packages/EntityFrameworkCore.Jet/ – Dai Jul 12 '23 at 21:17
  • 3
    May I ask why you want to support Access at all? Access' SQL engine is _literally_ unchanged since Access 97: it doesn't support _any_ SQL features introduced after 1992, it even requires you to wrap each `JOIN` level in parentheses (ew) - let alone support for CTEs, recursive queries, windowing-functions, it doesn't even support multiple statements in a batch. If you want a simple in-proc database I strongly recommend you consider Sqlite instead. – Dai Jul 12 '23 at 21:18
  • 1
    @Dai Yet MS-Access is used by many customers who are willing to pay to integrate this database into their processes. Now, having alerted the customer to the shortcomings of this system, the developer often has no choice but to "stick the donkey where the customer tells him to" (Freely translated from an Italian idiom) – Steve Jul 12 '23 at 21:43
  • @Steve You'd be surprised at how possible it is to convince clients/customers/PHBs that the short-term pain in migrating away from MS Access (as a data-store) to, say, SQL Server, while still using Access for Front-End/Forms/Queries/Reports/etc is worth it for the long-term benefits of not using a Billgatesforsaken database engine. – Dai Jul 12 '23 at 21:47
  • 1
    @Dai To be fair, the Access SQL engine isn't "literally" unchanged, it got support for multi-value fields and attachments with multiple attachments per row in 2007. Which is something almost no other database engine has (probably because it actively defeats normalization, but still). And there's been some recent introductions of data types SQL server got decades ago. It's not totally abandoned, just 99% – Erik A Jul 13 '23 at 08:01
  • @ErikA I understand that those new features in Access 2007 didn't require making any changes to Access' SQL engine: multivalued columns are stored using the preexisting "OLE" (i.e. blob/varbinary-equivalent) type, and all their logic is handled by the COM/VBA part of Access' codebase. (Postgres _almost_ had/has it _with_ FK support, but it's been on the cusp of release since 2014 - argh: https://stackoverflow.com/questions/41054507/postgresql-array-of-elements-that-each-are-a-foreign-key ) – Dai Jul 13 '23 at 08:11
  • @Dai You're mistaken there, since multi-valued fields/attachments have multiple entries per row they substantially influence the SQL engine, since you have these sub-tables with multiple rows within a row, and for attachments, they have multiple fields as well (filename, filedata, filetype). All good fun, especially if you want to migrate such a table to a different RDBMS, if it was just OLE and could map to BLOB that wouldn't be an issue. To make it even more fun, as of yet this appears to be entirely undocumented. – Erik A Jul 13 '23 at 08:19
  • @ErikA Yes, you're right - I was mostly wrong there. I'll admit that I haven't really had any exposure to post-`.accdb` Access features - instead my day-job frequently involves small one-off systems/data-integration work and every-so-often a JET 4 `.mdb` will land in my todo list and it only gets worse from there. IIRC, last time I had a proper deep-dive into JET Red 4.0 (about 2 years ago?) it ended up with me using Ghidra and Windbg to essentially single-step-through `msjet40.dll` - I learned _too much_ then... – Dai Jul 13 '23 at 08:35
  • @Dai. Yikes! I can't imagine anyone who doesn't work for Microsoft's support organization stepping through `msjet40.dll` with Windbg. I'm glad I don't have your job. – Flydog57 Jul 18 '23 at 15:33

0 Answers0