将SQL结果转换为Linq及ApplicationSummary视图技术咨询

Got it, let's break down your original SQL view and translate it to LINQ step by step. First, let's recap what your query does:

• Generates a random ID (using NEWID())
• Calculates the average ApplicationTime (falling back to 0 if there are no valid records)
• Calculates the average ResponseTime (also falling back to 0)
• Computes the acceptance percentage (number of accepted applications divided by total valid applications, as a float, defaulting to 0)
• Filters for records where IsValid = 1 and CreatedOn is within the last 30 days (excluding today's date)

First, Define a DTO for the Result #

We'll use a simple data transfer object to hold the aggregated results, matching the view's output:

public class ApplicationSummaryDto
{
    public string ID { get; set; }
    public double AvgApplicationTime { get; set; }
    public double AvgResponseTime { get; set; }
    public double PctAccepted { get; set; }
}

LINQ Implementation for EF Core (Database-First/Code-First) #

If you're using Entity Framework Core to query your Application database table, here's the equivalent LINQ query:

using System;
using System.Linq;
using System.Threading.Tasks;

// Calculate date boundaries (matches CAST(GETDATE() AS date) logic)
var today = DateTime.Today;
var thirtyDaysAgo = today.AddDays(-30);

// Query the database
var summary = await _context.Applications
    // Apply the same filters as your SQL WHERE clause
    .Where(a => a.IsValid && a.CreatedOn >= thirtyDaysAgo && a.CreatedOn < today)
    // Group into a single group to aggregate the entire filtered dataset
    .GroupBy(a => 1)
    .Select(g => new ApplicationSummaryDto
    {
        // Match NEWID() converted to VARCHAR(50)
        ID = Guid.NewGuid().ToString(),
        // ISNULL(AVG(ApplicationTime), 0) equivalent
        AvgApplicationTime = g.Average(a => (double?)a.ApplicationTime) ?? 0,
        // ISNULL(AVG(ResponseTime), 0) equivalent
        AvgResponseTime = g.Average(a => (double?)a.ResponseTime) ?? 0,
        // ISNULL(CAST(1.0 * COUNT(...) / COUNT(*) AS float), 0) equivalent
        PctAccepted = g.Count() == 0 
            ? 0 
            : (double)g.Count(a => a.IsAccepted) / g.Count()
    })
    .FirstOrDefaultAsync();

Key Details: #

• GroupBy(a => 1): This tricks LINQ into aggregating the entire filtered dataset (since we're grouping on a constant, all records fall into one group), matching your SQL's implicit aggregation without a GROUP BY clause.
• (double?)a.ApplicationTime: Casting to a nullable double ensures Average() returns null if all values are null, which we then replace with 0 using ?? 0 (matching ISNULL).
• (double)g.Count(...) / g.Count(): Casting one operand to double ensures floating-point division (instead of integer division), matching CAST(1.0 * ... AS float).

LINQ Implementation for In-Memory Collections (LINQ to Objects) #

If you're working with an in-memory list of Application objects, the logic is almost identical—just remove the async method call and adjust the average handling slightly to avoid exceptions:

var today = DateTime.Today;
var thirtyDaysAgo = today.AddDays(-30);

var summary = applicationsList
    .Where(a => a.IsValid && a.CreatedOn >= thirtyDaysAgo && a.CreatedOn < today)
    .GroupBy(a => 1)
    .Select(g => new ApplicationSummaryDto
    {
        ID = Guid.NewGuid().ToString(),
        // Use DefaultIfEmpty() to handle empty collections safely
        AvgApplicationTime = g.Select(a => a.ApplicationTime).DefaultIfEmpty().Average() ?? 0,
        AvgResponseTime = g.Select(a => a.ResponseTime).DefaultIfEmpty().Average() ?? 0,
        PctAccepted = g.Count() == 0 
            ? 0 
            : (double)g.Count(a => a.IsAccepted) / g.Count()
    })
    .FirstOrDefault();

内容的提问来源于stack exchange，提问作者John Ohara