# Entity Framework Core

Entity Framework Core (EF Core) powers FlexBase’s relational story, but it is not wired as a “DataStore Provider” the way document DB, file store, etc. are.

In generated apps, relational infrastructure is organized under:

* `Infrastructure/Db/*` (EF `DbContext` base + provider-specific repositories/bridges + connection providers)
* `EndPoints/DbMigrations/*` (provider-specific migrations runners)

### Why it matters

* **Change Tracking** keeps entity graphs in sync during persistence.
* **LINQ/Projection** simplifies reporting and pagination from handlers.
* **Navigation properties** let you compose rich joins when handlers need related data.
* **Resilience** features from EF Core keep transient failures transparent to your handlers.

> Flex note: for async `IQueryable<T>` execution, prefer `Sumeru.Flex.FlexQueryableAsyncExtensions` (e.g., `ToListAsyncFlex`, `FirstOrDefaultAsyncFlex`) over EF Core’s native async query helpers.

## Infrastructure Arrangement

Rather than inventing domain repositories, generated code leans on `DomainHandler/.../Queries` and `DomainHandler/.../Handlers` folders (for example `List{Entities}`, `Create{Entity}Handler`, etc.). Each query/handler receives relational services through DI (for example `IRepoFactory`, `IPostBus`, logging), executes LINQ queries or persists changes, and then publishes events.

Typical layout:

* EF base context: `Infrastructure/Db/{YourApplication}.BaseEF/ApplicationEFDbContext.cs`
* Provider-specific repos/bridges: `Infrastructure/Db/{YourApplication}.BaseEF.SqlServer/*`, `...PostgreSql/*`, `...MySql/*`
* Connection providers (appsettings-driven): `Infrastructure/Db/{YourApplication}.BaseEF/ConnectionProviders/*`
* Migrations runners: `EndPoints/DbMigrations/{YourApplication}.Migrations.{Provider}/*`

## Handler Example (Insert/Update)

Handlers typically:

1. Hydrate the request context (`cmd.Dto.GetAppContext()`)
2. Initialize relational context with the DTO parameters
3. Create/update the domain model
4. Persist changes
5. Optionally raise follow-up events through the generated `Fire(...)` pipeline

```csharp
using Sumeru.Flex;

public partial class CreateApplicationHandler : ICreateApplicationHandler
{
  protected readonly IFlexHost _flexHost;
  protected readonly IRepoFactory _repoFactory;
  protected FlexAppContextBridge? _flexAppContext;
  protected string EventCondition = "";

  public CreateApplicationHandler(IFlexHost flexHost, IRepoFactory repoFactory)
  {
    _flexHost = flexHost;
    _repoFactory = repoFactory;
  }

  public virtual async Task Execute(CreateApplicationCommand cmd, IFlexServiceBusContext serviceBusContext)
  {
    _flexAppContext = cmd.Dto.GetAppContext();  //do not remove this line
    _repoFactory.Init(cmd.Dto);

    var model = _flexHost.GetDomainModel<Application>().CreateApplication(cmd);
    var repo = _repoFactory.GetRepo();

    repo.InsertOrUpdate(model);
    var records = await repo.SaveAsync();

    // TODO: Set EventCondition based on business logic (e.g., "ONSUCCESS")
    await this.Fire(EventCondition, serviceBusContext);
  }
}

public partial class UpdateReferralHandler : IUpdateReferralHandler
{
  protected readonly IRepoFactory _repoFactory;
  protected FlexAppContextBridge? _flexAppContext;
  protected string EventCondition = "";

  public UpdateReferralHandler(IRepoFactory repoFactory) => _repoFactory = repoFactory;

  public virtual async Task Execute(UpdateReferralCommand cmd, IFlexServiceBusContext serviceBusContext)
  {
    _flexAppContext = cmd.Dto.GetAppContext();  //do not remove this line
    _repoFactory.Init(cmd.Dto);
    var repo = _repoFactory.GetRepo();

    var model = await repo.FindAll<EmployeeReferral>()
      .FirstOrDefaultAsyncFlex(m => m.Id == cmd.Dto.Id);

    if (model == null)
    {
      // Optionally set EventCondition to a failure condition.
      await this.Fire(EventCondition, serviceBusContext);
      return;
    }

    model.UpdateReferral(cmd);
    repo.InsertOrUpdate(model);
    await repo.SaveAsync();

    // TODO: Set EventCondition based on business logic (e.g., "ONSUCCESS")
    await this.Fire(EventCondition, serviceBusContext);
  }
}
```

