I've tried for many hours now to create a Linq Query with distinct and orderby. In this article the general problem with this is described. The only way i found a solution was to create a plain SQL Query
SELECT TOP (10) [DATA]
FROM TestResult
inner join TestResultRelated on TestResultRelated.TestResult = TestResult.ID
WHERE TestResultRelated.PATH = 'TestResultRelatedAdditionalData.SoftwareVersion'
AND TestResult.USER_LOGIN_NAME = 'emilwkj'
AND TestResultRelated.DATA != ''
group by TestResultRelated.DATA order by max([START_DATE_TIME]) desc
I don't need a solution to this exact problem, but here is an example on the problem:
Table 1:
ID | DateUsed | Version
1 | 01/01/2018 | 1.0
2 | 02/03/2018 | 1.1
3 | 05/05/2018 | 1.0
4 | 04/06/2018 | 1.4
In the table above, how would it be possible in LinQ to extract the 3 last used different softwareversions?
Can this be achieved in LinQ?
do something like this : (Update column name as per yours)
var query = (from c in TestResult
join tbl2 in TestResultRelated on c.ID equals tbl2.TestResult
where c.USER_LOGIN_NAME = 'emilwkj' and tbl2.DATA != ''
orderby tbl2.DATA
group tbl2 by tbl2.DATA into g
select new
{
Id = g.Key.Id,
Name = g.Key.Name,
TestResultRelated = g
})
.Distinct() //.Distinct(x=>x.someColumn)
.OrderByDescending(x => x.date);
.Take(10)
As we understand you are using efcore and DbContext heaving TestResult and TestResultRelated table.
Here is the code of problem that we understand from your question.
var db=new DbContext();
var result = (from a in db.TestResult
join b in db.TestResultRelated on a.ID equals b.TestResult
where b.PATH == "TestResultRelatedAdditionalData.SoftwareVersion"
& a.USER_LOGIN_NAME == "emilwkj" & b.DATA!=" "
group b by b.DATA into item
orderby item.Max(e=>e.START_DATE_TIME) descending
select item)
.Take(10);