how to write query and use distinct and order by in LINQ?

Question

Can you please help me how to write this query in LINQ syntax

select distinct machine_id , sample_id  from results where custid = 18
order by sample_id

I tried this but not working like the query it shows only machine_id in sample no 1 but I need to select distinct machine_id for each sample

 public ActionResult Indexs()
        {
            int UserId = Convert.ToInt32(Session["UserID"]);
            var samples = _context.RESULTS.GroupBy(mach => mach.machine_id).Select( x=> x.FirstOrDefault()).Where(x=> x.custid == UserId).ToList();
            return View(samples);
        }

I cannot find DistinctBy<> only Distinct available I tried the solutions in this question but its not cover my case

https://stackoverflow.com/questions/14321013/distinct-in-linq-based-on-only-one-field-of-the-table

please I need your help and thank you in advance

UPDATE :

Still the solution not working as expected and not show all data this is the code I use :

var samples = _context.RESULTS.GroupBy(mach => new { mach.machine_id, mach.sample_id }).Select(x=>x.FirstOrDefault()).OrderBy(n=> new { n.sample_id,n.program_id }).Where(x => x.custid == UserId).ToList();

This is example what I have in RESULTS table and what I need to select:

user     sample no 1        machine_id 

 1           1                     4

 1           1                   2026

 1           1                   2030

 1           1                   2046

 1           1                   2053

 1           1                   2058

 1           1                   2061

 1           1                   2080 

 1           1                   2081

 1           1                   2083

 1           1                   2084

but with this command it shows only 4 machines:

2080

2081

2083

2084

and I think groupby and firstordefault cause this please how to solve it and select distinct machine_id from each sample like the SELECT statement why its very difficult to make simple query in LINQ ?

this is the VIEW code , I dont know if it will be useful :

@model IEnumerable<warehouse.Models.RESULT>

@{
    ViewBag.Title = "Indexs";
    Layout = "~/Views/Shared/_LayoutDashboard.cshtml";
}

<img style="margin-left:250px;" src="~/images/weblogo.png" />
<p style="margin-left:40px;">

    <h3 style="margin-left:100px; font-family:Andalus;text-underline-position:below">
        @Html.Label("Hospital Name :")
        @Html.DisplayFor(model => Model.FirstOrDefault().sys_users.user_full_name)

    </h3>


    <table class="table table-bordered">
        <tr style="background-color:hotpink">

            <th>
                @Html.DisplayNameFor(model => model.Program.name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.sample.name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Machine.Machine_name)
            </th>

            <th></th>
        </tr>

        @foreach (var item in Model)
        {
            <tr>

                <td>
                    @Html.DisplayFor(modelItem => item.Program.name)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.sample.name)
                </td>

                <td>
                    @Html.DisplayFor(modelItem => item.Machine.Machine_name)
                </td>

                <td>
                    @Html.ActionLink("Enter", "Edit", new { id = item.sample_id, programId = item.program_id, custId = item.custid, machineId = item.machine_id }, new { @class = "btn btn-primary" }) |
                    @Html.ActionLink("Report", "Details", new { id = item.sample_id, programId = item.program_id, custId = item.custid }, new { @class = "btn btn-danger" }) |
                    @Html.ActionLink("Statistics", "Edit", "Statistics", new { programId = item.program_id, hospitalNo = item.custid }, new { @class = "btn btn-info" })
                </td>
            </tr>
        }

    </table>

Answer 1

here is an example using distinctBy

Obj obj0 = new() { Id = 1, SimpleId = 1, Name = "name1" };
Obj obj1 = new() { Id = 1, SimpleId = 1, Name = "name2" };
Obj obj2 = new() { Id = 1, SimpleId = 1, Name = "name1" };
Obj obj3 = new() { Id = 1, SimpleId = 1, Name = "name2" };
Obj obj4 = new() { Id = 1, SimpleId = 2, Name = "name4" };
Obj obj5 = new() { Id = 1, SimpleId = 2, Name = "name2" };
Obj obj6 = new() { Id = 1, SimpleId = 2, Name = "name3" };

