CODEWITHSUNDEEP
asp.net-mvclinq
Moe Bataineh
Moe Bataineh

Reputation: 1080

Building an Advanced Search Bar

I'm trying to figure out how I can make a advanced search feature on my website. The code I'm using right now is not efficient and creates a really expensive query. What would be a good resource/example on creating something like this:

My Search Controller:

public ActionResult Index(string q = null, string authors = null, string category = null, decimal rating = 0, string published = null, int completed = 0, int page = 0)
        {
            List<string> categories = new List<string>();
            List<string> authorss = new List<string>();
            DateTime DateBy = new DateTime();
            DateTime.TryParse(published, out DateBy);

            if(!string.IsNullOrEmpty(authors))
                authorss = authors.Split(',').ToList();
            if (!string.IsNullOrEmpty(category))
                categories = category.Split(',').ToList();


            IEnumerable<Comic> Comics = db.Comics.Where(i => i.Title.Contains(q)).Include(i => i.ComicRatings).Include(i => i.ComicAuthors).Include("ComicAuthors.User");

            if(authorss.Count() >= 1)
            {
                Comics = Comics.Where(i => i.ComicAuthors.Where(j => authorss.Contains(j.User.UserName)).GroupBy(j => j.Comic_Id).Where(j => j.Count() >= authorss.Count()).Any());
            }

            if (categories.Count() >= 1)
            {
                Comics = Comics.Where(i => i.ComicCategories.Where(j => categories.Contains(j.Category.Name)).GroupBy(j => j.Comic_Id).Where(j => j.Count() >= categories.Count()).Any());
            }

            if (rating != 0)
            {
                Comics = Comics.Where(i => i.ComicRatings.Where(j => j.Rating >= rating).Any());
            }

            if (completed == 1)
            {
                Comics = Comics.Where(i => i.Completed == false);
            }
            else if (completed == 2)
            {
                Comics = Comics.Where(i => i.Completed == true);
            }

            if (!string.IsNullOrEmpty(published))
            {
                Comics = Comics.Where(i => i.DatePublished >= DateBy);
            }

            if(page <= (Comics.Count() / 20))
                page = 0;

            Comics = Comics.Skip(page * 20).Take(20);

            IEnumerable<LocalComicCategoriesModel> testing = helper.getCategories();
            ViewSearchModel post = new ViewSearchModel
            {
                Comic = Comics.ToList(),
                Categories = testing
            };

            return View(post);
        }

Upvotes: 1

Views: 342

Answers (2)

JTMon
JTMon

Reputation: 3199

I think your main problem comes from the fact that you are retrieving too many comics and then trying to filter them. I would try to limit the numbers of comics I am retrieving from the database as a first step. To do this you can either build your query one filter at a time without actually causing it to execute (like you do with the use of Any() at the end of your calls) until the very end, or to build the query using predicate builder. Have a look at these two questions as they may provide all you need:

Creating dynamic queries with entity framework

and

Building dynamic where clauses in LINQ to EF queries

Upvotes: 0

spaceman
spaceman

Reputation: 1648

If you're trying to do a lot of text searching I would take a look at Lucene.Net Lucene is a non relational full text search engine, thats in use in a lot of places.

We spent ages trying to do text searching in sql and linq before throwing it all away and having a fully dedicated search system.

Upvotes: 1

Related Questions