public async Task SetupRestOfDto(BooksFilterBy filterBy, string filterValue, int pageSize,
int expectedPageNum, int expectedNumPages)
{
//SETUP
var numBooks = 12;
var options = SqliteInMemory.CreateOptions <BookDbContext>();
using (var context = new BookDbContext(options))
{
context.Database.EnsureCreated();
context.SeedDatabaseDummyBooks(numBooks);
var sfpDto = new SortFilterPageOptions
{
FilterBy = BooksFilterBy.ByVotes,
FilterValue = "Dummy",
PageSize = 10,
PageNum = 2
};
//need to do this to to setup PrevCheckState
await sfpDto.SetupRestOfDtoAsync(context.Books);
//ATTEMPT
sfpDto.PageNum = 2;
sfpDto.FilterBy = filterBy;
sfpDto.FilterValue = filterValue;
sfpDto.PageSize = pageSize;
await sfpDto.SetupRestOfDtoAsync(context.Books);
//VERIFY
sfpDto.PageNum.ShouldEqual(expectedPageNum);
sfpDto.NumPages.ShouldEqual(expectedNumPages);
}
}
public static IQueryable <BookListDto> FilterBooksEventsBy(
this IQueryable <BookListDto> books,
BooksFilterBy filterBy, string filterValue)
{
if (string.IsNullOrEmpty(filterValue))
{
return(books);
}
switch (filterBy)
{
case BooksFilterBy.NoFilter:
return(books);
case BooksFilterBy.ByVotes:
var filterVote = int.Parse(filterValue);
return(books.Where(x =>
x.ReviewsAverageVotes > filterVote));
case BooksFilterBy.ByPublicationYear:
if (filterValue == AllBooksNotPublishedString)
{
return(books.Where(x => x.PublishedOn > DateTime.UtcNow));
}
var filterYear = int.Parse(filterValue);
return(books.Where(x => x.PublishedOn.Year == filterYear && x.PublishedOn <= DateTime.UtcNow));
default:
throw new ArgumentOutOfRangeException
(nameof(filterBy), filterBy, null);
}
}
public void SetupRestOfDto(BooksFilterBy filterBy, string filterValue, int pageSize,
int expectedPageNum, int expectedNumPages)
{
//SETUP
var inMemDb = new SqliteInMemory();
const int numBooks = 12;
using (var db = inMemDb.GetContextWithSetup())
{
db.Books.AddRange(EfTestData.CreateDummyBooks(numBooks, false));
db.SaveChanges();
var sfpDto = new SortFilterPageOptions
{
FilterBy = BooksFilterBy.ByVotes,
FilterValue = "Dummy",
PageNum = 2
};
//need to do this to to setup PrevCheckState
sfpDto.SetupRestOfDto(db.Books);
//ATTEMPT
sfpDto.PageNum = 2;
sfpDto.FilterBy = filterBy;
sfpDto.FilterValue = filterValue;
sfpDto.PageSize = pageSize;
sfpDto.SetupRestOfDto(db.Books);
//VERIFY
sfpDto.PageNum.ShouldEqual(expectedPageNum);
sfpDto.NumPages.ShouldEqual(expectedNumPages);
}
}
public static IQueryable <BookListDTO> FilterBooksBy(
this IQueryable <BookListDTO> books,
BooksFilterBy filterBy, string filterValue) //#A
{
if (string.IsNullOrEmpty(filterValue)) //#B
{
return(books); //#B
}
switch (filterBy)
{
case BooksFilterBy.NoFilter: //#C
return(books); //#C
case BooksFilterBy.ByVotes:
var filterVote = int.Parse(filterValue); //#D
return(books.Where(x => //#D
x.ReviewsAverageVotes > filterVote)); //#D
case BooksFilterBy.ByPublicationYear:
if (filterValue == AllBooksNotPublishedString) //#E
{
return(books.Where( //#E
x => x.PublishedOn > DateTime.UtcNow)); //#E
}
var filterYear = int.Parse(filterValue); //#F
return(books.Where( //#F
x => x.PublishedOn.Year == filterYear && //#F
x.PublishedOn <= DateTime.UtcNow)); //#F
default:
throw new ArgumentOutOfRangeException
(nameof(filterBy), filterBy, null);
}
}
/// <summary>
/// This makes the various Value + text to go in the dropdown based on the FilterBy option
/// </summary>
/// <param name="filterBy"></param>
/// <returns></returns>
public IEnumerable <DropdownTuple> GetFilterDropDownValues(BooksFilterBy filterBy)
{
switch (filterBy)
{
case BooksFilterBy.NoFilter:
//return an empty list
return(new List <DropdownTuple>());
case BooksFilterBy.ByVotes:
return(FormVotesDropDown());
case BooksFilterBy.ByTags:
return(_db.Tags
.Select(x => new DropdownTuple
{
Value = x.TagId,
Text = x.TagId
}).ToList());
case BooksFilterBy.ByPublicationYear:
var today = DateTime.UtcNow.Date; //#A
var result = _db.Books //#B
.Where(x => x.PublishedOn <= today) //#B
.Select(x => x.PublishedOn.Year) //#B
.Distinct() //#B
.OrderByDescending(x => x) //#C
.Select(x => new DropdownTuple //#D
{ //#D
Value = x.ToString(), //#D
Text = x.ToString() //#D
}).ToList(); //#D
var comingSoon = _db.Books. //#E
Any(x => x.PublishedOn > today); //#E
if (comingSoon) //#F
{
result.Insert(0, new DropdownTuple //#F
{
Value = BookListDtoFilter.AllBooksNotPublishedString,
Text = BookListDtoFilter.AllBooksNotPublishedString
});
}
return(result);
/*****************************************************************
#A Today's date so we can filer out books that haven't be published yet
#B This long command gets the year of publication by filters out the future books, select the date and uses distinct to only have one of each year
#C Orders the years, with newest year at the top
#D I finally use two client/server evaluations to turn the values into strings
#E This returns true if there is a book in the list that is not yet published
#F Finally I add a "coming soon" filter for all the future books
* ***************************************************************/
default:
throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
public IQueryable <BookListDto> SearchBooks(OrderOption orderOption, BooksFilterBy filterBy, string filterValue, int pageNumber = 0, int pageSize = 10)
{
var query = _context.Books
.AsNoTracking() // Because this is a read only query, you add AsNoTracking
.MapBookToDto() // Uses the Select query object, which will pick out/calculate the data it needs
.OrderBooksBy(orderOption) // Adds the commands to order the data by using the given option
.FilterBooksBy(filterBy, filterValue); // Adds the commands to filter data
return(query.Page(pageNumber, pageSize));
}
public IEnumerable <DropdownTuple> GetFilterDropDownValues(BooksFilterBy filterBy)
{
switch (filterBy)
{
case BooksFilterBy.NoFilter:
return(new DropdownTuple[0]);
case BooksFilterBy.ByVotes:
return(GetVotesDropDown());
case BooksFilterBy.ByPublicationYear:
return(GetPublicationYearDropDown());
default: throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
/// <summary>
/// This makes the various Value + text to go in the dropdown based on the FilterBy option
/// </summary>
/// <param name="filterBy"></param>
/// <returns></returns>
public IEnumerable <DropdownTuple> GetFilterDropDownValues(BooksFilterBy filterBy)
{
switch (filterBy)
{
case BooksFilterBy.NoFilter:
//return an empty list
return(new List <DropdownTuple>());
case BooksFilterBy.ByVotes:
return(FormVotesDropDown());
case BooksFilterBy.ByTags:
return(_db.Tags
.Select(x => new DropdownTuple
{
Value = x.TagId,
Text = x.TagId
}).ToList());
case BooksFilterBy.ByPublicationYear:
var comingSoon = _db.Books.
Any(x => x.PublishedOn > DateTime.Today);
var result = _db.Books
.Where(x => x.PublishedOn <= DateTime.Today)
.Select(x => x.PublishedOn.Year)
.Distinct()
.OrderByDescending(x => x)
.Select(x => new DropdownTuple
{
Value = x.ToString(),
Text = x.ToString()
}).ToList();
if (comingSoon)
{
result.Insert(0, new DropdownTuple
{
Value = BookListDtoFilter.AllBooksNotPublishedString,
Text = BookListDtoFilter.AllBooksNotPublishedString
});
}
return(result);
default:
throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
/// <summary>
/// This makes the various Value + text to go in the dropdown based on the FilterBy option
/// </summary>
/// <param name="filterBy"></param>
/// <returns></returns>
public IEnumerable <DropdownTuple> GetFilterDropDownValues(BooksFilterBy filterBy)
{
switch (filterBy)
{
case BooksFilterBy.NoFilter:
//return an empty list
return(new List <DropdownTuple>());
case BooksFilterBy.ByVotes:
return(FormVotesDropDown());
case BooksFilterBy.ByPublicationYear:
var comingSoon = _db.Books.
Any(x => x.PublishedOn > DateTime.UtcNow);
var nextYear = DateTime.UtcNow.AddYears(1).Year;
var result = _db.Books
.Select(x => x.PublishedOn.Year)
.Distinct()
.Where(x => x < nextYear)
.OrderByDescending(x => x)
.Select(x => new DropdownTuple
{
Value = x.ToString(),
Text = x.ToString()
}).ToList();
if (comingSoon)
{
result.Insert(0, new DropdownTuple
{
Value = BookListDtoFilter.AllBooksNotPublishedString,
Text = BookListDtoFilter.AllBooksNotPublishedString
});
}
return(result);
/*****************************************************************
#A This returns true if there is a book in the list that is not yet published
#B This gets next year so we can filter out all future publications
#C This long command gets the year of publication, uses distinct to only have one of each year, filters out the future books and orders with newest year at the top
#D I finally use two client/server evaluations to turn the values into strings
#E Finally I add a "coming soon" filter for all the future books
* ***************************************************************/
default:
throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
public IEnumerable <DropdownTuple> GetFilterDropDownValues(BooksFilterBy filterBy)
{
switch (filterBy)
{
case BooksFilterBy.NoFilter:
return(new List <DropdownTuple>());
case BooksFilterBy.ByVotes:
return(FormVotesDropDown());
case BooksFilterBy.ByPublicationYear:
var comingSoon = Context.Books. //#A
Any(x => x.PublishedOn > DateTime.UtcNow); //#A
var nextYear = DateTime.UtcNow.AddYears(1).Year; //#B
var result = Context.Books //#C
.Select(x => x.PublishedOn.Year) //#C
.Distinct() //#C
.Where(x => x < nextYear) //#C
.OrderByDescending(x => x) //#C
.Select(x => new DropdownTuple //#D
{ //#D
Value = x.ToString(), //#D
Text = x.ToString() //#D
}).ToList(); //#D
if (comingSoon) //#E
{
result.Insert(0, new DropdownTuple //#E
{
Value = BookListDtoFilter.AllBooksNotPublishedString,
Text = BookListDtoFilter.AllBooksNotPublishedString
});
}
return(result);
default:
throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
public static IQueryable <CosmosBook> FilterBooksBy(
this IQueryable <CosmosBook> books,
BooksFilterBy filterBy, string filterValue)
{
if (string.IsNullOrEmpty(filterValue))
{
return(books);
}
switch (filterBy)
{
case BooksFilterBy.NoFilter:
return(books);
case BooksFilterBy.ByVotes:
var filterVote = int.Parse(filterValue);
return(books.Where(x => x.ReviewsAverageVotes > filterVote));
case BooksFilterBy.ByTags:
return(books.Where(x => x.TagsString.Contains($"| {filterValue} |")));
case BooksFilterBy.ByPublicationYear:
var now = DateTime.UtcNow;
if (filterValue == AllBooksNotPublishedString)
{
return(books.Where(
x => x.PublishedOn > now));
}
var filterYear = int.Parse(filterValue);
return(books.Where(
x => x.YearPublished == filterYear &&
x.PublishedOn <= now));
default:
throw new ArgumentOutOfRangeException
(nameof(filterBy), filterBy, null);
}
}
public static async Task <IEnumerable <DropdownTuple> > GetFilterDropDownValuesAsync(this CosmosDbContext context,
BooksFilterBy filterBy, string databaseName)
{
var container = context.GetCosmosContainerFromDbContext(databaseName);
switch (filterBy)
{
case BooksFilterBy.NoFilter:
//return an empty list
return(new List <DropdownTuple>());
case BooksFilterBy.ByVotes:
return(FormVotesDropDown());
case BooksFilterBy.ByTags:
var tagResults = container.GetItemQueryIterator <string>(
new QueryDefinition("SELECT DISTINCT value f.TagId FROM c JOIN f in c.Tags"));
var tags = (await tagResults.ReadNextAsync()).OrderBy(x => x).ToList();
return(tags
.Select(x => new DropdownTuple
{
Value = x,
Text = x
}).ToList());
case BooksFilterBy.ByPublicationYear:
var comingSoonResultSet = container.GetItemQueryIterator <int>(
new QueryDefinition($"SELECT value Count(c) FROM c WHERE c.YearPublished > {DateTime.Today:yyyy-MM-dd} OFFSET 0 LIMIT 1"));
var comingSoon = (await comingSoonResultSet.ReadNextAsync()).First() > 0;
var now = DateTime.UtcNow;
var resultSet = container.GetItemQueryIterator <int>(
new QueryDefinition($"SELECT DISTINCT VALUE c.YearPublished FROM c WHERE c.YearPublished > {now:yyyy-mm-dd}"));
var years = (await resultSet.ReadNextAsync()).ToList();
var nextYear = DateTime.UtcNow.AddYears(1).Year;
var result = years
.Where(x => x < nextYear)
.OrderByDescending(x => x)
.Select(x => new DropdownTuple
{
Value = x.ToString(),
Text = x.ToString()
}).ToList();
if (comingSoon)
{
result.Insert(0, new DropdownTuple
{
Value = DisplayConstants.AllBooksNotPublishedString,
Text = DisplayConstants.AllBooksNotPublishedString
});
}
return(result);
default:
throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
/// <summary>
/// This makes the various Value + text to go in the dropdown based on the FilterBy option
/// </summary>
/// <param name="filterBy"></param>
/// <returns></returns>
public async Task <IEnumerable <DropdownTuple> > GetFilterDropDownValuesAsync(BooksFilterBy filterBy)
{
switch (filterBy)
{
case BooksFilterBy.NoFilter:
//return an empty list
return(new List <DropdownTuple>());
case BooksFilterBy.ByVotes:
return(FormVotesDropDown());
case BooksFilterBy.ByTags:
if (_sqlContext == null)
{
throw new NotImplementedException();
}
return(_sqlContext.Tags
.Select(x => new DropdownTuple
{
Value = x.TagId,
Text = x.TagId
}).ToList());
case BooksFilterBy.ByPublicationYear:
var container = _db.GetCosmosContainerFromDbContext(
_settings.CosmosDatabaseName);
var comingSoonResultSet =
container.GetItemQueryIterator <int>(
new QueryDefinition("SELECT value Count(c) FROM c WHERE" +
$" c.YearPublished > {DateTime.Today:yyyy-MM-dd} " +
"OFFSET 0 LIMIT 1"));
var comingSoon = (await
comingSoonResultSet.ReadNextAsync())
.First() > 0;
var now = DateTime.UtcNow;
var resultSet = container.GetItemQueryIterator <int>(
new QueryDefinition("SELECT DISTINCT VALUE c.YearPublished " +
$"FROM c WHERE c.YearPublished > {now:yyyy-mm-dd}"));
var years = (await resultSet.ReadNextAsync()).ToList();
var nextYear = DateTime.UtcNow.AddYears(1).Year;
var result = years
.Where(x => x < nextYear)
.OrderByDescending(x => x)
.Select(x => new DropdownTuple
{
Value = x.ToString(),
Text = x.ToString()
}).ToList();
if (comingSoon)
{
result.Insert(0, new DropdownTuple
{
Value = DisplayConstants.AllBooksNotPublishedString,
Text = DisplayConstants.AllBooksNotPublishedString
});
}
//This was the old one that took too long
//var now = DateTime.UtcNow;
//var comingSoon = await _db.Books
// .FirstOrDefaultAsync(x => x.PublishedOn > now) != null;
//var nextYear = DateTime.UtcNow.AddYears(1).Year;
//var allYears = await _db.Books
// .Select(x => x.YearPublished)
// .Distinct().ToListAsync();
////see this issue in EF Core about why I had to split the query - https://github.com/aspnet/EntityFrameworkCore/issues/16156
//var result = allYears
// .Where(x => x < nextYear)
// .OrderByDescending(x => x)
// .Select(x => new DropdownTuple
// {
// Value = x.ToString(),
// Text = x.ToString()
// }).ToList();
//if (comingSoon)
// result.Insert(0, new DropdownTuple
// {
// Value = BookListDtoFilter.AllBooksNotPublishedString,
// Text = BookListDtoFilter.AllBooksNotPublishedString
// });
return(result);
default:
throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
public static IQueryable <BookListDto> FilterBooksBy(this IQueryable <BookListDto> books, BooksFilterBy filterBy, string filterValue)
{
if (string.IsNullOrEmpty(filterValue))
{
return(books);
}
switch (filterBy)
{
case BooksFilterBy.NoFilter:
return(books);
case BooksFilterBy.ByVotes:
var filterVotes = int.Parse(filterValue);
return(books.Where(x => x.ReviewAverageVotes > filterVotes));
case BooksFilterBy.ByPublicationYear:
var filterYear = int.Parse(filterValue);
return(books.Where(x => x.PublishedOn.Year == filterYear));
case BooksFilterBy.ContainsString:
// Note: Contains, StartsWith and EndsWith are the only methods that can be translated to SQL
// to compare strings
return(books.Where(x => x.Title.Contains(filterValue)));
default:
throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
public JsonResult OnPost(BooksFilterBy filterBy)
{
return(new JsonResult(_filterService.GetFilterDropDownValues(filterBy)));
}
/// <summary>
/// This makes the various Value + text to go in the dropdown based on the FilterBy option
/// </summary>
/// <param name="filterBy"></param>
/// <returns></returns>
public async Task <IEnumerable <DropdownTuple> > GetFilterDropDownValuesAsync(BooksFilterBy filterBy)
{
switch (filterBy)
{
case BooksFilterBy.NoFilter:
//return an empty list
return(new List <DropdownTuple>());
case BooksFilterBy.ByVotes:
return(FormVotesDropDown());
case BooksFilterBy.ByPublicationYear:
var now = DateTime.UtcNow;
var comingSoon = _db.Books.Where(x => x.PublishedOn > now).Select(_ => 1).AsEnumerable().Any();
var nextYear = DateTime.UtcNow.AddYears(1).Year;
var allYears = await _db.Books
.Select(x => x.YearPublished)
.Distinct().ToListAsync();
//see this issue in EF Core about why I had to split the query - https://github.com/aspnet/EntityFrameworkCore/issues/16156
var result = allYears.Where(x => x < nextYear)
.OrderByDescending(x => x)
.Select(x => new DropdownTuple
{
Value = x.ToString(),
Text = x.ToString()
}).ToList();
if (comingSoon)
{
result.Insert(0, new DropdownTuple
{
Value = BookListDtoFilter.AllBooksNotPublishedString,
Text = BookListDtoFilter.AllBooksNotPublishedString
});
}
return(result);
default:
throw new ArgumentOutOfRangeException(nameof(filterBy), filterBy, null);
}
}
