Click here to Skip to main content
15,064,915 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have 3 tables in my database which have no relation with each other. what i want is to implement the search operation on my website. So that when a word is submit in search box the query go through all the tables and fetch the data wherever it find that word. I can fetch the data from single table.


Please don't confuse about what i return in action method, its a very long and unnecessary code for that question.

By using this code I successfully get the search result from one table. Now I want same result from all the tables present in the database. that's where I'm stuck. I searched so many article for that but didn't find any solution that's in the last I myself asking this.

Thanks


What I have tried:

public PartialViewResult Searchpartial(string searchString)
		{

			
			var article = (from c in db.Tbl_Article select new 
                        SearchModel() { Tbl_Article = c });
			article = article.Where(s => 
                      s.Tbl_Article.Article_Title.Contains(searchString));

			var blog = (from c in db.Tbl_Blog select new SearchModel() { 
                        Tbl_Blog = c });
			blog = blog.Where(s => 
                             s.Tbl_Blog.Blog_Title.Contains(searchString));
 
			var history = (from c in db.Tbl_History select new 
                        SearchModel() { Tbl_History = c });
			history = history.Where(s => 
                          s.Tbl_History.Title.Contains(searchString));

			var result = article.Select(x => 
                            x.Tbl_Article.Article_Title).Union(blog.Select(x => 
                          x.Tbl_Blog.Blog_Title)).Union(history.Select(x => 
                              x.Tbl_History.Title)).ToList();
				

		
			


			if (result != null)
			{
				return PartialView("Searchpartial", result);
			}
			else
			{
				string res = "No Record Found";
				return PartialView("Searchpartial", res);
			}

		}



This is my partialView:


        @model IEnumerable<project.Models.SearchModel>
             @using Project.Models
           <div class="col-md-8">
            	@{


		if (Model.Count() == 0)
		{<br />
			<br />
			<br />
			<div class="col-md-12">
				<p>
					Sorry! No result found
				</p>
			</div>
		}
		else
		{<br />
			<br />
			<br />
			foreach (var item in Model)
			{

				<div class="col-md-12">
					<p>

		<a href="@Url.Action("Article","ArticleView", new { id = 
                           HttpUtility.UrlEncode(new 
                      StandardModule().Encrypt(item.Tbl_Article.Id.ToString())) 
                        })">@item.Tbl_Article.Article_Title</a>
		<a  href="@Url.Action("Blog","BlogView", new { id = 
                      HttpUtility.UrlEncode(new 
                    StandardModule().Encrypt(item.Tbl_Blog.Id.ToString())) 
                              })">@item.Tbl_Blog.Blog_Title</a>
		<a href="@Url.Action("History","HistoryView", new { id = 
                      HttpUtility.UrlEncode(new 
                      StandardModule().Encrypt(item.Tbl_History.Id.ToString())) 
                      })">@item.Tbl_History.Title</a>
					</p>
				</div>
			}
		}

	}
</div>



Model Class :

public class SearchModel
	{
		public Tbl_Article Tbl_Article { get; set; }
		public Tbl_Blog Tbl_Blog { get; set; }
		public Tbl_History Tbl_History { get; set; }	

	}
Posted
Updated 14-Jun-19 1:43am
v5

1 solution

Run the search three times, one for each table you want to search on. If you want to present a single result set then combine the three results into a single list and return that list.
   
Comments
Umair Nafis 14-Jun-19 5:33am
   
Thanks for the reply sir, I tried your suggestion , now i'm confused about how to display the result. I mean by your logic i got the final result from all table , now can you pleas tell me how to display that result in my view ?
F-ES Sitecore 14-Jun-19 5:46am
   
You have to make one list of results from the three separate results then bind your view to that list. I can't give specifics as I can't see your code. Update the original question to include any changes\new code.
Umair Nafis 14-Jun-19 7:26am
   
Sir I have updated the code plz check it.
F-ES Sitecore 14-Jun-19 8:55am
   
If you look at the type of "result" it is probably List<string> as you are selecting only the title properties in your Select so you get a list of strings.

Looking at your code and how you have structured SearchModel it makes no sense. It seems that you are expecting the exact same number of results from each of your three searches so you'll have three lists of, for example, 10 objects and you want a list of 10 SearchModel objects with the first SearchModel object containing a reference to the first item in each of your three separate lists, and I doubt your data is like that. If it is then you'll need to do a "for" loop looping through from 0 to n where n is the number of rows in one of your lists (it won't matter which as they are all the same size). In the for loop create a new instance of SearchModel and set

sm.Tbl_Article = article[i].SearchModel.Tbl_Article;
sm.Tbl_Blog = blog[i].SearchModel.Tbl_Blog;
sm.Tbl_History = history[i].SearchModel.Tbl_History;

Add each "sm" object to a new List of SearchModel and that will give you the data in a format you need for the view.

I suspect what you really want is something else though.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900