Beispiel #1
0
        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);
        }
Beispiel #3
0
        public void TestLinqSearch()
        {
            var app = SetupHelper.BooksApp;
              var session = app.OpenSession();
              //We use catalog controller's SearchBooks method to invoke search method.
              // This is also a demo of using Api controllers outside Web server environment
              var contr = new CatalogController(app.CreateSystemContext());

              //User search helper method with all possible search terms. Let's find c# book
              var searchParams = new BookSearch() {
            Title = "C#", Categories = "Programming,Fiction", MaxPrice = 100.0, Publisher = "MS",
              PublishedAfter = new DateTime(2000, 1, 1), PublishedBefore = DateTime.Now,
              AuthorLastName = "Sharp", OrderBy = "Price-desc,pubname,PublishedOn-desc",
              Skip = 0, Take = 5
              };
              var bookResults = contr.SearchBooks(searchParams);
              //PrintLastSql(session);
              Assert.AreEqual(1, bookResults.Results.Count, "Failed to find c# book");
              /* Here's the resulting SQL for MS SQL 2012:

            SELECT f$.[Id], f$.[Title], f$.[Description], f$.[PublishedOn], f$.[Abstract], f$.[Category], f$.[Editions], f$.[Price],
            f$.[CreatedIn], f$.[UpdatedIn], f$.[Publisher_Id]
              FROM [books].[Book] f$, [books].[Publisher] t1$
              WHERE (t1$.[Id] = f$.[Publisher_Id]) AND ((f$.[Title] LIKE @P5 ESCAPE '\') AND
            (f$.[Price] <= @P0) AND (t1$.[Name] LIKE @P6 ESCAPE '\') AND
            (f$.[PublishedOn] >= @P1) AND
            (f$.[PublishedOn] <= @P2) AND
            f$.[Category] IN (0, 1) AND
            (f$.[Id] IN (SELECT ba$.[Book_Id]
                     FROM [books].[BookAuthor] ba$, [books].[Author] t2$
                     WHERE (t2$.[Id] = ba$.[Author_Id]) AND (t2$.[LastName] LIKE @P7 ESCAPE '\'))))
              ORDER BY f$.[Price] DESC, t1$.[Name], f$.[PublishedOn] DESC
              OFFSET @P3 ROWS FETCH NEXT @P4 ROWS ONLY
            -- Parameters: @P0=100, @P1=[2000-01-01T00:00:00], @P2=[2015-02-28T02:05:42], @P3=0, @P4=5, @P5='c#%', @P6='MS%', @P7='Sharp%'
               */

              // run with empty terms, 'get-any-top-10'
              searchParams = new BookSearch() {Take = 10};
              bookResults = contr.SearchBooks(searchParams);
              Assert.IsTrue(bookResults.Results.Count > 3, "Must return all books");
        }
Beispiel #4
0
        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);
        }