Dapper, ODATA, and IQueryable in ASP.NET

asp.net-mvc dapper iqueryable mvvm odata

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?

Accepted Answer

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...


Popular Answer

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



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why