LINQ to Entities Change Entity at Runtime

user_4259187 0
Viewed 302

I have a question. I have a dbContext that has 200+ classes that represent all the tables in the database. All the tables follow the same format. Is it possible to dynamically change the code at runtime in the following way?

var coffeeList = new ObservableCollection<GenericCoffeeList>();
var query = (from c in ctxCoin.Coffee1
             select new GenericCoffeeList { CoffeeCatalogId = c.Id, Name = c.Name, Type = c.Type })
             .ToList();

foreach (var c in query)
{
    coinList.Add(c);
}

Here is the next entity type that is almost the same. The only change is the entity

var coffeeList = new ObservableCollection<GenericCoffeeList>();

var query = (from c in ctxCoin.Coffee2
             select new GenericCoffeeList { CoffeeCatalogId = c.Id, Name = c.Name, Type = c.Type })
             .ToList();

foreach (var c in query)
{
    coinList.Add(c);
}

Is there a way to change the entity at runtime or will I have to code each entity? Thanks for any direction on this question.

dbcontext dynamic entity linq c#
5 Answers

this should work:

var coffeeRepoList = new List<IQueryable<ICoffee>>()
{
    ctxCoin.Coffee1,
    ctxCoin.Coffee2,
    //...
    ctxCoin.CoffeeN,
};

var coffeeList = (
    from coffeeRepo in coffeeRepoList
    from coffee in coffeeRepo
    select new GenericCoffeeList
    {
        CoffeeCatalogId = coffee.Id,
        Name = coffee.Name,
        Type = coffee.Type
    }).ToList();

public interface ICoffee // make all your entities implement this
{
    int Id { get; set; }
    string Name { get; set; }
    string Type { get; set; }
}

public partial class Coffee1 : ICoffee { }
public partial class Coffee2 : ICoffee { }
//...
public partial class CoffeeN : ICoffee { }

you can generate coffeeRepoList running this query in your database:

"SELECT '    ctxCoin.' + name + ',' FROM sys.tables ORDER BY name"

it will generate this:

    ctxCoin.Coffee1,
    ctxCoin.Coffee2,
    //...
    ctxCoin.CoffeeN,

or using reflection:

public static List<IQueryable<ICoffee>> GetCoffeeDbSets(DbContext ctxCoin)
{
    return (
        from property in ctxCoin.GetType().GetProperties()
        where typeof(IQueryable<ICoffee>).IsAssignableFrom(property.PropertyType)
        select (IQueryable<ICoffee>)property.GetValue(ctxCoin)).ToList();
}

to make all your entites implement the interface you can use this query:

"SELECT 'public partial class ' + name + ' : ICoffee { }' FROM sys.tables ORDER BY name"

it will generate this:

public partial class Coffee1 : ICoffee { }
public partial class Coffee2 : ICoffee { }
//...
public partial class CoffeeN : ICoffee { }
user_33239410

This should work for EF6 but I was testing on EFCore.

I've done something somewhat similar to this when I needed to modify all the DbSets that implement a specific interface.

You have two options for your situation. Since all of your model types don't have a common base type (other than object), you could refactor those generated classes to extract the common properties to a base class (the Id, Name, and Type properties).

Here I'm assuming your Coffee1 and Coffee2 tables just have the entity type Coffee1 and Coffee2 respectively.

Option 1:

Refactor classes and extract those common properties

public partial class Coffee1 : BaseCoffee {}
public partial class Coffee2 : BaseCoffee {}
public abstract class BaseCoffee
{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Type { get; set; }
}

then your query would look like this

var dbSets = ctxCoin.GetType().GetProperties()
    .Where(p => p.PropertyType.IsAssignableTo(typeof(IQueryable<BaseCoffee>)))
    .Select(set => set.GetValue(ctxCoin))
    .Cast<IQueryable<BaseCoffee>>();

var coffeeList = new ObservableCollection<GenericCoffeeList>();

foreach (var coffee in dbSets)
{
    var query = coffee.Select(c => new GenericCoffeeList { CoffeeCatalogId = c.Id, Name = c.Name, Type = c.Type });

    foreach (var c in query)
    {
        coffeeList.Add(c);
    }
}

This option is much more type-safe and less error-prone.

Option 2:

Use reflection on IQueryable<object>

Keep your models the same and use this:

