如何在LINQ中基于StartsWith条件实现两张表的关联？

我需要关联两张表，关联条件不是列值完全相等，而是其中一张表的列值以另一张表的列值开头。例如p.Id = P__20240623_2KI9T，rt.PackageBusinessId = P__20240623_2KI9T__SMSA，后者以前者开头，要基于这个规则实现关联。当前我的LINQ关联条件存在问题，需要修正，完整函数代码如下：

public async Task<List<GetAllPackagesWithCamundaTaskOutput>> GetAllWithCamundaTaskForCarrierAsync(GetPackageInput input)
{
    try
    {
        var dbSet = await GetDbSetAsync();

        // Fetch IQueryable<RuTasks> by awaiting the task
        var ruTasksQueryable = await _ruTasksRepository.GetQueryableAsync();


        var tasks = await _tasksRepository.GetQueryableAsync();

        var passportPackageId = new GetPassportInput
        {
            PackageId = input.Id
        };
        var getPaspportCarrierTypeStmt = await _passportRepository.GetPassportCarrierTypeAsync(passportPackageId);

        // Fetch distinct user IDs from the filtered ruTasks
        var userIds = ruTasksQueryable.Where(rt => rt.AssignedUserId.HasValue && rt.AssignedUserId != Guid.Empty)
                                     .Select(rt => rt.AssignedUserId.Value)
                                     .Distinct()
                                     .ToList();


        // Get user info from the userInfo service
        var userInfoList = await _userInfoManager.GetUserInfo(new getUserInfoInput
        {
            Ids = userIds.Cast<Guid?>().ToList(),
            JWT = _httpContextAccessor.HttpContext.Items["UserToken"]?.ToString()
        });

        // Convert the userInfoList to a dictionary for efficient lookup
        var userInfoDict = userInfoList.ToDictionary(user => user.Id);

        // Fetch the necessary data from the database
        var packages = await dbSet.ToListAsync();

        // Join data in memory
        var query = from p in packages
                    join rt in ruTasksQueryable.Where(rt => rt.PackageBusinessId.StartsWith(p.Id)) on p.Id equals !rt.PackageBusinessId.Equals(p.Id) into rtGroup
                    from rt in rtGroup.DefaultIfEmpty()
                    join t in tasks on rt?.TasksId equals t.Id into tGroup
                    from t in tGroup.DefaultIfEmpty()
                    join user in userInfoList on rt?.AssignedUserId equals user.Id into userGroup
                    from user in userGroup.DefaultIfEmpty()
                    join pct in getPaspportCarrierTypeStmt on p.Id equals pct.PackageId into pctGroup
                    from pct in pctGroup.DefaultIfEmpty()
                    orderby p.LastModificationTime descending
                    select new GetAllPackagesWithCamundaTaskOutput
                    {
                        ShortId = p.ShortId,
                        PackageId = p.Id,
                        PackageBusinessId = rt?.PackageBusinessId,
                        PassportCount = p.PassportCount,
                        PackageStatusName = p.PackageStatus,
                        PackageReceivedAt = p.CreationTime,
                        PackageLastUpdated = p.LastModificationTime,
                        CamundaTaskId = rt?.Id,
                        AssignedGroupId = rt?.AssignedGroupId,
                        AssignedUserId = rt?.AssignedUserId,
                        AssignedUser = user?.FirstName + " " + user?.LastName,
                        AssignedUsername = user?.Username,
                        PhaseName = rt?.TasksId,
                        CountPassports = pct?.CountPassports,
                        CountSmsa = pct?.CountSMSA,
                        CountSpl = pct?.CountSPL
                    };

        // Apply additional filters and execute the query
        query = GetAllWithCamundaTaskForCarrierAsyncApplyFilters(query, input);
        var result = query.ToList();

        return result;
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "An error occurred in GetAllWithCamundaTaskForCarrierAsync");
        throw new Exception("An internal error occurred during your request. Please try again later.");
    }
}

当前错误的关联代码行：

join rt in ruTasksQueryable.Where(rt => rt.PackageBusinessId.StartsWith(p.Id)) on p.Id equals !rt.PackageBusinessId.Equals(p.Id) into rtGroup

修正方案 #

LINQ查询语法中的join仅支持等值关联，对于"一列值以另一列值开头"这种非等值关联，需要改用from...where的方式实现左关联，或者使用方法语法的GroupJoin自定义关联条件。

方式一：查询语法实现左关联（最简方案） #

将原错误的join部分替换为from...where结构，直接匹配符合StartsWith条件的记录，并用DefaultIfEmpty()保持左关联逻辑：

