"Make us filters like in Excel" is a fairly popular development request. Unfortunately, the general query implementation is "slightly" longer than its laconic statement. If you have never used these filters, here's an example . The main feature is that drop-down lists with values from the selected range appear in the line with the column names. For example, in columns A and B - 4000 lines and 3999 values (the first line is occupied by the column names). Thus, the corresponding drop-down lists will contain 3999 values. Column C has 220 lines and 219 values in the drop-down list, respectively.
ToDropdownOption
.NET has a great interface for centuries IQuerable<T>
that provides access to a variety of data sources. We will use it. Let's define an extension method ToDropdownOption
on top of the interface.
public static IQueryable<DropdownOption<TValue>> ToDropdownOption<TQueryable, TValue, TDropdownOption>(
this IQueryable<TQueryable> q,
Expression<Func<TQueryable, string>> labelExpression,
Expression<Func<TQueryable, TValue>> valueExpression)
where TDropdownOption: DropdownOption<TValue>
{
//
// Cache<TValue, TDropdownOption>.Constructor reflection
var newExpression = Expression.New(Cache<TValue, TDropdownOption>.Constructor);
//
// https://habr.com/ru/company/jugru/blog/423891/#predicate-builder
var e2Rebind = Rebind(valueExpression, labelExpression);
var e1ExpressionBind = Expression.Bind(
Cache<TValue, TDropdownOption>.LabelPropertyInfo, labelExpression.Body);
var e2ExpressionBind = Expression.Bind(
Cache<TValue, TDropdownOption>.ValuePropertyInfo, e2Rebind.Body);
// Label Value
var result = Expression.MemberInit(
newExpression, e1ExpressionBind, e2ExpressionBind);
var lambda = Expression.Lambda<Func<TQueryable, DropdownOption<TValue>>>(
result, labelExpression.Parameters);
/*
return q.Select(x => new DropdownOption<TValue>
{
Label = labelExpression
Value = valueExpression
});
,
API Expression Trees
*/
return q.Select(lambda);
}
If the method code seems incomprehensible, read the transcript or watch the report Expression Trees in Enterprise Development . It will become much clearer.
The classes themselves DropdownOption
and DropdownOption<T>
vylgyadyat follows.
public class DropdownOption
{
// DropdownOption
//
internal DropdownOption() {}
internal DropdownOption(string label, object value)
{
Value = value ?? throw new ArgumentNullException(nameof(value));
Label = label ?? throw new ArgumentNullException(nameof(label));
}
//
public string Label { get; internal set; }
public object Value { get; internal set; }
}
public class DropdownOption<T>: DropdownOption
{
internal DropdownOption() {}
//
public DropdownOption(string label, T value) : base(label, value)
{
_value = value;
}
private T _value;
//
public new virtual T Value
{
get => _value;
internal set
{
_value = value;
base.Value = value;
}
}
}
The internal constructor trick allows you to cast anything DropdownOption<T>
to DropdownOption
without a generic parameter, while at the same time preventing the creation of instances of a class without a generic parameter outside the assembly.
/ .new
. , .
API . .
public IEnumerable GetDropdowns(IQueryable<SomeData> q) =>
q.ToDropdownOption(x => x.String, x => x.Id)
IDropdownProvider
? , :
public IActionResult GetData(
[FromServices] IQueryable<SomeData> q
[FromQuery] SomeDataFilter filter) =>
Ok(q
.Filter(filter)
.ToList());
SomeData
SomeDataFilter
:
public class SomeDataFilter
{
public int[] Number { get; set; }
public DateTime[]? Date { get; set; }
public string[]? String { get; set; }
}
public class SomeData
{
public int Number { get; set; }
public DateTime Date { get; set; }
public string String { get; set; }
}
Filter
:
public static IQueryable<SomeData> Filter(
this IQueryable<SomeData> q,
SomeDataFilter filter)
{
if (filter.Number != null)
{
q = q.Where(x => filter.Number.Contains(x.Number));
}
if (filter.Date != null)
{
q = q.Where(x => filter.Date.Contains(x.Date));
}
if (filter.String != null)
{
q = q.Where(x => filter.String.Contains(x.String));
}
return q;
}
,
SomeDataFilter
, , - , :
public IActionResult GetSomeDataFilterDropdownOptions(
[FromServices] IQueryable<SomeData> q)
{
var number = q
.ToDropdownOption(x => x.Number.ToString(), x => x.Number)
.Distinct()
.ToList();
var date = q
.ToDropdownOption(x => x.Date.ToString("d"), x => x.Date)
.Distinct()
.ToList();
var @string = q
.ToDropdownOption(x => x.String, x => x.String)
.Distinct()
.ToList();
return Ok(new
{
number,
date,
@string
});
}
, SomeDataFilters, .
public interface IDropdownProvider<T>
{
Dictionary<string, IEnumerable<DropdownOption>> GetDropdownOptions();
}
, :
public class SomeDataFiltersDropdownProvider: IDropdownProvider<SomeDataFilter>
{
private readonly IQueryable<SomeData> _q;
public SomeDataFiltersDropdownProvider(IQueryable<SomeData> q)
{
_q = q;
}
public Dictionary<string, IEnumerable<DropdownOption>> GetDropdownOptions()
{
return new Dictionary<string, IEnumerable<DropdownOption>>()
{
{
"name", _q
.ToDropdownOption(x => x.Number.ToString(), x => x.Number)
.Distinct()
.ToList();
},
{
"date", _q
.ToDropdownOption(x => x.Date.ToString("d"), x => x.Date)
.Distinct()
.ToList();
},
{
"string", _q
.ToDropdownOption(x => x.String, x => x.String)
.Distinct()
.ToList();
}
};
}
}
, DropdownProvider
.
[HttpGet]
[Route("Dropdowns/{type}")]
public async IActionResult Dropdowns(
string type,
[FromServices] IServiceProvider serviceProvider
[TypeResolver] ITypeResolver typeResolver)
{
var t = typeResolver(type);
if (t == null)
{
return NotFound();
}
// dynamic, .
// T , .
dynamic service = serviceProvider
.GetService(typeof(IDropdownProvider<>)
.MakeGenericType(t));
if (service == null)
{
return NotFound();
}
var res = service.GetDropdownOptions();
return Ok(res);
}
, , , . , . , . IQueryable
ORM, Unit Of Work
ORM ( change tracking). (scope) ServiceProvider
.
public static async Task<TResult> InScopeAsync<TService, TResult>(
this IServiceProvider serviceProvider,
Func<TService, IServiceProvider, Task<TResult>> func)
{
using var scope = serviceProvider.CreateScope();
return await func(
scope.ServiceProvider.GetService<TService>(),
scope.ServiceProvider);
}
DropdownProvider
:
public async Task<Dictionary<string, IEnumerable<DropdownOption>>>
GetDropdownOptionsAsync()
{
var dict = new Dictionary<string, IEnumerable<DropdownOption>>();
var name = sp.InScopeAsync<IQueryable<SomeData>>(q => q
.ToDropdownOption(x => x.Number.ToString(), x => x.Number)
.Distinct()
.ToListAsync());
var date = sp.InScopeAsync<IQueryable<SomeData>>(q => q
.ToDropdownOption(x => x.Date.ToString("d"), x => x.Date)
.Distinct()
.ToListAsync());
var @string = sp.InScopeAsync<IQueryable<SomeData>>(q => q
.ToDropdownOption(x => x.String, x => x.String)
.Distinct()
.ToListAsync());
//
await Task.WhenAll(new []{name, date, @string}});
dict["name"] = await name;
dict["date"] = await date;
dict["string"] = await @string;
return dict;
}
All that remains is to clean up the code, eliminate duplication, and provide a better API. The builder design pattern works well for this . I will omit the implementation details. An inquisitive reader will certainly be able to design a similar API on his own.
public async Task<Dictionary<string, IEnumerable<DropdownOption>>>
GetDropdownOptionsAsync()
{
return sp
.DropdownsFor<SomeDataFilters>
.With(x => x.Number)
.As<SomeData, int>(GetNumbers)
.With(x => x.Date)
.As<SomeData, DateTime>(GetDates)
.With(x => x.String)
.As<SomeData, string>(GetStrings)
}