Linq Entity Framework

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?

We need to see your C# code to be able to help i.e. your entity classes and DbContext. What you are trying to achieve should be fairly simple. — Johnathan Barclay
– Johnathan Barclay, Commented Jun 21, 2018 at 11:16
So the problem is that i need to find the top ten last used SoftwareVersions. And you can't order after a distinct in Linq or anything. I can't show you DBContext as this is internally for the company i work for. Sorry guys. But isn't it fairly simple to translate the SQL to LinQ, if its possible? You don't have to write the exact code for me. I have used LinQ for a long time. — Emil Wismann Kirkebæk-Jensen
– Emil Wismann Kirkebæk-Jensen, Commented Jun 21, 2018 at 11:19
Just updated the page. I can't give you original context, but my problem is better described with a little example now — Emil Wismann Kirkebæk-Jensen
– Emil Wismann Kirkebæk-Jensen, Commented Jun 21, 2018 at 11:25

Saurin Vala · Accepted Answer · 2018-06-21 12:20:17Z

2

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)

edited Jun 21, 2018 at 12:20

answered Jun 21, 2018 at 11:33

Saurin Vala

1,9381 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ivan Stoev Over a year ago

Hmm, and where is Distinct(x => ...) method coming from?

habib · Accepted Answer · 2018-06-21 11:51:50Z

0

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

answered Jun 21, 2018 at 11:51

habib

2,4665 gold badges28 silver badges44 bronze badges

Collectives™ on Stack Overflow

Linq Entity Framework

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related