如何解决EF Core跨不同DbContext关联数据库表的报错问题？

方案一：将单上下文数据加载到内存后合并 #

由于EF Core无法直接跨上下文生成SQL，可将其中一个上下文的查询结果先加载到内存（转为List），再与另一个上下文的查询在内存中关联或合并。

修改示例代码如下：

// 先从dataSyncDbContext拉取所需数据到内存
var syncDeviceData = dataSyncDbContext.Device
    .Where(d => d.Status != "ACTIVE")
    .Join(dataSyncDbContext.PostedFile, d => d.DeviceId, p => p.DeviceId, (d, p) => new { d, p })
    .Join(dataSyncDbContext.CheckIn, dp => dp.d.DeviceId, c => c.DeviceId, (dp, c) => new 
    {
        DeviceId = dp.d.DeviceId,
        dp.d.SerialNumber,
        dp.d.TailId,
        CheckInDate = c.UpdatedDate,
        DownloadDate = dp.p.CreatedDate
    })
    .ToList();

// 从主上下文查询Alerts，并关联内存中的同步数据
var avWifiDevicesAffected = from a in context.Alerts
                            where !a.Inactive && a.Name.Equals(alertName)
                            join sd in syncDeviceData on a.ResourceId equals sd.DeviceId
                            select new AffectedEntity.Device 
                            {
                                Id = a.Id,
                                DeviceId = sd.DeviceId,
                                TenantId = a.TenantId,
                                OperatorName = context.Operators
                                    .First(o => o.Id == a.TenantId).Name,
                                DeviceSerialNumber = sd.SerialNumber,
                                LastCheckIn = sd.CheckInDate,
                                LastDownload = sd.DownloadDate,
                                TailId = Guid.Parse(sd.TailId)
                            };

// 将两个查询结果都转为内存集合后合并
var qarDevicesAffectedList = qarDevicesAffected.ToList();
var avWifiDevicesAffectedList = avWifiDevicesAffected.ToList();
var devicesAffected = qarDevicesAffectedList.Concat(avWifiDevicesAffectedList);

适用场景：数据量较小的场景，避免大量数据占用内存。

方案二：统一DbContext实现跨库查询 #

若两个数据库部署在同一服务器（如SQL Server），且当前数据库账号拥有跨库访问权限，可将两个库的表映射到同一个DbContext中。

1. 在实体类上指定跨库表的位置：

// 示例：dataSyncDb库中的Device表
[Table("Device", Schema = "dataSyncDb")]
public class SyncDevice
{
    public string DeviceId { get; set; }
    public string SerialNumber { get; set; }
    public string TailId { get; set; }
    public string Status { get; set; }
}

2. 在DbContext中添加对应的DbSet：

public DbSet<SyncDevice> SyncDevices { get; set; }

3. 后续即可用单个DbContext直接编写跨库查询，无需切换上下文，避免原错误。

适用场景：数据库同服务器、有权限跨库访问，代码改动成本最低。

方案三：数据库层面创建跨库视图/存储过程 #

在数据库侧创建跨库视图或存储过程，整合两个库的关联数据，再在EF Core中映射为实体直接查询。

示例跨库视图（SQL Server）：

CREATE VIEW AffectedDevicesView AS
-- 第一个查询分支：主库数据
SELECT 
    a.Id,
    d.Id AS DeviceId,
    d.TenantId,
    o.Name AS OperatorName,
    d.SerialNumber AS DeviceSerialNumber,
    d.CellModuleId,
    d.CellModuleSerialNumber,
    d.LastCheckIn,
    d.LastDownload,
    d.LastPackageCreation,
    t.TailNumber,
    t.Id AS TailId,
    CASE 
        WHEN 'CheckIn' = 'CheckIn' THEN d.LastCheckIn 
        WHEN 'LastDownload' = 'LastDownload' THEN d.LastDownload 
        ELSE d.LastPackageCreation 
    END AS CreationDate,
    CASE 
        WHEN NULL IS NOT NULL AND d.TenantId IN (NULL) THEN 1 
        ELSE 0 
    END AS IsPinned
FROM MainDb.dbo.Alerts a
JOIN MainDb.dbo.Devices d ON a.ResourceId = d.Id
JOIN MainDb.dbo.Operators o ON d.TenantId = o.Id
JOIN MainDb.dbo.Tail t ON d.TailId = t.Id
WHERE d.Inactive = 0 AND a.Inactive = 0 AND a.Name = '目标告警名'

UNION ALL

-- 第二个查询分支：同步库数据
SELECT 
    a.Id,
    d.DeviceId,
    a.TenantId,
    o.Name AS OperatorName,
    d.SerialNumber AS DeviceSerialNumber,
    NULL AS CellModuleId,
    NULL AS CellModuleSerialNumber,
    c.UpdatedDate AS LastCheckIn,
    p.CreatedDate AS LastDownload,
    NULL AS LastPackageCreation,
    NULL AS TailNumber,
    CAST(d.TailId AS UNIQUEIDENTIFIER) AS TailId,
    NULL AS CreationDate,
    0 AS IsPinned
FROM MainDb.dbo.Alerts a
JOIN DataSyncDb.dbo.Device d ON a.ResourceId = d.DeviceId
JOIN DataSyncDb.dbo.PostedFile p ON a.ResourceId = p.DeviceId
JOIN DataSyncDb.dbo.CheckIn c ON a.ResourceId = c.DeviceId
JOIN MainDb.dbo.Operators o ON a.TenantId = o.Id
WHERE d.Status != 'ACTIVE' AND a.Inactive = 0 AND a.Name = '目标告警名'

之后在EF Core中创建与视图字段匹配的实体，直接查询该视图即可获取合并后的结果。

适用场景：数据量大、跨库逻辑复杂，将数据处理压力转移到数据库层面。

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