Search From database and sort using Entity Framework

Question

In my project i have a textbox which is used for search now suppose if someone is searching hot pizza then i have to show them all result containing whether hot or pizza but i have to order them so than result containing how pizza comes first then shows result of hot and then pizza Currently I am Using this

  List<Project> allSearchedProject = new List<Project>();
  List<string> queryList = query.ToLower().Split(new char[] { '-' },                   StringSplitOptions.RemoveEmptyEntries).Where(q => q.Length > 3).ToList();

            foreach (var q in queryList)
            {
                var matchedProjects = (from project in unitOfWork.ProjectRepository
                                      .Find(p => p.IsActive && p is Project && new[] { p.Name, p.ProjectAddress.City.Name, p.ProjectAddress.Address1 }
                                       .Any(prjt=>prjt.Contains(q)))
                                       select project as Project).AsParallel().ToList();
                allSearchedProject.AddRange(matchedProjects);
            }

            return allSearchedProject;

but problem is this it hits database for every word, how to do get similar result in one hit can anyone tell me how to search and sort in single hit

-Thanks

Mashton · Accepted Answer · 2014-09-08T15:57:15.103

3

Change

foreach (var q in queryList)
            {
                var matchedProjects = (from project in unitOfWork.ProjectRepository
                                      .Find(p => p.IsActive && p is Project && new[] { p.Name, p.ProjectAddress.City.Name, p.ProjectAddress.Address1 }
                                       .Any(prjt=>prjt.Contains(q)))
                                       select project as Project).AsParallel().ToList();
                allSearchedProject.AddRange(matchedProjects);
            }

to

var matchedProjects = (from project in unitOfWork.ProjectRepository
    where project.IsActive 
      && project is Project
      && (queryList.Contains(project.Name) 
         || queryList.Contains(project.ProjectAddress.City.Name)
         || queryList.Contains(project.ProjectAddress.Address1))
    select project as Project).AsParallel().ToList();
    allSearchedProject.AddRange(matchedProjects);

Edit: removed projection and made contains searching explicit for fields on project being searched

edited Sep 08 '14 at 15:57

answered Sep 08 '14 at 15:10

Mashton

6,037
2
25
35

didn't get this Line .Any(queryList.Contains(prjt=>prjt)) and it has compile error too. – Vishal Pandey Sep 08 '14 at 15:15
Sorry, just realised a mistake in the `.Any` lambda, which is now corrected. – Mashton Sep 08 '14 at 15:16
thanks i have corrected it but it is not working do i have to convert it in ToLower() – Vishal Pandey Sep 08 '14 at 15:22
Edit made, now that I can see shape of data you were projecting into. Note projection part removed from `Find` – Mashton Sep 08 '14 at 15:32
it's working fine but in Sql it search like (([Filter1].[Name1] IN (N'vishal')) AND ([Filter1].[Name1] IS NOT NULL)) but what i need is (([Filter1].[Name1] Like (N'%vishal%')) AND ([Filter1].[Name1] IS NOT NULL)) – Vishal Pandey Sep 09 '14 at 08:10
That is a different question. http://stackoverflow.com/questions/3748289/linq2sql-like-command-for-phrase leads to http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods.like.aspx – Mashton Sep 09 '14 at 08:34
.net doesn't support LIKE in LINQ to entities. see this http://social.msdn.microsoft.com/Forums/en-US/5877ea99-cf4a-470b-ad99-54ff9893b490/linq-to-entities-does-not-recognize-the-method-boolean-likestring?forum=adodotnetentityframework – Vishal Pandey Sep 09 '14 at 08:52
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/60875/discussion-between-vishal-pandey-and-mashton). – Vishal Pandey Sep 09 '14 at 08:56

Search From database and sort using Entity Framework

1 Answers1