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. 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.

 

 

 

Choose the project type as MVC.

 

The project is created and opens as shown below.

 

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.

 

 

 

 

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

 

 

Right-click on Tables >> New Query.

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.

CREATE TABLE [dbo].[Category] (
    [CategoryId]   INT           NOT NULL,
    [CategoryName] NVARCHAR (30) NOT NULL,
    [Remark]       NVARCHAR (50) NOT NULL,
    PRIMARY KEY CLUSTERED ([CategoryId] ASC)
);

INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (1, N'Computer', N'Desktop & Laptopd')
INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (2, N'Storage', N'Pendrive & HardDisk')
INSERT INTO [dbo].[Category] ([CategoryId], [CategoryName], [Remark]) VALUES (3, N'Acssories', N'Mouse etc')

CREATE TABLE [dbo].[Product] (
    [ProductId]    INT           NOT NULL,
    [ProductName]  NVARCHAR (30) NOT NULL,
    [Price]        DECIMAL (18)  NOT NULL,
    [ReorderLevel] INT           NOT NULL,
    [CatId]        INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([ProductId] ASC),
    CONSTRAINT [fk_cat_product_id] FOREIGN KEY ([CatId]) REFERENCES [dbo].[Category] ([CategoryId]) ON DELETE CASCADE
);

INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (1, N'Lenovo5000', CAST(25500 AS Decimal(18, 0)), 24, 1)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (2, N'SanDisk Penrive', CAST(250 AS Decimal(18, 0)), 300, 2)
INSERT INTO [dbo].[Product] ([ProductId], [ProductName], [Price], [ReorderLevel], [CatId]) VALUES (3, N'sonyharddisk', CAST(3000 AS Decimal(18, 0)), 31, 2)
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.

 

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.

 

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.

 

Here is the code for the ProductWithCategory View Model Class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace ShoppingCart.Models
{
    public class ProductWithCategoryVM
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public decimal Price { get; set; }
        public int ReorderLevel { get; set; }
        public string CategoryName { get; set; }
        public Product Product { get; set; }
    }
}

Code for Controller Home Controller is given below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ShoppingCart.Models;

namespace ShoppingCart.Controllers
{
    public class HomeController : Controller
    {
        DBContext db = new DBContext();
        public ActionResult Products(string q, string S)
        {
            int id = Convert.ToInt32(Request["SearchType"]);
            var searchParameter = "Searching";
            var ProductWithCategoryVMlist = (from P in db.Products
                                             join C in db.Categories on
              P.CatId equals C.CategoryId

                                             select new ProductWithCategoryVM
                                             {
                                                 ProductId = P.ProductId,
                                                 ProductName = P.ProductName,
                                                 Price = P.Price,
                                                 ReorderLevel = P.ReorderLevel,
                                                 CategoryName = P.Category.CategoryName
                                             });
            if (!string.IsNullOrWhiteSpace(q))
            {
                switch (id)
                {
                    case 0:
                        int iQ = int.Parse(q);
                        ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.ProductId.Equals(iQ));
                        searchParameter += " ProductId for ' " + q + " '";
                        break;
                    case 1:
                        ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.ProductName.Contains(q));
                        searchParameter += " Product Name for ' " + q + " '";
                        break;
                    case 2:
                        ProductWithCategoryVMlist = ProductWithCategoryVMlist.Where(p => p.CategoryName.Contains(q));
                        searchParameter += " Category Name for '" + q + "'";
                        break;
                }
            }
            else
            {
                searchParameter += "ALL";
            }
            ViewBag.SearchParameter = searchParameter;
           return View(ProductWithCategoryVMlist.ToList()); //List of Products with Category Name)
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }


        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
    }
}

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

@model List<ShoppingCart.Models.ProductWithCategoryVM>

