public SearchResults <Book> SearchBooks([FromUrl] BookSearch searchParams) { searchParams = searchParams.DefaultIfNull(defaultOrderBy: "title"); //creates empty object if null and sets Take = 10 and default sort var session = OpenSession(); return(session.SearchBooks(searchParams)); }
public SearchResults <Book> SearchBooks([FromQuery] BookSearch searchParams) { searchParams = searchParams.DefaultIfNull(defaultOrderBy: "title"); //creates empty object if null and sets Take = 10 and default sort var session = OpenSession(); // Original method returns results with IBook entities var tempResults = session.SearchBooks(searchParams); // convert to Book dto objects var results = new SearchResults <Book>() { TotalCount = tempResults.TotalCount, Results = tempResults.Results.Select(b => b.ToModel()).ToList() }; return(results); }
public SearchResults <Book> SearchBooks([FromUrl] BookSearch searchParams) { searchParams = searchParams.DefaultIfNull(defaultOrderBy: "title"); //creates empty object if null and sets Take = 10 and default sort var session = OpenSession(); // Warning about substring match (LIKE): Be careful using it in real apps, against big tables // Match by fragment results in LIKE operator which NEVER works on real volumes. // For MS SQL, it is OK to do LIKE with pattern that does not start with % (so it is StartsWith(smth) operator). // AND column must be indexed - so server will use index. For match inside the string, LIKE is useless on big tables. // In our case, Title is indexed and we use StartsWith, so it's OK // An interesting article about speeding up string-match search in MS SQL: // http://aboutsqlserver.com/2015/01/20/optimizing-substring-search-performance-in-sql-server/ var categories = ConvertHelper.ParseEnumArray <BookCategory>(searchParams.Categories); var where = session.NewPredicate <IBook>() .AndIfNotEmpty(searchParams.Title, b => b.Title.StartsWith(searchParams.Title)) .AndIfNotEmpty(searchParams.MaxPrice, b => b.Price <= (Decimal)searchParams.MaxPrice.Value) .AndIfNotEmpty(searchParams.Publisher, b => b.Publisher.Name.StartsWith(searchParams.Publisher)) .AndIfNotEmpty(searchParams.PublishedAfter, b => b.PublishedOn.Value >= searchParams.PublishedAfter.Value) .AndIfNotEmpty(searchParams.PublishedBefore, b => b.PublishedOn.Value <= searchParams.PublishedBefore.Value) .AndIf(categories != null && categories.Length > 0, b => categories.Contains(b.Category)); // A bit more complex clause for Author - it is many2many, results in subquery if (!string.IsNullOrEmpty(searchParams.AuthorLastName)) { var qAuthBookIds = session.EntitySet <IBookAuthor>() .Where(ba => ba.Author.LastName.StartsWith(searchParams.AuthorLastName)) .Select(ba => ba.Book.Id); where = where.And(b => qAuthBookIds.Contains(b.Id)); } // Alternative method for author name - results in inefficient query (with subquery for every row) // if(!string.IsNullOrEmpty(authorLastName)) // where = where.And(b => b.Authors.Any(a => a.LastName == authorLastName)); //Use VITA-defined helper method ExecuteSearch - to build query from where predicate, get total count, // add clauses for OrderBy, Take, Skip, run query and convert to list of model objects with TotalCount var results = session.ExecuteSearch(where, searchParams, ibook => ibook.ToModel(), b => b.Publisher, _orderByMapping); return(results); }