locked
How to get perfect Result within same table and result without duplicate record when searching via LINQ MVC RRS feed

  • Question

  • User-1679375916 posted


    Dear all expert,

    I am a new  LINQ trainer and  trying to join 1 table in a query with Linq to get data from all combine 3 tables but unlucky getting a unique result on MVC web  . I getting mistake in my coding  for Controller , View and model part in MVC. Please help me on this issue and would be greatly appreciated. Thank You .

    Below is My SQL query result:

    <div> SELECT a.[Admin_Id] ,a.[Admin_Name], a.[Admin_Status],a.[Admin_Remarks] </div> <div>             ,a.[Admin_CreateBy] ,a2.[Admin_Name],a.[Admin_UpdateBy],a1.Admin_Name </div> <div>           FROM [ABC].[dbo].[Admin]a </div> <div>            join [ABC].[dbo].[Admin]a1 on a.[Admin_UpdateBy] = a1.Admin_Id </div> <div>            join [ABC].[dbo].[Admin]a2 on  a.[Admin_CreateBy] = a2.Admin_Id </div> <div> where a.[Admin_Name] like '%l%' or a.[Admin_Remarks] like '%l%'</div> <div> or a1.[Admin_Name] like '%l%' or a2.[Admin_Name] like '%l%'</div> <div></div> <div>SQL Result : </div> <div>

    Admin_Id Admin_Name Admin_Status Admin_Remarks Admin_CreateBy Admin_Name Admin_CreateDate Admin_UpdateBy Admin_Name Admin_UpdateDate
    A0005 Admin L 1 Superadmin3 A0001 ABCDE 57:22.5 A0001 ABCDE 40:47.9
    A0010 Admin C 2 NULL A0005 Admin L 53:32.4 A0001 ABCDE 54:51.3
    A0011 Admin D 2 NULL A0005 Admin L 55:59.9 A0001 ABCDE 10:45.3

    </div>

    Now, i am getting unique result from Web that Create By and Update By Column is null value and sometime will get duplicate record when searching by name . example : "a"

    Name Status Remarks Create By Admin_CreateDate Update By Admin_UpdateDate
    Admin L Active Superadmin3   14/10/2020   14/10/2020
    Admin C InActive   Admin L 20/10/2020   27/10/2020
    Admin D InActive   Admin L 20/10/2020   27/10/2020

    Here Coding in Model Part:

    using ABC.Edmx; using PagedList; using System.Collections.Generic; namespace ABC {     public class EntityWM     {             //Get A row DB         public Status status { get; set; }         public Admin AdminModel { get; set; }  // a row from db                // get whole list DB         public List<Status> statusList { get; set; }         public AdminModelList { get; set; }                    // pager list         public IPagedList<Admin> AdminPageList { get; set; }          }}

    Coding in Controller :

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Net;
    using System.Web.Mvc;
    using ABC.Edmx;
    using ABC.Models;
    using PagedList;
    namespace ABC.Controllers
    {
        public class AdminController : Controller
        {
            private ABCEntities _edmx = new ABCEntities();
         
    
            // GET: Admin
            [HttpGet]
            public ActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
    
            {
               
                EntityWM _Rmodel = null;
                _Rmodel = new EntityWM();
                
               
                _Rmodel.AdminModelList = _edmx.Admins.ToList().Select(p => new Admin
                {
                    Admin_Id = p.Admin_Id,
                    Admin_Name = p.Admin_Name,
                    Admin_Status = p.Admin_Status,
                    Admin_Remarks = p.Admin_Remarks,
                    Admin_CreateBy = p.Admin_CreateBy,
                    Admin_CreateDate = p.Admin_CreateDate,
                    Admin_UpdateBy = p.Admin_UpdateBy,
                    Admin_UpdateDate = p.Admin_UpdateDate
    
                }).ToList();
    
                _Rmodel.statusList = _edmx.Status.ToList().Select(p => new Status
                {
                    Status_Id = p.Status_Id,
                    Status_Desc = p.Status_Desc
                }).ToList();
                
              
                if (searchString != null)
                {
                    page = 1;
                }
                else
                {
                    searchString = currentFilter;
                }
    
                ViewBag.CurrentFilter = searchString;
    
                /*
                SELECT a.[Admin_Id] ,a.[Admin_Name] ,a.[Admin_Password],a.[Admin_Status],a.[Admin_Remarks]
                 ,a.[Admin_CreateBy] ,a.[Admin_CreateDate],a2.[Admin_Name],a.[Admin_UpdateBy],a1.Admin_Name
                 ,a.[Admin_UpdateDate]
                FROM [OnlineTest_DEV2].[dbo].[Admin]a
                join [OnlineTest_DEV2].[dbo].[Admin]a1 on a.[Admin_UpdateBy] = a1.Admin_Id 
                join [OnlineTest_DEV2].[dbo].[Admin]a2 on  a.[Admin_CreateBy] = a2.Admin_Id
                */
    
                if (!String.IsNullOrEmpty(searchString))
    
                {
                    _Rmodel.AdminModelList = (from s in _Rmodel.AdminModelList
                                              join a in _Rmodel.AdminModelList on s.Admin_CreateBy equals a.Admin_Id into AdminTable1
                                              from a in AdminTable1.ToList()
                                              join a1 in _Rmodel.AdminModelList on s.Admin_UpdateBy equals a1.Admin_Id into AdminTable2
                                              from a1 in AdminTable2.ToList()
                                              where s.Admin_Name?.ToLower().Contains(searchString.ToLower()) == true
                   || s.Admin_Remarks?.ToLower().Contains(searchString.ToLower()) == true ||
                   a.Admin_Name?.ToLower().Contains(searchString.ToLower()) == true
                   || a1.Admin_Name?.ToLower().Contains(searchString.ToLower()) == true
                                              select new Admin
                                              {
                                                  Admin_Id = s.Admin_Id,
                                                  Admin_Name = s.Admin_Name,
                                                  Admin_Status = s.Admin_Status,
                                                  Admin_Remarks = s.Admin_Remarks,
                                                  Admin_CreateDate = s.Admin_CreateDate,
                                                  Admin_UpdateDate = s.Admin_UpdateDate,
                                                  Admin_CreateBy = s.Admin_CreateBy,
                                                  Admin_UpdateBy = s.Admin_UpdateBy
                                                  //Admin_UpdateBy = a1.Admin_Id,
                                                  //Admin_CreateBy = a.Admin_Id
    
                                              }).ToList();
                }
    
                int pageSize = 5;
                int pageNumber = (page ?? 1);
    
                _Rmodel.AdminPageList = _Rmodel.AdminModelList.ToPagedList(pageNumber, pageSize);
    
                return View(_Rmodel);
            }
    



    -------------------------------------------------------------------------------------------------------

    Coding in csthml:

    @model ABC.Models.EntityWM
    
    @using PagedList.Mvc;
    <link href="~/Content/PagedList.css" rel="stylesheet" type="text/css" />
    
    <h2>Admin Index</h2>
    
    @using (Html.BeginForm("Index", "Admin", FormMethod.Get))
    {
        <p>
            @Html.TextBox("searchString", ViewBag.CurrentFilter as string, new { @placeholder = "Search..." })
            <button type="submit" class="btn btn-success"><span class="glyphicon glyphicon-search"></span></button>
            <a class="btn btn-success pull-right" data-modal="" onclick="@("window.location.href='" + @Url.Action("Create", "Admin") + "'")" id="btnCreate">
                <span class="glyphicon glyphicon-plus"></span>
            </a>
        </p>
    
    }
    
    <table class="table">
        <tr>
            <th>
              
                @Html.ActionLink(Model.ResourcesModel.FirstOrDefault(x => x.Resources_Name == "Name").Resources_Value, "Index", new { sortOrder = ViewBag.NameSortParm, currentFilter = ViewBag.CurrentFilter })
               </th>
               <th>
                @Html.ActionLink(Model.ResourcesModel.FirstOrDefault(x => x.Resources_Name == "Status").Resources_Value, "Index", new { sortOrder = ViewBag.StatusSortParm, currentFilter = ViewBag.CurrentFilter })
             </th>
            <th>
                @Html.ActionLink("Remarks", "Index", new { sortOrder = ViewBag.RemarksSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th>
                @Html.ActionLink("Create By", "Index", new { sortOrder = ViewBag.CreateBySortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th>
               
                @Html.ActionLink("Create Date", "Index", new { sortOrder = ViewBag.CreateDateSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th>
                @Html.ActionLink("Update By", "Index", new { sortOrder = ViewBag.UpdateBySortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th>
                @Html.ActionLink("Update Date", "Index", new { sortOrder = ViewBag.UpdateDateSortParm, currentFilter = ViewBag.CurrentFilter })
            </th>
            <th></th>
        </tr>
    
        @foreach (var item in Model.AdminPageList)
    
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Admin_Name)
    
                </td>
              
                <td>
                    @Html.DisplayFor(modelItem => Model.statusList.FirstOrDefault(x => x.Status_Id == item.Admin_Status).Status_Desc)
               </td>
    
                <td>
                        @Html.DisplayFor(modelItem => item.Admin_Remarks)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => Model.AdminModelList.Where(x => x.Admin_Id == item.Admin_CreateBy).FirstOrDefault().Admin_Name)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.Admin_CreateDate)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => Model.AdminModelList.Where(x => x.Admin_Id == item.Admin_UpdateBy).FirstOrDefault().Admin_Name)
                    </td>
                    <td>
                        @Html.DisplayFor(modelItem => item.Admin_UpdateDate)
                    </td>
                    <td>
                        @Html.ActionLink(" ", "Edit", new { id = item.Admin_Id }, new { @class = "glyphicon glyphicon-edit" })
                        @Html.ActionLink(" ", "Details", new { id = item.Admin_Id }, new { @class = "glyphicon glyphicon-th-list" })
                        @Html.ActionLink(" ", "Delete", new { id = item.Admin_Id }, new { @class = "glyphicon glyphicon-trash" })
                    </td>
            </tr>
        }
    
    </table>
    <br />
    Page @( Model.AdminPageList.PageCount < Model.AdminPageList.PageNumber ? 0 : Model.AdminPageList.PageNumber) of @Model.AdminPageList.PageCount
    
    @Html.PagedListPager(Model.AdminPageList, page => Url.Action("Index",
        new { page, sortOrder = ViewBag.CurrentSort, currentFilter = ViewBag.CurrentFilter }))
    
    Friday, November 13, 2020 2:53 AM

Answers

  • User-1330468790 posted

    Hi Hermosa,

     

    I understand what you have done in LINQ codes.

    I tried the linq codes and the values were populated in the result list correctly. 

    Could you please confirm below points?

    1. the values are populated in the result list 
    2. and the error occurs in html page

       

    I notice that you render the 'CreateBy' and 'UpdateBy' value in the page using below codes.

     <td>
    @Html.DisplayFor(modelItem => Model.AdminModelList.Where(x => x.Admin_Id == item.Admin_CreateBy).FirstOrDefault().Admin_Name)
    </td>
    <td>
    @Html.DisplayFor(modelItem => Model.AdminModelList.Where(x => x.Admin_Id == item.Admin_UpdateBy).FirstOrDefault().Admin_Name)
    </td>

    However, the problem is that 'Model.AdminModelList' contains filtered results so that you could only get user name/Id for filtered result.

    For example, the results contains below three rows but the Admin 'ABCDE' (ID - 'A0001') is not included in that list. 

    Admin_Id Admin_Name
    A0005 Admin L
    A0010 Admin C
    A0011 Admin D

    Hence, you could not get Admin 'ABCDE' using where-clause.

     

    The solution is to get whole information from LINQ and populate it directly 

    select new Admin
                             {
                                 Admin_Id = s.Admin_Id,
                                 Admin_Name = s.Admin_Name,
                                 Admin_Status = s.Admin_Status,
                                 Admin_Remarks = s.Admin_Remarks,
                                 Admin_CreateDate = s.Admin_CreateDate,
                                 Admin_UpdateDate = s.Admin_UpdateDate,
                                 Admin_CreateBy = a.Admin_Name,
                                 Admin_UpdateBy = a1.Admin_Name
    
                             }).ToList();
    <td>
                        @Html.DisplayFor(modelItem => item.Admin_CreateBy)
                    </td>
    <td>
                        @Html.DisplayFor(modelItem => item.Admin_UpdateBy)
                    </td>

     

    Hope helps.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 19, 2020 8:42 AM
  • User-1679375916 posted

    Dear Sean, 

    Thank you so much.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 25, 2020 6:58 AM

All replies

  • User-1330468790 posted

    Hermosa,

     

    From my understanding, currently your problem is that you can not get expected result from LINQ, correct?

    If so, let's make problem simpler, could you please provide a sample data so that we could reproduce the problem?

     

    I tried your codes with hand-made data and got it working correctly. The demo is a bit different:

    • I removed the join-into temporary identifier
    • I assigned the create-by and update-by with the admin names

    Codes:

     static void Main(string[] args)
            {
    
                EntityWM _Rmodel = new EntityWM();
    
                _Rmodel.AdminModelList = new List<Admin>
                {
                    new Admin{
                        Admin_Id = "A0001",
                        Admin_Name = "ABCDE",
                        Admin_Status = new Status{
                            Status_Id = 1,
                            Status_Desc="Good"
                        }
                    },
                    new Admin{
                        Admin_Id = "A0005",
                        Admin_Name = "Admin L",
                        Admin_Status = new Status{
                            Status_Id = 1,
                            Status_Desc="Good"
                        },
                        Admin_Remarks="Superadmin3",
                        Admin_CreateBy = "A0001",
                        Admin_CreateDate = new DateTime(2020,10,14),
                        Admin_UpdateBy = "A0001",
                        Admin_UpdateDate = new DateTime(2020,10,14)
    
                    },
                   new Admin{
                        Admin_Id = "A0010",
                        Admin_Name = "Admin C",
                        Admin_Status = new Status{
                            Status_Id = 2,
                            Status_Desc="Bad"
                        },
                        Admin_CreateBy = "A0005",
                        Admin_CreateDate = new DateTime(2020,10,20),
                        Admin_UpdateBy = "A0001",
                        Admin_UpdateDate = new DateTime(2020,10,27)
    
                    },
                   new Admin{
                        Admin_Id = "A0011",
                        Admin_Name = "Admin D",
                        Admin_Status = new Status{
                            Status_Id = 2,
                            Status_Desc="Bad"
                        },
                        Admin_CreateBy = "A0005",
                        Admin_CreateDate = new DateTime(2020,10,20),
                        Admin_UpdateBy = "A0001",
                        Admin_UpdateDate = new DateTime(2020,10,27)
    
                    }
                };
    
                string searchString = "a";
    
    
                var list = (from s in _Rmodel.AdminModelList
                             join a in _Rmodel.AdminModelList on s.Admin_CreateBy equals a.Admin_Id 
                             join a1 in _Rmodel.AdminModelList on s.Admin_UpdateBy equals a1.Admin_Id
                             where s.Admin_Name?.ToLower().Contains(searchString.ToLower()) == true
    || s.Admin_Remarks?.ToLower().Contains(searchString.ToLower()) == true ||
    a.Admin_Name?.ToLower().Contains(searchString.ToLower()) == true
    || a1.Admin_Name?.ToLower().Contains(searchString.ToLower()) == true
                             select new Admin
                             {
                                 Admin_Id = s.Admin_Id,
                                 Admin_Name = s.Admin_Name,
                                 Admin_Status = s.Admin_Status,
                                 Admin_Remarks = s.Admin_Remarks,
                                 Admin_CreateDate = s.Admin_CreateDate,
                                 Admin_UpdateDate = s.Admin_UpdateDate,
                                 Admin_CreateBy = a.Admin_Name,
                                 Admin_UpdateBy = a1.Admin_Name
    
                             }).ToList();
    
                Console.WriteLine("===============Original Data=================");
                foreach (var item in _Rmodel.AdminModelList)
    
                {
                    Console.WriteLine(item);
                }
    
               
    
                Console.WriteLine("===============List Data=================");
                foreach (var item in list)
                {
                    Console.WriteLine(item);
                }
    
                Console.ReadLine();
    
            }
    
    
            public class EntityWM
            {
                //Get A row DB         
                public Status status { get; set; }
                public Admin AdminModel { get; set; }  // a row from db  
    
                // get whole list DB         
                public List<Status> statusList { get; set; }
    
                public List<Admin> AdminModelList { get; set; }
    
    
            }
    
            public class Admin
            {
                public string Admin_Id { set; get; }
                public string Admin_Name { get; set; }
    
                public Status Admin_Status { get; set; }
    
                public string Admin_Remarks { get; set; }
    
                public string Admin_CreateBy { get; set; }
    
                public DateTime Admin_CreateDate { get; set; }
    
                public string Admin_UpdateBy { get; set; }
    
                public DateTime Admin_UpdateDate { get; set; }
    
                public override string ToString()
                {
                    StringBuilder sb = new StringBuilder();
                    sb.AppendLine("Admin_Id: " + Admin_Id);
                    sb.AppendLine("Admin_Name: " + Admin_Name);
                    sb.AppendLine("Admin_Status: " + Admin_Status.Status_Id);
                    sb.AppendLine("Admin_Remarks: " + Admin_Remarks);
                    sb.AppendLine("Admin_CreateBy: " + Admin_CreateBy);
                    sb.AppendLine("Admin_CreateDate: " + Admin_CreateDate.ToString());
                    sb.AppendLine("Admin_UpdateBy: " + Admin_UpdateBy);
                    sb.AppendLine("Admin_UpdateDate: " + Admin_UpdateDate.ToString());
                    return sb.ToString();
                }
    
            }
            public class Status
            {
                public int Status_Id { set; get; }
                public string Status_Desc { get; set; }
            }

    Result:

    ===============Original Data=================
    Admin_Id: A0001
    Admin_Name: ABCDE
    Admin_Status: 1
    Admin_Remarks:
    Admin_CreateBy:
    Admin_CreateDate: 0001/1/1 0:00:00
    Admin_UpdateBy:
    Admin_UpdateDate: 0001/1/1 0:00:00
    
    Admin_Id: A0005
    Admin_Name: Admin L
    Admin_Status: 1
    Admin_Remarks: Superadmin3
    Admin_CreateBy: A0001
    Admin_CreateDate: 2020/10/14 0:00:00
    Admin_UpdateBy: A0001
    Admin_UpdateDate: 2020/10/14 0:00:00
    
    Admin_Id: A0010
    Admin_Name: Admin C
    Admin_Status: 2
    Admin_Remarks:
    Admin_CreateBy: A0005
    Admin_CreateDate: 2020/10/20 0:00:00
    Admin_UpdateBy: A0001
    Admin_UpdateDate: 2020/10/27 0:00:00
    
    Admin_Id: A0011
    Admin_Name: Admin D
    Admin_Status: 2
    Admin_Remarks:
    Admin_CreateBy: A0005
    Admin_CreateDate: 2020/10/20 0:00:00
    Admin_UpdateBy: A0001
    Admin_UpdateDate: 2020/10/27 0:00:00
    
    ===============List Data=================
    Admin_Id: A0005
    Admin_Name: Admin L
    Admin_Status: 1
    Admin_Remarks: Superadmin3
    Admin_CreateBy: ABCDE
    Admin_CreateDate: 2020/10/14 0:00:00
    Admin_UpdateBy: ABCDE
    Admin_UpdateDate: 2020/10/14 0:00:00
    
    Admin_Id: A0010
    Admin_Name: Admin C
    Admin_Status: 2
    Admin_Remarks:
    Admin_CreateBy: Admin L
    Admin_CreateDate: 2020/10/20 0:00:00
    Admin_UpdateBy: ABCDE
    Admin_UpdateDate: 2020/10/27 0:00:00
    
    Admin_Id: A0011
    Admin_Name: Admin D
    Admin_Status: 2
    Admin_Remarks:
    Admin_CreateBy: Admin L
    Admin_CreateDate: 2020/10/20 0:00:00
    Admin_UpdateBy: ABCDE
    Admin_UpdateDate: 2020/10/27 0:00:00

     

    Best regards,

    Sean

    Monday, November 16, 2020 12:36 PM
  • User-1679375916 posted

    Dear Sean, 

    yes, i cannot get a expected result from LINQ, MVC.  i am still getting error in search function  part that createBy and updateBy is null value..

    please follow result on  first page i posted. Thank You

    Wednesday, November 18, 2020 6:56 AM
  • User-1330468790 posted

    Hi Hermosa,

     

    I understand what you have done in LINQ codes.

    I tried the linq codes and the values were populated in the result list correctly. 

    Could you please confirm below points?

    1. the values are populated in the result list 
    2. and the error occurs in html page

       

    I notice that you render the 'CreateBy' and 'UpdateBy' value in the page using below codes.

     <td>
    @Html.DisplayFor(modelItem => Model.AdminModelList.Where(x => x.Admin_Id == item.Admin_CreateBy).FirstOrDefault().Admin_Name)
    </td>
    <td>
    @Html.DisplayFor(modelItem => Model.AdminModelList.Where(x => x.Admin_Id == item.Admin_UpdateBy).FirstOrDefault().Admin_Name)
    </td>

    However, the problem is that 'Model.AdminModelList' contains filtered results so that you could only get user name/Id for filtered result.

    For example, the results contains below three rows but the Admin 'ABCDE' (ID - 'A0001') is not included in that list. 

    Admin_Id Admin_Name
    A0005 Admin L
    A0010 Admin C
    A0011 Admin D

    Hence, you could not get Admin 'ABCDE' using where-clause.

     

    The solution is to get whole information from LINQ and populate it directly 

    select new Admin
                             {
                                 Admin_Id = s.Admin_Id,
                                 Admin_Name = s.Admin_Name,
                                 Admin_Status = s.Admin_Status,
                                 Admin_Remarks = s.Admin_Remarks,
                                 Admin_CreateDate = s.Admin_CreateDate,
                                 Admin_UpdateDate = s.Admin_UpdateDate,
                                 Admin_CreateBy = a.Admin_Name,
                                 Admin_UpdateBy = a1.Admin_Name
    
                             }).ToList();
    <td>
                        @Html.DisplayFor(modelItem => item.Admin_CreateBy)
                    </td>
    <td>
                        @Html.DisplayFor(modelItem => item.Admin_UpdateBy)
                    </td>

     

    Hope helps.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, November 19, 2020 8:42 AM
  • User-1679375916 posted

    Dear Sean, 

    Thank you so much.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 25, 2020 6:58 AM