Linq Entity Framework

c# entity-framework-core linq sql

Question

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?

1
2
6/21/2018 11:24:48 AM

Accepted Answer

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)
2
6/21/2018 12:20:17 PM

Popular Answer

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);


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow