BUG-005: MemberInitExpression Not Supported in Snowflake SelectCase

[improveTheWorld]

BUG-005: MemberInitExpression Not Supported in Snowflake Cases Pattern

Summary

When using SelectCase() with the Snowflake Cases pattern, object initializer expressions (new T { Prop = value }) throw NotSupportedException. The SnowflakeSqlTranslator doesn't support MemberInitExpression nodes.

Error Message

System.NotSupportedException: Expression type not supported: MemberInitExpression

Affected Components

• DataFlow.Snowflake v1.2.0 (SnowflakeSqlTranslator)
• Not affected: Spark (uses different expression model)

Root Cause

Location: SnowflakeQuery.cs in SnowflakeSqlTranslator.TranslateExpression()

The SelectCase() pattern uses lambda expressions to transform data. When users write new T { Prop = value }, C# compiles this to a MemberInitExpression, which the translator doesn't handle.

// User code:
.SelectCase(
    o => new HighValueOrder { Id = o.Id, Priority = "HIGH" },  // MemberInitExpression
    o => o  // Pass-through for default
)

// The translator sees:
// MemberInit(New(HighValueOrder), Bind(Id, o.Id), Bind(Priority, "HIGH"))
// And throws because MemberInit is not handled

Reproduction Steps

using DataFlow.Snowflake;

var context = Snowflake.Connect("connection-string");
var orders = context.Read.Table<Order>("ORDERS");

// Define output types
public class HighValueOrder
{
    public int Id { get; set; }
    public string Priority { get; set; }
}

// This FAILS:
var categorized = orders
    .Cases(o => o.Amount > 10000)  // Condition
    .SelectCase(
        o => new HighValueOrder { Id = o.Id, Priority = "HIGH" },  // FAILS here
        o => new HighValueOrder { Id = o.Id, Priority = "NORMAL" }
    );

var results = categorized.ToList();  // Throws NotSupportedException

Failing Tests

Project	Test
SnowflakeIntegrationTests	SelectCase_TransformsByCategory

Current Workarounds

Workaround 1: Use Pass-through and post-process

var results = orders
    .Cases(o => o.Amount > 10000)
    .ToList();

var highValue = results
    .Where(x => x.Category == 0)
    .Select(o => new HighValueOrder { Id = o.Id, Priority = "HIGH" });

Workaround 2: Use separate queries

var highValue = orders.Where(o => o.Amount > 10000)
    .Select(o => new { o.Id, Priority = "HIGH" });

var normal = orders.Where(o => o.Amount <= 10000)
    .Select(o => new { o.Id, Priority = "NORMAL" });

Proposed Fix

Add MemberInitExpression handling to SnowflakeSqlTranslator:

case ExpressionType.MemberInit:
    var memberInit = (MemberInitExpression)expression;
    var columns = new List<string>();
    
    foreach (var binding in memberInit.Bindings.OfType<MemberAssignment>())
    {
        var columnName = GetColumnName(binding.Member);
        var valueSql = TranslateExpression(binding.Expression);
        columns.Add($"{valueSql} AS \"{columnName}\"");
    }
    
    return string.Join(", ", columns);

Related Issue

This is similar to BUG-001 in that both involve object construction in expressions. However:

• BUG-001: Affects materialization (client-side)
• BUG-005: Affects SQL translation (server-side)

Impact

• Severity: MEDIUM
• Frequency: Low (Cases pattern is advanced feature)
• User Impact: Limits Cases pattern flexibility

Labels

bug, snowflake, expression-translator, cases-pattern