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 >
<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>
CodePudding user response：
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; }
}
}
CodePudding user response：
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