CODEWITHSUNDEEP
c#linqdapper
cs0815
cs0815

Reputation: 17388

dynamic queries in dapper

I am trying to work with this. I have written a unit test as a starter for ten like so:

[Fact]
public void TestOredGuids()
{
    // Arrange
    const string expectedSql = "SELECT * FROM Products WHERE SomeExternalForeignKey = @SomeExternalForeignKey OR Name = SomeExternalForeignKey = @SomeExternalForeignKey";

    // Act
    var result = DynamicQuery.GetDynamicQuery<Product>("Products", p => p.SomeExternalForeignKey == new Guid("28D3BCFB-9472-4141-BD88-BE5E7E1230F0") || p.SomeExternalForeignKey == new Guid("0F0DBA45-F842-4E46-9ED4-F50B5BCF0509"));

    // Assert

}

internal class Product
{
    public string Name { get; set; }
    public decimal Price { get; set; }
    public DateTime ExpiryDate { get; set; }
    public int CategoryId { get; set; }
    public Guid SomeExternalForeignKey { get; set; }
}

Unfortunately, I am always getting:

Additional information: 'System.Linq.Expressions.NewExpression' does not contain a definition for 'Value'

in the WalkTree method. Am I using GetDynamicQuery wrongly?

If there are any other implementation of dynamic sql mappers like this for dapper, I would appreciate any pointers. Thanks!

Upvotes: 0

Views: 1320

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205539

From what I see in the source code of the component you are trying to use, it expects the right operand of the expression to be a ConstantExpression (although for some unknown reason the author is using dynamic and expects a Value property), so to make it work, modify you code as follows

var someExternalForeignKey1 = new Guid("28D3BCFB-9472-4141-BD88-BE5E7E1230F0");
var someExternalForeignKey2 = new Guid("0F0DBA45-F842-4E46-9ED4-F50B5BCF0509");
var result = DynamicQuery.GetDynamicQuery<Product>("Products", p => p.SomeExternalForeignKey == someExternalForeignKey1 || p.SomeExternalForeignKey == someExternalForeignKey2);

Update: It turns out that the above also doesn't work, because of course it produces a closure which is not ConstantExpression. To make it work (as well as your original code), here are the required modifications of the DynamicQuery class

private static void WalkTree(BinaryExpression body, ExpressionType linkingType,
                             ref List<QueryParameter> queryProperties)
{
    if (body.NodeType != ExpressionType.AndAlso && body.NodeType != ExpressionType.OrElse)
    {
        string propertyName = GetPropertyName(body);
        var propertyValue = GetPropertyValue(body.Right);
        string opr = GetOperator(body.NodeType);
        string link = GetOperator(linkingType);

        queryProperties.Add(new QueryParameter(link, propertyName, propertyValue, opr));
    }
    else
    {
        WalkTree((BinaryExpression)body.Left, body.NodeType, ref queryProperties);
        WalkTree((BinaryExpression)body.Right, body.NodeType, ref queryProperties);
    }
}

private static object GetPropertyValue(Expression source)
{
    var constantExpression = source as ConstantExpression;
    if (constantExpression != null)
        return constantExpression.Value;
    var evalExpr = Expression.Lambda<Func<object>>(Expression.Convert(source, typeof(object)));
    var evalFunc = evalExpr.Compile();
    var value = evalFunc();
    return value;
}

But note that the whole class (as the author states) is just an example, and for instance maps just one parameter (thus one value) per property, so in order to make it really useful, the GetDynamicQuery method needs additional work. You might try this one instead. Hope that helps.

Upvotes: 1

Related Questions