From MS-Access to EF Core (C#)
Tags: database, csharp, dotnetcore
Published: 2023-11-10 20:44:32

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](https://github.com/karenpayneoregon/efcore-msaccess)

## 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

```csharp
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](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.dbcontext?view=efcore-7.0) 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.

```csharp
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

```csharp
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](https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools) 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](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.storage.relationaldatabasecreator.ensuredeletedasync?view=efcore-7.0) to ensure there is a fresh start, if the database exist this method deletes the database file then use Database.[EnsureCreatedAsync](https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.storage.relationaldatabasecreator.ensurecreatedasync?view=efcore-7.0) 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.

```csharp
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();
    }
}
```

![Show what EF Core code above created](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uylxv3viyd3b7ib7n2pf.png)

## 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.

```csharp
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();
    }
}
```


![Results](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uad3v4uq7ehwrazfqtdf.png)

## 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](https://stackoverflow.com/questions/tagged/entity-framework-core) where the link points to EF Core on Stackoverflow.

## Project dependencies

- [EntityFrameworkCore.Jet](https://www.nuget.org/packages/EntityFrameworkCore.Jet/7.0.3?_src=template) NuGet package for working with EF Core and MS-Access
- [System.Data.OleDb](https://www.nuget.org/packages/System.Data.OleDb/7.0.0?_src=template) NuGet package
- [ConfigurationLibrary](https://www.nuget.org/packages/Spectre.Console/0.46.0?_src=template) 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 Acces](https://learn.microsoft.com/en-us/sql/ssma/access/sql-server-migration-assistant-for-access-accesstosql?view=sql-server-2017)s (AccessToSQL) is a utility which can move an Access database to SQL-Server