Geek Answers Handbook

Dapper, ODATA, and IQueryable in ASP.NET

I have seen great Dapper performance and some examples of how to use it.

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

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

+4
source share
3 answers

No, not at all. Well, you can enclose any sequence with .AsQueryable () if you really want to, but it will use LINQ-to-Objects. Dupper is intentionally simple; he is trying to do very little, but what he is doing is trying to do: he is damn good (even if I say that myself). The right IQueryable interface is the exact opposite of dapper ...

+8
source

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

My solution was to create an 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); }

It looks simple, and it still works for me, so I stick with that. I just had to limit what users can filter as far as odata is concerned p>

  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, eg /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; }
+2
source

To use Dapper with OData, you will need to intercept the Get(ODataQueryOptions<SpotView> queryOptions) parameter Get(ODataQueryOptions<SpotView> queryOptions) , Get(ODataQueryOptions<SpotView> queryOptions) it, and create Dapper where clause from it. You also need to take into account any other OData settings that you have in your WebApiConfig.cs, in

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

But you can also use MySqlConnection with EF, and you don't need to reinvent the wheel with the above parsing. But then this is your decision if Dapper performance is more important than using a full ORM, such as EF, which already has a built-in implementation in it.

+1
source

More articles:


All Articles