Dapper, ODATA, and IQueryable in ASP.NET

Question

I saw the great performance of Dapper and some samples of how to use it.

I was wondering if I can use it to provide IQueryable data and integrate it with UI layer using ODATA to transfer data to the UI (like grids, lists,...).

Is there any way to return Dapper objects AsQueryable instead of IEnumerable to query using ODATA?

Answer 1

No, not really. Well, you can wrap any sequence with .AsQueryable() if you really want, but it will just be using LINQ-to-Objects. Dapper is deliberately simple; it tries to do very little, but what it does try to do: it does damned well (even if I do say so myself). A proper IQueryable interface is the exact opposite of dapper...

Answer 2

I recently found myself in this situation where I wanted to use Dapper with OData but I did not want to use Entity Framework for SQLite.

My solution was to create a extension method for ODataQueryOptions so my controllers would look like this

public IHttpActionResult GetDevices(ODataQueryOptions<User> queryOptions)
{
    var usersQuery = new UserQuery(page, perPage);
    var users = usersQuery.Search(queryOptions.WhereClause(), queryOptions.OrderByClause());
    return Ok<IEnumerable<User>>(users);
}

This looks straightforward and it works for me so far so I'm sticking with it. I just had to limit what users can filter as far as odata is concerned

    protected ODataValidationSettings _validationSettings =
        new ODataValidationSettings
        {
            // These validation settings prevent anything except: (equals, and, or) filter and sorting
            AllowedFunctions = AllowedFunctions.None,
            AllowedLogicalOperators = AllowedLogicalOperators.Equal | AllowedLogicalOperators.And | AllowedLogicalOperators.Or | AllowedLogicalOperators.NotEqual,
            AllowedArithmeticOperators = AllowedArithmeticOperators.None,
            AllowedQueryOptions = AllowedQueryOptions.Filter | AllowedQueryOptions.OrderBy
        };

And here is the extension method

    public static string WhereClause(this ODataQueryOptions options)
    {
        string s = "";
        if (options.Filter != null && options.Filter.FilterClause != null)
        {
            var node = options.Filter.FilterClause.Expression as BinaryOperatorNode;
            s = getWhereClause(node);
        }
        return s;
    }

    private static string getWhereClause(BinaryOperatorNode node)
    {
        // PARSE FILTER CLAUSE
        // Parsing a filter, e.g. /Users?$filter=Id eq '1' or Id eq '100'  
        var s = "";
        if (node.Left is SingleValuePropertyAccessNode && node.Right is ConstantNode)
        {
            var property = node.Left as SingleValuePropertyAccessNode ?? node.Right as SingleValuePropertyAccessNode;
            var constant = node.Left as ConstantNode ?? node.Right as ConstantNode;

            if (property != null && property.Property != null && constant != null && constant.Value != null)
            {
                s += $" {property.Property.Name} {getStringValue(node.OperatorKind)} '{constant.Value}' ";
            }
        }
        else
        {
            if (node.Left is BinaryOperatorNode)
                s += getWhereClause(node.Left as BinaryOperatorNode);

            if (node.Right is BinaryOperatorNode)
            {
                s += $" {getStringValue(node.OperatorKind)} ";
                s += getWhereClause(node.Right as BinaryOperatorNode);
            }
        }
        return s;
    }

Answer 3

To use Dapper with OData, you would have to intercept the Get(ODataQueryOptions<SpotView> queryOptions) parameter, parse it, and create your Dapper's where clause out of it. You would also need to take into consideration any other OData settings you have in your WebApiConfig.cs in the

config.AddODataQueryFilter(new EnableQueryAttribute()
{
...
});

But, you can also use MySqlConnection with EF and you don't have to re-invent the wheel with the above parsing. But, then it's your decision if Dapper's performance is more important than using a full ORM like EF which already has differed execution built into it.