// Join data in memory
var query = from p in packages
            from rt in ruTasksQueryable.Where(rt => rt.PackageBusinessId.StartsWith(p.Id)).DefaultIfEmpty()
            join t in tasks on rt?.TasksId equals t.Id into tGroup
            from t in tGroup.DefaultIfEmpty()
            join user in userInfoList on rt?.AssignedUserId equals user.Id into userGroup
            from user in userGroup.DefaultIfEmpty()
            join pct in getPaspportCarrierTypeStmt on p.Id equals pct.PackageId into pctGroup
            from pct in pctGroup.DefaultIfEmpty()
            orderby p.LastModificationTime descending
            select new GetAllPackagesWithCamundaTaskOutput
            {
                ShortId = p.ShortId,
                PackageId = p.Id,
                PackageBusinessId = rt?.PackageBusinessId,
                PassportCount = p.PassportCount,
                PackageStatusName = p.PackageStatus,
                PackageReceivedAt = p.CreationTime,
                PackageLastUpdated = p.LastModificationTime,
                CamundaTaskId = rt?.Id,
                AssignedGroupId = rt?.AssignedGroupId,
                AssignedUserId = rt?.AssignedUserId,
                AssignedUser = user?.FirstName + " " + user?.LastName,
                AssignedUsername = user?.Username,
                PhaseName = rt?.TasksId,
                CountPassports = pct?.CountPassports,
                CountSmsa = pct?.CountSMSA,
                CountSpl = pct?.CountSPL
            };

方式二：方法语法实现分组左关联 #

如果需要明确保留分组关联的逻辑，可以使用GroupJoin结合SelectMany：

var query = packages.GroupJoin(
        ruTasksQueryable,
        p => p.Id,
        rt => rt.PackageBusinessId,
        (p, rtGroup) => new { Package = p, RuTasks = rtGroup }
    )
    .SelectMany(
        pr => pr.RuTasks.Where(rt => rt.PackageBusinessId.StartsWith(pr.Package.Id)).DefaultIfEmpty(),
        (pr, rt) => new { pr.Package, RuTask = rt }
    )
    .Join(tasks.DefaultIfEmpty(), 
        prt => prt.RuTask?.TasksId, 
        t => t.Id, 
        (prt, t) => new { prt.Package, prt.RuTask, Task = t }
    )
    .Join(userInfoList.DefaultIfEmpty(), 
        prtt => prtt.RuTask?.AssignedUserId, 
        u => u.Id, 
        (prtt, u) => new { prtt.Package, prtt.RuTask, prtt.Task, User = u }
    )
    .Join(getPaspportCarrierTypeStmt.DefaultIfEmpty(), 
        prttu => prttu.Package.Id, 
        pct => pct.PackageId, 
        (prttu, pct) => new GetAllPackagesWithCamundaTaskOutput
        {
            ShortId = prttu.Package.ShortId,
            PackageId = prttu.Package.Id,
            PackageBusinessId = prttu.RuTask?.PackageBusinessId,
            PassportCount = prttu.Package.PassportCount,
            PackageStatusName = prttu.Package.PackageStatus,
            PackageReceivedAt = prttu.Package.CreationTime,
            PackageLastUpdated = prttu.Package.LastModificationTime,
            CamundaTaskId = prttu.RuTask?.Id,
            AssignedGroupId = prttu.RuTask?.AssignedGroupId,
            AssignedUserId = prttu.RuTask?.AssignedUserId,
            AssignedUser = prttu.User?.FirstName + " " + prttu.User?.LastName,
            AssignedUsername = prttu.User?.Username,
            PhaseName = prttu.RuTask?.TasksId,
            CountPassports = pct?.CountPassports,
            CountSmsa = pct?.CountSMSA,
            CountSpl = pct?.CountSPL
        }
    )
    .OrderByDescending(o => o.PackageLastUpdated);

性能优化建议 #

如果ruTasks和packages数据量较大，建议不要提前调用ToListAsync()将数据加载到内存，保持IQueryable状态，这样StartsWith会被转换为SQL的LIKE '前缀%'语句，在数据库端执行关联，性能会大幅提升：

// 不要提前ToListAsync，保持IQueryable
var packagesQuery = await GetDbSetAsync();

var query = from p in packagesQuery
            from rt in ruTasksQueryable.Where(rt => rt.PackageBusinessId.StartsWith(p.Id)).DefaultIfEmpty()
            // 其他关联的表如果是数据库表，也保持IQueryable，不要提前ToList
            ...

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