SharpCoreDBDataReader returns aliased column names as values when EF Core generates aliased queries

[YBazanPro]

Bug Summary

SharpCoreDBDataReader returns SQL alias fragments (e.g., w"."Id, w"."PayloadJson") instead of actual column values when EF Core generates aliased SELECT queries. This makes the EF Core LINQ provider completely unusable for data retrieval — all ToListAsync(), FirstOrDefaultAsync(), etc. calls fail.

Affected Version

SharpCoreDB.EntityFrameworkCore 1.6.0 (latest on NuGet, master branch)

Reproduction

1. Create a simple EF Core DbContext with any entity:

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
}

public class Product
{
    public string Id { get; set; }
    public string Name { get; set; }
}

2. Configure with SharpCoreDB:

services.AddDbContextFactory<AppDbContext>(options =>
    options.UseSharpCoreDB("Data Source=./test.db;Password=test"));

3. Insert a row (this works):

db.Products.Add(new Product { Id = "1", Name = "Test" });
await db.SaveChangesAsync(); // ✅ Works

4. Try to read it back:

var products = await db.Products.ToListAsync(); // ❌ Crashes

Error

System.FormatException: The input string 'w"."Id' was not in a correct format.
   at System.Number.ThrowFormatException[TChar](ReadOnlySpan`1 value)
   at System.Convert.ToInt32(String value)
   at SharpCoreDB.EntityFrameworkCore.Storage.SharpCoreDBDataReader.GetInt32(Int32 ordinal)

For string columns, the error manifests differently:

System.Text.Json.JsonException: 'w' is an invalid start of a value.

Because GetString(ordinal) returns w"."PayloadJson" instead of the actual JSON payload.

Root Cause

EF Core always generates aliased queries:

SELECT "w"."Id", "w"."Name", "w"."PayloadJson" FROM "Products" AS "w"

The AstExecutor.ResolveSourceRows() method in SqlParser.DML.cs calls QualifyRow(), which adds alias-prefixed keys to the result dictionary:

private static Dictionary<string, object> QualifyRow(Dictionary<string, object> row, string alias, string tableName)
{
    var qualified = new Dictionary<string, object>(row.Count * 3, StringComparer.OrdinalIgnoreCase);
    foreach (var (key, value) in row)
    {
        qualified[key] = value;           // ✅ "Id" → actual value
        qualified[$"{alias}.{key}"] = value;   // ✅ "w.Id" → actual value
        qualified[$"{tableName}.{key}"] = value; // ✅ "Products.Id" → actual value
    }
    return qualified;
}

This is correct — the values are stored under both bare and aliased keys.

The bug is in SharpCoreDBDataReader. When the constructor extracts column names from the first row:

public SharpCoreDBDataReader(List<Dictionary<string, object>> results)
{
    _rows = results;
    _columnNames = results.FirstOrDefault()?.Keys.ToList() ?? [];  // ← BUG
    // ...
}

It takes all dictionary keys as column names, including the alias-prefixed ones like "w.Id", "w.Name", "Products.Id", etc. So _columnNames ends up with 3× the expected columns.

Then when EF Core calls GetValue(ordinal):

public override object GetValue(int ordinal)
{
    var name = GetName(ordinal);  // Returns "w.Id" for ordinal 3
    var row = CurrentRow;
    if (!row.TryGetValue(name, out var value))
        return DBNull.Value;
    return value ?? DBNull.Value;
}

This actually works for QualifyRow-produced rows because the aliased keys exist in the dictionary. However, the problem is that _columnNames contains the aliased keys in an unpredictable order, and EF Core expects the columns to match the SQL SELECT list order ("w"."Id", "w"."Name", "w"."PayloadJson").

The real issue is that when the SQL engine's ApplyProjection() runs, it produces rows with only the aliased column names as keys (e.g., "w"."Id" as a single key), and the DataReader's _columnNames picks up these malformed keys from the dictionary.

Additionally, for non-AST (regex-based) query paths, the ExecuteSelectQuery() method returns rows with bare column names, but EF Core's SQL generation uses "w"."ColumnName" syntax. The DataReader's GetOrdinal() method does an exact match lookup:

public override int GetOrdinal(string name)
{
    var index = _columnNames.IndexOf(name);
    if (index < 0)
        throw new IndexOutOfRangeException($"Column '{name}' not found.");
    return index;
}

When EF Core asks for column "Id" but _columnNames only has "w"."Id" (as a single string key from the SQL alias), the lookup fails.

Suggested Fix

The SharpCoreDBDataReader constructor should strip table alias prefixes from column names and deduplicate:

public SharpCoreDBDataReader(List<Dictionary<string, object>> results)
{
    _rows = results;
    
    // Extract column names, stripping alias prefixes and deduplicating
    var rawKeys = results.FirstOrDefault()?.Keys.ToList() ?? [];
    var seen = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
    _columnNames = [];
    
    foreach (var key in rawKeys)
    {
        // Strip alias prefix: "w.Id" → "Id", "Products.Name" → "Name"
        var bareName = key.Contains('.') ? key[(key.LastIndexOf('.') + 1)..] : key;
        
        // Also strip SQL quoting: "w""."Id" → Id (handles the malformed key case)
        bareName = bareName.Trim('"');
        
        if (seen.Add(bareName))
        {
            _columnNames.Add(bareName);
        }
    }
    
    _columnTypes = [];
    if (results.Count > 0)
    {
        foreach (var col in _columnNames)
        {
            var firstRow = results[0];
            // Try bare name first, then aliased variants
            if (firstRow.TryGetValue(col, out var value) ||
                firstRow.FirstOrDefault(kv => kv.Key.EndsWith($".{col}", StringComparison.OrdinalIgnoreCase)) is { Key: not null } match && firstRow.TryGetValue(match.Key, out value))
            {
                _columnTypes[col] = value?.GetType() ?? typeof(object);
            }
        }
    }
}

And GetValue() should also resolve aliased column names:

public override object GetValue(int ordinal)
{
    var name = GetName(ordinal);
    var row = CurrentRow;
    
    // Try exact match first
    if (row.TryGetValue(name, out var value))
        return value ?? DBNull.Value;
    
    // Try matching by bare column name (strip alias prefix from dictionary keys)
    foreach (var kv in row)
    {
        var bareKey = kv.Key.Contains('.') ? kv.Key[(kv.Key.LastIndexOf('.') + 1)..] : kv.Key;
        if (string.Equals(bareKey, name, StringComparison.OrdinalIgnoreCase))
            return kv.Value ?? DBNull.Value;
    }
    
    return DBNull.Value;
}

Impact

This bug makes the EF Core provider completely unusable for any LINQ query that reads data. The v1.3.0 release notes already acknowledge this as a known issue:

"A known issue exists with the EF Core LINQ Query Provider, where queries currently return null due to an incomplete implementation of IDatabase.CompileQuery."

However, the actual behavior is worse than "returns null" — it throws FormatException or JsonException because the DataReader returns SQL alias fragments as column values.

Workaround

Using FromSqlRaw with unaliased SQL works:

var products = await db.Products.FromSqlRaw("SELECT * FROM Products").ToListAsync();

But this defeats the purpose of using EF Core's LINQ provider.

Environment

• .NET 10
• SharpCoreDB.EntityFrameworkCore 1.6.0
• Microsoft.EntityFrameworkCore 10.0.5
• Windows 11