c# - Is there a way to query properties, by name, of a DbSet, by name?

Question

We have a large number of entities which contain user ID properties, I've created a custom attribute UserIdAttribute to annotate those models. For example:

public class MyEntity
{
  [UserId]
  public Guid Owner { get; set; }
}

We'll have a background service whose intention is to find user ID properties across all entities and perform lookups on them to populate another table. We'd like to find these values dynamically since the state of our models/application are undergoing constant change. I know I can achieve what I want using Microsoft.Data.SqlClient as below:

var allUserIds = new List<Guid>();

foreach (var entityType in dbContext.Model.GetEntityTypes())
{
  foreach (var property in entityType.GetProperties())
  {
    foreach var attribute in property.PropertyInfo.GetCustomAttributes())
    {
      if (attribute is MyCustomAttribute)
      {
        //
        // do this using EF & reflection
        //
        using (var connection = new SqlConnection("my_conn_string"))
        {
          try
          {
            var tableName = entityType.GetTableName();
            var command = new SqlCommand(
              $"select {property.Name} from {tableName}",
              conn);
            conn.Open();
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
              Console.WriteLine($"{entityType.ClrType.Name}.{property.Name}: {reader[property.Name]}");
            }
          }
          finally
          {
            conn.Close();
          }
        }
      }
    }
  }
}

PopulateUserInfoTable(allUserIds);

We would prefer use EF here, the problem is that I can't see any way to query DbSets when I only have the string representation of the DbSet name. Is this even achievable?

question from:https://stackoverflow.com/questions/65835003/is-there-a-way-to-query-properties-by-name-of-a-dbset-by-name

Answer 1

Actually it is easy without any non wanted roundtrips to database. LINQ is very cool.

var valuesQuery = CreateValuesQuery<Guid>(ctx, (e, p) => 
   p.PropertyInfo.GetCustomAttributes().Any(a => a is MyCustomAttribute));

if (valuesQuery != null)
{
   var userInfoQuery = 
      from ui in dbContext.UserInfoTable
      join v in valuesQuery on ui.UserId equals v
      select ui;

   var infoResut = userInfoQuery.ToList();
}

And implementation:

public static IQueryable<T> CreateValuesQuery<T>(DbContext ctx, Func<IEntityType, IProperty, bool> filter)
{
    Expression query = null;
    IQueryProvider provider = null;
    var ctxConst = Expression.Constant(ctx);
    foreach (var entityType in ctx.Model.GetEntityTypes())
    {
        ParameterExpression entityParam = null;
        foreach (var property in entityType.GetProperties().Where(p => p.ClrType == typeof(T) && filter(entityType, p)))
        {
            entityParam ??= Expression.Parameter(entityType.ClrType, "e");
            var setQuery = Expression.Call(ctxConst, nameof(DbContext.Set), new[] {entityType.ClrType});
            provider ??= ((IQueryable) Expression.Lambda(setQuery).Compile().DynamicInvoke()).Provider;
            var propertyLambda = Expression.Lambda(Expression.MakeMemberAccess(entityParam, property.PropertyInfo), entityParam);
            var projection = Expression.Call(typeof(Queryable), nameof(Queryable.Select), new[]
                {
                    entityType.ClrType,
                    typeof(T)
                }, 
                setQuery, 
                propertyLambda
            );
            if (query != null)
                query = Expression.Call(typeof(Queryable), nameof(Queryable.Union), new []{typeof(T)}, query, projection);
            else
                query = projection;
        }
    }

    if (query == null)
        return null;

    return provider.CreateQuery<T>(query);
}