none
Get entity by properties entity framework 4.1 RRS feed

  • Question

  • I am wondering how to get an entity by properties

    I don’t want to use SqlQuery method where you pass actual SQL query.  Find() work with PK values only.
    I want to be able to have a method in my repository where I can pass in a dictionary of property with respective value to filter my entities.

    public IList<TEntity> FindByProperties(IDictionary<string, object> propertyValues)

    NHibernate provides criteria query api, is there anything similar in EF?

    Please suggest. Thanks


    -= JL =-

    Tuesday, October 16, 2012 1:30 AM

All replies

  • Interesting.  Would you want all criteria to match, just one, or ?

    Linq-to-Entities provides a huge amount of flexibility for querying, but I don't think there's a built in, narrowly scoped query mechanism like that.  I may be wrong.

    With Linq-to-Entites, you can filter/query/sort/etc. to your hearts content.

    http://msdn.microsoft.com/en-us/library/bb386964.aspx


    WNC

    Tuesday, October 16, 2012 9:57 AM
  • Hi WineNCheese

    I want all the criteria's to match. I don't think I can use query expression as shown in the samples, since I am trying to write a generic implementation and I wouldn't know my entity type and criteria's before hand.


    -= JL =-

    Tuesday, October 16, 2012 11:06 AM
  • Hi Job Lot,

    Welcome to the MSDN forum.

    I think you can use expression trees. Combine all the sub expression trees (property value conditions) to create an expression, which is the expression in Where clause. There is a sample about Expression Tress you can refer to: http://msdn.microsoft.com/en-us/library/bb882637.aspx

    Best Regards,


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, October 17, 2012 5:35 AM
  • Wow, that was helpful. Thanks Alexander.

    What I am trying to figure out is how to dynamically construct the conditional operations. The sample you mentioned above performs predefine set of operations.

    For instance, if I have method in my generic repository FindByProperties which take a dictionary of properties and corresponding values to evaluate

    public IList<TEntity> FindByProperties(IDictionary<string, object> propertyValues)
    <”Property1”, “value 1”>
    <”Property2”, value2>

    I might want to evaluate first expression for equality, second for greater than on second and then Or operation on those two expressions, or any combination for that matter.

    Please suggest. Thanks



    -= JL =-

    Friday, October 19, 2012 12:53 AM
  • Hi Job Lot,

    I wrote a simple sample which works on my side and you can refer to. Please check it:

        class Program
        {
            static void Main(string[] args)
            {
                var dict = new Dictionary<String, dynamic>();
                dict.Add("Name", "Dominating");
                dict.Add("ID", 15);
                var list=FindByProperties<CompanyProduct>(dict);
    
                Console.ReadLine();
            }
            public static IList<TEntity> FindByProperties<TEntity>(IDictionary<String, dynamic> propertyValues) where TEntity : class
            {
                IList<TEntity> results;
                using (var context = new Entities())
                {
                    IQueryable<TEntity> queryableData = context.CreateObjectSet<TEntity>().AsQueryable<TEntity>();
                    Expression e1 = null;
                    Expression e2 = null;
                    ParameterExpression pe = Expression.Parameter(typeof(TEntity), "p");
    
                    for (Int32 i = 0; i < propertyValues.Count; i++)
                    {
                        if (i == 0)
                        {
                            Expression left = Expression.Property(pe, typeof(TEntity).GetProperty(propertyValues.Keys.ElementAt(i)));
                            Expression right = Expression.Constant(propertyValues.Values.ElementAt(i));
                            e1 = Expression.Equal(left, right);
                        }
                        else
                        {
                            Expression left = Expression.Property(pe, typeof(TEntity).GetProperty(propertyValues.Keys.ElementAt(i)));
                            Expression right = Expression.Constant(propertyValues.Values.ElementAt(i));
                            e2 = Expression.GreaterThan(left, right);
                        }
                    }
    
                    Expression predicateBody = Expression.OrElse(e1, e2);
    
                    MethodCallExpression whereCallExpression = Expression.Call(typeof(Queryable), "Where", new Type[] { queryableData.ElementType }, queryableData.Expression, Expression.Lambda<Func<TEntity, Boolean>>(predicateBody, new ParameterExpression[] { pe }));
                    results = queryableData.Provider.CreateQuery<TEntity>(whereCallExpression).ToList<TEntity>();
                }
                return results;
            }
        }
    

    Have a nice day.


    Alexander Sun [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Alexander Sun Tuesday, October 23, 2012 9:02 AM
    • Unmarked as answer by Job Lot Friday, October 26, 2012 4:37 AM
    Friday, October 19, 2012 8:51 AM
  • I believe the code you provided constructs following expression

    Where(Name=”Dominating” || ID > 15)

    which is somewhat similar to the sample on msdn you provided earlier. The expression contains predetermined operations.

    what if I had to evaluate

    Where(ID == 1 || ID > 15)  or may be
    Where(ID > 15 && Name=”Dominatin”)

    I can’t use the same FindByProperty method.

    NHibernate provides Criterias to dynamically construct your expressions, something like this

    return session.CreateCriteria<Trade>()
        .CreateAlias("AccountFrom", "af")
        .CreateAlias("AccountTo", "at")
        .Add(Restrictions.Or(
            Restrictions.Eq("af.Company.CompanyId", companyId),
            Restrictions.Eq("at.Company.CompanyId", companyId)))
        .List<Trade>();

    I want to be able to create the expression and simply pass it to my repository method to evaluate. Can that be done with EF?

    Thanks


    -= JL =-

    Friday, October 26, 2012 4:46 AM
  • I believe the code you provided constructs following expression

    Where(Name=”Dominating” || ID > 15)

    which is somewhat similar to the sample on msdn you provided earlier. The expression contains predetermined operations.

    what if I had to evaluate

    Where(ID == 1 || ID > 15)  or may be 
    Where(ID > 15 && Name=”Dominatin”)

    I can’t use the same FindByProperty method.

    NHibernate provides Criterias to dynamically construct your expressions, something like this

    return session.CreateCriteria<Trade>()
        .CreateAlias("AccountFrom", "af")
        .CreateAlias("AccountTo", "at")
        .Add(Restrictions.Or(
            Restrictions.Eq("af.Company.CompanyId", companyId), 
            Restrictions.Eq("at.Company.CompanyId", companyId)))
        .List<Trade>();

    I want to be able to create the expression and simply pass it to my repository method to evaluate. Can that be done with EF?

    Thanks

    -= JL =-

    Tuesday, October 30, 2012 10:44 AM