# Value Convertors

Transform values between C# types and database representations on every read and write operation.

## Overview

A value convertor sits between a C# property and the database column. It runs automatically on every INSERT, UPDATE, and SELECT. You never invoke it manually. A convertor can change both the direction and the type of the value, so the C# type and the column type do not need to match.

Common uses include encrypting sensitive columns, storing enums as strings, serializing collections to a delimited column, normalizing text casing, or adapting any C# type to a compatible database type.

## IDbValueConvertor Interface

```csharp
public interface IDbValueConvertor<TFrom>
{
    object? ConvertToDbValue(TFrom? value);    // C# → DB  (called on INSERT / UPDATE)
    TFrom? ConvertFromDbValue(object? dbValue); // DB → C# (called on SELECT)
}
```

`TFrom` is the declared C# property type. `ConvertToDbValue` receives the in-memory value and must return whatever the database driver expects. `ConvertFromDbValue` receives the raw value from the driver and must return the C# representation.

| Method | Direction | When called |
|--------|-----------|-------------|
| `ConvertToDbValue(TFrom?)` | C# → DB | INSERT, UPDATE |
| `ConvertFromDbValue(object?)` | DB → C# | SELECT |

**Requirements:**
- The convertor class must have a public, parameterless constructor. The source generator instantiates it with `new TConvertor()`.
- The convertor does not persist instance state across calls; do not rely on fields surviving between rows or queries.
- The `ConvertFromDbValue` parameter is the raw value returned by `IDataRecord.GetValue(i)`. For most column types this is the CLR type Npgsql maps to (`string`, `int`, `Guid`, etc.).

## Applying a Convertor

Place `[ValueConvertor(typeof(TConvertor))]` on the property you want to convert:

```csharp
[ValueConvertor(typeof(LowerCaseConvertor))]
public string Email { get; set; }
```

The value type of the property does not need to match the column type. For example, a `List<string>` property maps cleanly to a `TEXT` column if the convertor handles serialization and deserialization.

## Examples

### Lowercase Normalizer

Stores text in lowercase on every write; reads it back as-is (already lowercase in the database).

```csharp
using Socigy.OpenSource.DB.Core.Convertors;

public class LowerCaseConvertor : IDbValueConvertor<string>
{
    public object? ConvertToDbValue(string? value)
        => value?.ToLowerInvariant();

    public string? ConvertFromDbValue(object? dbValue)
        => dbValue?.ToString();
}
```

```csharp
[Table("user_logins")]
public partial class UserLogin
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ValueConvertor(typeof(LowerCaseConvertor))]
    public string Username { get; set; }

    public string? PasswordHash { get; set; }
}
```

### AES Encryption

Encrypts a string before persisting and decrypts on read. The database column stores `TEXT` (base-64 ciphertext).

```csharp
public class AesConvertor : IDbValueConvertor<string>
{
    public object? ConvertToDbValue(string? value)
        => value is null ? null : Encrypt(value);

    public string? ConvertFromDbValue(object? dbValue)
        => dbValue is null ? null : Decrypt(dbValue.ToString()!);

    private static string Encrypt(string plain) { /* AES encrypt + base-64 */ return plain; }
    private static string Decrypt(string cipher) { /* base-64 + AES decrypt */ return cipher; }
}
```

```csharp
[ValueConvertor(typeof(AesConvertor))]
public string SocialSecurityNumber { get; set; }
```

> **WARNING** Storing encryption keys in source code is shown here for brevity only. Use a secrets manager or environment variable in production.

### Enum as String

Stores the enum member name as text, making database values human-readable and safe across enum value reorderings.

```csharp
public class StatusConvertor : IDbValueConvertor<OrderStatus>
{
    public object? ConvertToDbValue(OrderStatus? value)
        => value?.ToString().ToLowerInvariant();

    public OrderStatus? ConvertFromDbValue(object? dbValue)
        => dbValue is null
            ? null
            : Enum.Parse<OrderStatus>(dbValue.ToString()!, ignoreCase: true);
}
```

```csharp
[ValueConvertor(typeof(StatusConvertor))]
public OrderStatus Status { get; set; } = OrderStatus.Active;
```

> **TIP** This pattern also prevents enum renaming from silently corrupting stored data. If you rename a member, old rows still contain the old string and `Enum.Parse` will throw a clear exception rather than silently mapping to a wrong value.

### List to CSV String

Maps a `List<string>` property to a single `TEXT` column by joining with a separator on write and splitting on read.

```csharp
public class CsvConvertor : IDbValueConvertor<List<string>>
{
    public object? ConvertToDbValue(List<string>? value)
        => value is null ? null : string.Join(',', value);

    public List<string>? ConvertFromDbValue(object? dbValue)
        => dbValue is null
            ? null
            : dbValue.ToString()!.Split(',').ToList();
}
```

```csharp
[ValueConvertor(typeof(CsvConvertor))]
public List<string> Tags { get; set; } = new();
```

> **NOTE** The database column type must be `TEXT` (or equivalent). The migration tool uses the column attribute for the DDL type. The convertor affects runtime values only, not schema generation.

## When Convertors Run

| Operation | Method called |
|-----------|---------------|
| INSERT    | `ConvertToDbValue` |
| UPDATE    | `ConvertToDbValue` |
| SELECT    | `ConvertFromDbValue` |

Convertors do not affect the DDL emitted by the migration tool. Define the column type to match what `ConvertToDbValue` returns, not the C# property type.