YMJake.EntityFrameworkCore.FromMemory 0.1.0-preview.1

EntityFrameworkCore.FromMemory

Lightweight EF Core experiment for joining database tables with in-memory rows.

The goal is smaller than linq2db/FreeSql-style ORM extension: provide a focused FromMemory table source for common business queries.

First API

var ranks = db.FromMemory(
    [
        new ProductRank { ProductId = 3, Sort = 1, Label = "Hot" },
        new ProductRank { ProductId = 1, Sort = 2, Label = "Pinned" }
    ],
    rank => new { rank.ProductId, rank.Sort, rank.Label });

Fluent style is also available when configuration reads better as a chain:

var ranks = db
    .FromMemoryRows(memoryRows)
    .WithColumns(rank => new { rank.ProductId, rank.Sort, rank.Label })
    .WithAlias("rank")
    .AsQueryable();

The resulting queryable composes with EF Core LINQ:

var query =
    from product in db.Products
    join rank in ranks on product.Id equals rank.ProductId
    orderby rank.Sort
    select new
    {
        product.Id,
        product.Name,
        rank.Label,
        rank.Sort
    };

ProductRank does not need to be part of the EF Core model when using the DbContext overload:

public sealed class ProductRank
{
    public int ProductId { get; set; }
    public int Sort { get; set; }
    public string Label { get; set; } = string.Empty;
}

Current Implementation

• Uses Database.SqlQueryRaw<TEntity>(...) for unmapped projection types.
• Also keeps DbSet<TEntity>.FromMemory(...) overloads for mapped/keyless types when that style is useful.
• Generates a composable inline table.
• Splits SQL generation into provider-specific Runners, inspired by FlexLabs.EntityFrameworkCore.Upsert.
• PostgreSQL defaults to SELECT ... FROM (VALUES (...)) AS alias(columns).
• SQL Server defaults to OPENJSON(@rows) WITH (...), following the EF Core team's recommendation for list-shaped raw SQL inputs.
• SQL Server can still use VALUES explicitly with options.Shape = FromMemorySqlShape.Values.
• SQLite defaults to SELECT @p AS Column UNION ALL SELECT ....
• Supports PostgreSQL, SQL Server, and SQLite identifier/parameter conventions.
• EF Core extension methods choose runners from EF provider names, for example Npgsql.EntityFrameworkCore.PostgreSQL.
• Direct FromMemorySqlBuilder.Build(DbConnection, ...) calls still auto-detect from DbConnection type.
• Supports strongly typed column selection with x => new { x.Id, x.Sort }.
• Supports simple scalar property types only.
• Validates provider parameter limits early for parameter-per-cell shapes: SQLite 999, SQL Server 2100, PostgreSQL 32767.
• MySQL is not supported yet.

Current Limitations

• Empty row sets are supported by generating a typed zero-row projection.
• Null-heavy first rows may need explicit casts in future provider-specific SQL.
• The recommended DbContext.FromMemory(...) API does not require the memory row type to be configured in the EF Core model.
• This is not a full LINQ provider and does not parse arbitrary in-memory expressions.
• SQLite VALUES (...) AS v(col1, col2) is intentionally avoided for now because its column alias behavior is less portable under EF composition.
• Runner replacement is not wired into DI yet; for now the built-in runner registry chooses PostgreSQL, SQL Server, or SQLite.
• Large in-memory row sets can exceed database parameter limits with VALUES or UNION ALL SELECT; SQL Server's default OPENJSON shape avoids this by using a single JSON parameter.

Runner Layout

Provider differences are isolated under src/EntityFrameworkCore.FromMemory/Runners:

• FromMemorySqlRunnerBase owns shared parameter creation, empty-row SQL, UNION ALL SELECT, and VALUES generation.
• PostgreSqlFromMemorySqlRunner owns PostgreSQL quoting, parameter prefix, store types, and default VALUES shape.
• SqlServerFromMemorySqlRunner owns SQL Server quoting, store types, and default OPENJSON shape.
• SqliteFromMemorySqlRunner keeps SQLite on UNION ALL SELECT by default.

Answer To The FreeSql-Style Question

EF Core does not provide a first-class FromMemory(List<T>) table source API today.

For a lightweight plugin, the practical composition point is Database.SqlQueryRaw<T> for unmapped projection types, or DbSet.FromSqlRaw(...) for mapped/keyless types. This package hides that raw SQL from callers and exposes:

var memoryQuery = db.FromMemory(
    memoryRows,
    row => new { row.ProductId, row.Sort, row.Label },
    options => options.Shape = FromMemorySqlShape.UnionAllSelect);

The query composes with normal EF Core LINQ:

var query =
    from product in db.Products
    join memory in memoryQuery on product.Id equals memory.ProductId
    orderby memory.Sort
    select new
    {
        product.Id,
        product.Name,
        memory.Label,
        memory.Sort
    };

Generated SQL shape:

SELECT ...
FROM "Products" AS "p"
INNER JOIN (
    SELECT @__fm_0 AS "ProductId", @__fm_1 AS "Sort", @__fm_2 AS "Label"
    UNION ALL SELECT @__fm_3 AS "ProductId", @__fm_4 AS "Sort", @__fm_5 AS "Label"
) AS "p0" ON "p"."Id" = "p0"."ProductId"
ORDER BY "p0"."Sort"

SQL Server default shape:

SELECT [__fm].[ProductId], [__fm].[Sort], [__fm].[Label]
FROM OPENJSON(@__fm_json)
WITH (
    [ProductId] int '$.ProductId',
    [Sort] int '$.Sort',
    [Label] nvarchar(max) '$.Label'
) AS [__fm]

Reference Notes

linq2db reference areas:

• SqlValuesTable
• EnumerableBuilder
• provider-specific values table SQL builders
• parameter normalization in SQL generation

Test

dotnet test EntityFrameworkCore.FromMemory.slnx -c Release

SQLite integration tests and provider SQL shape tests run by default.

Set these environment variables to enable real PostgreSQL and SQL Server integration tests:

$env:FROMMEMORY_POSTGRES='Host=127.0.0.1;Port=5432;Database=pgAdmin;Username=pgAdmin;Password=123456'
$env:FROMMEMORY_SQLSERVER='Data Source=127.0.0.1,14333;Initial Catalog=TestORM;User ID=sa;Password=Aa123456.;Encrypt=True;TrustServerCertificate=True'

Package

dotnet pack src\EntityFrameworkCore.FromMemory\EntityFrameworkCore.FromMemory.csproj -c Release