How to create a C# Generic Linq Querier?

Question

Consider a database with multiple tables built using Entity Framework code first. Each table contains a different type of object, but I wish to create a single generic query builder class for extensibility's sake. So far as a framework for this class I have a generic class as so intended to act as a wrapper for Linq to SQL:

public class DBQuerier<T>
    where T : class
{
    DbSet<T> relation;

    public DBQuerier(DbSet<T> table)
    {
        relation = table;
    }

    public bool Exists(T toCheck);
    public void Add(T toAdd);
    public T (Get Dictionary<String, Object> fields);
    public bool SubmitChanges();
    public void Update(T toUpdate, Dictionary<String, Object> fields);
    public void Delete(T toDelete);
}

My problem comes at the first hurdle when trying to check to see if a record exists as I cannot convert between generic type T and an object type that I am trying to work with. If I use base Linq:

public bool Exists(T toCheck)
{
    return (from row in relation
        where row.Equals(toCheck)
        select row).Any();
}

A run-time exception occurs as SQL cannot work with anything but primitive types even if I implement IComparable and designate my own Equals that compares a single field. Lambda Expressions seem to come closer, but then I get problems again with SQL not being able to handle more than primitive types even though my understanding was that Expression.Equal forced it to use the class' comparable function:

public bool Exists(T toCheck)
{
    ParameterExpression T1 = Expression.Parameter(typeof(myType), "T1");
    ParameterExpression T2 = Expression.Parameter(typeof(myType), "T2");
    BinaryExpression compare = Expression.Equal(T1, T2);
    Func<T, T, bool> checker =
        Expression.Lambda<Func<T, T, bool>>
            (compare, new ParameterExpression[] { T1, T2 }).Compile();
    return relation.Where(r => checker.Invoke(r, toCheck)).Any();
}

The expression tree was designed in mind so that later I could add a switch statement to build the query according to the type I was trying to look at. My question is: Is there a much simpler / better way to do this (or fix what I've tried so far) as the only other options I can see are to write a class for each table (not as easy to extend) or check each record application side (potentially horrendously slow if you have to transfer the whole database!)? Apologies if I've made so very basic mistakes as I haven't worked with much of this for very long at all, thanks in advance!

Answer 1

Don't compile it. Func<T,bool> means "run this in memory" while Expression<Func<T,bool>> means "keep the logical idea of what this predicate is" which allows frameworks like entity framework to translate that into the query.

As a side note, I don't think that entity framework lets you do a.Equals(b) for querying, so you'll have to do a.Id == b.Id

Answer 2

Entity framework is unlikely to work with your custom linq, it's quite rigid in the commands that it supports. I am going to ramble a bit and it's pseudocode, but I found two solutions that worked for me.

I first used generics approach, where my generic database searcher would accept a Func<T, string> nameProperty to access the name I was going to query. EF has many overloads for accessing sets and properties so I could make this work, by passing in c => c.CatName and using that to access the property in a generic fashion. It was a bit messy though, so:

I later refactored this to use interfaces.

I have a function that performs a text search on any table/column you pass into the method.

I created an interface called INameSearchable which simply contains a property that will be the name property to search. I then extended my entity objects (they are partial classes) to implement INameSearchable. So I have an entity called Cat which has a CatName property. I used the interface to return CatName; as the Name property of the interface.

I can then create a generic Search method where T : INameSearchable and it will expose the Name property that my interface exposed. I then simply use that in my method to perform the query, eg. (Pseudocode from memory!)

doSearch(myContext.Cats);

and in the method

public IEnumerable<T> DoSearch<T>(IDbSet<T> mySet, string catName)
{
   return mySet.Where(c => c.Name == catName);
}

And quite beautifully, it allows me to generically search anything.

I hope this helps.