如何基于现有LINQ查询实现分组拼接（Group Concatenation）

Great question! Let's break down how to add group concatenation to your existing LINQ query, with examples tailored to both in-memory (LINQ to Objects) and Entity Framework scenarios.

First, Recap Your Base Query #

Your existing query filters ReferralDetails by status, joins with ReferralRepository, and orders results. We'll build on this to add grouping and concatenation.

Scenario 1: LINQ to Objects (In-Memory Data) #

If your data is already loaded into memory (or you don't mind fetching it first), string.Join is the simplest way to concatenate values per group. Here's how to modify your query:

// Build your base joined query and project needed fields
var baseQuery = from t1 in _unitOfWork.ReferralDetailsRepository.Get()
                .Where(m => 
                    (m.ChartStatusID == (int)Utility.ReferralChartStatus.NotStaffed || 
                     m.ChartStatusID == (int)Utility.ReferralChartStatus.Partially_Staffed || 
                     m.ChartStatusID == (int)Utility.ReferralChartStatus.Restaff) && 
                    !m.IsDeleted)
                .OrderByDescending(x => x.ReferralDetailsId)
                join t2 in _unitOfWork.ReferralRepository.Get() on t1.ReferralId equals t2.ReferralId
                // Add any additional joins here (e.g., t3)
                join t3 in _unitOfWork.SomeOtherRepo.Get() on t1.SomeId equals t3.SomeId
                // Project only the fields you need for grouping and concatenation
                select new {
                    ReferralId = t2.ReferralId,
                    ReferralNumber = t2.ReferralNumber,
                    // Replace with the field you want to concatenate (e.g., staff names, notes)
                    FieldToConcatenate = t1.AssignedStaffName
                };

// Group by your target key (e.g., ReferralId) and concatenate values
var lstAssignmentDetails = baseQuery
    .GroupBy(item => item.ReferralId)
    .Select(group => new {
        ReferralId = group.Key,
        ReferralNumber = group.First().ReferralNumber, // Assumes this value is consistent per group
        // Join all values in the group with a separator (e.g., ", ")
        ConcatenatedValues = string.Join(", ", group.Select(g => g.FieldToConcatenate))
    })
    .ToList();

Scenario 2: Entity Framework (LINQ to Entities) #

If you're using EF Core 3.0+, string.Join is directly supported and will translate to SQL's STRING_AGG function (for SQL Server 2017+). For older EF versions, you'll need to either load data to memory first or use Aggregate.

EF Core 3.0+ (Direct SQL Translation)

var lstAssignmentDetails = (from t1 in _unitOfWork.ReferralDetailsRepository.Get()
                            .Where(m => 
                                (m.ChartStatusID == (int)Utility.ReferralChartStatus.NotStaffed || 
                                 m.ChartStatusID == (int)Utility.ReferralChartStatus.Partially_Staffed || 
                                 m.ChartStatusID == (int)Utility.ReferralChartStatus.Restaff) && 
                                !m.IsDeleted)
                            join t2 in _unitOfWork.ReferralRepository.Get() on t1.ReferralId equals t2.ReferralId
                            // Add other joins as needed
                            group t1 by t2.ReferralId into referralGroup
                            select new {
                                ReferralId = referralGroup.Key,
                                // Get other group-level fields (use First() if consistent per group)
                                ReferralNumber = referralGroup.First().Referral.ReferralNumber,
                                // Translates to SQL STRING_AGG
                                ConcatenatedStaffNames = string.Join(", ", referralGroup.Select(t => t.AssignedStaffName))
                            })
                            .ToList();

Older EF Versions (Load to Memory First)

If string.Join isn't supported, fetch the base data to memory first with AsEnumerable(), then apply grouping:

var lstAssignmentDetails = _unitOfWork.ReferralDetailsRepository.Get()
                            .Where(m => 
                                (m.ChartStatusID == (int)Utility.ReferralChartStatus.NotStaffed || 
                                 m.ChartStatusID == (int)Utility.ReferralChartStatus.Partially_Staffed || 
                                 m.ChartStatusID == (int)Utility.ReferralChartStatus.Restaff) && 
                                !m.IsDeleted)
                            .Join(_unitOfWork.ReferralRepository.Get(), 
                                  t1 => t1.ReferralId, 
                                  t2 => t2.ReferralId, 
                                  (t1, t2) => new { t1, t2 })
                            .AsEnumerable() // Load data to memory
                            .GroupBy(x => x.t2.ReferralId)
                            .Select(group => new {
                                ReferralId = group.Key,
                                ReferralNumber = group.First().t2.ReferralNumber,
                                ConcatenatedValues = string.Join(", ", group.Select(g => g.t1.AssignedStaffName))
                            })
                            .ToList();

Key Notes #

• Grouping Key: Adjust GroupBy(item => item.ReferralId) to whatever field you want to group by (e.g., t2.PatientId).
• Distinct Values: Add .Distinct() to the Select inside string.Join if you want to avoid duplicate values (e.g., string.Join(", ", group.Select(g => g.FieldToConcatenate).Distinct())).
• Separator: Replace ", " with any separator you need (e.g., "; ", "\n").

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