实体框架跨两个数据库联接 [英] Entity Framework join across two databases

问题描述

我有一个WebAPI.我需要返回ActivityRegisters的列表.我们有一个这样做的存储库:

I have a WebAPI. I need to return a list of ActivityRegisters. We have a repository that does it like this:

public IQueryable<ActivityRegister> GetRegisters(int activityID) 
{
    return ActivityDBContext.ActivityRegisters.Where(x => x.ActivityID == activityID x.IsActive == true).OrderBy(x => x.ActivityRegisterID).AsQueryable();
}

但是，在ActivityRegister表上有一个可为空的列，称为roomID.有一个Rooms表，但是它在另一个数据库中，我们有一个AdminDBContext.我需要API返回管理数据库中存在的有效负载中的roomName.如何获得上述方法以使用roomID返回roomName?谢谢，我是新来的，正在学习.

However, there is a nullable column on the ActivityRegister table called roomID. There is a Rooms table but it is in a different database which we have a AdminDBContext for. I need the API to return the roomName in the payload which exists in the Admin DB. How can I get the above method to return the the roomName using the roomID? Thank you, I'm new and learning.

推荐答案

您可以像这样在两个不同的上下文中对表执行联接:

You can perform a join on tables across two different contexts like this:

public IQueryable<ActivityRegister> GetRegisters(int activityID) 
{
    var activityRegisters = ActivityDBContext.ActivityRegisters.Where(x => x.ActivityID == activityID x.IsActive == true).OrderBy(x => x.ActivityRegisterID).ToList();
    var roomIdsFromActivityRegisters = activityRegisters.Select(activityRegister => activityRegister.roomID);
    var rooms = AdminDBContext.Rooms.Where(room => roomsIdFromActivityRegisters.Contains(room.Id)).ToList();
    var resultFromJoinAcrossContexts = (from activityRegister in activityRegisters 
                                        join room in rooms on activityRegister.roomID equals room.Id
                                        select new ActivityRegister
                                        {
                                            Room = room,
                                            roomID = room.Id,
                                            Id = activityRegister
                                         });
    return resultFromJoinAcrossContexts.AsQueryable();
}

这篇关于实体框架跨两个数据库联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！