Both handlers keep the intent logic in the command class while reusing the generated infrastructure for EF Core context resolution and event publishing.

## Sample Usage

```csharp
using Sumeru.Flex;

public class ListApplications : FlexiQueryPagedListBridgeAsync<Application, ListApplicationsParams, ListApplicationsDto, FlexAppContextBridge>
{
  protected readonly ILogger<ListApplications> _logger;
  protected ListApplicationsParams _params;
  protected readonly IRepoFactory _repoFactory;

  public ListApplications(ILogger<ListApplications> logger, IRepoFactory repoFactory)
  {
    _logger = logger;
    _repoFactory = repoFactory;
  }

  public virtual ListApplications AssignParameters(ListApplicationsParams @params)
  {
    _params = @params;
    return this;
  }

  public override async Task<FlexiPagedList<ListApplicationsDto>> Fetch()
  {
    _repoFactory.Init(_params);

    var items = await Build<Application>()
      .Where(a => !a.IsDeleted)
      .Select(a => new ListApplicationsDto { Id = a.Id, JobTitle = a.JobPosting.JobTitle })
      .ToListAsyncFlex();

    return BuildPagedOutput(items);
  }

  protected override IQueryable<T> Build<T>()
  {
    _repoFactory.Init(_params);
    var query = _repoFactory.GetRepo().FindAll<T>();
    return CreatePagedQuery(query, _params.Skip, _params.PageSize);
  }
}
```

### Simple list example (lookup)

Use a simple enumerable query for “lookup” lists (the pattern matches typical `Get{Entity}ForLookup` queries).

```csharp
using Sumeru.Flex;

public class GetDepartmentsForLookup : FlexiQueryEnumerableBridgeAsync<OrgAccount, GetDepartmentsForLookupDto>
{
  protected readonly ILogger<GetDepartmentsForLookup> _logger;
  protected GetDepartmentsForLookupParams _params;
  protected readonly IRepoFactory _repoFactory;

  public GetDepartmentsForLookup(ILogger<GetDepartmentsForLookup> logger, IRepoFactory repoFactory)
  {
    _logger = logger;
    _repoFactory = repoFactory;
  }

  public virtual GetDepartmentsForLookup AssignParameters(GetDepartmentsForLookupParams @params)
  {
    _params = @params;
    return this;
  }

  public override async Task<IEnumerable<GetDepartmentsForLookupDto>> Fetch()
  {
    _repoFactory.Init(_params);

    var items = await Build<OrgAccount>()
      .Where(o => o.IsActive)
      .Where(o => o.OrgAccountTypeId == OrgAccountTypeEnum.Department.Value)
      .OrderBy(o => o.Name)
      .SelectTo<GetDepartmentsForLookupDto>()
      .ToListAsyncFlex();

    return items;
  }

  protected override IQueryable<T> Build<T>()
  {
    _repoFactory.Init(_params);
    return _repoFactory.GetRepo().FindAll<T>();
  }
}

public class GetDepartmentsForLookupParams : DtoBridge { }
```

### Get-by-id example

Use a single-result query for `Get{Entity}ById` patterns.

