Displaying Data From Two Tables With Custom Search Using Entity Framework Database-First Method

This simple tutorial explains how to retrieve data from two one-to-many linked tables (category and product) and display in a web grid.

This simple tutorial explains how to retrieve data from two one-to-many linked tables (category and product) and display in a web grid. The data can be searched with multiple search facilities - search by id or search by part of name. Models were created using Entity Framework Database Method.

Software Required

  1. Visual Studio 2013 or above
  2. Entity Framework 6.0 (Available with Visual Studio)
  3. NET Framework 4.5 or above(Available with Visual Studio)
  4. SQL Server Database (Available with Visual Studio)

Start Visual Studio.

Then, create a new MVC project as displayed below, by going to File >> New >> Project.

Name the project as ShoppingCart.

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 
 
Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 

Choose the project type as MVC.

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 

The project is created and opens as shown below.

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 

For creating a database file, Store.mdf, right-click the App_Data directory and go to Add >> New Item. Choose SQL Server Database here. Name it as Store.mdf.

The Server Explorer window opens. Create two tables - Category and Product - and populate them by executing the queries given below.
 
Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 
 
Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 

The Store.mdf database file is created successfully. Right-click on Store.mdf file and click "Open".

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method
 
Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 

Right-click on Tables >> New Query.

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method

Copy and paste the below SQL for creating the "Category" table as above and execute it by clicking the arrow symbol on the left. The Category table will be created.

  1. CREATE TABLE [dbo].[Category] (  
  2.     [CategoryId]   INT           NOT NULL,  
  3.     [CategoryName] NVARCHAR (30) NOT NULL,  
  4.     [Remark]       NVARCHAR (50) NOT NULL,  
  5.     PRIMARY KEY CLUSTERED ([CategoryId] ASC)  
  6. );  
  7.   
  8. INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (1, N'Computer', N'Desktop & Laptopd')  
  9. INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (2, N'Storage', N'Pendrive & HardDisk')  
  10. INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (3, N'Acssories', N'Mouse etc')  
  11.   
  12. CREATE TABLE [dbo].[Product] (  
  13.     [ProductId]    INT           NOT NULL,  
  14.     [ProductName]  NVARCHAR (30) NOT NULL,  
  15.     [Price]        DECIMAL (18)  NOT NULL,  
  16.     [ReorderLevel] INT           NOT NULL,  
  17.     [CatId]        INT           NOT NULL,  
  18.     PRIMARY KEY CLUSTERED ([ProductId] ASC),  
  19.     CONSTRAINT [fk_cat_product_id] FOREIGN KEY ([CatId]) REFERENCES [dbo].[Category] ([CategoryId]) ON DELETE CASCADE  
  20. );  
  21.   
  22. INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (1, N'Lenovo5000'CAST(25500 AS Decimal(18, 0)), 24, 1)  
  23. INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (2, N'SanDisk Penrive'CAST(250 AS Decimal(18, 0)), 300, 2)  
  24. INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (3, N'sonyharddisk'CAST(3000 AS Decimal(18, 0)), 31, 2)  
  25. INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (4, N'Sony Mouse'CAST(350 AS Decimal(18, 0)), 21, 3)  

Insert the records by executing the INSERT query, as displayed below.

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 

Similarly, create the Product table and insert records.

Refresh the Tables folder in the Server Explorer window. The newly created tables can be seen there.

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 

Entity Framework database models are created by Database-First method.

 Please click here to go to my article describing how to create models from database tables using the Database-First method.

Creating View Model Class (ProductWithCategoryVM)

This class would be created by combining the properties from Category class and Product class. It would be used in the Controller as well as for creating the View.

Right-click on Models folder and go to Add >> Class.

Name it as ProductWithCategoryVM.

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method 

Here is the code for the ProductWithCategory View Model Class.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.ComponentModel.DataAnnotations;  
  6.   
  7. namespace ShoppingCart.Models  
  8. {  
  9.     public class ProductWithCategoryVM  
  10.     {  
  11.         public int ProductId { getset; }  
  12.         public string ProductName { getset; }  
  13.         public decimal Price { getset; }  
  14.         public int ReorderLevel { getset; }  
  15.         public string CategoryName { getset; }  
  16.         public Product Product { getset; }  
  17.     }  
  18. }  

