Retrieve Data from Host while inside a Tenant! #2662

bilalhaidar created 7 years ago

Hello, I am facing a very complicated scenario here.

I login as admin of Default Tenant. (Id = 1)

Then, I have a table lets call it Events (Stored per Tenant). On the Event record there is Location (Stored as ID on Event record and refers to the ID of a Location).

Location data (I have several locations stored in the system) as stored as tenant-unaware (Saved in Host). Why? Because Locations are shared by all tenants.

Now in my query to return an Event including Location Name, I have to LEFT OUTER JOIN with Location table.

However, I am always getting NULL for the Location Name.

I believe I know what's happening. The query is adding additional filter on Tenant Id.

I have the code something as:

from e in _eventsRepo.GetAll() 
join l in _locationsRepo.GetAll() on e.LocationId equals l.Id into JoinLocations
from lo in JoinLocations.DefaultIfEmpty()
select new {
  EventId = e.Id,
  EventLocation = lo.LocationName
}

The above always returns null for LocationName.

Again, Locations are stored per host, while events are stored per tenant.

I am sure you passed through this before, I truly appreciate your feedback.

Regards Bilal

18 Answer(s)

0
ismcagdas created 7 years ago
Support Team
Hi,

I can suggest you two ways of doing this, but in the future if you want to seperate tenant databases, you will not be able to join locations on host database with the data in your tenant database.

If location data is not big, you can switch to host context <a class="postlink" href="http://aspnetboilerplate.com/Pages/Documents/Multi-Tenancy#switching-between-host-and-tenants">http://aspnetboilerplate.com/Pages/Docu ... nd-tenants</a> and get all location data first (or you can make a cached list for this if it is not changing so frequently), then swtich back to tenant context and get all data with related locationIds and set location names manually by code.

You can disable MayHaveTenant filter and add tenantId filters into your query by yourself but as I said, if you want to seperate tenant databases in the future this will be a problem. In order to do that, you can write such a code in your app service:

using (CurrentUnitOfWork.DisableFilter(AbpDataFilters.MayHaveTenant)) { }
0

bilalhaidar created 7 years ago

Hi Ismail,

For first option, you mean I bring Locations and the rest of reference data while SetTenantId is Null. Then, use Linq to SQL to bring in all events without any left outer joins. Then, loop over data and bring in location name, etc.?

For second option, I know I've used IMayHaveTenant on all data that is part of host (I made that just in case). So, when I disable this filter, I can then for each join specify that TenantId is null?

Did I understand your point correctly?

IMayHaveTenant: What does this filter add to the query anyway? For me i noticed that if an entity has IMayHaveTenant, and then if a query is executing inside a Tenant (!= Null), then a WHERE is added to that entity trying to filter based on TenantID since it was marked IMayHaveTenant. Correct?

What if I remove IMayHaveTenant? What happens.

Thanks
0

ismcagdas created 7 years ago
Support Team

Hi,

You understand it correctly :). IMayHaveTenant is explained here <a class="postlink" href="http://aspnetboilerplate.com/Pages/Documents/Multi-Tenancy#imayhavetenant-interface">http://aspnetboilerplate.com/Pages/Docu ... -interface</a>
0

bilalhaidar created 7 years ago

So basically the problem is having that IMayHaveTenant filter, correct? In my case, I want to bring them and do joins to those tables irrespective of the tenant.

So removing the IMustHaveTenant filter should solve the issue here?

Thanks
0

ismcagdas created 7 years ago
Support Team

Hi,

You can do that but in that case, you need to add tenant filter by yourself.
0

bilalhaidar created 7 years ago

But I don't need that filter for the reference data stored on Host.

So if I am doing a query that combines both data from host and Tenant 1, then knowing how IMayHaveTenant is removed, there won't be any WHERE clause to filter on Tenant for entities that don't implement that interface, only WHERE clause filter applies for entities with IMustHaveTenant.

I tried it and it works.

Thanks
0

JeffMH created 7 years ago

Can you mark the Location entity with a MultitenancySide.Host attribute: [MultiTenancySide(MultiTenancySides.Host)]? Not sure if you use both in one query if it still applies the filter to each table.

You might be pushing the limits of the Filtering technique they are using with EF.

Ok, I am getting back to work now :)
0

ismcagdas created 7 years ago
Support Team

Hi,

Thanks @JeffMH :),

That is an alternative as well. If your entity only belongs to Host then you are right, there is no meaning to use IMayHaveTenant/MustHaveTenant interfaces.
0

bilalhaidar created 7 years ago

Very true Ismail, this is what I've concluded that no need to use any of the filters, but even with IMayHaveTenant would add a filter of TenantId.

Thanks @JeffHM :-)

By the way, the data filter you employee, is it something supported by EF? Any reference on that pls.

Thanks
0

ismcagdas created 7 years ago
Support Team

Hi,

No, it is not a default feature of EF, we use this library for data filters <a class="postlink" href="https://github.com/jcachat/EntityFramework.DynamicFilters">https://github.com/jcachat/EntityFramew ... micFilters</a>.
0

bilalhaidar created 7 years ago

Thanks a lot :)
0

JeffMH created 7 years ago

<a class="postlink" href="https://github.com/jcachat/EntityFramework.DynamicFilters">https://github.com/jcachat/EntityFramew ... micFilters</a>

This is the library they use to do the filtering. It's not baked into EF.
0

bilalhaidar created 7 years ago

Thanks Jeff.

You look like you are an advanced user on this framework. Do you share y our findings in a blog or so?

Regards Bilal
0

JeffMH created 7 years ago

We are in our 3rd product with it so just getting more familiar as we go! I am a quiet behind the scenes guy. Unfortunately no blogs about it. I am a part owner of a consulting firm, so spend most my hours coding and managing clients. Just like to help out where i can. I like the framework also so I like to hop on sometime and answer questions if I can. I really want to see the framework do good. When I first saw it I thought it was a heck of an idea.
0

bilalhaidar created 7 years ago

Me too. I was so much overwhelmed. I am trying slowly to learn more about the framework by digging through the code etc.

My main concern now is to be able to upgrade whenever they release a new release.

Tomorrow, I will download a project 2 releases before. Create a new Solution Folder with .Core, .Application, .Web and .EntityFramework.

Then manage the references between all the modules and DependsOn.

Once all works out, I will try to replace all the framework files from the latest and try to run again. I believe I would need to run an Update-Database here in case there is anything changed in DB.

I will keep you posted :)

Thanks
0

bilalhaidar created 7 years ago

Hey Jeff,

Can you please have a look at this post: #2694

Thanks
0

scharada created 6 years ago

@bilalhaidar , sorry to reopen the discussion here again. but i do have the same case as you and was wondering how you solved it. i have read about the subject and most answers point to using the [MultiTenancySide(MultiTenancySides.Host)] attribute. but that does not prevent the framework from creating the table on the tenant side as well... in fact the only thing preventing a tenant from accessing the data in the host only table is not having access to the ui ... this is not the wanted behavior. i think is should not be the expected behavior as host only means specifically the entity resides only on the host side.

would appreciate your feedback on this.
0

ismcagdas created 6 years ago
Support Team

Continues on <a class="postlink" href="https://github.com/aspnetzero/aspnet-zero-core/issues/1005">https://github.com/aspnetzero/aspnet-ze ... ssues/1005</a>

Have an answer to this question? Log in and write your answer.