How to implement for a Web API project field level permission with EF Core 6 and value objects-CodePudding

I'm quite frustrated trying to figure out a possible implementation.

Pursuant DDD and CQS concepts the write side of my application uses various aggregates and associated repositories that are implemented with EF Core.

On the read side I want to use queries that in some cases link related data. This could be just the resolution of an id (i.e. the name of the user that made the last edit) or for performance reasons a list of child objects (i.e. address book of a person).

Therefore the structure of the returned object (GetPersonDTO) including resolutions or children is different of the write object (Person). I use value objects as types for all properties in the entities to keep validation in one spot (always valid objects).

My problems are on the read side. The returned resource representation from a GET request is JSON. Permissions associated with the requesting subject decide if a field is included in the JSON.

My idea was that I use EF Core to return a query object and a permission object that holds the field permissions of that object for the current subject (user). If the subject has read permission for a certain field it will be mapped to the DTO. The DTO uses Optional<T> and a custom JsonConverter as shown here. As a result all Optional<T> fields that are not set will not be included in the JSON response, but it preserves fields that are set to NULL.

I write the queries in EF Core using raw SQL, because I didn't manage to write more complex queries using LINQ. EF Core requires keyless entities for raw SQL queries. I expected EF Core to convert the read fields back into value objects using the converters that have been created for the write side.

But keyless entities cannot be principal end of relationship hence they cannot have owned entities. As various GitHub issues show it is not yet possible that EF Core recreates the object graph from a raw SQL query. It is stated that

In EF each entityType maps to one database object. Same database object can be used for multiple entityTypes but you cannot specify a database object to materialize a graph of object.

If I understand correctly it is also not possible to achieve this with a view or stored procedure. It sounds to me that it is also not possible to define another fully connected GetPerson object that uses existing DbSet objects.

How can this be implemented? What are the alternatives?

I can think of

a) using a flat object with primitive types for the raw SQL result and then use it to map to the DTO. A side effect of creating the object graph with the original is that creating the value objects validate this data from the DB. So I either have to trust the database or I need to call the validation methods that are public in my value objects manually.

b) forget EF Core and use ADO.NET. The return object is then the ADO.NET record. Considering the permissions the fields of the record will then be mapped to the DTO. This is simple with less overhead, but requires another part of the framework.

Are there any other options? How have you solved returning a combined object considering field permissions?

CodePudding user response：

EF6 core does not support persisting value objects, this is a feature planned in EF7: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/plan#value-objects

The purpose of an ORM such as EF, is to allow programmers to manipulate the RDBMS through objects rather than a text based language such as SQL. This object model is not a business model, but symbols of RDBMS concepts (class = table, row = object, column = property, ...). For trivial applications, you can confuse your models, but you will quickly find yourself limited because a business model has different constraints than a database schema. For larger applications, you write a persistence model, consisting of DPO that match your database structure, and translate that model to other models in the infrastructure layer. Decoupling your domain model from your persistence model allows more flexibility in your application, and re-hydrating your domain objects as a polysemic model, limiting side effects of independent use cases.

An example here would be to have a normalized RDBMS with tables including surrogate keys that are hidden from the domain model in the projection process done by the repository. This allows your database to resolve the complexity of relational mapping, while re-hydrating value objects without identities in the domain layer.

For queries, you should not publish your entities with a GetPerson model. The domain layer's purpose is to protect your application from violating any business rules. When querying your application state, you do not modify the state of the application, and cannot violate any rule. A domain model is only useful for state changing use cases. Therefore, when handling a query, you should directly map your DTO from the DPO. You will save performance and allow your DTO to project directly sort/filter/paging features to the database through libraries such as AutoMapper, as long as the projection is in this library's translation capacity. Also your business rules implementation will not impact / be impacted by large and complex query models, which is the initial purpose of a CQS architecture.

Whether you manipulate your database through an ORM such as EF, or as raw SQL queries manipulated directly at ADO.NET level is an implementation detail of your infrastructure layer. Choice depends on whether you think you can write "better" queries than the ORM, "better" being a subjective matter depending on your project constraints.

Update 1: Regarding mapping to your DTO with Optional<T>, EF core has limited ability to map relational data into a model that does not simply represent the database schema. This is by design and you shouldn't force EF to try to restore data into the DTO directly. Using a single model adds adherence between your API interface and database persistence scheme. You would risk interface breaking changes each time you update the persistence schema, and vice-versa. You should have two different models to decouple presentation from persistence.

Whether you use EF core or ADO.NET to read the database does not change much conceptually. In both cases you read database information into a memory model, then translate that model into a DTO. The difference is whether this in-memory model is based on OOP (EF DPO model) or a key-value table (ADO.NET datarow).

Pros of using EF core, is that it is less prone to SQL injection as queries are generated and values are always escaped for you. Also, the persistence model can be translated into DTO through a mapping library, such as AutoMapper. AutoMapper makes the translation easier and cheaper to write and maintain. Also, it can project some of the translation into the relational model.

If you manage to model your security into the map profile, your database could only select columns required for data exposition in the DTO. In other words, if the user is not allowed to expose DTO.Name, then the database would not include Table.Name into the select statement. This is not always possible to do, though, but it is much easier done this way than writing "clever" queries in SQL.

One downside of EF, however, is that it is slower than ADO.NET.

If you really need to split your query into a two phase transformation (mapping and security), you should put the security layer closer to the database, unless the translation logic requires that data to map accordingly.

CodePudding user response：

This is a bit of subjective and best-practice question, but I'll answer with how I've solved a similar problem - given that I actually understand your question properly.

As long as you've mapped the database model fully using navigation properties, it is possible to generate very complex queries without resorting to raw queries.

    var dto = await context.Persons
        .Where(p => p.Id == id)
        .Select(p => new GetPersonDTO
        {
            Id = p.Id,
            InternallyVerifiedField = !p.UsersWithAccess.Contains(currentUser) ? new Optional<int>(p.InternallyVerifiedField) : new Optional<int>(),
            ExternallyVerifiedField = permissions.Contains(nameof(GetPersonDTO.ExternallyVerifiedField)) ? new Optional<int>(p.ExternallyVerifiedField) : new Optional<int>()
        })
        .SingleOrDefaultAsync();

In this example the InternallyVerifiedField will depend on some query inline, and ExternallyVerifiedField will depend on some external permission object. The benefit of ExternallyVerifiedField is that it might be optimized out from the expression before even reaching the sql server, if the user does not have permission.

If you want to build the dto object from a fully connected object it can still be done in one query similar to

    var dto = await context.Persons
        .Where(p => p.Id == id)
        .Select(p => new
        {
            permissions = new GetPersonDTOPermissions
            {
                FieldA = context.Permissions.Where(...)
            },
            person = p
        })
        .SingleOrDefaultAsync();

But with this solution you need to manually craft the dto from the graph object person given the resulting permissions, as long as you start with context and add a filter using Where the query will be inlined.