From MS-Access to EF Core (C#)

In this article learn how to go from working with a .mdb MS-Access database using OleDb data provider to a .accdb using EF Core 7.

Inspiration comes from coders asking how I can take a .NET Framework application to .NET Core Framework and use EF Core rather than a managed data provider which does not require VBA and/or macros.

Although a better idea is to move completely away from MS-Access, lets take this one step at a time by moving from .mdb to .accdb as perhaps this is a personal project used by one person on a local computer. If the application is for multiple users, the smart choice is to examine the current database structure and evaluate if changes can be made or not before moving to a database like SQL Server.

Source code

Clone the following GitHub repository

Example database

Rather than a single table, this example will store books with a relationship to categories as its rare to have a single table.

Models



public class Book
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }
    [Precision(precision: 10, scale: 2)] 
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    public Category Category { get; set; }
    public override string ToString() => Title;
}

public class Category
{
    [Key]
    public int CategoryId { get; set; }
    public string Description { get; set; }
    public List<Book> Books { get; set; }
}

DbContext

The DbContext instance represents a session with the database and can be used to query and save instances of your entities. DbContext is a combination of the Unit Of Work and Repository patterns.



public class BookContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Category> Categories { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseJet(ConfigurationHelper.ConnectionString());
    }
}

Getting into more configurations, in this case moving to SQL-Server.

Indicating which column is the primary key
Indicate known indices
Which columns are required
Relationships



public void Configure(EntityTypeBuilder<Products> entity)
{
    entity.HasKey(e => e.ProductID);

    entity.HasIndex(e => e.CategoryID, "IX_Products_CategoryID");

    entity.HasIndex(e => e.SupplierID, "IX_Products_SupplierID");

    entity.Property(e => e.ProductName)
    .IsRequired()
    .HasMaxLength(40);
    entity.Property(e => e.QuantityPerUnit).HasMaxLength(20);
    entity.Property(e => e.UnitPrice).HasColumnType("money");

    entity.HasOne(d => d.Category).WithMany(p => p.Products)
    .HasForeignKey(d => d.CategoryID)
    .HasConstraintName("FK_Products_Categories");

    entity.HasOne(d => d.Supplier).WithMany(p => p.Products)
    .HasForeignKey(d => d.SupplierID)
    .HasConstraintName("FK_Products_Suppliers");

    OnConfigurePartial(entity);
}

partial void OnConfigurePartial(EntityTypeBuilder<Products> entity);
}

The example above may appear daunting for first timers but when moving to SQL-Server there is a Visual Studio extension, EF Power Tools which will handle this all for you but not for MS-Access.

Creating and populating

When first starting out, until you are satisfied with the table structures use Database.EnsureDeletedAsync to ensure there is a fresh start, if the database exist this method deletes the database file then use Database.EnsureCreatedAsync which creates a new .accdb MS-Access file using the code below.

After satisfied with the database struture

Comment out the code which starts with creating builder and ends with EnsureCreatedAsync. If later there are more changes, uncomment the code. If changes are made and the code is not uncomments EF Core will not know about the updates and will provide wrong information or throw a runtime exception.



internal class InitialDatabaseSetup
{
    /// <summary>
    /// Create database and populate if the database does not exists
    /// </summary>
    /// <returns></returns>
    public static async Task Create()
    {
        await using var context = new BookContext();

        OleDbConnectionStringBuilder builder = new(context.Database.GetConnectionString());
        var fileName = builder.DataSource;

        if (File.Exists(fileName))
        {
            return;
        }

        await context.Database.EnsureDeletedAsync();
        await context.Database.EnsureCreatedAsync();

        context.Categories.Add(new Category()
        {
            Description = "Action"
        });
        context.Categories.Add(new Category()
        {
            Description = "Suspense & Thriller"
        });
        context.Categories.Add(new Category()
        {
            Description = "Fiction"
        });
        context.Categories.Add(new Category()
        {
            Description = "Learn C#"
        });
        context.Categories.Add(new Category()
        {
            Description = "EF Core 7"
        });

        await context.SaveChangesAsync();

        context.Books.Add(new Book()
        {
            Price = 12.50m, 
            Title = "EF Core 1", 
            CategoryId = 5
        });
        context.Books.Add(new Book()
        {
            Price = 26.96m, Title = 
                "The Exchange: After The Firm", 
            CategoryId = 2
        });
        context.Books.Add(new Book()
        {
            Price = 79.99m, 
            Title = "C# Vol 1", 
            CategoryId = 4
        });
        context.Books.Add(new Book()
        {
            Price = 14.99m, 
            Title = "The Manor House: A Novel", 
            CategoryId = 2
        });
        context.Books.Add(new Book()
        {
            Price = 27m, 
            Title = "The Wager: A Tale of Shipwreck, Mutiny and Murder", 
            CategoryId = 3
        });

        await context.SaveChangesAsync();
    }
}

Test everything

Once the create and populated code runs, write code to view the data. In this case all coding is done in a console project. Once satistied move the code to perhaps WPF, MAUI, Window forms or ASP.NET Core.



internal partial class Program
{
    static async Task Main(string[] args)
    {
        await InitialDatabaseSetup.Create();
        await using var context = new BookContext();

        var books = context.Books
            .Include(b => b.Category)
            .OrderBy(b => b.Category.Description)
            .ToList();

        AnsiConsole.MarkupLine("[cyan]All books[/]");
        foreach (var book in books)
        {
            Console.WriteLine($"{book.Id,-3}{book.Title.PadRight(70, '.')}" +
                              $"{book.Price,-8:C}{book.Category.Description}");
        }

        AnsiConsole.MarkupLine("[cyan]All Suspense & Thriller books[/]");
        books = context
            .Books
            .Include(b => b.Category)
            .Where(b => b.CategoryId == 2).ToList();


        foreach (var book in books)
        {
            Console.WriteLine($"{book.Id,-3}{book.Title.PadRight(70,'.')}" +
                              $"{book.Price,-8:C}{book.Category.Description}");
        }

        Console.WriteLine("Done");
        Console.ReadLine();
    }
}

When things go wrong

There are many possible things that can go wrong which can not all be covered here. The first thing to examine is the configuration for the models, in this case Books and Categories are each property defined with the proper type, also ensure that each table has an auto incrementing primary key. If still having issue, post a question on Stackoverflow where the link points to EF Core on Stackoverflow.

Project dependencies

EntityFrameworkCore.Jet NuGet package for working with EF Core and MS-Access
System.Data.OleDb NuGet package
ConfigurationLibrary Nuget package for accessing appsettings.json to get the database connection string
Spectre.Console NuGet package for console output

For your project

Only the first two packages are required.

Summary

This article has provided steps to using MS-Access database with EF Core via a special NuGet package in a .NET Core project.

If VBA or macros are used than its up to the reader to figure out alternatives.

In closing, if the reader is using MS-Access on a network, consider moving to a database like SQL Server and if for personal use consider SQLite rather than MS-Access.

SQL Server Migration Assistant for Access (AccessToSQL) is a utility which can move an Access database to SQL-Server