Variable across multiple columns in datatable

Question

Trying to do what the SQL query ( SELECT DISTINCT (first,second),third FROM table ) will do, but I am doing this with LINQ and datatable.

---

EDIT

SQL should look like mysql

select first, second, third FROM table group by first, second

---

 DataTable secondTable = new DataTable(); secondTable.Columns.Add("name", typeof(string)); secondTable.Columns.Add("date", typeof(string)); secondTable.Columns.Add("clockIn", typeof(string)); secondTable.Columns.Add("clockOut", typeof(string)); var t4 = (from a in firstTable.AsEnumerable() select new { name = a.Field<string>("name"), date = a.Field<string>("date"), clockIn = a.Field<string>("clockIn"), clockOut = a.Field<string>("clockOut") }).Distinct(); var t5 = (from a in firstTable.AsEnumerable() select new { name = a.Field<string>("name"), date = a.Field<string>("date") }).Distinct(); var t6 = (from d in t5 join a in t4 on new { d.name, d.date } equals new { a.name, a.date } select secondTable.LoadDataRow( new object[] { d.name,d.date,a.clockIn,a.clockOut }, false)).ToList(); ViewBag.Data = secondTable; 

What this code does is it combines t4 and t5 into t6 without exception. Although I want all the lines from t4 that are present in t5 should join t6 based on (name, date). And all lines from t5 that do not exist in t4 should be excluded. Can anybody help?

Answer 1

From your comments, you can simply group by the desired fields and take any of the grouped results.

You can order a clockin or timeout to get a less "random" result.

 var t6 = firstTable.AsEnumerable() .GroupBy(a => new { name = a.Field<string>("name"), date = a.Field<string>("date") } ) .Select(g => g.First()) //or Select(g => g.OrderBy(a => a.Field<string>("clockIn")).First() .ToList();