Reflection Linq-Entity “IN()” function for “contai

Question

I'm building my own reflection functions for certain types of searches.

The problem is that I want to search a group of IDs within a list of IDs and filter my search/select query to have only these specific objects.

This is the same as using "IN()" in Linq-Entity framework. But I can't use a.objid.

return query.Where(a => ObjectsToFind.Contains(a.objid));

However, "a.objid" is causing errors because I use T Template.

So a is "T a" instead of "MyTable a" so that I can call it's "objid" property.

I know there is a way to do this with parameter expressions. However, I can't figure it out.

Here's what I tried to replace that above line with:

public static IQueryable<T> WhereFunctionContains<T>(this IQueryable<T> query, string contains)
{    
    var ObjectsToFind = new List<int>(); // I want to search IN() this function that gets filled in here.
    ObjectsToFind = FillObjectsToFind(); // just the object id integers I want to filter
    var parameter = Expression.Parameter(typeof(T), "type");
    var propertyExpression = Expression.Property(parameter, "objid"); // I look for this
    MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(int) }); // int type
    var vars = Expression.Variable(List<int>,); // I can't use "constant", but what do I use here?
    var containsExpression = Expression.Call(propertyExpression, method, vars);
    return query.Where(Expression.Lambda<Func<T, bool>>(containsExpression, parameter));
}

Replacing "T" with the actual table entity, causes a lot of problems, so I decided to keep the T.

---

else if (s.ST == "function")
{ // search func
    myTable a;
    DC2 = MyUtility.WhereFunctionContains(DC2, a => a.objid, s.S);
    // s.S is my search query string.
    // s.ST means I'm searching for functions (from another associated table)
    // I don't understand how a.objid gets called, I wanted to use Template/Reflections.
}

Here's how I call Zev's function.

public static IQueryable<T> WhereFunctionContains<T>(this IQueryable<T> query, Expression<Func<T, int>> converter, string contains)
{
    FunctionsClass fc = new FunctionsClass();
    var ObjectsToFind = new List<int>();
    ObjectsToFind = fc.SearchContainFunction(contains); // I grabbed my list of IDs to search
    return query.Where(t => ObjectsToFind.Contains(converter(t)));
}

Answer 1

If I understand you correctly, you have a number of queries on different types:

IQueryable<Person> personsQry = ...
IQueryable<Sale>   salesQry   = ...
IQueryable<Order>  ordersQry  = ...

and you have a method that generates a List<int>, called FillObjectsToFind:

public List<int> FillObjectsToFind()
{
    //code here
}

You want to be able to limit each of the above queries to only have the id in the returned list. As an added bonus, this should be an extension method, so you can call it like this:

var personsFiltered = personsQry.WhereFunctionContains(...);
var salesFiltered   = salesQry.WhereFunctionContains(...);
var ordersFiltered  = ordersQry.WhereFunctionContains(...);

The problem is each query is of a separate type, and you would prefer to write one method that covers all of them.

---

The first part of the solution is to define a generic method:

public static IQueryable<T> WhereFunctionContains<T>(this IQueryable<T> query)
{
    //code here
}

but there is still a problem: the only type we know of is type T which is not a real type, but a placeholder for actual types. Since these types could be anything -- string, System.Random, SqlConnection, an ASP.NET Label, a WPF TextBlock -- there is no way of knowing how to compare each object to a List of ints.

---

The most straightforward solution is to define an interface:

interface IHasObjID
{
    int ObjID {get;set;}
}

Then each type should implement this interface:

class Person : IHasObjID
{
    int objID;
    int ObjID
    {
        get {return objID;}
        set {objID = value;}
    }
}

//implement sales and orders similarly

Once that is done, you can define a constraint on the types allowed by the method. Now that the type definitely has an ObjID property, we can query on that:

public static IQueryable<T> WhereFunctionContains<T>(this IQueryable<T> query) where T : IHasObjID
{
    var intsToFind = FillObjectsToFind();
    return query.Where(t => intsToFind.Contains(t.ObjID));
}

This is what King King was telling you in this comment.

---

I propose that when calling this function, you also pass in how to get at the integer from the type:

public static IQueryable<T> WhereFunctionContains<T>(this IQueryable<T> query, Expression<Func<T,int>> converter)
{    
    var intsToFind = FillObjectsToFind();
    return query.Where(t => intsToFind.Contains(converter(t)));
}

However, I haven't tested this code, and since we are working with Entity Framework and expressions I suspect there is still an issue: An expression cannot be "called" within an expression. I wanted to suggest the above, but it doesn't compile with the following error -- 'converter' is a 'variable' but used like a 'method'.

After all that, the solution is straightforward, using Join:

public static IQueryable<T> WhereFunctionContains<T>(this IQueryable<T> query, Expression<Func<T,int>> converter) 
{
    var ints = new List<int>() { 1, 2, 3, 4, 5 };
    return query.Join(ints,converter,i=>i,(t,i) => t);
}

to be called like this:

var filteredPersons = query.WhereFunctionContains(p => p.PersonID);

---

If this is only used with a single type MyTable:

public static IQueryable<MyTable> WhereFunctionContains(this IQueryable<MyTable> query)
{
    var ints = new List<int>() { 1, 2, 3, 4, 5 };
    return query.Join(ints, mt=>mt.objid, i=>i, (t,i) => t);
}

---

Some links from the C# Programming Guide (in order of the answer):

• Extension methods
• Generics
  • Generic methods
  • Generic constraints
• Interfaces
• LINQ inner joins

Also, see here for a nice overview of LINQ operators, such as Select, Where, Count and ToList.