CODEWITHSUNDEEP
c#sqllinq
Hounshell
Hounshell

Reputation: 5459

Pulling the WHERE clause out of LINQ to SQL

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?

Upvotes: 2

Views: 357

Answers (6)

constantine g
constantine g

Reputation: 169

To anyone else with the same question out there. Pulling out the where clause from LINQ-to-SQL isn’t quite as straightforward, as one would’ve hoped for. Additionally, doing that by itself is probably meaningless. There are a couple of options, depending on the requirements – either grab it from the generated string, but then it would contain parameter references and object property mappings that would also have to be resolved, so those would also have to be pulled out of the original provider somehow, otherwise this would be pointless. Another – would be to find a modular provider that can do that, as well as make member mappings easily accessible, but once again, without the rest of the query, I see little utility in doing that, because the where clause would reference table/column aliases from the select statement.

I had a similar task to write a full blown provider for a custom ORM/DAL a couple of years ago. While it qualifies as the most complex thing I’ve worked on, being an experience developer, I can say it’s not as bad, as some people claim once you wrap your head around the concepts that lie at the foundation of such a component. Some solutions that I’ve seen go the wrong way about it, add redundant functionality and have extra code addressing problems introduced by underlying logic. E.g. the “optimization” stage/module that attempts to re-factor bloated, nested SQL produced by the main parser. If the latter was designed in such a way that would output clean SQL from the start, then no clean-up phase would be needed. I’ve seen providers that create a new level of nesting for each where and join call. That’s a bad strategy. By breaking down a query into three/four main parts – select, from, where and orderby, which are built individually as the tree is being visited, this problem is avoided altogether. I’ve developed an object-to-data (aka LINQ-to-SQL) provided based on these principles for a custom ORM/DAL and it produces nice, clean SQL, with an excellent performance, as each statement is compiled to IL and cached.

For anyone that is looking to do something similar, please see my posts that include a sample project with a tutorial/barebones implementation that makes it easy to see how it works. Included is also the full solution:

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062550

(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;


        }
    }
}

Upvotes: 4

Partha Choudhury
Partha Choudhury

Reputation: 534

Try Dynamic Linq

Upvotes: 0

David Brunelle
David Brunelle

Reputation: 6430

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.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294217

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.

Upvotes: 1

Tony The Lion
Tony The Lion

Reputation: 63190

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?

Upvotes: 0

Related Questions