如何使用Linq按日期和ID分组求和并返回IQueryable<Money>类型数据

Here's a clean, efficient way to group your transaction data by customer ID and date, calculate the net value for each group, and return an IQueryable<Money> as requested:

Core LINQ Query #

Assuming your repository exposes an IQueryable<Money> (e.g., via a GetAll() method), you can build the query like this:

// Get the base query from your repository
var rawTransactions = _moneyRepository.GetAll();

// Group by ID and Date, then calculate net value
var groupedTransactions = rawTransactions
    // Group transactions by customer ID and transaction date
    .GroupBy(transaction => new { transaction.ID, transaction.Date })
    // Project each group into a new Money object
    .Select(group => new Money
    {
        ID = group.Key.ID,
        Date = group.Key.Date,
        // Sum all values (Sell transactions are already negative, so this gives net value)
        Value = group.Sum(t => t.Value),
        // Optional: Set Action based on net value (adjust based on your business rules)
        Action = group.Sum(t => t.Value) >= 0 ? "Buy" : "Sell"
    });

// groupedTransactions is already an IQueryable<Money> - ready to use!

Key Details to Note #

• Database-side Execution: Since we're using IQueryable throughout, the grouping and summing will happen directly in the database (not in memory), which is much more efficient for large datasets.
• Date Precision Handling: If your Date field includes time components (e.g., DateTime with hours/minutes), and you need to group by calendar day instead of exact timestamp, adjust the grouping key to truncate the time:

  .GroupBy(transaction => new { transaction.ID, Date = transaction.Date.Date })
  

  This works with most databases (like SQL Server) and ensures all transactions from the same day are grouped together, regardless of time.
• Action Field: The example sets the Action field based on the net value (positive = net buy, negative = net sell). If your Money entity doesn't require this field, you can omit it or set it to null based on your schema.
• Entity Compatibility: If your Money entity has other required properties, make sure to populate them in the Select clause (e.g., set default values where appropriate).

内容的提问来源于stack exchange，提问作者Little Programmer