private static FormattableString CreateQueryWithConditions(UniversityFilterInSql filter)
{
string sqlQuery;
if (filter.ComplexQuery)
{
sqlQuery = @$ "SELECT
{filter.SelectedFieldsForSql}
{(!string.IsNullOrWhiteSpace(filter.Region) ? filter.RegionConditionForSqlInnerJoinApproach : string.Empty)}
UNION
SELECT
{filter.SelectedFieldsForSql}
{filter.Where}
{(!string.IsNullOrWhiteSpace(filter.SearchText) ? filter.SearchTextConditionForSql : string.Empty)}
{(filter.IsGoverment != null ? filter.GovermentConditionForSql : string.Empty)}";
}
else
{
sqlQuery = @$ "SELECT
{filter.SelectedFieldsForSql}
{(!string.IsNullOrWhiteSpace(filter.Region) ? filter.RegionConditionForSqlInnerJoinApproach : string.Empty)}
{filter.Where}
{(!string.IsNullOrWhiteSpace(filter.SearchText) ? filter.SearchTextConditionForSql : string.Empty)}
{(filter.IsGoverment != null ? filter.GovermentConditionForSql : string.Empty)}";
}
var parametersData = filter.GetParametersSequence();
for (int index = 0; index < parametersData.Count; index++)
{
sqlQuery = sqlQuery.Replace(parametersData.ElementAt(index).Key, index.ToString());
}
return(FormattableStringFactory.Create(sqlQuery, parametersData.Values.ToArray()));
}
public async Task <List <University> > GetAllBySql(UniversityFilterInSql filter) => await _currentRepo .FromSqlInterpolated(CreateQueryWithConditions(filter)) .Include(e => e.Region) .ToListAsync();
