Entity Framework Query Nested Query

Question

I am new to the entity framework and am trying to convert the following query into the correct function calls.

Select Distinct a.nodeId FROM 
    (SELECT *
    FROM reportContents
    Where fitId = '29' and reportId = 
       (select max(reportId) 
       from reportContents
       where fitId = '29')
     ) a Where (a.nodeId IS NOT NULL)

I know this query does what i want, however i'm not sure how to translate that into the entitiy framework!

Here was my attempt.

var prevSelectedNodes = db.reportContents.Where(
f => f.fitId == id).Select(
f => f.nodeId).Distinct().ToList();

I need to somehow put a .Select() in the where call. However that kind of thing dosen't seem possible

Thank you in advance!

Looks like Ksven has already answered, but my advice if your new to EF is try not to think of it as querying a bunch of tables. EF is composed of objects, not relational tables. — Eric Scherrer
– Eric Scherrer, Commented Jul 1, 2014 at 15:28

Perfect28 · Accepted Answer · 2014-07-01 15:26:08Z

2

As you can't make two LINQ nested lambda expression. You can do it with two requests :

var maxReportId = db.reportContents.Where(r => r.fitId = "29").Max(r => r.RepordId);
var result = db.reportContents.Where(r => r.fitId == "29" && r.reportId == maxReportId && r.nodeId != null).Select(a => a.nodeId).Distinct().ToList() ;

answered Jul 1, 2014 at 15:26

Perfect28

11.3k3 gold badges29 silver badges50 bronze badges

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

2 Comments

Maarten Over a year ago

If you add the first linq statement into the second statement, I would expect it to generate a single SQL statement.

Eric Scherrer Over a year ago

As written is should do it in one statement. If the first line was terminated with a .ToList() it would force it to be executed separately. Basically it gathers up the work until something actually needs the results.

Collectives™ on Stack Overflow

Entity Framework Query Nested Query

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related