Open Closed

[Database][Navigation Property] Domain Entity stored in a tenanted database, but its associated navigation property is stored in a separate database #481


User avatar
0
christianvpernix created

Hi,

The issue is -

  • How to run SQL across both databases to return the entity with domain properties? The entity exists in a tenanted database but the associated navigation property is stored in a separate database

<br> Current behavior -

  • When fetching the domain entity under the entities tenant context, the entity is returned but the navigation properties of that entity are null/empty

<br> Work around -

  • The current work around is to change tenant context and then fetch the associated navigation properties from the correct database, but this has significant performance impacts and is not best practice.

<br> The architecture we are structuring the solution on can be summarized as below:

The domain entity exists in the tenant databases, but the navigation property is stored in the primary database. The primary database connection string is tied to the null/default tenant, and each tenant database connection string is tied to a tenant id (non-null).

Please let me know if any of this explanation is unclear and I will clarify.

<br> Thanks, Christian

ABP Framework version: v3.0.5 UI type: MVC Tiered (MVC) or Identity Server Seperated (Angular): no Exception message and stack trace: NA Steps to reproduce the issue: NA


5 Answer(s)
  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi,

    I think there is no better way, you need to query multiple times to get navigation properties. I don't think there will be performance problem. Maybe you can cache the navigation properties to get better perfoormance.

  • User Avatar
    0
    christianvpernix created

    Hi,

    In that case can you please advise what would be the best practice approach within ABP to achieve this?

    For example, should we discard the concept of navigation properties and just rely on id mapping?

    Thanks

  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Hi In fact, I think it has nothing to do with ABP. Yes,you just need rely on id mapping. Since the data is not in the same database, you have to manually maintain data integrity

  • User Avatar
    0
    christianvpernix created

    Sure thing, is there anything we can do with DbContexts to make this as close to ABP as possible?

  • User Avatar
    0
    liangshiwei created
    Support Team Fullstack Developer

    Sorry, I don't understand what is make this as close to ABP. ABP will not restrict you. Maybe you want an elegant solution(Like: automatically query data from other databases when using repository query related data),but this is a big feature.

Made with ❤️ on ABP v9.1.0-preview. Updated on December 10, 2024, 06:38