MVC中SQL转Linq时遇匿名类型映射错误技术咨询

That error pops up because you're using an anonymous type in a scenario where your ORM (like Entity Framework) expects a concrete, mapped type—anonymous types don't have a predefined mapping to your data layer, so the framework can't process them correctly. Here's how to fix this and convert your SQL query properly:

Step 1: Create a Concrete DTO Class #

First, define a Data Transfer Object (DTO) to hold your query results. This gives the ORM a known type to work with:

public class OoeSummaryDto
{
    public int Id { get; set; }
    public string Title { get; set; }
    public decimal? TotalAppro { get; set; }
    public decimal? TotalAllotment { get; set; }
    public decimal? TotalCost { get; set; }
    public decimal Unobligated { get; set; }
}

Step 2: Rewrite the LINQ Query Using the DTO #

Assuming your DbContext has DbSets for pmTA_OoeGeneral (named OoeGenerals), pmTA_Particulars (named Particulars), and the related tables for approvals, allotments, and costs, here's the LINQ equivalent of your SQL:

var query = from ooe in dbContext.OoeGenerals
            // Inner join with particulars
            join parts in dbContext.Particulars 
                on ooe.particular_id equals parts.id
            
            // Subquery for totalAppro (adjust based on your actual approval table structure)
            join appro in (
                from approval in dbContext.Approvals
                group approval by approval.oe_id into approvalGroup
                select new { OoeId = approvalGroup.Key, TotalAppro = approvalGroup.Sum(a => a.amount) }
            ) on ooe.id equals appro.OoeId into approGroup
            let totalAppro = approGroup.FirstOrDefault()?.TotalAppro
            
            // Subquery for TotalAllotment (adjust based on your allotment table)
            join allo in (
                from allotment in dbContext.Allotments
                group allotment by allotment.oe_id into allotmentGroup
                select new { OoeId = allotmentGroup.Key, TotalAllotment = allotmentGroup.Sum(al => al.amount) }
            ) on ooe.id equals allo.OoeId into alloGroup
            let totalAllotment = alloGroup.FirstOrDefault()?.TotalAllotment
            
            // Subquery for total_cost (adjust based on your cost table)
            join cost in (
                from costEntry in dbContext.Costs
                group costEntry by costEntry.oe_id into costGroup
                select new { OoeId = costGroup.Key, TotalCost = costGroup.Sum(c => c.amount) }
            ) on ooe.id equals cost.OoeId into costGroup
            let totalCost = costGroup.FirstOrDefault()?.TotalCost
            
            // Project to our DTO instead of an anonymous type
            select new OoeSummaryDto
            {
                Id = ooe.id,
                Title = parts.title,
                TotalAppro = totalAppro,
                TotalAllotment = totalAllotment,
                TotalCost = totalCost,
                // Translate the CASE statement to a ternary operator
                Unobligated = totalCost.HasValue 
                    ? (totalAllotment ?? 0) - totalCost.Value 
                    : (totalAllotment ?? 0)
            };

Key Fixes Explained #

• Concrete DTO: Replacing the anonymous type with OoeSummaryDto gives the ORM a predictable type to map results to, eliminating the error.
• Null Handling: Using ?. and ?? 0 ensures we handle null values from the LEFT JOINs correctly, matching your SQL's CASE logic.
• Subquery Structure: The grouped subqueries replicate the aggregate values (totalAppro, TotalAllotment, total_cost) from your original SQL.

Adjustments for Your Context #

• If you have navigation properties set up (e.g., oe.Approvals), you can simplify the subqueries to use oe.Approvals.Sum(a => a.amount) instead of explicit joins.
• Tweak the table/column names to match your actual DbContext and database schema.

内容的提问来源于stack exchange，提问作者Mace Munch