Welcome toVigges Developer Community-Open, Learning,Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
852 views
in Technique[技术] by (71.8m points)

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

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?


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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);
}

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to Vigges Developer Community for programmer and developer-Open, Learning and Share
...