How do I call a function each time entity is populated with data

[srafay]

On Data Load event

Hi, I am trying to implement custom row and column level security. For that purpose, I need to call my function which deletes some rows and resets some columns values depending on the user access.

Right now I am wrapping every function which returns data from the database, but that is very repetitive. Consider the sample below:

public class UsersService
{
        private readonly MyDBContext _context;
        private readonly RowAndColumnSecurityService _secure;

        public UsersService(MyDBContext context, RowAndColumnSecurityService secure)
        {
            _context = context;
            _secure = secure;
        }

        public <IEnumerable<Users>> GetUsers()
        {
            return _secure.SecureData( _context.Users.ToList() );
        }
}

This is just the user service, I have around 20 more services which get the data from the database. So instead of wrapping every _context.Entity.ToList(), I want to write a function once which gets called on each entity whenever its data is loaded, probably a OnDataLoad Event or similar. Is it possible to implement something like this with the current EntityFramework Core?

Include provider and version information

EF Core version: 6.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer 6.0.0
Target framework: .NET 6.0

[roji]

Duplicate of #15911

[roji]

#15911 tracks adding an event for when an entity is materialized (loaded).

However, that event is usually requested in order to perform some sort of change on the entity itself, and not modify the database when loading some entity. Could you please provide a bit more context on exactly what it is you're trying to achieve? Are you sure you're looking to delete rows whenever some row is loaded?

FWIW custom row- and column-level security is usually implemented via the use of global query filters, when a column determines which user owns the row, and the query filter makes sure a given user only sees their row. Otherwise, database triggers are also a way of doing something in the database when an event occurs - but you can typically only configure triggers on changes (INSERT/UPDATE/DELETE) and not on queries.

[srafay]

#15911 tracks adding an event for when an entity is materialized (loaded).

However, that event is usually requested in order to perform some sort of change on the entity itself, and not modify the database when loading some entity. Could you please provide a bit more context on exactly what it is you're trying to achieve? Are you sure you're looking to delete rows whenever some row is loaded?

FWIW custom row- and column-level security is usually implemented via the use of global query filters, when a column determines which user owns the row, and the query filter makes sure a given user only sees their row. Otherwise, database triggers are also a way of doing something in the database when an event occurs - but you can typically only configure triggers on changes (INSERT/UPDATE/DELETE) and not on queries.

Hey @roji, this issue is a duplicate of #15911 indeed. It seems like it is being worked on, someone has also posted an interim solution for this on that issue, which I didn't know about, thanks for pointing it out. ObjectMaterialized event will do the work for me, so I will wait for it to be implemented and then upgrade when its available.

Regarding my use case, it seems like I didn't put enough information. I am not deleting rows from the database, I am only manipulating the rows (data) returned by EF Core, in memory, removing specific rows (items in list) depending on a few conditions (user access level for example) and then returning it to the Logic layer.
User Access level can be changed from time to time, along with the clients requirements so I am not using Global Query Filters. Making my own logic for column and row level security because its easily customizable according to the use cases.

Also hoping that #15911 gets released soon, thanks again!

[roji]

@srafay note that the ObjectMaterialized event tracked by #15911 is about customizing materialized entities, not filtering out which entities are loaded and which aren't (@ajcvickers can confirm). I don't think that event would be the way forward for applying a row filtering feature. Even if so, note that if the filtering is applied client-side (in .NET), that means that all rows are loaded from the database (for all users), which means you're transferring potentially lots of data that you're not going to end up needing.

(it's true that if you're looking to hide certain columns, that could be achieved via ObjectMaterialized)

User Access level can be changed from time to time, along with the clients requirements so I am not using Global Query Filters. Making my own logic for column and row level security because its easily customizable according to the use cases.

I'm not sure why changing client requirements should make global query filters problematic (or why an ObjectMaterialized event would be less problematic). With global query filters you can fully customize your row level security - you can think of it as an extra WHERE clause that you automatically attach to all queries going to a table, and which you can customize.

I'd spend some time thinking about exactly what you want to achieve, and why exactly you're looking for something like ObjectMaterialized rather than global query filters.

[ajcvickers]

@ajcvickers can confirm

Confirmed.

[srafay]

Hey @roji thanks for your detailed reply. I think I am customizing materialized entities, not filtering out which entities are loaded and which aren't.

Even if so, note that if the filtering is applied client-side (in .NET), that means that all rows are loaded from the database (for all users)

I am exactly doing the same thing, filtering rows on client-side after loading all the rows from the database.