Code for Controller Home Controller is given below.

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6. using ShoppingCart.Models;  
  7.   
  8. namespace ShoppingCart.Controllers  
  9. {  
  10.     public class HomeController : Controller  
  11.     {  
  12.         DBContext db = new DBContext();  
  13.         public ActionResult Products(string q, string S)  
  14.         {  
  15.             int id = Convert.ToInt32(Request["SearchType"]);  
  16.             var searchParameter = "Searching";  
  17.             var ProductWithCategoryVMlist = (from P in db.Products  
  18.                                              join C in db.Categories on  
  19.               P.CatId equals C.CategoryId  
  20.   
  21.                                              select new ProductWithCategoryVM  
  22.                                              {  
  23.                                                  ProductId = P.ProductId,  
  24.                                                  ProductName = P.ProductName,  
  25.                                                  Price = P.Price,  
  26.                                                  ReorderLevel = P.ReorderLevel,  
  27.                                                  CategoryName = P.Category.CategoryName  
  28.                                              });  
  29.             if (!string.IsNullOrWhiteSpace(q))  
  30.             {  
  31.                 switch (id)  
  32.                 {  
  33.                     case 0:  
  34.                         int iQ = int.Parse(q);  
  35.                         ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.ProductId.Equals(iQ));  
  36.                         searchParameter += " ProductId for ' " + q + " '";  
  37.                         break;  
  38.                     case 1:  
  39.                         ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.ProductName.Contains(q));  
  40.                         searchParameter += " Product Name for ' " + q + " '";  
  41.                         break;  
  42.                     case 2:  
  43.                         ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.CategoryName.Contains(q));  
  44.                         searchParameter += " Category Name for '" + q + "'";  
  45.                         break;  
  46.                 }  
  47.             }  
  48.             else  
  49.             {  
  50.                 searchParameter += "ALL";  
  51.             }  
  52.             ViewBag.SearchParameter = searchParameter;  
  53.            return View(ProductWithCategoryVMlist.ToList()); //List of Products with Category Name)  
  54.         }  
  55.   
  56.         public ActionResult About()  
  57.         {  
  58.             ViewBag.Message = "Your application description page.";  
  59.   
  60.             return View();  
  61.         }  
  62.           
  63.   
  64.         public ActionResult Contact()  
  65.         {  
  66.             ViewBag.Message = "Your contact page.";  
  67.   
  68.             return View();  
  69.         }  
  70.     }  
  71. }  

