0

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.

Salah Akbari
  • 39,330
  • 10
  • 79
  • 109
pgtips
  • 1,328
  • 6
  • 24
  • 43

1 Answers1

2

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();
}
Syed Farjad Zia Zaidi
  • 3,302
  • 4
  • 27
  • 50
  • 1
    This pulls the whole `Rooms` table into memory! You could at least get the rooms of which the `Id`s occur in `activityRegisters`. – Gert Arnold Jul 14 '15 at 22:37
  • `Exists`?? I think you mean `Any`. Anyway, you can't use `activityRegisters` in a subsequent EF query just like that. – Gert Arnold Jul 14 '15 at 22:56
  • And now you've gone full circle back to pulling all rooms from the database again. The point is: create an array of room ids from `activityRegisters` and use that in a `Contains` statement to fetch the relevant rooms only. – Gert Arnold Jul 14 '15 at 23:19