public IPagedList<Product> Search(string searchText, int siteID, int pageNumber, int pageSize, bool showTrustedPrdocuts)

        {

            string sqlString = string.Format

            (@"

                SELECT product.ID 

                    FROM product 

                        INNER JOIN FREETEXTTABLE([Product], title, @p1) AS KEY_TBL ON product.[ID] = KEY_TBL.[KEY] 

                        INNER JOIN purchase on purchase.id = product.purchaseID

                    WHERE product.SiteID=@p0 AND product.isHidden=0  AND purchase.siteID=@p0 AND purchase.[status]=1 {0} 

                ORDER BY RANK DESC, product.id DESC OFFSET @p2 ROWS FETCH NEXT @p3 ROWS ONLY",

               !showTrustedPrdocuts ? " AND purchase.ForTrustedUsersOnly=0 " : ""

            );

​

            string sqlStringCount = string.Format

            (@"

                SELECT COUNT(product.ID) 

                    FROM product 

                        INNER JOIN FREETEXTTABLE([Product], title, @p1) AS KEY_TBL ON product.[ID] = KEY_TBL.[KEY] 

                        INNER JOIN purchase on purchase.id = product.purchaseID

                    WHERE product.SiteID=@p0 AND product.isHidden=0  AND purchase.siteID=@p0 AND purchase.[status]=1 {0} 

                ",

               !showTrustedPrdocuts ? " AND purchase.ForTrustedUsersOnly=0 " : ""

            );

​

            var productIds = Query<int>(sqlString, siteID, searchText, (pageNumber - 1) * pageSize, pageSize);

            var count = Query<int>(sqlStringCount, siteID, searchText, (pageNumber - 1) * pageSize, pageSize).FirstOrDefault();

​

            var products = Get(x => productIds.Contains(x.ID), includes: new List<Expression<Func<Product, object>>> { x => x.Purchase, x => x.Purchase.ActiveBuyout }).ToDictionary(x=> x.ID);

​

            List<Product> sortedProducts = new List<Product>();

​

            foreach (var pid in productIds)

            {

                if (products[pid] == null) continue;

                sortedProducts.Add(products[pid]);

            }

            return new PagedList<Product>

            (

                source: sortedProducts,

                totalCount: count,

                index: pageNumber - 1,

                pageSize: pageSize

            );

​

        }