Code for View Products.cshtml page will be as below.

  1. @model List<ShoppingCart.Models.ProductWithCategoryVM>  
  2.   
  3. @{  
  4.     ViewBag.Title = "Product List";  
  5.     Layout = null;     
  6.   
  7. }  
  8.   
  9. <!DOCTYPE html>  
  10.   
  11. <html>  
  12. <head>  
  13.     <meta name="viewport" content="width=device-width" />  
  14.     <title>Index</title>  
  15.     <style type="text/css">  
  16.         .webGrid {  
  17.             margin: 4px;  
  18.             border-collapse: collapse;  
  19.             width: 700px;  
  20.             font-family: Tahoma;  
  21.             font-size: small;  
  22.         }  
  23.   
  24.         .grid-header {  
  25.             background-color: #990000;  
  26.             font-weight: bold;  
  27.             color: White !important;  
  28.         }  
  29.   
  30.         .webGrid th a {  
  31.             color: White;  
  32.             text-decoration: none;  
  33.         }  
  34.   
  35.         .webGrid th, .webGrid td {  
  36.             border: 1px solid black;  
  37.             padding: 5px;  
  38.         }  
  39.   
  40.         .alt {  
  41.             background-color: #F4EFEF;  
  42.         }  
  43.   
  44.         .webGrid th a:hover {  
  45.             text-decoration: underline;  
  46.         }  
  47.   
  48.         .to-the-right {  
  49.             text-align: right;  
  50.         }  
  51.     </style>  
  52. </head>  
  53. <body>  
  54.   
  55.     <center>  
  56.   
  57.         <h2>Custom Search for Product</h2>  
  58.         @using (Html.BeginForm("Products", "Home", FormMethod.Get, new { @class = "Search-form" }))  
  59.         {  
  60.             <div id="txtBox">  
  61.                 @Html.Label("Search Products ")  
  62.                 <input type="text" name="q" />  
  63.             </div>  
  64.             <div id="radioList">  
  65.                 @Html.RadioButton("SearchType", "0")  
  66.                 @Html.Label("ID")  
  67.                 @Html.RadioButton("SearchType", "1", true)  
  68.                 @Html.Label("Product Name")  
  69.                 @Html.RadioButton("SearchType", "2")  
  70.                 @Html.Label("Category Name")  
  71.             </div>  
  72.             <div id="btnSearch">  
  73.                 <input type="submit" value="Search" id="btnSubmit" />  
  74.             </div>  
  75.         }  
  76.         <br />  
  77.         <div id="DivGrid">  
  78.             @{  
  79.                            var grid = new WebGrid(source: Model, canPage: true, rowsPerPage: 2, defaultSort: "ProductId");  
  80.                            if (Model.Count() > 0)  
  81.                      {  
  82.                                       <div><strong> @ViewBag.SearchParameter</strong> | @grid.TotalRowCount @Html.Label("Record(s) found")</div>  
  83.                                     @grid.GetHtml(  
  84.         tableStyle: "webGrid",  
  85.         headerStyle: "grid-header",  
  86.         rowStyle: "gridRow",  
  87.         alternatingRowStyle: "alt",  
  88.         mode: WebGridPagerModes.All,  
  89.         firstText: "<< First",  
  90.         previousText: " < Previous",  
  91.         nextText: "Next >",  
  92.         lastText: "Last >>",  
  93.         caption: "Products",  
  94.          columns: grid.Columns(  
  95.   
  96.                                             grid.Column("ProductId", "Product Id"),  
  97.                                             grid.Column("ProductName", "Product Name"),  
  98.                                             grid.Column("Price", "Price"),  
  99.                                             grid.Column("ReorderLevel", "Reorder Level"),  
  100.                                             grid.Column("CategoryName", "Category"),  
  101.                                             grid.Column(header: "Action", format: (item) =>  
  102.                                                {  
  103.                                                    var link = Html.ActionLink("Edit", "Edit", new { id = item.ProductId });  
  104.                                                    return link;  
  105.                                                }),  
  106.   
  107.                                              grid.Column(header: "Action", format: (item) =>  
  108.                                                {  
  109.                                                     var link = Html.ActionLink("Delete", "Delete", new { id = item.ProductId });  
  110.                                                     return link;  
  111.                                                }),  
  112.                                              grid.Column(header: "Action", format: @<text>@Html.ActionLink("Select", null, null, new { @onclick = "return GetSelectedRow(this);" })</text>)  
  113.                                                ))  
  114.                                     <script type="text/javascript">  
  115.                                         function GetSelectedRow(link) {  
  116.                                             var row = link.parentNode.parentNode;  
  117.                                             var message = "Selected Row:";  
  118.                                             message += "\n\n Product Id: " + row.getElementsByTagName("TD")[0].innerHTML;  
  119.                                             message += "\n Product Name: " + row.getElementsByTagName("TD")[1].innerHTML;  
  120.                                             message += "\n Price: " + row.getElementsByTagName("TD")[2].innerHTML;  
  121.                                             message += "\n ReorderLevel: " + row.getElementsByTagName("TD")[3].innerHTML;  
  122.                                             message += "\n Category Name: " + row.getElementsByTagName("TD")[4].innerHTML;  
  123.                                             alert(message);  
  124.                                             return false;  
  125.                                         }  
  126.   
  127.                                     </script>          
  128.     }  
  129.     else  
  130.     {  
  131.                     <hr />@Html.Label("No, Record(s) not found")<hr />  
  132.                 }  
  133.             }            
  134.   
  135.         </div>  
  136. </center>  
  137.     <div>  
  138.         
  139.             }  
  140. </div>  
  141. </body>  
  142. </html>  

Connection String in Web.Config file can be set up using the following code.

  1. <connectionStrings>  
  2.     <add name="DBContext" connectionString="metadata=res://*/Models.StoreModel.csdl|res://*/Models.StoreModel.ssdl|res://*/Models.StoreModel.msl;provider=System.Data.SqlClient;provider connection string="data source=(LocalDB)\MSSQLLocalDB;attachdbfilename=|DataDirectory|\Store.mdf;integrated security=True;connect timeout=30;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />  
  3. </connectionStrings>  

Run the project. The result page is displayed below which contains the web grid with data and search by id and name functionality.

Data Displayed From Two Tables With Custom Search Functionality Using Entity Framework Database First Method