Linq多表关联求和：模型设计与查询语句修正求助

Let's work through this problem step by step. First, we'll refine the entity model (since we need a way to distinguish between Admin and Finance usage in the UsedAmount table), then build the correct LINQ query to get your desired detailed data plus the total row.

First, let's fix naming inconsistencies and add a critical field to the UsedAmount entity—without this, we can't separate UsedAdmin and UsedFinance values as required.

RowMultipleValues Entity (Fixed Naming from Original) #

public class RowMultipleValues 
{ 
    public int ID { get; set; } 
    public string Year { get; set; } 
    public string Country { get; set; } 
    public decimal Admin { get; set; } 
    public decimal Finance { get; set; } 
    public virtual ICollection<UsedAmount> UsedAmounts { get; set; } 
}

UsedAmount Entity (Added Category Field) #

We add a Category field to flag whether a usage record belongs to Admin or Finance:

public class UsedAmount 
{ 
    public int ID { get; set; } 
    public string Year { get; set; } 
    public string Country { get; set; } 
    public string Category { get; set; } // Values: "Admin" or "Finance"
    public decimal UsedAmountValue { get; set; } 
    public int RowMultipleValuesID { get; set; } 
    public virtual RowMultipleValues RowMultipleValues { get; set; } 
}

Your original query had spelling errors (like multiplerowvaluetable and usedtsble) and didn't account for the detailed row structure or total. Here's the corrected implementation:

// Assuming your DbContext has properly named DbSets:
// public DbSet<RowMultipleValues> RowMultipleValues { get; set; }
// public DbSet<UsedAmount> UsedAmounts { get; set; }

// 1. Fetch detailed data with calculated UsedAdmin and UsedFinance
var detailedResults = context.RowMultipleValues
    .Select(row => new 
    {
        ID = row.ID.ToString(), // Convert to string to match total row's "合计"
        Year = row.Year,
        Country = row.Country,
        Admin = row.Admin,
        UsedAdmin = row.UsedAmounts
            .Where(ua => ua.Category == "Admin")
            .Sum(ua => ua.UsedAmountValue),
        Finance = row.Finance,
        UsedFinance = row.UsedAmounts
            .Where(ua => ua.Category == "Finance")
            .Sum(ua => ua.UsedAmountValue)
    }).ToList();

// 2. Calculate the total row
var totalRow = new 
{
    ID = "合计",
    Year = string.Empty,
    Country = string.Empty,
    Admin = detailedResults.Sum(r => r.Admin),
    UsedAdmin = detailedResults.Sum(r => r.UsedAdmin),
    Finance = detailedResults.Sum(r => r.Finance),
    UsedFinance = detailedResults.Sum(r => r.UsedFinance)
};

// 3. Combine detailed rows with the total row
var finalResult = detailedResults.Cast<dynamic>().Concat(new[] { totalRow }).ToList();

Key Notes: #

• We use the navigation property UsedAmounts on RowMultipleValues to cleanly join tables, avoiding manual join errors.
• Converting the ID to a string in detailed rows ensures we can seamlessly combine it with the "合计" string in the total row.
• The Category field in UsedAmount is essential to split usage totals between Admin and Finance—adjust the values ("Admin"/"Finance") to match your actual data if needed.

内容的提问来源于stack exchange，提问作者paingsoethu 2015