技巧：自定义解析表达式树，实现动态聚合列 sum(case when ...)

[2881099]

SELECT
a."Time",
v1 = sum(case when a."Id" == 1 then 1 else 0 end),
v2 = sum(case when a."Id" == 2 then 1 else 0 end),
v3 = sum(case when a."Id" == 3 then 1 else 0 end)
FROM "table" a
WHERE a."Id" IN (1,2,3)
GROUP BY a."Time"

如上 v1,v2,v3 是动态聚合值，如果 where IN (1,2,3,4) 那就会产生 v1-v4

正常情况下，静态的 lambda 查询没办法处理这种动态列查询。

---

变通一下，这样查询：

SELECT
a."Time",
v = sum(case when a."Id" == 1 then 1 else 0 end) + ','
    sum(case when a."Id" == 2 then 1 else 0 end) + ','
    sum(case when a."Id" == 3 then 1 else 0 end)
FROM "table" a
WHERE a."Id" IN (1,2,3)
GROUP BY a."Time"

如此便可以使用 FreeSql 实现：

var ids = new int[] { 1,2,3 };
fsql.Select<table>()
    .Where(a => ids.Contains(a.Id))
    .GroupBy(a => a.Time)
    .ToList(g => new 
    {
        Time = g.Key,
        Values = MyExt.SumCase(ids, g.Value.Id)
    });

自定义解析表达式树，实现如下：

[ExpressionCall]
public static class MyExt
{
    internal static ThreadLocal<ExpressionCallContext> expContext = new ThreadLocal<ExpressionCallContext>();

    public static string SumCase<TValue>([RawValue] TValue[] values, TValue column)
    {
        var ctx = expContext.Value;
        ctx.Result = ctx.Utility.CommonUtils.StringConcat(
            values.Select((val, idx) => 
                new [] {
                    ctx._commonExp._common.IsNull($"SUM(case when {ctx.ParsedContent["column"]} = {ctx.FormatSql(val)} then 1 else 0 end)", 0),
                    idx == values.Length - 1 ? "''" : "','"
                }).SelectMany(a => a).ToArray(), 
            values.Select(val => 
                new[]{
                    typeof(TValue),
                    typeof(string)
                }).SelectMany(a => a).ToArray());
        return default;
    }
}

[Zonciu]

总体思路：查询后拼接成字符串作为一列，在代码中自行解析。
作用：窄表转为宽表

这个写法支持分别设置条件字段和查询字段，支持数据为NULL的情况

internal static ThreadLocal<ExpressionCallContext> expContext = new ThreadLocal<ExpressionCallContext>()

[ExpressionCall]
public static string SumCase<TKey, TValue>([RawValue] TKey[] values, TKey key, TValue column)
{
    var ctx = expContext.Value;
    ctx.Result =
        "CONCAT("
      + string.Join(",',',", values.Select(val => $"MAX(case when {ctx.ParsedContent["key"]} = {ctx.FormatSql(val)} then {ctx.ParsedContent["column"]} else NULL end)"))
      + ")";
    return "";
}

var ids = new int[] { 1,2,3 };
var queryResult = fsql.Select<table>()
    .Where(a => ids.Contains(a.Id))
    .GroupBy(a => a.Time)
    .ToList(g => new 
    {
        Time = g.Key,
        Values = MyExt.SumCase(ids, g.Value.Id, g.Value.Value)
    });

var result = queryResult.ToDictionary(e => e.Time, e => e.Data.Split(",").Select(v => (double?)(v is {Length: >0} ? double.Parse(v) : null)).ToArray());