In a .NET5 public web API project, I have many endpoints with many data models that include identifiers used as unique reference to the single entity itself and also as references to other related entities. I'm using SQL Server database, .NET5 EntityFramework and MediatR libraries.

Those identifiers are not the [Id] fields of the entities on the database tables, instead they're "speaking IDs" in form of strings, like: "REG0001A", "T00001A1A", etc. and each entity has it's own format.

Whenever I have to manipulate those data models I often need to get the corresponding Id value by searching on the database using the Identifier or even the opposite.

Since I need to do those kind of operations on many different entities and in many different positions on my code, I was thinking about creating 2 generic MediatR Queries that, given the entity model (or type or name) and the [Id] field value, gives back the [Identifier] field value and vice-versa. Something like this:

// Get Identifiers from IDs
string registryIdentifier = await Mediator.Send(new GetEntityIdentifierByIdQuery(Registry, registryId), cancellationToken);
string transactionIdentifier = await Mediator.Send(new GetEntityIdentifierByIdQuery(Transaction, transactionId), cancellationToken);

// Get IDs from Identifiers
int registryId = await Mediator.Send(new GetEntityIdByIdentifierQuery(Registry, registryIdentifier), cancellationToken);
int transactionId = await Mediator.Send(new GetEntityIdByIdentifierQuery(Transaction, transactionIdentifier), cancellationToken);

// ...etc. for other entities...

It seems easy but there's quiet a few points to keep in mind and some difficulties that I met:

1. For what I know, creating a MediatR generic Handler can lead to DI problems, depending on the specific DI library used. I'm using the .NET5 built-in DI container.
2. Of course, each entity has its own names for both the [Id] field and [Identifier] field, so by knowing the entity type (given by the entity class parameter in my method samples above) I'd like to use reflection to automatically get both of them. This would be the "top", but I'm also open to find a compromise solution, using Linq.Expression parameters for getting in advance the Id property (and/or Identifier property) of the specific entity, like:

await Mediator.Send(new GetEntityIdentifierByIdQuery(Registry, e => e.RegistryId, registryId), cancellationToken)

3. The database make use of table schemas, so not all entities are on the same schemas (i.e.: reg.Registers and trn.Transactions).
4. Applying reflection to EntityFramework entities in general seems not to be a so straightforward thing from what I read on some QA, mostly in my case, where EF entities are configured using Fluent Api. The information I need to get are: table schema, table name, table key field name, table identifier field name.
5. Once I've all that I need, I'm not sure if it possible to use the Fluent Api to write the code for querying the database (like Context.<Entity>.Where(...)) or if I need to use a plain SQL command manually composed with all the parameters. Anyway this is the last point as it would not be really a problem, it's more a matter of coolness!

About reflection on EntityFramework I didn't find much and those QA seems not to fit my case:

• Reflection in Entity Framework C#
• EntityFramework 6 How to get identity-field with reflection?

This is just a draft of the MediatR Query (of course it doesn't compile):

public class GetEntityIdByIdentifierQuery<TEntity> : IRequest<int?> where TEntity : class
{
    internal ValidationContext Entity { get; }
    internal Expression<Func<TEntity, string>> IdProperty { get; }
    internal string Identifier { get; }

    public GetEntityIdByIdentifierQuery(ValidationContext entity, Expression<Func<TEntity, string>> idProperty, string identifier)
    {
        Entity = entity;
        IdProperty = idProperty;
        Identifier = identifier;
    }
}

internal class GetEntityIdByIdentifierQueryHandler<TEntity> : ServiceBaseHandler, IRequestHandler<GetEntityIdByIdentifierQuery<TEntity>, int?> where TEntity : class
{
    public GetEntityIdByIdentifierQueryHandler(RENTRIContext context, IMapper mapper)
        : base(context, mapper)
    { }

    public async Task<int?> Handle(GetEntityIdByIdentifierQuery<TEntity> request, CancellationToken cancellationToken)
    {
        int? id = null;

        using var connection = Context.Database.GetDbConnection();
        if (connection.State == System.Data.ConnectionState.Closed)
            connection.Open();

        using var command = connection.CreateCommand();
        command.CommandText = $"SELECT {(request.IdProperty.Body as MemberExpression).Member.Name} FROM <SCHEMA?>.{typeof(TEntity).Name}";
        
        using var reader = command.ExecuteReader();
        while (reader.Read())
        {
            // How to get the key field name?
            id = reader.GetFieldValue<TKey?>(1);
        }

        return id;
    }
}