@{
    ViewBag.Title = "Product List";
    Layout = null;

}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style type="text/css">
        .webGrid {
            margin: 4px;
            border-collapse: collapse;
            width: 700px;
            font-family: Tahoma;
            font-size: small;
        }

        .grid-header {
            background-color: #990000;
            font-weight: bold;
            color: White !important;
        }

        .webGrid th a {
            color: White;
            text-decoration: none;
        }

        .webGrid th, .webGrid td {
            border: 1px solid black;
            padding: 5px;
        }

        .alt {
            background-color: #F4EFEF;
        }

        .webGrid th a:hover {
            text-decoration: underline;
        }

        .to-the-right {
            text-align: right;
        }
    </style>
</head>
<body>

    <center>

        <h2>Custom Search for Product</h2>
        @using (Html.BeginForm("Products", "Home", FormMethod.Get, new { @class = "Search-form" }))
        {
            <div id="txtBox">
                @Html.Label("Search Products ")
                <input type="text" name="q" />
            </div>
            <div id="radioList">
                @Html.RadioButton("SearchType", "0")
                @Html.Label("ID")
                @Html.RadioButton("SearchType", "1", true)
                @Html.Label("Product Name")
                @Html.RadioButton("SearchType", "2")
                @Html.Label("Category Name")
            </div>
            <div id="btnSearch">
                <input type="submit" value="Search" id="btnSubmit" />
            </div>
        }
        <br />
        <div id="DivGrid">
            @{
                           var grid = new WebGrid(source: Model, canPage: true, rowsPerPage: 2, defaultSort: "ProductId");
                           if (Model.Count() > 0)
                     {
                                      <div><strong> @ViewBag.SearchParameter</strong> | @grid.TotalRowCount @Html.Label("Record(s) found")</div>
                                    @grid.GetHtml(
        tableStyle: "webGrid",
        headerStyle: "grid-header",
        rowStyle: "gridRow",
        alternatingRowStyle: "alt",
        mode: WebGridPagerModes.All,
        firstText: "<< First",
        previousText: " < Previous",
        nextText: "Next >",
        lastText: "Last >>",
        caption: "Products",
         columns: grid.Columns(

                                            grid.Column("ProductId", "Product Id"),
                                            grid.Column("ProductName", "Product Name"),
                                            grid.Column("Price", "Price"),
                                            grid.Column("ReorderLevel", "Reorder Level"),
                                            grid.Column("CategoryName", "Category"),
                                            grid.Column(header: "Action", format: (item) =>
                                               {
                                                   var link = Html.ActionLink("Edit", "Edit", new { id = item.ProductId });
                                                   return link;
                                               }),

                                             grid.Column(header: "Action", format: (item) =>
                                               {
                                                    var link = Html.ActionLink("Delete", "Delete", new { id = item.ProductId });
                                                    return link;
                                               }),
                                             grid.Column(header: "Action", format: @<text>@Html.ActionLink("Select", null, null, new { @onclick = "return GetSelectedRow(this);" })</text>)
                                               ))
                                    <script type="text/javascript">
                                        function GetSelectedRow(link) {
                                            var row = link.parentNode.parentNode;
                                            var message = "Selected Row:";
                                            message += "\n\n Product Id: " + row.getElementsByTagName("TD")[0].innerHTML;
                                            message += "\n Product Name: " + row.getElementsByTagName("TD")[1].innerHTML;
                                            message += "\n Price: " + row.getElementsByTagName("TD")[2].innerHTML;
                                            message += "\n ReorderLevel: " + row.getElementsByTagName("TD")[3].innerHTML;
                                            message += "\n Category Name: " + row.getElementsByTagName("TD")[4].innerHTML;
                                            alert(message);
                                            return false;
                                        }

                                    </script>
    }
    else
    {
                    <hr />@Html.Label("No, Record(s) not found")<hr />
                }
            }

        </div>
</center>
    <div>

            }
</div>
</body>
</html>

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

<connectionStrings>
    <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" />
</connectionStrings>

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