DynamicSearch 1.1.4

Dynamic sql query generator from model

Dynamic search is giving functionality for creating predicate expression from specified search models for using EF Core. It also has paging functionality with ordering by given property name and direction.

Usage

Inject or create instance of your context class somewhere you want.

public class Person
{
    public int Id { get; set; }
    public int Age { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    
    public Country Country { get; set; }
    public ICollection<Order> { get; set; }
}

public class PersonManager
{
    public async Task<Person> GetPersons()
    {
            var options = new SearchOptions
            {
                PageNumber = 1,
                PageSize = 10,
            };

            using var context = new AppContext();
            var query = context.Persons.ApplyOptions(options, out var totalCountTask);
            var totalCount = await totalCountTask;
            var page = query.ToListAsync();
    }
}

This query will be translated to sql as shown where @__p_0 is 0 and @__p_1 is 10.

      SELECT [p].[Id], [p].[Age], [p].[Name], [p].[Surname]
      FROM [Persons] AS [p]
      ORDER BY (SELECT 1)
      OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

Be careful await the totalCountTask before starting any other query on database to avoid from "A second operation started on this context before a previous operation completed" InvalidOperationException. Please use ApplyOptions after all 'where' statements because total count may be changed because of adding new condition.

In case of when you want also order the data use OrderProperty.

    public async Task<Person> GetPersons()
    {
            var options = new SearchOptions
            {
                PageNumber = 1,
                PageSize = 10,
                DescendingOrder = true,
                OrderProperty = nameof(Person.Name)
            };

            using var context = new AppContext();
            var query = context.Persons.ApplyOptions(options, out var totalCountTask);
            var totalCount = await totalCountTask;
            var page = query.ToListAsync();
    }

This query will be translated to sql as

      SELECT [p].[Id], [p].[Age], [p].[Name], [p].[Surname]
      FROM [Persons] AS [p]
      ORDER BY [p].[Name] DESC
      OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

In case of when you want also filter the data add search models.

    public async Task<Person> GetPersons()
    {
            var options = new SearchOptions
            {
                PageNumber = 1,
                PageSize = 10,
                DescendingOrder = true,
                OrderProperty = nameof(Person.Name)
            };

            options.Search.Add(new SearchModel 
                { 
                    Term = "15", 
                    Property = nameof(Person.Age) 
                });

            using var context = new AppContext();
            var query = context.Persons.ApplyOptions(options, out var totalCountTask);
            var totalCount = await totalCountTask;
            var page = query.ToListAsync();
    }

This query will be translated to sql as shown where @__Parse_0 is 15.

           SELECT [p].[Id], [p].[Age], [p].[Name], [p].[Surname]
           FROM [Persons] AS [p]
           WHERE [p].[Age] = @__Parse_0
           ORDER BY [p].[Name] DESC
           OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

OrderProperty can be used with '.' (dots) for example "Country.Name" this will join related table and order data by related table column.

SearchModel also has from and to properties which are used different logic for various types. For numeric,date,datetime types it has greaterThanOrEqual and lessThan logic. For string types it has StartsWith and EndsWith logic. There is nonEqual property in SearchModel also, it is used to negate the condition. The aggregate enumeration is for filtering by aggregate function. For example if person has many orders we can filter them by sum or count of orders.

...
 options.Search.Add(new SearchModel { Term = "1500", Property = "Orders.Amount", Aggregate = Aggregate.Sum });
 options.Search.Add(new SearchModel { Term = "1500", Property = "Orders.Id", Aggregate = Aggregate.Count });
...