How to get perfect Result within same table and result without duplicate record when searching via LINQ MVC[Answered] RSS

4 replies

Last post Nov 25, 2020 06:58 AM by Hermosa

‹ Previous Thread|Next Thread ›

Shortcuts

Hermosa

Member

1 Points

5 Posts

How to get perfect Result within same table and result without duplicate record when searching vi...

Nov 13, 2020 02:53 AM|Hermosa|LINK

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 }))

Sean Fang

Contributor

2840 Points

839 Posts

Re: How to get perfect Result within same table and result without duplicate record when searchin...

Nov 16, 2020 12:36 PM|Sean Fang|LINK

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

.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.

- Reply
Hermosa
Member

1 Points

5 Posts

Re: How to get perfect Result within same table and result without duplicate record when searchin...

Nov 18, 2020 06:56 AM|Hermosa|LINK

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
- Reply
Sean Fang
Contributor

2840 Points

839 Posts
Re: How to get perfect Result within same table and result without duplicate record when searchin...

Nov 19, 2020 08:42 AM|Sean Fang|LINK
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
- Reply
Hermosa
Member

1 Points

5 Posts

Re: How to get perfect Result within same table and result without duplicate record when searchin...

Nov 25, 2020 06:58 AM|Hermosa|LINK

Dear Sean,

Thank you so much.

‹ Previous Thread|Next Thread ›