将含左连接与分组的SQL查询转换为LINQ语句

Let's break down how to translate your SQL query into LINQ, whether you're using Entity Framework Core (EF Core) with a database context or working with in-memory collections (LINQ to Objects).

First, let's recap what your original SQL does: it performs left joins between ITEM_DATA, PART_OPTIONS, and ATTRIBUTE_DATA, groups the results by PART_OPTIONS.PART_ID, and concatenates CODE values using a newline character (CHAR(13) = \r).

Assumptions About Your Entity Classes #

We'll assume you have these entity classes set up (with navigation properties for relationships):

public class ItemData
{
    public int ID { get; set; }
    public string ENT_DESC { get; set; }
    public string CAT_DESC { get; set; }
    public string PART_NUM { get; set; }
    public string PART_DESC { get; set; }
    
    // Navigation properties
    public ICollection<PartOptions> PartOptions { get; set; }
    public ICollection<AttributeData> AttributeData { get; set; }
}

public class PartOptions
{
    public int PART_ID { get; set; }
    public string CODE { get; set; }
    public string PROMPT { get; set; }
    public string DESCRIPTION { get; set; }
    public decimal PRICE { get; set; }
    
    public ItemData ItemData { get; set; }
}

public class AttributeData
{
    public int PART_ID { get; set; }
    public string NOTE { get; set; }
    // Renamed to avoid conflict with PartOptions.DESCRIPTION
    public string AttrDesc { get; set; }
    
    public ItemData ItemData { get; set; }
}

LINQ Query for EF Core #

If you're using EF Core with a database context, here's the equivalent query:

using (var dbContext = new YourDbContext())
{
    var query = from item in dbContext.ItemData
                // Left join with PART_OPTIONS
                join partOpt in dbContext.PartOptions 
                    on item.ID equals partOpt.PART_ID into partOptGroup
                from partOpt in partOptGroup.DefaultIfEmpty()
                // Left join with ATTRIBUTE_DATA
                join attrData in dbContext.AttributeData 
                    on item.ID equals attrData.PART_ID into attrGroup
                from attrData in attrGroup.DefaultIfEmpty()
                // Group by PART_OPTIONS.PART_ID
                group new { item, partOpt, attrData } by partOpt.PART_ID into grouped
                select new
                {
                    ENT_DESC = grouped.Select(x => x.item.ENT_DESC).FirstOrDefault(),
                    CAT_DESC = grouped.Select(x => x.item.CAT_DESC).FirstOrDefault(),
                    PART_NUM = grouped.Select(x => x.item.PART_NUM).FirstOrDefault(),
                    PART_DESC = grouped.Select(x => x.item.PART_DESC).FirstOrDefault(),
                    // Equivalent to GROUP_CONCAT with CHAR(13)
                    CODE = string.Join("\r", grouped.Where(x => x.partOpt != null).Select(x => x.partOpt.CODE)),
                    PROMPT = grouped.Select(x => x.partOpt.PROMPT).FirstOrDefault(),
                    DESCRIPTION = grouped.Select(x => x.partOpt.DESCRIPTION).FirstOrDefault(),
                    PRICE = grouped.Select(x => x.partOpt.PRICE).FirstOrDefault(),
                    NOTE = grouped.Select(x => x.attrData.NOTE).FirstOrDefault(),
                    ATTR_DESC = grouped.Select(x => x.attrData.AttrDesc).FirstOrDefault()
                };

    // Execute the query
    var result = query.ToList();
}

LINQ to Objects (In-Memory Collections) #

If you're working with in-memory lists, you can simplify using navigation properties:

// Assume you have these in-memory lists
List<ItemData> itemDataList = GetYourItemData();
List<PartOptions> partOptionsList = GetYourPartOptions();
List<AttributeData> attributeDataList = GetYourAttributeData();

// First, wire up navigation properties (if not already done)
foreach (var item in itemDataList)
{
    item.PartOptions = partOptionsList.Where(p => p.PART_ID == item.ID).ToList();
    item.AttributeData = attributeDataList.Where(a => a.PART_ID == item.ID).ToList();
}

// LINQ query
var result = itemDataList
    // Flatten left join with PartOptions
    .SelectMany(item => item.PartOptions.DefaultIfEmpty(), (item, partOpt) => new { item, partOpt })
    // Flatten left join with AttributeData
    .SelectMany(x => x.item.AttributeData.DefaultIfEmpty(), (x, attrData) => new { x.item, x.partOpt, attrData })
    // Group by PART_ID (handle null for items with no PartOptions)
    .GroupBy(x => x.partOpt?.PART_ID)
    .Select(grouped => new
    {
        ENT_DESC = grouped.First().item.ENT_DESC,
        CAT_DESC = grouped.First().item.CAT_DESC,
        PART_NUM = grouped.First().item.PART_NUM,
        PART_DESC = grouped.First().item.PART_DESC,
        CODE = string.Join("\r", grouped.Where(x => x.partOpt != null).Select(x => x.partOpt.CODE)),
        PROMPT = grouped.FirstOrDefault(x => x.partOpt != null)?.partOpt.PROMPT,
        DESCRIPTION = grouped.FirstOrDefault(x => x.partOpt != null)?.partOpt.DESCRIPTION,
        PRICE = grouped.FirstOrDefault(x => x.partOpt != null)?.partOpt.PRICE ?? 0,
        NOTE = grouped.FirstOrDefault(x => x.attrData != null)?.attrData.NOTE,
        ATTR_DESC = grouped.FirstOrDefault(x => x.attrData != null)?.attrData.AttrDesc
    })
    .ToList();

Key Notes: #

• Left Joins: We use into + DefaultIfEmpty() in EF Core LINQ, or SelectMany with DefaultIfEmpty() for in-memory collections to replicate SQL's LEFT JOIN.
• GROUP_CONCAT: string.Join("\r", ...) replaces GROUP_CONCAT(PART_OPTIONS.CODE, CHAR(13)) since CHAR(13) is the carriage return character (\r).
• Grouped Field Selection: Since SQL allows selecting non-aggregated fields in a group (which picks the first value in most databases), we use FirstOrDefault() or First() to mimic this behavior.
• Null Handling: For fields from left-joined tables (like PROMPT or NOTE), we use null-conditional operators (?.) to avoid null reference exceptions.

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