which means you're transferring potentially lots of data that you're not going to end up needing.

This is not true according to my use case. If I load 5000 rows, I only remove like 10 rows which are sensitive and user does see the rest of 4990 rows. I am only filtering out sensitive rows, which are like not even 1% of all the rows. Some of the users are allowed to view sensitive records and then can see all of the 5000 rows.

I'm not sure why changing client requirements should make global query filters problematic (or why an ObjectMaterialized event would be less problematic). With global query filters you can fully customize your row level security - you can think of it as an extra WHERE clause that you automatically attach to all queries going to a table, and which you can customize.

One of the requirements is to not change the existing database or table schema, which I think will be needed for global query filters to work. Also, most of the data is coming from the Views and I do not have any access to change or modify them, or to change the schema of the tables.

Anyway, I am just done with the row and column security implementation and it seems to be working fine, will test it thoroughly to make sure it works as expected. I had to replace all the _context.Entity.ToList() with RowAndColumnSecurityService.SecureData( _context.Entity.ToList() ). It kind of looks like this:

public class UsersService
{
        private readonly MyDBContext _context;
        private readonly RowAndColumnSecurityService _secure;

        public UsersService(MyDBContext context, RowAndColumnSecurityService secure)
        {
            _context = context;
            _secure = secure;
        }

        public <IEnumerable<Users>> GetUsers()
        {
            return _secure.SecureData<Users>( _context.Users.ToList() );
        }
}

public class RowAndColumnSecurityService
{
        public List<TData> SecureData<TData>(dynamic Data)
        {
            return SecureColumns<TData>(SecureRows<TData>(Data));
        }

        public List<TData> SecureRows<TData>(dynamic Data)
        {
            /* code kind of looks like this - pseudocode */

            userName = GetUserName();
            usersForbiddenRows = GetUsersForbiddenRows(userName);
            entityInformation = GetEntityInformation(Data);
            
            foreach (forbiddenRow in usersForbiddenRows)
            {
                 if (forbiddenRow.databaseName == entityInformation.databaseName
                 && forbiddenRow.tableName == entityInformation.tableName)
                 {
                      foreach(dataRow in Data)
                      {
                           /* Use FastMember package for accessing and setting dynamic data */
                           if (dataRow[forbiddenRow.columnName] == forbiddenRow.Value)
                           {
                                Data.Remove(dataRow);
                           }
                      }
                 }
            }
            return Data;

            /* pseudocode */
        }

        public List<TData> SecureColumns<TData>(dynamic Data)
        {
             /* Has similar logic as SecureRows */
             return Data;
        }
}

GetUsersForbiddenRows returns something like this:

# /api/GetUsersForbiddenRows?user_name={user_name}'
{
    "forbidden_rows": [
      {
          "databaseName": "MyDB",
          "tableName": "USERS",
          "columnName": "Grade",
          "value": "ABC",
          "authorized": false
      }
    ]
}

[roji]

I think I am customizing materialized entities, not filtering out which entities are loaded and which aren't.

I'm a bit confused - it definitely seems below this sentence that you're describing a row filtering solution. Specifically, in your code sample you're calling GetUsersForForbiddenRows - I'm assuming that function filters out only rows which the user may see (that's what row-level security is usually about).

Stepping back, you seem to want to do two things: row filtering and column data removal. For the column data removal, the ObjectMaterialized event tracked by #15911. However, filtering out rows isn't something that this event will support; this is what global query filters are for.

One of the requirements is to not change the existing database or table schema, which I think will be needed for global query filters to work. Also, most of the data is coming from the Views and I do not have any access to change or modify them, or to change the schema of the tables.

I really don't see why global query filters would require a change in the existing database any more than any client-side row filtering scheme. In your above code sample, it once again seems that GetUsersForbiddenRows filters rows, presumably based on some column; if so, that means that your database already contains information that tells you which user can see which rows. Just as GetUsersForbiddenRows can look at that information, a global query filter could too; both are a form of filter, the difference is only where they work (client or server side).

Anyway, I am just done with the row and column security implementation and it seems to be working fine [...]

If you're happy with your solution, that's great - feel free to close this issue.

[srafay]

Hmm maybe row filtering, how I want it, might not be possible using ObjectMaterialized event. I will try it out once #15911 is released.

I did read about global query filters before implementing custom row and column level security but I concluded that it wasn't possible to achieve the results I wanted. Still, I will try implementing the same thing using global query filters, just to see if I really get my desired results, as you insist so much.

Closing the issue. Thanks for providing all the information and support.