# Entity Framework Core

- As of 2022, mdb entity ONLY support EF Core 3.x
- [EntityFrameworkCore.Jet](https://github.com/bubibubi/EntityFrameworkCore.Jet)
- [Tutorial](https://www.entityframeworktutorial.net/efcore/entity-framework-core.aspx)
- [EF Core best practice (Youtube)](https://www.youtube.com/watch?v=qkJ9keBmQWo&t=7122s)

## Code First Approach
- Create the code for DbContext and models
- Run the migration script to generate the db tables
- [Tutorial](https://www.yogihosting.com/code-first-entity-framework-core/)

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

```

## Connection String
- [ConnectionString](https://www.connectionstrings.com/)

- 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

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