Home > database >  How do I display unique database values from a column to avoid duplicates using LINQ with Razor? Dis
How do I display unique database values from a column to avoid duplicates using LINQ with Razor? Dis

Time:11-04

I have 2 issues, the first is I'm trying to avoid duplicate values and display SELECT DISTINCT Names in a column as shown in the image, there are 2 rows with the name Rob, I only want to display 1 since both rows have the same name.

My other issue is I'm trying to fill the textbox with the column information from the selected name that is clicked on

index.cshtml

@page
@model IndexModel
@{ ViewData["Title"] = "Index"; }

<form method="post">
    <table>
        <tr>
            <td>
                @Html.TextBox("TxtDepartment")
            </td>
            <td>
                <button type="button" id="DepartmentSearch">Search</button>
            </td>
        </tr>
    </table>
</form>
<br />

<table>
    <tr>
        <td><div id="DepartmentResult"></div></td>&nbsp;
        <td><div id="EmployeeResult"></div></td>
    </tr>
</table>

<form method="post">
    <label>Department Name:</label>
    <input type="text" id="DeptName" />
    <label>Photo File Name:</label>
    <input type="text" id="NameResult" />
</form>

@section Scripts {
    <script>
        $("#DepartmentSearch").click(function()
        {
            $.ajax(
            {
                url: "/Index?handler=DisplayDepartment",
                type: "POST",
                data: { value: $("#TxtDepartment").val() },
                headers: { RequestVerificationToken: $('input:hidden[name="__RequestVerificationToken"]').val() },
                success: function(data) { $("#DepartmentResult").html(data); }
            });
        });
    </script>
}

index.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using PracticeApp.Models;
using System.Linq;

namespace PracticeApp.Pages
{
    public class IndexModel : PageModel
    {
        public CompanyContext _context;

        public IndexModel(CompanyContext context) { _context = context; }

        public PartialViewResult OnGetDisplayDepartment(int value)
        {
            return Partial("_DisplayDepartmentPartial", _context.Departments.Where(x => x.DepartmentId == value).ToList());
        }

        public PartialViewResult OnGetDisplayEmployee(string value)
        {
            return Partial("_DisplayEmployeePartial", _context.Employees.Where(x => x.DepartmentName == value).ToList());
        }

        public PartialViewResult OnGetDisplayInfo(string value)
        {
            return Partial("_DisplayEmployeePartial", _context.Employees.Where(x => x.EmployeeName == value).ToString());
        }
    }
}

_DisplayDepartmentPartial.cshtml

@model IEnumerable<Models.Department>

@if (Model.Count() != 0)
 {
    <table style="border: 1px solid black">
        <thead>
            <tr>
                <th colspan="2" style="border: 1px solid black; text-align: center;">Department Results</th>
            </tr>
        </thead>

        <tbody>
            <tr>
                <td align="center" style="border: 1px solid black; font-weight: bold;">
                    @Html.DisplayNameFor(m => m.DepartmentName)
                </td>
            </tr>

            @foreach (Models.Department item in Model)
             {
                <tr>
                    <td id="TxtEmployee" align="center" style="border: 1px solid black;">
                        <a id="EmployeeSearch" href="javascript:aa()">@item.DepartmentName</a>
                    </td>
                </tr>
             }
        </tbody>
    </table>
 }
else
{
    <p>No data</p>
}

<script>
    $("#EmployeeSearch").click(function()
    {
        $.ajax(
        {
            url: "Index?handler=DisplayEmployee",
            type: "POST",
            data: { value: $("#EmployeeSearch").text() },
            headers: { RequestVerificationToken: $('input:hidden[name="__RequestVerificationToken"]').val() },
            success: function(data) { $("#EmployeeResult").html(data); }
        });
    });
</script>

_DisplayEmployeePartial.cshtml

@model IEnumerable<Models.Employee>

@if (Model.Count() != 0)
 {
    <table style="border: 1px solid black">
        <thead>
            <tr>
                <th colspan="2" style="border: 1px solid black; text-align: center;">Employee Results</th>
            </tr>
        </thead>

        <tbody>
            <tr>
                <td align="center" style="border: 1px solid black; font-weight: bold;">
                    @Html.DisplayNameFor(m => m.EmployeeName)
                </td>
            </tr>

            @foreach (Models.Employee item in Model)
             {
                <tr>
                    <td align="center" style="border: 1px solid black;">
                        <a id="PopulateNameData" href="javascript:aa()">@item.EmployeeName</a>
                    </td>
                </tr>
             }
        </tbody>
    </table>
 }
else
{
    <p>No data</p>
}

<script>
    $("#PopulateNameData").click(function()
    {
        $.ajax(
        {
            url: "Index?handler=DisplayEmployee",
            type: "POST",
            data: { value: $("#PopulateNameData").text() },
            headers: { RequestVerificationToken: $('input:hidden[name="__RequestVerificationToken"]').val() },
            success: function(data) { $("#NameResult").html(data); }
        });
    });
</script>

Employee Model

using System;
using System.ComponentModel.DataAnnotations;

namespace PracticeApp.Models
{
    public partial class Employee
    {
        [Display(Name = "Employee ID")] public int EmployeeId { get; set; }
        [Display(Name = "Department ID")] public int DepartmentId { get; set; }
        [Display(Name = "Name")] public string EmployeeName { get; set; } = null!;
        public string DepartmentName { get; set; } = null!;
        public DateTime DateofJoining { get; set; }
        public string PhotoFileName { get; set; }
    }
}

enter image description here

enter image description here

CodePudding user response:

Have you tried something like this?

_context.Employees
.Where(x => x.DepartmentName == value)
.Distinct()
.ToList());

Or you can try specifying the fields:

_context.Employees
.Where(x => x.DepartmentName == value)
.Select(x => new {
  EmployeeId = x.EmployeeId,
  EmployeeName = x.EmployeeName 
 })
.Distinct()
.ToList());

CodePudding user response:

Modify your linq like below:

public PartialViewResult OnGetDisplayEmployee(string value)
{
    return Partial("_DisplayEmployeePartial", _context.Employees.Where(x => x.DepartmentName == value).GroupBy(x=>x.EmployeeName).Select(x => x.First()).ToList());
}
  • Related