CODEWITHSUNDEEP
c#asp.netlinqentity-frameworkef-code-first
jackncoke
jackncoke

Reputation: 2020

Optimizing Search Results using LINQ

I have a search that uses pagination. My Queries bring back more results then I can list per page. I am afraid that down the road as my site scales this could become a performance issue. I am unsure how to effectively attack this problem and was hoping for some guidance. Below is the method that i call to perform my query. 

   private List<dynamic> AdminSearchAll(string keyword)
        {
            string DDL = DDLAddDivision.SelectedValue;
            int DDLInt;
            int searchID = 0;

            if (int.TryParse(DDL, out DDLInt))
            {
                //int searchID;
                if (!int.TryParse(keyword, out searchID))
                    searchID = -1;  // set to an invalid ID
            }

            ItemContext db = new ItemContext();
            var contacts = (from c in db.Contacts
            join cat in db.Categories on c.CategoryID equals cat.CategoryID
            join div in db.Divisions on c.DivisionID equals div.DivisionID

                            where
                 (DDLInt == 1 || c.DivisionID == DDLInt) &&
                    (c.Deleted == false) &&
                                //Contains    
                    (
                     c.ContactName.Contains(keyword) ||
                     c.ContactEmail.Contains(keyword) ||
                     c.ContactOPhone.Contains(keyword) ||
                     c.ContactID.Equals(searchID)

                       )
                            select new
                            {

                                Name = c.ContactName,
                                Phone = c.ContactOPhone,
                                Type = c.Type,
                                Email = c.ContactEmail,
                                ID = c.ContactID

                            });
            var items = (from i in db.Item
            join cat in db.Categories on i.CategoryID equals cat.CategoryID
            join div in db.Divisions on i.DivisionID equals div.DivisionID
                         where
                           (DDLInt == 1 || i.DivisionID == DDLInt) &&
                           (i.Deleted == false) &&

                          //Contains

                     (
        i.ItemName.Contains(keyword) ||
        i.Email.Contains(keyword) ||
        i.Description.Contains(keyword) ||
        i.ItemID.Equals(searchID)
                      )
                         select new
                         {

                             Name = i.ItemName,
                             Phone = i.Phone,
                             Type = i.Type,
                             Email = i.Email,
                             ID = i.ItemID
                         });

            var all = contacts.Union(items);

            return all.ToList<dynamic>();

I use a Asp.net Data Pager for my Pagination. I display 10 results per page. My Data Pager uses a method OnPreRender="Pager_PreRender"

protected void Pager_PreRender(object sender, EventArgs e)
        {
    if (IsPostBack)
                {


                        string keyword = txtSearch.Text.Trim();


                        List<dynamic> Cresults = AdminSearchAll(keyword);

    }
}

My question is what is the correct approach using LINQ to SQL to limit my Queries to RETURN only ten results as I page instead of returning all results and only displaying 10 because of the pager. PS I apologize for all the code but I wanted you to see exactly what I was doing.

Upvotes: 1

Views: 541

Answers (1)

Jeric Cantos
Jeric Cantos

Reputation: 278

You can implement your pagination at the LINQ level instead of delegating it to the data pager, but it would take a little bit of re-coding. LINQ allows you to perform a Skip-Take operation where you can specify appropriate page sizes to, well, skip and take.

There are many ways to implement this, but since this is the only part of the code I see, here's what I would suggest.

  1. Pass your page size and current page parameters to your search method
  2. Order your list so that proper pagination can be done by LINQ
  3. Use the Skip() and Take() methods after ordering

Point #1

private List<dynamic> AdminSearchAll(string keyword, int pageSize, int currentPage)

The search method needs to accept a pageSize and currentPage parameter which will be used later.

Point #2

var all = contacts.Union(items).OrderBy(a => a.Id);

Your list needs to be ordered so that when succeeding calls to the procedure occur, you will still be able to accurately locate what set you want to extract.

Point #3

var all = contacts.Union(items).OrderBy(a => a.Id).Skip(pageSize * currentPage)
                  .Take(pageSize);

The Skip(int X) method tells LINQ that you want to pass-over the first X number of entries. In this case, we skipped pageSize * currentPage. If you're on the very first page (index 0), no items will be skipped. The Take (int X) method tells LINQ that you only want to extract X number of elements from the starting index, which corresponds to your page size.

When you return your "all.ToList()", it should only contain as many elements as indicated in your pageSize parameter. On your main application, you should be able to easily keep track of your pageSize and currentPage per session.

Upvotes: 2

Related Questions