0

I have one master & detail in my 'db1' and there is one column named 'EntryByUserId' in master table. User table is available in 'db2'.
When all the tables are available in one single database we can directly get user detail by using include function. But here my reference table is in another database so in my case user object will return null value. So anyone please help me to achieve this. I have created multiple dbcontext in my project but don't know how to get this.

Below is the code we use when all tables are available in single database.

dbcontext1.tbl_Master.Include(m => m.tbl_Detail).Include(m => m.tbl_user)
                    .AsNoTracking().FirstOrDefault();
vijay v
  • 1,888
  • 1
  • 12
  • 19
Jayesh Suthar
  • 28
  • 7
  • Not sure, if I fully understood your question. But if you have two databases (two contexts) you have to send two individual queries. First send to the master table and receive the userIds. Then send a second query with all desired userIds to the second context to get the details. To use the now local list for the second query, a simple `.Contains()` [should work](https://stackoverflow.com/a/41266353/1838048). – Oliver Feb 09 '21 at 07:37
  • Thank you @Oliver for your response. Now my concern is that here is just one table user I required from second context but I have many other tables in which more than 10 reference columns are available so in that case it will execute multiple queries to get each one reference table detail and it will become time consuming process. So is there any way to join multiple context to achieve this? – Jayesh Suthar Feb 09 '21 at 08:08
  • If your data is spread over multiple contexts, you have to query each context individually with the needed filters. Be aware that you can query multiple contexts in parallel, by calling the async functions without await prefix to get the tasks and then calling `await Task.WhenAll(query1Task, query2Task, etc.)` – Oliver Feb 09 '21 at 09:14
  • So why do you have multiple DbContexts if they contain related data? – David Browne - Microsoft Feb 09 '21 at 13:33
  • @DavidBrowne-Microsoft This is the requirement to manage multiple Dbcontexts whether it is related or non related. – Jayesh Suthar Feb 11 '21 at 06:00

1 Answers1

1

One option to accommodate this cleanly, especially for something as frequently accessed as a "User" reference for something like reporting on CreatedBy or ModifiedBy tracking on rows would be to implement a view within Db2 that lists the users from Db1. Then in your main application context you can map a User entity to the view rather than a table. I would put guards in your DbContext and entities to discourage/prevent modifications to this User entity, and leave maintenance of users to a DbContext overseeing the Db1 tables.

If this is something like a multi-tenant system with a system database for authentication and separate DBs per tenant which are tracking things like CreatedBy against records, I would recommend considering a system to inspect and replicate users between the auth database and the respective tenant databases. The reason for this would be to help enforce referential integrity for the data and the user references. The issue with the view approach is that there is no constraint available to ensure that a UserId reference actually corresponds with a row in the Users table over in the other database. It can be indexed, but not constrained so you have to handle the possibility of invalid data.

Steve Py
  • 26,149
  • 3
  • 25
  • 43