Parsing Expression Tree

Question

I want to build Dapper string using a LINQ expression as method argument. I've found at MS Docs an example of parsing and integrated it in my code:

public static List<Notification> GetNotifs(Expression<Func<Notification, bool>> p)
        {
            using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
            {
                string sqlQ = "SELECT * FROM Notifications WHERE ";
                ParameterExpression param = p.Parameters[0];
                BinaryExpression operation = (BinaryExpression)p.Body;
                ParameterExpression left = (ParameterExpression)operation.Left;
                for (int i = 0; i < left.Name.Length; i++) { if (i <= param.Name.Length) { } else { sqlQ += left.Name[i]; } }
                ConstantExpression right = (ConstantExpression)operation.Right;
                if (operation.NodeType.ToString() == "LessThan") sqlQ += " <";
                else if (operation.NodeType.ToString() == "GreaterThan") sqlQ += " >";
                else if (operation.NodeType.ToString() == "LessThanOrEqual") sqlQ += " <=";
                else if (operation.NodeType.ToString() == "GreaterThanOrEqual") sqlQ += " >=";
                else if (operation.NodeType.ToString() == "Equal") sqlQ += " =";
                else if (operation.NodeType.ToString() == "NotEqual") sqlQ += " !=";
                sqlQ += " " + right.Value;
                return connection.Query<Notification>(sqlQ).ToList();
            }
        }

But, unfortunately it gives an InvalidCastException at the

ParameterExpression left = (ParameterExpression)operation.Left;

The call of this method is like:

DRepository.GetNotifs(uid => uid.U_Id == id)

Could you help me to find out, where am I incorrect?

KozhevnikovDmitry · Accepted Answer · 2018-09-21 08:59:12Z

1

The example below generates SQL SELECT * FROM Notifications WHERE U_Id = 1:

[Test]
public void DapperExpression()
{
    // Arrange
    var id = 1;

    // Act
    var list = GetNotifs(uid => uid.U_Id == id);

    // Assert 
    Assert.IsNotEmpty(list);
}

public static List<Notification> GetNotifs(Expression<Func<Notification, bool>> p)
{
    using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
    {
        string sqlQ = "SELECT * FROM Notifications WHERE ";
        ParameterExpression param = p.Parameters[0];
        BinaryExpression operation = (BinaryExpression)p.Body;
        var t = operation.Left.GetType();
        MemberExpression left = (MemberExpression)operation.Left;
        sqlQ += left.Member.Name;
        MemberExpression right = (MemberExpression)operation.Right;
        ConstantExpression cnst = (ConstantExpression) right.Expression;
        var field = cnst.Type.GetFields().Single();
        var val = field.GetValue(cnst.Value);

        if (operation.NodeType.ToString() == "LessThan") sqlQ += " <";
        else if (operation.NodeType.ToString() == "GreaterThan") sqlQ += " >";
        else if (operation.NodeType.ToString() == "LessThanOrEqual") sqlQ += " <=";
        else if (operation.NodeType.ToString() == "GreaterThanOrEqual") sqlQ += " >=";
        else if (operation.NodeType.ToString() == "Equal") sqlQ += " =";
        else if (operation.NodeType.ToString() == "NotEqual") sqlQ += " !=";
        sqlQ += " " + val;

        return connection.Query<Notification>(sqlQ).ToList();
    }
}

Notice that it would work in case of int. You should add some type dependent logic to add quoutes for string or Guid for example. You can get this type from field variable. Hope it helps.

edited Sep 21, 2018 at 8:59

answered Sep 21, 2018 at 8:52

KozhevnikovDmitry

1,72014 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rottor171 Over a year ago

I will try to find it out (about types). Thank you a lot.

KozhevnikovDmitry Over a year ago

Happy to help=)

Collectives™ on Stack Overflow

Parsing Expression Tree

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related