Pulling the WHERE clause out of LINQ to SQL

Question

I'm working with a client who wants to mix LINQ to SQL with their in-house DAL. Ultimately they want to be able to query their layer using typical LINQ syntax. The point where this gets tricky is that they build their queries dynamically. So ultimately what I want is to be able to take a LINQ query, pull it apart and be able to inspect the pieces to pull the correct objects out, but I don't really want to build a piece to translate the 'where' expression into SQL. Is this something I can just generate using Microsoft code? Or is there an easier way to do this?

Answer 1

I just read an interesting article on Expression Trees, LINQ to SQL uses these to translate the query into SQL and send it over the wire.

Maybe that's something you could use?

Answer 2

(you mean just LINQ, not really LINQ-to-SQL)

Sure, you can do it - but it is massive amounts of work. Here's how; I recommend "don't". You could also look at the source code for DbLinq - see how they do it.

If you just want Where, it is a bit easier - but as soon as you start getting joins, groupings, etc - it will be very hard to do.

---

Here's just Where support on a custom LINQ implemention (not a fully queryable provider, but enough to get LINQ with Where working):

using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Reflection;

namespace YourLibrary
{
    public static class MyLinq
    {
        public static IEnumerable<T> Where<T>(
            this IMyDal<T> dal,
            Expression<Func<T, bool>> predicate)
        {
            BinaryExpression be = predicate.Body as BinaryExpression;
            var me = be.Left as MemberExpression;
            if(me == null) throw new InvalidOperationException("don't be silly");
            if(me.Expression != predicate.Parameters[0]) throw new InvalidOperationException("direct properties only, please!");
            string member = me.Member.Name;
            object value;
            switch (be.Right.NodeType)
            {
                case ExpressionType.Constant:
                    value = ((ConstantExpression)be.Right).Value;
                    break;
                case ExpressionType.MemberAccess:
                    var constMemberAccess = ((MemberExpression)be.Right);
                    var capture = ((ConstantExpression)constMemberAccess.Expression).Value;
                    switch (constMemberAccess.Member.MemberType)
                    {
                        case MemberTypes.Field:
                            value = ((FieldInfo)constMemberAccess.Member).GetValue(capture);
                            break;
                        case MemberTypes.Property:
                            value = ((PropertyInfo)constMemberAccess.Member).GetValue(capture, null);
                            break;
                        default:
                            throw new InvalidOperationException("simple captures only, please");
                    }
                    break;
                default:
                    throw new InvalidOperationException("more complexity");
            }
            return dal.Find(member, value);
        }
    }
    public interface IMyDal<T>
    {
        IEnumerable<T> Find(string member, object value);
    }
}
namespace MyCode
{
    using YourLibrary;
    static class Program
    {
        class Customer {
            public string Name { get; set; }
            public int Id { get; set; }

        }
        class CustomerDal : IMyDal<Customer>
        {
            public IEnumerable<Customer> Find(string member, object value)
            {
                Console.WriteLine("Your code here: " + member + " = " + value);
                return new Customer[0];
            }
        }
        static void Main()
        {
            var dal = new CustomerDal();
            var qry = from cust in dal
                      where cust.Name == "abc"
                      select cust;

            int id = int.Parse("123");
            var qry2 = from cust in dal
                      where cust.Id == id // capture
                      select cust;


        }
    }
}

Answer 3

Technically if your DAL exposes IQueryable<T> instead of IEnumerable<T> you can also implement a IQueryProvider and do exactly what you describe. However, this is not for the faint of heart.

But if you expose the LINQ to SQL tables themselves in the DAL, they will do exactly this for you. There is a (big) risk though since you'll be handling the client code total control over how to express SQL queries, and the usual result is some complex query that joins everything and slaps pagination a top of it with less than spectacular run time performance.

I think you should consider carefully what is actually needed from the DAL and expose only that.

Answer 4

Just some though. I know some language support building a string that can be execute in the code itself. I never tried it with .Net, but this is common in functional languages like LISP. Since .Net support lambdas, maybe this is possible. Since F# is coming to .Net soon, maybe it will possible if it is not right now.

What I am trying to say is if you can do this then maybe you can build that string that will be use as the LINQ statement and then execute it. Since it is a string, it will be possible to analyse the string and get the information you want.

Answer 5

Try Dynamic Linq