交易统计SQL转C# LINQ实现技术求助

Hey there! Let's convert that SQL query into clean, readable C# LINQ code. First, let's recap the context to make sure we're aligned:

Original Transaction Data Structure #

--REQUEST_ID----ITEM_ID----ITEM_STATUS_CD----EXECUTION_DTTM
1 1 1 2016-08-29 12:36:07.000
1 2 0 2016-08-29 12:37:07.000
2 3 5 2016-08-29 13:37:07.000
2 4 1 2016-08-29 15:37:07.000
2 5 10 2016-08-29 15:41:07.000
3 6 0 2016-08-29 15:41:07.000

Expected Aggregated Result #

--REQUEST_ID--Transactions----------EndTime----------Success----Warning----Error
1 2 2016-08-29 12:37:07.000 50 50 0
2 3 2016-08-29 15:41:07.000 0 33 66
3 1 2016-08-29 15:41:07.000 100 0 0

LINQ Implementation #

First, let's assume you have an entity class representing your table (adjust property names if needed):

public class ItemTransaction
{
    public int RequestId { get; set; }
    public int ItemId { get; set; }
    public int ItemStatusCd { get; set; }
    public DateTime ExecutionDttm { get; set; }
}

For LINQ to Entities (Entity Framework) #

If you're querying directly from a database via EF, use this:

var aggregatedResults = dbContext.ItemTransactions
    .GroupBy(txn => txn.RequestId)
    .Select(group => new
    {
        RequestId = group.Key,
        Transactions = group.Count(),
        EndTime = group.Max(txn => txn.ExecutionDttm),
        Success = (int)Math.Round(group.Count(txn => txn.ItemStatusCd == 0) * 100.0 / group.Count()),
        Warning = (int)Math.Round(group.Count(txn => txn.ItemStatusCd == 1) * 100.0 / group.Count()),
        Error = (int)Math.Round(group.Count(txn => txn.ItemStatusCd > 1) * 100.0 / group.Count())
    })
    .OrderByDescending(result => result.RequestId)
    .ToList();

For LINQ to Objects (In-Memory Collections) #

If you're working with an in-memory list (like a List<ItemTransaction>), the logic is identical—just swap out the dbContext reference:

// Assume 'transactions' is your in-memory list of ItemTransaction
var aggregatedResults = transactions
    .GroupBy(txn => txn.RequestId)
    .Select(group => new
    {
        RequestId = group.Key,
        Transactions = group.Count(),
        EndTime = group.Max(txn => txn.ExecutionDttm),
        Success = (int)Math.Round(group.Count(txn => txn.ItemStatusCd == 0) * 100.0 / group.Count()),
        Warning = (int)Math.Round(group.Count(txn => txn.ItemStatusCd == 1) * 100.0 / group.Count()),
        Error = (int)Math.Round(group.Count(txn => txn.ItemStatusCd > 1) * 100.0 / group.Count())
    })
    .OrderByDescending(result => result.RequestId)
    .ToList();

Key Notes About the Implementation: #

• We skip the SQL's self-join entirely because LINQ's grouping lets us compute all aggregated values directly in one pass—no need to rejoin the original table.
• Using * 100.0 ensures we do floating-point division instead of integer division (which would truncate results incorrectly). Math.Round gives us the integer percentage matching your expected output.
• The Count method in LINQ doesn't return null, so we don't need an equivalent to SQL's coalesce here.
• To handle edge cases like empty groups (no transactions for a RequestId), you could add a check: e.g., group.Count() == 0 ? 0 : (int)Math.Round(...) to avoid division-by-zero errors.

内容的提问来源于stack exchange，提问作者Joe Doe