Entity Framework DbSet dynamic query

Question

I am creating a DbSet from a Type that is passed in and I need to query the database for a dynamic field and value. Using generics I would use the where function with a lambda expression. Can this be achieved from a standard dbSet created as follows ?

DbSet table = dataContext.Set(EntityType);
PropertyInfo propertyInfo = EntityType.GetProperty(PropertyName);

// Expression: "entity"
ParameterExpression parameter = Expression.Parameter(EntityType, "entity");

// Expression: "entity.PropertyName"
MemberExpression propertyValue = Expression.MakeMemberAccess(parameter, propertyInfo);

// Expression: "value"
object convertedValue = Convert.ChangeType(value, propertyInfo.PropertyType);
ConstantExpression rhs = Expression.Constant(convertedValue);

// Expression: "entity.PropertyName == value"
BinaryExpression equal = Expression.Equal(propertyValue, rhs);

// Expression: "entity => entity.PropertyName == value"
LambdaExpression lambda = Expression.Lambda(equal, parameter);

Now need to query the table to get data.

Not sure if this works with `EF`. But you could try the dynamic linq lib http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx — mxmissile, Apr 28 '14 at 13:37
What do you want to achive with this? Maybe there is a better solution... — Christoph Fink, Apr 28 '14 at 13:46
@chrfin - This is a part of validation Attribute, I am passing the attribute name and entity type and need to query — user3581461, Apr 28 '14 at 14:00
Can you show a demo entity and call? Because this sounds like it could be solved differently... — Christoph Fink, Apr 28 '14 at 14:06
Can you give us what the parameters you are getting? I assume a type and is it just the name of the property and an object value or is it an Expression> — CharlesNRice, Apr 28 '14 at 15:06
@chrfin - if you check this "http://stackoverflow.com/questions/2691444/how-can-i-create-a-generic-uniquevalidationattribute-in-c-sharp-and-dataannotati" where someone tried to create UniqueRecordAttribute (same as me). Check the answer given by 'sabbour' because I am using the same validation attribute for my gridview but there is a bit problem with that. This does work perfectly if you try to add a new record but this validation does not work if you try to edit a record and rename it to an existing one, and validation gets failed. — user3581461, Apr 29 '14 at 09:06
Now, in order to make this validation work for update/edit, I was trying this approach (as in my question). Suggestion would be welcomed, if you think this validation can be achieved through any other approach. — user3581461, Apr 29 '14 at 09:07
Can you show one attribute definition and your attribute implementation how you call/have it at the moment? I am prettys shure this can be solved using generics and strongly typed lambdas... — Christoph Fink, Apr 29 '14 at 09:17
@chrfin [Display(Name = "User Login")] [Required()] [UniqueRecordValidation(typeof (AssetTrackingEntities), typeof(ATUser_Account), "User_Login", "UN_AT_User", ErrorMessage = "User Login Already Exists.")] public string User_Login { get; set; } — user3581461, Apr 29 '14 at 10:16
trying to do the same as the original question... any progress. — Seabizkit, Feb 08 '16 at 11:45

score 1 · Answer 1 · answered Apr 29 '14 at 19:48

There is a nuget package called 'System.Linq.Dynamic'.

http://dynamiclinq.codeplex.com/

This package allows you to form statements against DbSets using strings, like this:

myContext.MyDbSet.Where("PropertyName == @0", "aValue");

It is possible to do this, with Expressions as you suggest in your question, but this library takes out all the heavy lifting.

I've used this with great success in one of my previous projects.

Christoph Fink · Answer 2 · 2014-04-30T08:52:43.450

You can try something like the following:

public class UniqueRecordValidationAttribute : ValidationAttribute
{
    public IValidationAttribute DynamicInstance { get; private set; }

    public UniqueRecordValidationAttribute(Type type, 
        params object[] arguments )
    {
        DynamicInstance = 
            Activator.CreateInstance(type, arguments) as IValidationAttribute;
    }

    public override bool IsValid(object value)
    {
        return DynamicInstance.IsValid(value);
    }
}

public class UniqueRecordValidator<C, E, P> : IValidationAttribute
    where C : DataContext, new() where E : class
{
    Func<E, P, bool> Check { get; set; }

    public UniqueRecordValidator(Func<E, P, bool> check)
    {
        Check = check;
    }

    public bool IsValid(object value)
    {
        DataContext dataContext = new C();
        Table<E> table = dataContext.GetTable<E>();

        return table.Count(i => Check(i as E, (P)value)) == 0;
    }
}

public interface IValidationAttribute
{
    bool IsValid(object value);
}

and

[UniqueRecordValidation(
    typeof(UniqueRecordValidator<AssetTrackingEntities, ATUser_Account, string>), 
    new Func<ATUser_Account, string, bool>((i, p) =>  i.User_Login == p))]
public string User_Name { get; set; }

This would be a completely strongly typed solution, but I am not shure the Func<E, P, bool> inside the Count is supported by EF as I could not test that here at the moment. But for LINQ to objects this code does work.

If this does not work you can at least improve it with generics and dynamic LINQ to the following:

public class UniqueRecordValidator<C, E> : IValidationAttribute
    where C : DataContext, new() where E : class
{
    string PropertyName { get; set; }

    public UniqueRecordValidator(string propertyName)
    {
        PropertyName = propertyName;
    }

    public bool IsValid(object value)
    {
        DataContext dataContext = new C();
        Table<E> table = dataContext.GetTable<E>();

        return table.Count(PropertyName + " = @0", value) == 0;
    }
}

[UniqueRecordValidation(
    typeof(UniqueRecordValidator<AssetTrackingEntities, ATUser_Account>)
    "User_Login")] 
public string User_Login { get; set; }

Thanks for your suggestions, but are you sure that you can use generics with attributes?? Please check this: http://stackoverflow.com/questions/9788593/workaround-for-c-sharp-generic-attribute-limitation — user3581461, Apr 30 '14 at 07:50
Ok, I did not know that. You can use something like here: http://whathecode.wordpress.com/2013/10/19/generic-attributes-in-c/ - use the attribute just as a wrapper around the actual generic check. — Christoph Fink, Apr 30 '14 at 08:40

Entity Framework DbSet dynamic query

2 Answers2