Reflection in Entity Framework C#
Asked Answered
P

5

7

I am trying to use reflection to make a dynamic select through Entity Framework.

The idea is that the method will get as parameters the column name, the value for each column to search and the order of each column.

For example:

 public anEntity list(String ColumnName, String Value, String Order)
 {
    //
    //...
    items = (from r in context.Products
             where r.GetType().GetProperty(ColumnName). Contains(Value)))
             select r).OrderBy(Order).ToList();
    returns Items
 }

Is it possible? Could you help me?

Palliasse answered 18/7, 2014 at 4:33 Comment(1)
You must build the expression tree.Keating
D
9

I had the same thing! Spent 3 hours and found the solution!

Expression.Lambda and query generation at runtime, simplest "Where" example

It's very good work with EF, and Expression>, and LinqKit.

Change code, for using dynamic types:

private Expression<Func<Goods, bool>> LambdaConstructor (string propertyName, string inputText, Condition condition)
    {

            var item = Expression.Parameter(typeof(Goods), "item");
            var prop = Expression.Property(item, propertyName);
            var propertyInfo = typeof(Goods).GetProperty(propertyName);
            var value = Expression.Constant(Convert.ChangeType(inputText, propertyInfo.PropertyType));
            BinaryExpression equal;
            switch (condition)
            {
                case Condition.eq:
                    equal = Expression.Equal(prop, value);
                    break;
                case Condition.gt:
                    equal = Expression.GreaterThan(prop, value);
                    break;
                case Condition.gte:
                    equal = Expression.GreaterThanOrEqual(prop, value);
                    break;
                case Condition.lt:
                    equal = Expression.LessThan(prop, value);
                    break;
                case Condition.lte:
                    equal = Expression.LessThanOrEqual(prop, value);
                    break;
                default:
                    equal = Expression.Equal(prop, value);
                    break;
            }
            var lambda = Expression.Lambda<Func<Goods, bool>>(equal, item);
            return lambda;
        }

And for OrderBy using: Unable to sort with property name in LINQ OrderBy

Dardanus answered 23/4, 2017 at 20:37 Comment(0)
O
0

Yes and No.

If you execute the items first into IEnumerable<T> then you can make reflection works, otherwise you can't have reflection to become sql query, unless you build an Expression.

But you don't have to reinvent the wheel, somebody has already built it for you, just use Dynamic Linq

public IQueryable<T> List<T>(string columnName, string value, string order)
   where T : class
{
    return context.Set<T>().Where(columnName + " = @0", value).OrderBy(order);
}
Overrefinement answered 18/7, 2014 at 4:46 Comment(0)
F
0

You cant just send "complex queries" that involve such method calls as GetType to EF. EF must provide an Expression tree to the underlying data provider. The docu on the supported features:
Supported Linq methods in Linq to entities. Linq to EF

You can prepare dynamic queries but you will need a little time to prepare this. There are 2 basic approaches to Dynamic Expressions and Queries in LINQ.

a) String Dynamic Lambda

System.Linq.Dynamic can be found at following links
http://msdn.microsoft.com/en-US/vstudio/bb894665.aspx http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx http://www.scottgu.com/blogposts/dynquery/dynamiclinqcsharp.zip

b) Build Expression trees

More powerful but harder to master... Build expressions trees with code found here: http://msdn.microsoft.com/en-us/library/system.linq.expressions.aspx

-ANother tool that is quiet useful in this space is predicate builder.

http://www.albahari.com/nutshell/predicatebuilder.aspx

Fugato answered 18/7, 2014 at 4:52 Comment(0)
B
0

The easiest way is something like this in EF Core :

public anEntity list(String ColumnName, String Value, String Order)
 {
    //
    //...
    items = (from r in context.Products
             where  EF.Property<string>(r,ColumnName).Contains(Value)))
             select r).OrderBy(Order).ToList();
    returns Items
 }

or

 item = context.Products.Where(e=> EF.Property<string>(e,ColumnName).Contains(Value)  ).ToList();
Bashuk answered 19/11, 2023 at 8:33 Comment(0)
C
-1

why not just using raw sql statement

 public anEntity list(String ColumnName, String Value, String Order)
 {
      var items= context.Database.SqlQuery<anEntity>("select * from "+Products +" where "+ ColumnName +"like '%"+ Value +"%' order by "+ Order);

     returns items;
 }

more information here:

http://www.entityframeworktutorial.net/EntityFramework4.3/raw-sql-query-in-entity-framework.aspx

Chiou answered 18/11, 2017 at 21:37 Comment(1)
This code is vulnerable to SQL injection attacks, please check link you provided yourself how to avoid it.Alkanet

© 2022 - 2024 — McMap. All rights reserved.