C# 進階學習成果：非同步與 LINQ

// 同步：執行緒被阻塞，等 DB 回應
DataTable result = dbc.FillDataTable(sql, ht);  // 等待中...什麼都不能做

// 非同步：執行緒可以去服務其他 Request
DataTable result = await dbc.FillDataTableAsync(sql, ht);  // 先去忙別的

// Task：不回傳值的非同步操作
Task SaveLogAsync()
{
    await db.ExecuteAsync("INSERT INTO LOG ...");
}

// Task<T>：回傳值的非同步操作
Task<DataTable> GetDataAsync()
{
    return await db.FillDataTableAsync(sql, ht);
}

public async Task<IActionResult> Init()
{
    var data = await GetMasterDataAsync();
    var task1 = GetDropdownAsync("QL_YEAR");
    var task2 = GetDropdownAsync("QL_FUND");
    await Task.WhenAll(task1, task2);
    return Ok(data);
}

// 需求：從員工清單取得 IT 部門、按薪資降冪、只取姓名
List<Employee> filtered = new List<Employee>();
foreach (var emp in employees)
{
    if (emp.Department == "IT")
        filtered.Add(emp);
}
filtered.Sort((a, b) => b.Salary.CompareTo(a.Salary));
List<string> names = new List<string>();
foreach (var emp in filtered)
    names.Add(emp.Name);
// 12 行，邏輯分散、難以一眼看出意圖

// 命令式（舊）：告訴電腦「怎麼做」
var result = new List<string>();
foreach (var emp in employees)
    if (emp.Department == "IT")
        result.Add(emp.Name);

// 宣告式（LINQ）：告訴電腦「要什麼」
var result = employees
    .Where(e => e.Department == "IT")
    .Select(e => e.Name)
    .ToList();

// Lambda 的完整形式
(Employee e) => { return e.Department == "IT"; }

// 簡化形式（型別可推斷、單行可省略 return）
e => e.Department == "IT"

// 傳入 LINQ — 這裡就是在傳遞「篩選邏輯」給 Where 方法
employees.Where(e => e.Department == "IT")

var query = employees.Where(e => e.IsActive);
// 此時：沒有任何迴圈跑過，只是建立了「查詢描述」

foreach (var e in query) { }  // ← 這裡才真正執行
var list = query.ToList();     // ← 或這裡

// ❌ 效能陷阱：每次使用 query 都會重跑一次迴圈
var query = dt.AsEnumerable().Where(r => r.Field<string>("STATUS") == "A");
var count = query.Count();   // 跑一次
var first = query.First();   // 再跑一次

// ✅ 先具體化（materialize），再使用
var list = dt.AsEnumerable()
    .Where(r => r.Field<string>("STATUS") == "A")
    .ToList();  // 只跑一次
var count = list.Count;  // O(1)
var first = list[0];     // O(1)

var activeRows = dt.AsEnumerable()
    .Where(r => r.Field<string>("STATUS") == "A");

// 只要欄位值，不要整個 DataRow
var fundCodes = dt.AsEnumerable()
    .Select(r => r.Field<string>("FUND_NO"))
    .Distinct()
    .ToList();
// 結果：List<string>，而不是 IEnumerable<DataRow>

int activeCount = dt.AsEnumerable()
    .Count(r => r.Field<string>("STATUS") == "A");

decimal total = dt.AsEnumerable()
    .Sum(r => r.Field<decimal>("AMOUNT"));

var byFund = dt.AsEnumerable()
    .GroupBy(r => r.Field<string>("FUND_NO"))
    .Select(g => new {
        FundNo = g.Key,
        TotalAmount = g.Sum(r => r.Field<decimal>("AMOUNT")),
        Count = g.Count()
    });

// ❌ 每次都 LINQ 查，O(n)
var name = configs.First(c => c.Code == targetCode).Name;

// ✅ 先轉成 Dictionary，之後查詢 O(1)
var configDict = dt.AsEnumerable()
    .ToDictionary(
        r => r.Field<string>("CODE"),
        r => r.Field<string>("NAME")
    );
string name = configDict["TARGET_CODE"];

// 確認所有必填欄位都有值
string[] required = { "FUND_NO", "YEAR", "NAME" };
bool allFilled = required.All(f => !string.IsNullOrEmpty(row[f]?.ToString()));

DataTable dt = l_dbc.FillDataTable(sql, ht);

var dropdownItems = dt.AsEnumerable()
    .Where(r => r.Field<string>("STATUS") != "D")
    .OrderByDescending(r => r.Field<string>("YEAR"))
    .Select(r => new {
        Code = r.Field<string>("FUND_NO"),
        Name = r.Field<string>("FUND_NAME")
    })
    .ToList();

void l_Master_Searched(object sender, SearchedEventArgs e)
{
    // 取出所有不重複的年度，供篩選條件使用
    var years = e.masterData.AsEnumerable()
        .Select(r => r.Field<string>("YEAR"))
        .Distinct()
        .OrderByDescending(y => y)
        .ToList();
}

string[] required = new[] { "FUND_NO", "YEAR", "CODE", "NAME" };

var emptyFields = required
    .Where(f => string.IsNullOrEmpty(newRow[f]?.ToString()))
    .ToList();

if (emptyFields.Any())
{
    e.cancel = true;
    e.cancelMsg = $"以下欄位不可為空：{string.Join("、", emptyFields)}";
}

string[] keyFields = new[] { "FUND_NO", "YEAR", "SEQ_NO" };

string whereClause = string.Join(" AND ",
    keyFields.Select(k => $"M.{k} = @{k}"));
// 結果："M.FUND_NO = @FUND_NO AND M.YEAR = @YEAR AND M.SEQ_NO = @SEQ_NO"

public async Task<IActionResult> Search([FromBody] SearchClass searchData)
{
    // 非同步查詢 → 不阻塞執行緒
    var result = await doSearchResultAsync(searchData);

    // LINQ 後處理 → 記憶體內篩選與轉換
    var processed = result.AsEnumerable()
        .Where(r => r.Field<string>("STATUS") != "D")
        .Select(r => new {
            FundNo = r.Field<string>("FUND_NO"),
            Year = ShareModel.dispYear(r.Field<string>("YEAR"))
        })
        .ToList();

    return Ok(processed);
}

public async Task<IActionResult> Init()
{
    // 同時發出三個查詢，不依序等待
    var t1 = GetDropdownAsync("QL_YEAR");
    var t2 = GetDropdownAsync("QL_FUND");
    var t3 = GetMasterDataAsync();
    await Task.WhenAll(t1, t2, t3);

    // LINQ 整理下拉清單
    var years = t1.Result.AsEnumerable()
        .Where(r => r.Field<string>("STATUS") == "A")
        .Select(r => r.Field<string>("YEAR"))
        .Distinct()
        .ToList();

    return Ok(new { years, funds = t2.Result, master = t3.Result });
}