I am working on AspNetZero Core with Angular 8. I have read a lot of articles about using composite sql key for muti-tenant systems. It will keep the data consistent and retain the data integrity concept. It will save a lot of validations when you insert entity with related items (IDs). So no need to check if those IDs is owned by the current tenant. It will be maintained by Sql Server. If we decided to use it, is there any compatibility issues with AspNetZero framework?, since someone mentioned that he had an issue with soft delete: https://support.aspnetzero.com/QA/Questions/1378 what are your recommendations about this technique?
12 Answer(s)
-
0
Hi @moetarhini - your entities may be saved in a different database to your tenants. It would not be a good idea with the Zero framework which explicitly allows a multi-database solution. As a DBA I would also question the use of composite keys, amongst other things they can cause havoc with replication but maybe I'm just old-school.
-
0
Thanks for your reply, I am trying to solve the following problem may you can help me thinking about it. I have Ticket entity with the following properties: {Id, TenantId, Title, StatusId, PriorityId, UserId} every time we post/put new Ticket from Angular to the APIs we have to check if the selected {StatusId, PriorityId, UserId} is related to the current tenant so I need to hit the database three times to check that every item is related to the current tenant right? Is there any method to prevent those three hits? Since composite key will let the sql server do that since we add foreign key to both ** (Id, TenantId) ** in Status table and the same thing for the other Priority and User. No need to reference the Tenant table because tenant id is maintained by the framework and there no chance to be for other tenant.
-
0
Yes, implement an IQueryable for your query and then execute with the ToList() or await ToListAsync(). If you would like to post your code I can advise further.
-
0
But right now it's time for bed.
-
0
"Yes, implement an IQueryable for your query and then execute with the ToList() or await ToListAsync()." Could you explain your solution more later.
Good night...
-
0
Read the following page and take notice of the section on IQueryables. abp repositories
-
0
good evening @bobingham, suppose that I have the following Dto: CreateTicketInput{Id, Title, StatusId, PriorityId, UserId}
and the the following function to create ticket:
public async Task CreateTicket(CreateTicketInput input) {
// The following three lines will throw exception if IDs not belong to the current tenant // I need to do this checking on create and update var status = _statusRepository.Get(input.StatusId); var priority = _priorityRepository.Get(input.PriorityId); var user = _userRepository.Get(input.UserId); var ticket= ObjectMapper.Map<Ticket>(input); await _ticketRepository.InsertAsync(ticket);}
is there any efficient way to do those three checks? how your solution can help me?
Thanks
-
0
@moetarhini, Listen, I don't mind helping but I can't write your app for you. Why do you need the status, priority and user objects, they have no function in the above code. If you need to get data outside of your current tenant disable the data filter, inject UnitOfWork and refactor as follows:
public async Task CreateTicket(CreateTicketInput input) { using (_unitOfWorkManager.Current.DisableFilter(AbpDataFilters.MayHaveTenant)) { var status = _statusRepository.Get(input.StatusId); var priority = _priorityRepository.Get(input.PriorityId); var user = _userRepository.Get(input.UserId); } await _ticketRepository.InsertAsync(ObjectMapper.Map<Ticket>(input)); } -
0
You didn't catch my problem, if I use the function as bellow: public async Task CreateTicket(CreateTicketInput input) { await _ticketRepository.InsertAsync(ObjectMapper.Map<Ticket>(input)); }
Is there guaranty that the sent id of the Status is belong to the current tenant? not for another tenant? in case of my app hacking. :) that is my question... thanks for your time.
-
0
Please test and debug. If your Status class has a MayHaveTenant or MustHaveTenant interface the answer is yes, it must belong to same tenant. If it does not belong to the same tenant you have set things up incorrectly. Are status, priority and user selected on the client, do they all belong to the same tenant; are they populated from a call to the server, do they all belong to the same tenant. Please test and debug.
-
0
The problem is not with debugging, I wrote this code just to deliver my idea to you. Since we have an open APIs any user in the system can get token and begin to send you whatever he want using a lot of tools like Postman. So we need to protect ourself.
you can read this if you want: https://dba.stackexchange.com/questions/98118/composite-primary-key-in-multi-tenant-sql-server-database
-
0
@moetarhini, you have chosen Zero to handle all the points raised in the article posted. Calm down and run with the framework, it will all become clear as you get used to it. It seems you're starting out with a new framework and then before you use it you're looking for how it might go wrong. Just start to use Zero, populate it with some data and work out how it works and don't try change it.
