含多表连接、GROUP BY与MIN()的SQL转Linq to Sql语法咨询

Got it, let's break down how to translate your SQL into LINQ to SQL, focusing on the joins, GROUP BY, and MIN() aggregation you're stuck on. First, let's align with your original logic: you're joining three views, grouping by three specific fields, and grabbing the earliest creation date for each unique group.

Query Syntax (Most SQL-like) #

This syntax mirrors your original SQL structure closely, so it's easy to map directly:

// Assume you have an instance of your DataContext set up
using (var dc = new YourDataContext())
{
    var result = from asset in dc.vw_DimLabAsset
                 // First inner join: LabAsset to Worker
                 join worker in dc.vw_FactWorker 
                     on asset.LabAssetAssignedToWorkerKey equals worker.WorkerKey
                 // Second inner join: Joined result to Organization Hierarchy
                 join org in dc.vw_DimOrganizationHierarchy 
                     on worker.OrganizationHierarchyKey equals org.OrganizationHierarchyKey
                 // Group by the three fields from your SELECT clause
                 group asset by new 
                 {
                     org.OrganizationHierarchyUnitLevelThreeNm,
                     org.OrganizationHierarchyUnitLevelFourNm,
                     asset.LabAssetSerialNbr
                 } into groupedAssets
                 // Select the final result set with aggregation
                 select new 
                 {
                     // Access grouped fields via the Key property
                     groupedAssets.Key.OrganizationHierarchyUnitLevelThreeNm,
                     groupedAssets.Key.OrganizationHierarchyUnitLevelFourNm,
                     groupedAssets.Key.LabAssetSerialNbr,
                     // Use LINQ's Min() method to get the earliest creation date
                     MinCreated = groupedAssets.Min(item => item.SystemCreatedOnDtm)
                 };

    // Materialize results to a list if needed
    var resultList = result.ToList();
}

Key Details: #

• Joins: LINQ uses join ... on ... equals ... for inner joins, matching your SQL's INNER JOIN behavior exactly.
• GROUP BY: When grouping by multiple fields, wrap them in an anonymous type (the new { ... } block). Grouped results are stored in groupedAssets, and you access the grouping fields via the Key property.
• MIN() Aggregation: Call the Min() method on the grouped collection, passing a lambda that specifies which field to calculate the minimum for.
• DISTINCT: You don't need an explicit DISTINCT here—grouping already ensures each unique combination of the three fields returns only one row, which achieves the same effect as your original SELECT DISTINCT.

Method Syntax (Fluent Style) #

If you prefer the fluent method chain approach, here's the equivalent:

using (var dc = new YourDataContext())
{
    var result = dc.vw_DimLabAsset
        // Join LabAsset to Worker
        .Join(dc.vw_FactWorker,
              asset => asset.LabAssetAssignedToWorkerKey,
              worker => worker.WorkerKey,
              (asset, worker) => new { Asset = asset, Worker = worker })
        // Join the combined result to Organization Hierarchy
        .Join(dc.vw_DimOrganizationHierarchy,
              aw => aw.Worker.OrganizationHierarchyKey,
              org => org.OrganizationHierarchyKey,
              (aw, org) => new { aw.Asset, Org = org })
        // Group by the three target fields
        .GroupBy(x => new 
        {
            x.Org.OrganizationHierarchyUnitLevelThreeNm,
            x.Org.OrganizationHierarchyUnitLevelFourNm,
            x.Asset.LabAssetSerialNbr
        })
        // Select the final output with aggregation
        .Select(grouped => new 
        {
            grouped.Key.OrganizationHierarchyUnitLevelThreeNm,
            grouped.Key.OrganizationHierarchyUnitLevelFourNm,
            grouped.Key.LabAssetSerialNbr,
            MinCreated = grouped.Min(item => item.Asset.SystemCreatedOnDtm)
        });

    var resultList = result.ToList();
}

Using a Custom Strongly-Typed Class #

If you want to map results to a custom class instead of anonymous types, just swap the anonymous new { ... } with your class constructor:

// Example custom class
public class AssetCreationSummary
{
    public string LevelThreeOrgName { get; set; }
    public string LevelFourOrgName { get; set; }
    public string AssetSerialNumber { get; set; }
    public DateTime MinCreatedDate { get; set; }
}

// Update the select clause in your query:
select new AssetCreationSummary
{
    LevelThreeOrgName = groupedAssets.Key.OrganizationHierarchyUnitLevelThreeNm,
    LevelFourOrgName = groupedAssets.Key.OrganizationHierarchyUnitLevelFourNm,
    AssetSerialNumber = groupedAssets.Key.LabAssetSerialNbr,
    MinCreatedDate = groupedAssets.Min(item => item.SystemCreatedOnDtm)
}

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