DotnetStandardQueryBuilder

A Odata compliant Query Builder built using Dotnet Standard 2.0 for MongoDB, SQL, Azure Cosmos Db, In Memory database

Project

Features

1. Using DotnetStandardQueryBuilder.Odata packages provides Odata compliant any Query String to Middleware Request object to further build query using available database query builders
2. Support for DotnetStandardQueryBuilder.Mongo for Mongo Database
3. Support for DotnetStandardQueryBuilder.Sql for Sql Database
4. Support for DotnetStandardQueryBuilder.AzureCosmosSql for Azure Cosmos database for Sql Query
5. Support for DotnetStandardQueryBuilder.MemoryList for Memory List database

How to use

1. Parsing Odata Query to Request Object

   Install-Package DotnetStandardQueryBuilder.Core
   Install-Package DotnetStandardQueryBuilder.Odata

   var request = new UriParser().Parse<T>('?$select=id&$filter=(id eq 545648 and name='DotnetStandardQueryBuilder')&top=10');

   Let's see how you can use it in web api

   using DotnetStandardQueryBuilder.OData;
   [ApiController]
   [Route("[controller]")]
   public abstract class TestController<T> : ControllerBase
   {
       private readonly IService<T> _service;
   
       public TestController(IService<T> service)
       {
           _service = service;
       }
   
       [HttpGet]
       public async Task<dynamic> GetAsync()
       {
           var request = new UriParser().Parse<T>(Request.QueryString.ToString());
   
           if (request.Count)
           {
               return await _service.PaginateAsync(request);
           }
   
           return await _service.GetAsync(request);
       }
   }

   The DotnetStandardQueryBuilder.Odata packages uses Microsoft.OData.Core package internally to decouple ODataUriParser logic and map ODataUriParser objects to Request.

2. Using Request Object to build queries for Mongo,

   var query = new MongoQueryBuilder<T>(request, _mongoCollection).Query();

   or you can simply use extension method

   var query = _mongoCollection.Query(request);

   Example,

   using MongoDB.Driver;
   using DotnetStandardQueryBuilder.Core;
   using System.Threading.Tasks;
   
   public class BaseRepository<T>
       where T : BaseModel
   {
       private readonly IMongoCollection<T> _mongoCollection;
   
       public BaseRepository(IDbOptions dbOptions, string collectionName)
       {
           var client = new MongoClient(dbOptions.ConnectionString);
           var database = client.GetDatabase(dbOptions.DatabaseName);
   
           _mongoCollection = database.GetCollection<T>(collectionName);
       }
   
       public async Task<List<T>> GetAsync(IRequest request = null)
       {
           var query = new MongoQueryBuilder<T>(request, _mongoCollection).Query();
   
           return await Task.FromResult(query.ToList());
       }
   }

3. Using Request Object to build queries for Sql,

   var tableName = "Users";
   var sqlQueryBuilder = new SqlQueryBuilder(request, tableName);
   
   var sqlQuery = sqlQueryBuilder.Query();
   
   var sqlCountQuery = sqlQueryBuilder.QueryCount();
   
   var sqlCreateQuery = sqlQueryBuilder.CreateQuery<T>(new List<T> { }, new List<string> { nameof(SampleModel.Id) });
   
   var sqlUpdateQuery = sqlQueryBuilder.UpdateQuery<T>(new List<T> { }, new List<string> { nameof(SampleModel.Id) });
   
   var sqlDeleteQuery = sqlQueryBuilder.DeleteQuery();

   or you can simply use SqlExpression class and extension methods

   var tableName = "Users";
   var sqlExpression = new SqlExpression(request).Where().Select(tableName).OrderBy().Paginate();
   
   var sqlExpression = new SqlExpression(request).Where().Select(tableName).OrderBy();
   
   var sqlQuery = request.DeleteQuery(_tableName);

   The output Sql Query class provides output as expression and both values in seperate properties so you can pass them directly to avoid SqlInjection

   public class SqlQuery
   {
       public string Query { get; set; }
   
       public Dictionary<string, object> Values { get; set; }
   }

   4. In the same way, you can build queries for Azure Cosmos Data for Sql Query,

   var azureCosmosSqlQueryBuilder = new AzureCosmosSqlQueryBuilder(request);
   var azureCosmosSqlQuery = azureCosmosSqlQueryBuilder.Query();
   var azureCosmosCountSqlQuery = azureCosmosSqlQueryBuilder.QueryCount();

4. You can directly use Request object to even build queries from service to service without dependant on Odata query string

   var request = new Request
   {
       Filter = new CompositeFilter
       {
           LogicalOperator = LogicalOperator.And,
           Filters = new List<IFilter>
           {
               new Filter
               {
                   Property = "id",
                   Operator = FilterOperator.IsEqualTo,
                   Value = 457785
               },
               new CompositeFilter
               {
                   LogicalOperator = LogicalOperator.Or,
                   Filters = new List<IFilter>
                   {
                       new Filter
                       {
                           Property = "firstName",
                           Operator = FilterOperator.Contains,
                           Value = "DotnetStandard"
                       },
                       new Filter
                       {
                           Property = "lastName",
                           Operator = FilterOperator.StartsWith,
                           Value = "QueryBuilder"
                       },
                   }
               },
               age > 10 
               ?   new Filter
                   {
                       Property = "name",
                       Operator = FilterOperator.IsNotEqualTo,
                       Value = "DotnetStandardQueryBuilder"
                   } 
               : null
           }
       },
       Page = 2,
       PageSize = 10,
       Select = new List<string> { "id", "name" },
       Count = true
   };

5. Using Memory List

   Sometimes we can have a scenario where we have cached list data or list of items already in memory objects. Here you can use the DotnetStandardQueryBuilder.MemoryList to query.

   var memoryList = new List<SampleModel>();
   var memoryListQueryBuilder = new MemoryListQueryBuilder<SampleModel>(request, memoryList);
   var memoryListQuery = memoryListQueryBuilder.Query();
   var memoryListCountQuery = memoryListQueryBuilder.QueryCount();

   or you can simply use SqlExpression class and extension methods

   var memoryList = new List<SampleModel>();
   var sqlExpression = memoryList.Query(request);
   
   var sqlExpression = memoryList.QueryCount(request);

The package is newly created and aim to simplify query building, filtering using DotnetStandardQueryBuilder.Odata and different database packages.

Feel free contribute and raise PR's