How to convert Expression> to Sql query condition?

Question

For some reason I need to convert Linq Expression to SQL query condition. This is my code:

public class Program
{
    static void Main(string[] args)
    {
        List<Person> list = new List<Person>();
        list.Add(new Person() { Id = 1, Name = "Leo" });
        list.Add(new Person() { Id = 2, Name = "Evan" });
        list.Add(new Person() { Id = 3, Name = "Diego" });

        int Parameter_Id = 1;
        string Parameter_Name = "Leo";

        string sqlCondition = GetSql(list, (x => x.Name == Parameter_Name || x.Id == Parameter_Id));
        Console.WriteLine(sqlCondition);
        // "WHERE Name = 'Leo' OR Id = 1"
    }


    static string GetSql<T>(IEnumerable<T> models, Expression<Func<T, bool>> condition)
    {
        // Code
        var sqlString = "";
        return "WHERE " + sqlString;
        
    }
}

How to change GetSql method to sqlCondition print WHERE Name = 'Leo' OR Id = 1 ?

You really, really, really should not do this yourself. Use Entity framework or some other ORM that can work with linq. — Zohar Peled, Jan 05 '22 at 08:35
I have done exactly this; it is ... a lot of work; unless you *really, really* like reflection and metaprogramming, and don't mind having to deal with a myriad ways of representing the same condition: seriously: don't. Also, note that I would expect this to generate `WHERE Name = @p0 OR Id = @p1` or similar. Unless you have a lot of time to throw at this, for pure curiosity: just use an ORM — Marc Gravell, Jan 05 '22 at 08:38
what about this article: https://learn.microsoft.com/en-us/dotnet/api/system.linq.expressions.expression.convert?view=net-6.0 ? And i totally agree with people upon me, you shouldnt do this by yourself. — Bartosz Olchowik, Jan 05 '22 at 08:40
@BartoszOlchowik that just creates an expression-tree node that represents a semantic conversion - it doesn't convert an existing expression to anything else — Marc Gravell, Jan 05 '22 at 08:44
Does this answer your question? [How to convert an expression tree to a partial SQL query?](https://stackoverflow.com/questions/7731905/how-to-convert-an-expression-tree-to-a-partial-sql-query) — Bartosz Olchowik, Jan 05 '22 at 08:48
@MostafaAzarirad whatever you are using, you could use visitor pattern to create those queries as mentioned in the link above. When i was using dapper i was used to write queries by myself, just remember to keep them parametrized. Dapper is lightweight tool that requires from you a little SQL knowledge, but is a lot faster than EF for example (you will always write a better sql than automated EF queries, if you know SQL). If you dont want to care about sql queries, i suggest moving from Dapper to Nhibernate or Entity Framework. — Bartosz Olchowik, Jan 05 '22 at 09:05
@MostafaAzarirad You could also check this link: https://dapper-tutorial.net/knowledge-base/38826292/how-to-use-dapper-with-linq — Bartosz Olchowik, Jan 05 '22 at 09:08
@MostafaAzarirad "can I use dapper for this?" - simple answer: "no" (source: I wrote Dapper). EF will do this for you; why not use EF? or LLBLGenPro? or any of a range of ORMs? — Marc Gravell, Jan 05 '22 at 09:08
@MarcGravell there are also micro orms out there that are using dapper. A lot better and faster than EF. — Bartosz Olchowik, Jan 05 '22 at 09:10
@BartoszOlchowik please prefer the original link there: https://stackoverflow.com/questions/38826292/how-to-use-dapper-with-linq - "dapper-tutorial" is nothing whatsoever to do with Dapper; they are not affiliated in any way — Marc Gravell, Jan 05 '22 at 09:10
@MarcGravell Is there a good tutorial for Dapper somewhere? I've been using it for about two years now and I feel I'm only scratching the surface with it... — Zohar Peled, Jan 05 '22 at 10:23

score -1 · Answer 1 · answered Jan 05 '22 at 13:44

i think this is your answer :

using System.Linq.Expressions;

    class Program
    {
        static void Main()
        {
            List<User> users = new List<User>();
            users.Add(new User { Name = "Test1", Age = 12 });
            users.Add(new User { Name = "Test2", Age = 43 });
            users.Add(new User { Name = "Test3", Age = 34 });
    
            string query = SqlQuery<User>(a => (a.Name == "Test2"  || a.Age == 43));
            Console.WriteLine(query);
        }
    
        static string SqlQuery<T>(Expression<Func<T, bool>> expression)
        {
            return string.Format("Where {0}",string.Join(" OR ", new EXInfo(expression)
                .Values().Select(a=>a.Item1 + " = " + a.Item2)));
        }
    }
    
    class EXInfo : ExpressionVisitor
    {
        private string Name { get; set; }
        private List<(string, string)> List = new List<(string, string)>();
        public EXInfo(Expression expression)
        {
            Visit(expression);
        }
        public List<(string, string)> Values()
        {
            return List;
        }
    
        protected override Expression VisitConstant(ConstantExpression node)
        {
            if (node.Value?.ToString().All(a => char.IsDigit(a)) == true)
            {
                List.Add(new (Name, node.Value.ToString()));
            }
            else
            {
                List.Add(new (Name, $"'{node.Value}'"));
            }
            return base.VisitConstant(node);
        }
    
        protected override Expression VisitMember(MemberExpression node)
        {
            Name = node.Member.Name;
            return base.VisitMember(node);
        }
    }
    
    class User
    {
        public string? Name { get; set; }
        public int Age { get; set; }
    }

How to convert Expression> to Sql query condition?

1 Answers1