List<Obj> list = new() { obj0,obj1,obj2, obj3 , obj4 , obj5 , obj6};

var result = list.OrderByDescending(o => o.Id).DistinctBy(p => new{p.SimpleId, p.Name}).OrderBy(o => o.Id);
Console.WriteLine( "liste count " + list.Count);
int n = 0;
foreach (Obj obj in result)
{
    Console.WriteLine(++ n + " " +obj);
}
record Obj
{
    public int Id { get; set; }
    public int SimpleId { get; set;}
    public string Name { get; set; }
}

result display

liste count 7
1 Obj { Id = 1, SimpleId = 1, Name = name1 }
2 Obj { Id = 1, SimpleId = 1, Name = name2 }
3 Obj { Id = 1, SimpleId = 2, Name = name4 }
4 Obj { Id = 1, SimpleId = 2, Name = name2 }
5 Obj { Id = 1, SimpleId = 2, Name = name3 }

you see all duplicates are deleted

Answer 2

You may have typos in the query or using wrong properties. Below is getting correct results. I split the orderby into two pieces.

using System;
using System.Linq;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Data;


namespace ConsoleApp2
{
    class Program
    {
 
        static void Main(string[] args)
        {

            Context _context = new Context()
            {
                RESULTS = new List<RESULTS>()
                {
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 4 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2026 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2024 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2038 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2046 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2053 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2058 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2061 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2080 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2081 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2083 },
                    new RESULTS() { user = 1, sample_id = 1, machine_id = 2684 }

                }
            };
            int UserId = 1;
            var samples = _context.RESULTS.GroupBy(mach => new { mach.machine_id, mach.sample_id }).Select(x => x.FirstOrDefault()).OrderBy(n =>  n.sample_id ).ThenBy(x => x.machine_id).Where(x => x.user == UserId).ToList();
        }

    }
    public class Context
    {
        public List<RESULTS> RESULTS { get; set; }
    }
    public class RESULTS
    {
        public int user { get; set; }
        public int sample_id { get; set; }
        public int machine_id { get; set; }
    }
 
}

Answer 3

I find that your question post (as it currently stands) leaves some parts open to interpretation.

My understanding is that the displayed RESULTS selection

user_id  sample_id   machine_id
1        1           4
1        1           2026
1        1           2030
1        1           2046
1        1           2053
1        1           2058
1        1           2061
1        1           2080
1        1           2081
1        1           2083
1        1           2084

is in fact the selection you get when executing

select distinct user_id, sample_id, machine_id
from results
where user_id = 1
order by sample_id

, whereas the RESULTS table in your database may contain more entries than the 11 that are displayed.

Is that a correct assumption?

(If no, please inform me and I will delete this answer.)

If so, I believe that there may be entries in the RESULTS table where { sample_id, machine_id } are equal, but where user_id differ.

As an example, RESULTS may contain two entries where sample_id == 1 and machine_id == 4:

user_id  sample_id   machine_id
-------------------------------
2        1           4
1        1           4
...      ...         ...          // other entries

When grouping these two entries by { sample_id, machine_id }, you will get one grouping containing two elements:

Key:
    { 1, 4 }
Elements:
    { 2, 1, 4 },
    { 1, 1, 4 }

Selecting the first element in that grouping will give you

{ 2, 1, 4 }

, simply because { 2, 1, 4 } appeared before { 1, 1, 4 } in the table. We basically disregarded the database table entry { 1, 1, 4 } before even getting to the .Where() statement.

Now, by using .Where() to get only the elements that are associated with user_id == 1, the element { 2, 1, 4 } will be excluded, seeing as user_id == 2.

---

My suggestion is therefore to change the order of method calls, so that .Where() is used prior to the grouping and selection of the first group element. A minimal example:

var filteredResults = results
    .Where(r => r.user_id == 1) // filtering before grouping
    .GroupBy(r => new { r.machine_id, r.sample_id })
    .Select(r => r.First());

When doing this, the grouping will only be based on entries where userId == 1, and therefore, no entry associated with user_id == 1 will be "lost" in the stage of selecting the first element in each grouping.

This fiddle may help in displaying the difference regarding the order of method calls.