Linq2DB map property to result of SQL function

Question

I have SQLite database with table Reports.

CREATE TABLE IF NOT EXISTS Reports (
    Id          INTEGER PRIMARY KEY AUTOINCREMENT,
    ReportName  TEXT NOT NULL UNIQUE,
    CreatedAt   REAL NOT NULL
)

It contains column CreatedAt and the date is stored as the fractional number of days since November 24, 4714 B.C. (julianday)

INSERT INTO Reports (ReportName, CreatedAt) VALUES ('Report1', julianday('2023-03-19 13:10:43.254'))

When I need to retrieve back DateTime value, I need to use function strftime.

SELECT 
    ReportName, 
    strftime('%Y-%m-%d %H:%M:%f', CreatedAt) as CreatedAt
FROM Reports
WHERE Id = 123

Is it possible to create POCO class and map it with Linq2DB to this table?

public class Report 
{
    public long Id { get; set; }
    public string ReportName { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class ReportsContext : DataConnection 
{
    public ITable<Report> Reports => this.GetTable<Report>();
}

I know that I can use ExpressionMethodAttribute and map any property to computed value using LINQ expression, but in this case I would need to call different SQL functions when reading from and writing to DB.

score 1 · Answer 1 · edited Mar 20 '23 at 13:28

I have found this solution

public static class JulianDayExtensions
{
    private const double ConstValue = 2415018.5;

    public static double ToJulianDay(this DateTime value) => value.ToOADate() + ConstValue;

    public static DateTime FromJulianDay(this double value) => DateTime.FromOADate(value - ConstValue);
}

Then I use fluent mapping

private static void BuildReport(EntityMappingBuilder<Report> entityBuilder)
{
    entityBuilder.HasTableName("Reports");

    entityBuilder
        .Property(x => x.Id)
        .HasColumnName("Id")
        .IsPrimaryKey()
        .IsIdentity();

    entityBuilder
        .Property(x => x.ReportName)
        .HasColumnName("ReportName");

    entityBuilder
        .Property(x => x.CreatedAt)
        .HasColumnName("CreatedAt")
        .HasConversionFunc(x => x.ToJulianDay(), x => x.FromJulianDay());
}

And the class Report is simple

public class Report
{
    public long Id { get; set; }
    public string ReportName { get; set; } = null!;
    public DateTime CreatedAt { get; set; }
}

Linq2DB map property to result of SQL function

1 Answers1