```csharp
using Sumeru.Flex;

public class GetApplicationById : FlexiQueryBridgeAsync<Application, GetApplicationByIdDto>
{
  protected readonly ILogger<GetApplicationById> _logger;
  protected GetApplicationByIdParams _params;
  protected readonly IRepoFactory _repoFactory;

  public GetApplicationById(ILogger<GetApplicationById> logger, IRepoFactory repoFactory)
  {
    _logger = logger;
    _repoFactory = repoFactory;
  }

  public virtual GetApplicationById AssignParameters(GetApplicationByIdParams @params)
  {
    _params = @params;
    return this;
  }

  public override async Task<GetApplicationByIdDto> Fetch()
  {
    var result = await Build<Application>()
      .FlexInclude("ApplicationState")
      .FlexInclude("JobPosting")
      .FlexInclude("Candidate")
      .SelectTo<GetApplicationByIdDto>()
      .FirstOrDefaultAsyncFlex();

    return result;
  }

  protected override IQueryable<T> Build<T>()
  {
    _repoFactory.Init(_params);
    return _repoFactory.GetRepo().FindAll<T>().Where(t => t.Id == _params.Id);
  }
}

public class GetApplicationByIdParams : DtoBridge
{
  public string Id { get; set; }
}
```

Handlers follow this approach—initialize relational context, project entities, and return DTOs—so you can rely on the same pattern across modules.

## Configuration Keys

1. `FlexBase:AppDbConnection` – Primary relational connection string.

If you need separate read/write connections or tenant/master connections, follow the provider-specific connection providers under `Infrastructure/Db/*/ConnectionProviders`.

## Best Practices

1. Stick to the handlers/queries in `DomainHandler/...` so the generator can update wiring and keep telemetry consistent.
2. Emit events via `PostBus` inside handlers like `CreateApplicationHandler`/`SubmitReferralHandler` to keep integration points decoupled.
3. Use DTO projections (see `ListApplications`) to keep EF Core in read-only mode when possible.

## Testing & Tooling

* Unit tests typically execute the generated queries/handlers directly and provide any relational dependencies through DI.

## Provider-Specific SQL (ProviderSqlMap)

When you need raw SQL that differs per provider (SQL Server vs PostgreSQL vs MySQL), use `ProviderSqlMap` so a repository can pick the correct SQL based on the configured EF provider.

```csharp
using Sumeru.Flex;

var sqlMap = ProviderSqlMap.Create(sql => sql
  .ForSqlServer("SELECT TOP 10 * FROM dbo.MyEntities ORDER BY CreatedDate DESC")
  .ForPostgres("SELECT * FROM \"MyEntities\" ORDER BY \"CreatedDate\" DESC LIMIT 10")
  .ForMySql("SELECT * FROM `MyEntities` ORDER BY `CreatedDate` DESC LIMIT 10")
  .UseDefault("SELECT * FROM MyEntities ORDER BY CreatedDate DESC LIMIT 10"));

var repo = _repoFactory.GetRepo();
var results = await repo.ExecuteSqlRaw<MyEntity>(sqlMap).ToListAsyncFlex();
```

## Soft Delete Filter

If your entities derive from `TFlex`, you can enable a global query filter that hides rows where `IsDeleted` is `true`.

Enable either:

* Per context: `context.UseSoftDeleteFilters()`
* Via DI: `FlexSoftDeleteOptions` (e.g., `Enabled = true`)

To include deleted rows for a scope:

```csharp
using Sumeru.Flex;

using (queryRepository.SuppressSoftDeleteFilter())
{
  var all = await queryRepository.FindAll<MyEntity>()
    .Where(e => e.IsDeleted)
    .ToListAsyncFlex();
}
```

## Audit Trail

To capture audit trail rows for Added/Modified/Deleted entities, enable audit trail behavior on the EF context / interceptor as documented.

Typical options:

* Enable on your `FlexEFDbContext` implementation (often a one-liner such as `EnableAuditTrail()`).
* Or register an interceptor and options in DI and attach it via `AddDbContext`.

Querying audit trails is then just a normal EF query:

```csharp
using Sumeru.Flex;

var recent = await db.AuditTrails
  .OrderByDescending(a => a.DateTime)
  .Take(50)
  .ToListAsyncFlex();
```