var dbSets = ctxCoin.GetType().GetProperties()
    .Where(p => p.PropertyType.IsAssignableTo(typeof(IQueryable<object>)))
    .Select(set => set.GetValue(ctxCoin))
    .Cast<IQueryable<object>>();

var coffeeList = new ObservableCollection<GenericCoffeeList>();

foreach (var queryableObject in dbSets)
{
    var query = queryableObject.Select(GenerateGenericCoffee);

    foreach (var c in query)
    {
        coffeeList.Add(c);
    }
}

The GenerateGenericCoffee helper method

GenericCoffeeList GenerateGenericCoffee(object coffeeObject)
{
    var objType = coffeeObject.GetType();

    return new GenericCoffeeList
    {
        CoffeeCatalogId = GetProperty<int>("Id"),
        Name = GetProperty<string>("Name"),
        Type = GetProperty<string>("Type"),
    };

    T GetProperty<T>(string name)
    {
        return (T)objType.GetProperty(name).GetValue(coffeeObject);
    }
}

If all of your models contain Id, Name, and Type, you will be fine otherwise you'll need to check those properties exist first before making the GenericCoffeeList item.

user_65944670

I think, you create genetic class for the query. When you change Object(TEntity), query executed for the Object(TEntity).

public class QueryRepo<TEntity> where TEntity : class
{
    private readonly ctxCoin;
    public QueryRepo(){
        ctxCoin = new CtxCoin();
    }

    public IEnumerable<GenericCoffeeList> GetCoffeeList()
    {
        var entity = ctxCoin.Set<TEntity>();
        return (from c in entity
            select new GenericCoffeeList
            {
                CoffeeCatalogId = c.Id,
                Name = c.Name,
                Type = c.Type
            }).ToList();
    }
}
user_129580280

I personally would try using a generic method with a labda which is used to convert the different Coffee types into GenericCoffeeList.

public IEnumerable<GenericCoffeeList> GetCoffeeList<TCoffee>(Func<TCoffee, GenericCoffeeList> toGenericCoffeeList)
{
    return ctxCoin.Set<TCoffee>()
        .Select(coffee => toGenericCoffeeList(coffee)
        .ToList();
}

Doing it this way reduces the amount of code needed and the only thing that needs to be duplciated is the function that is passed as toGenericCoffeeList but also does not require refactoring 200+ classes to implement an interface.

This approach can be adapted depending on what you need to do (I'm not exactly sure what your method is supposed to do because coffeeList is never used and coinList is never declared)

user_152597180

You can map one Type to another by mapping the fields like this:

public static Expression<Func<T, R>> MapFields<T, R>(IDictionary<string, string> fieldNamesMapping) 
            where R: new()
{
    var parameter = Expression.Parameter(typeof(T), "o");  
             
    return Expression.Lambda<Func<T, R>>(
        Expression.MemberInit(
            Expression.New(typeof(R)), 
            GetMemberBindings<T,R>(fieldNamesMapping, parameter)), 
        parameter);
}
      

private static IEnumerable<MemberBinding> GetMemberBindings<T,R>(IDictionary<string, string> fieldNamesMapping,
    ParameterExpression parameter) 
        => fieldNamesMapping
            .Select(o => (MemberBinding)Expression.Bind(
                typeof(R).GetProperty(o.Value) ?? throw new InvalidOperationException(), 
                Expression.Property(parameter, typeof(T).GetProperty(o.Key) ?? throw new InvalidOperationException())));

This code assumes that the types have properties with similar types but different names. Thus the Dictionary with the corresponding filednames.

If the fields happen to have the same names you could of course infer the properties using reflection.

The usage is like so:

var toBeMapped = new List<FooA> {
                new FooA {A = 1, B = 2, C = 3},
                new FooA {A = 4, B = 5, C = 6},
                new FooA {A = 7, B = 8, C = 9},
                new FooA {A = 10, B = 11, C = 12}
            };

            var result = toBeMapped.AsQueryable().Select(
                MemberBindingExpressions.MapFields<FooA, FooB>(
                    new Dictionary<string, string> {["A"] = "A", ["B"] = "E"})).ToList();

            result[0].Should().Be(new FooB {A = 1, E = 2});
            result[3].Should().Be(new FooB { A = 10, E = 11 });
user_121198250
Related