linq expression tree many to many IQueryable extension

user_993310 0
Viewed 112

Im currently working on an asp.net web api 2 application. Please bare in mind my experience is in php,js,css and so on and not c#.

Im hoping someone can help me with my generic IQueryable Extension that uses the Linq Expressions tree.

I have 3 tables (Entity Framework).

Trades

Id,Name

ProductTrades (linking table)

Id,Trade_Id,Product_Id

Products

Id,Code

i need to return all trades that has at least one product code from a list of product codes passed as a variable

I have this working fine with the following

list<string> tocheck = new list<string>("productcode1","productcode2");
var trades = db.Trades.SqlQuery(
               "Select * from Trades T " +
               "left join ProductTrades PT on T.Id = PT.Trade_Id " +
               "left join Products P on PT.Product_Id = P.Id " +
               "where P.Code in (@prod)", new SqlParameter("@prod", String.Join(",", tocheck))).ToList();

However i would like to use Linq Expression tree to achieve this to make it generic

See below for my attempt ( i have put this together using multiple sources while trying to learn it)

public static IQueryable<T> FilterWhereItemIn<T>(this IQueryable<T> queryable, string propertyOrFieldName, List<string> values)
        {
            var elementType = typeof(T);
            var parameterExpression = Expression.Parameter(elementType);
            var propertyOrFieldExpression = propertyOrFieldName.Split('.').Aggregate((Expression)parameterExpression, Expression.PropertyOrField);
            var method = typeof(List<string>).GetMethod("Contains", new Type[] { typeof(string) });
            var someValue = Expression.Constant(values, typeof(List<string>));
            var containsExpression = Expression.Call(propertyOrFieldExpression, method, someValue);
            var selector = Expression.Lambda<Func<T, bool>>(containsExpression, parameterExpression);
            return queryable.Where(selector);
        }

and i call this with the following (IQueryable queryable is the type Trade)

list<string> tocheck = new list<string>("productcode1","productcode2");
queryable.FilterWhereItemIn("Products.Code", tocheck );

However the i get the error message that "Code" does not exist. i assume this is because Products is a collection. does anyone know how to achieve this.

(ps sorry this post was a little rushed)

asp.net-web-api2 linq-expressions iqueryable entity-framework
3 Answers

In case, if you ever wanted to build that custom WHERE clause, mentioned in your original question above. Here's a working example

public static class WhereGenerator
    {
        // Where Generator For Contains
        public static IQueryable<T> FilterWhereItemIn<T>(this IQueryable<T> queryable, string propertyOrFieldName, List<string> values)
        {
            var elementType = typeof(T);
            var parameterExpression = Expression.Parameter(elementType);
            var propertyOrFieldExpression = propertyOrFieldName.Split('.').Aggregate((Expression)parameterExpression, Expression.PropertyOrField);
            var method = typeof(List<string>).GetMethod("Contains", new Type[] { typeof(string) });
            var someValue = Expression.Constant(values, typeof(List<string>));
            var containsExpression = Expression.Call(someValue, method, propertyOrFieldExpression);
            var selector = Expression.Lambda<Func<T, bool>>(containsExpression, parameterExpression);
            return queryable.Where(selector);
        }
    }

        public class Products
        {
            public int Id { get; set; }
            public string Code { get; set; }
        }
        public class Trades
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }
        public class ProductTrades
        {
            public int Id { get; set; }
            public Trades Trade { get; set; }
            public Products Product { get; set; }
        }

        static void Main(string[] args)
        {
            List<ProductTrades> lpt2 = new List<ProductTrades> { new ProductTrades { Id = 1, 
                                                                                     Trade = new Trades { Id = 13, Name = "trade2" }, 
                                                                                     Product = new Products { Code = "productcode1", Id = 1 } },
                                                                new ProductTrades { Id = 11,
                                                                                     Trade = new Trades { Id = 13, Name = "trade4" },
                                                                                     Product = new Products { Code = "productcode2", Id = 2 } },
                                                                new ProductTrades { Id = 1,
                                                                                     Trade = new Trades { Id = 13, Name = "trade2" },
                                                                                     Product = new Products { Code = "productcode5", Id = 1 } }
                                                              };

            List<string> tocheck = new List<string>() { "productcode1", "productcode2" };
            var r1 = lpt2.AsQueryable().FilterWhereItemIn("Product.Code", tocheck);
            var r2 = r1.ToList();
}
Admin1

You have the parameters in the Expression.Call line mixed up. You should be calling the Contains method against the list, not the property. Use this instead:

var containsExpression = Expression.Call(someValue, method, propertyOrFieldExpression);
user_16630010

After discussing with DavidG (thank you) it looks like i took the wrong path when trying to achieve my goal.

there was no need for me to make an extension and i could achieve the results i needed using a simple one line linq query.

var trades = new List<Trade>();
trades = db.Trades.Where(t => t.Products.Any(p => getRequest.products.Contains(p.Code))).ToList();

getRequest is just a simple class that has a public list<string> products { get; set; } getter and setter

user_9933100
Related