如何用ASP.NET MVC（Entity Framework）复刻T-SQL子查询脚本

Got it, let's walk through how to translate your existing T-SQL script into ASP.NET MVC + Entity Framework code that works for browser-facing scenarios. First, let's recap the core logic your original script handles: grouping items by their ID, counting the number of associated PickIDs, and pulling the most recent usage date.

Step 1: Define Your Entity & DTO (if needed) #

First, make sure you have an entity class that maps to the table your T-SQL queries. Let's assume your table has columns like ItemID, PickID, and LastUsedDate:

public class ItemUsage
{
    public int ItemID { get; set; }
    public int PickID { get; set; }
    public DateTime LastUsedDate { get; set; }
    // Add any other relevant columns from your table
}

If you're returning aggregated results (not full entity rows), a DTO (Data Transfer Object) will help keep your view model clean:

public class ItemUsageSummary
{
    public int ItemID { get; set; }
    public int PickCount { get; set; }
    public DateTime MostRecentUsageDate { get; set; }
}

Step 2: LINQ to Entities Implementation (Preferred) #

This is the "EF-native" way to replicate your T-SQL logic without writing raw SQL. Here are two common syntax options:

Method Syntax #

using (var context = new YourDbContext())
{
    var itemSummaries = context.ItemUsages
        .GroupBy(u => u.ItemID)
        .Select(g => new ItemUsageSummary
        {
            ItemID = g.Key,
            PickCount = g.Count(), // Counts the number of PickIDs per group
            MostRecentUsageDate = g.Max(u => u.LastUsedDate)
        })
        .ToList();
}

Query Syntax #

If you prefer a syntax closer to traditional SQL:

using (var context = new YourDbContext())
{
    var itemSummaries = (from u in context.ItemUsages
                         group u by u.ItemID into g
                         select new ItemUsageSummary
                         {
                             ItemID = g.Key,
                             PickCount = g.Count(),
                             MostRecentUsageDate = g.Max(u => u.LastUsedDate)
                         }).ToList();
}

Step 3: Reuse Your Exact T-SQL Script (If Needed) #

If you want to stick directly to the tested T-SQL your supervisor wrote, you can execute raw SQL through EF. This is useful if the original script has complex logic that's hard to replicate in LINQ.

First, write your raw T-SQL (replace with your actual script):

SELECT 
    ItemID,
    COUNT(PickID) AS PickCount,
    MAX(LastUsedDate) AS MostRecentUsageDate
FROM 
    YourItemTable
GROUP BY 
    ItemID

Then execute it in EF (EF Core example):

using (var context = new YourDbContext())
{
    var sql = @"SELECT 
                    ItemID,
                    COUNT(PickID) AS PickCount,
                    MAX(LastUsedDate) AS MostRecentUsageDate
                FROM 
                    YourItemTable
                GROUP BY 
                    ItemID";

    var itemSummaries = context.Set<ItemUsageSummary>()
        .FromSqlRaw(sql)
        .ToList();
}

Note: For EF6, use context.Database.SqlQuery<ItemUsageSummary>(sql).ToList() instead.

Step 4: Integrate with ASP.NET MVC #

Once you have your aggregated data, pass it to a view from your controller:

Controller Action #

public ActionResult ItemUsageSummary()
{
    List<ItemUsageSummary> summaries;
    using (var context = new YourDbContext())
    {
        // Use either the LINQ or raw SQL query from above
        summaries = context.ItemUsages
            .GroupBy(u => u.ItemID)
            .Select(g => new ItemUsageSummary
            {
                ItemID = g.Key,
                PickCount = g.Count(),
                MostRecentUsageDate = g.Max(u => u.LastUsedDate)
            })
            .ToList();
    }

    return View(summaries);
}

View (Razor Example) #

Create a strongly-typed view to display the data in the browser:

@model List<ItemUsageSummary>

<h2>Item Usage Summary</h2>
<table class="table">
    <thead>
        <tr>
            <th>Item ID</th>
            <th>Total Pick Count</th>
            <th>Most Recent Usage Date</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var summary in Model)
        {
            <tr>
                <td>@summary.ItemID</td>
                <td>@summary.PickCount</td>
                <td>@summary.MostRecentUsageDate.ToString("MM/dd/yyyy")</td>
            </tr>
        }
    </tbody>
</table>

Quick Notes #

• Ensure your YourDbContext class includes a DbSet<ItemUsage> property mapped to your database table.
• Handle edge cases (e.g., items with no usage records) by adding a left join if needed (adjust the LINQ query or T-SQL to include LEFT JOIN logic).
• For performance, consider adding indexes on ItemID and LastUsedDate if they aren't already present—this will speed up the grouping and aggregation.

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