CODEWITHSUNDEEP
c#linqentity-frameworkjoin
pgtips
pgtips

Reputation: 1338

Entity Framework join across two databases

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();
}

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.

Upvotes: 0

Views: 4648

Answers (1)

Syed Farjad Zia Zaidi
Syed Farjad Zia Zaidi

Reputation: 3360

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();
}

Upvotes: 2

Related Questions