all-in-one/visualstudio/entityFramework.md

Entity Framework Core

• As of 2022, mdb entity ONLY support EF Core 3.x
• EntityFrameworkCore.Jet
• Tutorial
• EF Core best practice (Youtube)

Code First Approach

• Create the code for DbContext and models
• Run the migration script to generate the db tables
• Tutorial

PM> add-migration InitialDb
PM> update-database
PM> remove-migration
PM> Drop-Database
PM> script-migration

Database First Approach (Reverse Engineering)

• Note: For mdb, this only work with Jet.OleDB Provider
• Generate DbContext & model from database
• [Tutorial] (https://github-wiki-see.page/m/bubibubi/EntityFrameworkCore.Jet/wiki/Using-Entity-Framework-Core-with-Jet-in-Traditional-.Net-Applications)
• Nuget package
  • Microsoft.EntityFrameworkCore (3.1.31)
  • EntityFrameworkCore.Jet.OleDb (3.1.1)
  • Microsoft.EntityFrameworkCore.Tools (3.1.31)

PM> Scaffold-DbContext "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\MitLanguage.mdb;" EntityFrameworkCore.Jet -OutputDir Models -DataAnnotations -Force

Connection String

• ConnectionString

• Using OleDb (EntityFrameworkCore.Jet.OleDb)

  • x64 : Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\myFolder\\MitLanguage.mdb;
  • x86 : Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\Machine\\Database\\MitMc.mdb;

• Using ODBC (EntityFrameworkCore.Jet.Odbc)

  • x64 : Driver={Microsoft Access Driver (*.mdb, *.accdb)}; Dbq = C:\\myFolder\\MitCustom.mdb; Exclusive=1; Uid = Admin; Pwd =;
  • x86 : Driver={Microsoft Access Driver (*.mdb)}; Dbq = C:\\myFolder\\MitCustom.mdb; Exclusive=1; Uid = Admin; Pwd =;
  • For x86, use ODBC Data Source (32-bit) to add the UserDSN driver for 32-bit platform

Sample Query

using EFCoreTest.Models;
using System.Text.Json;

using (var db = new CustomContext())
{
    ReelAutoChanger? reelAutoChanger = db.ReelAutoChanger.FirstOrDefault();
    string jsonString = JsonSerializer.Serialize(reelAutoChanger);
    Console.WriteLine(jsonString);
    Console.ReadKey();
}

using (var db = new MachineContext())
{
    List<MotorProfile> motorProfiles = db.MotorProfile.ToList();
    //IQueryable<MotorProfile> motorProfiles = db.MotorProfile.FromSqlRaw("Select * from MotorProfile");
    //var emp = context.Employee.FromSqlRaw("Select * from Employee").OrderBy(x => x.Name).ToList();
    string jsonString = JsonSerializer.Serialize(motorProfiles);
    Console.WriteLine(jsonString);
    Console.ReadKey();

    List<Motor> motors = db.Motor.Where(b => b.ModuleName == "PnP").ToList();
    jsonString = JsonSerializer.Serialize(motors);
    Console.WriteLine(jsonString);
    Console.ReadKey();
}