I have this working SQL query: SELECT * FROM Events WHERE Events.Id NOT IN (SELECT Results.EventId FROM Results WHERE Results.UserId = 86); I'm new to entity framework, so what would be it's equivalent?

You should be able to do something like this: db.Events .Where(e => !db.Results .Where(r => r.UserId = 86) .Select(r => r.EventId) .Contains(e.Id) ); EF will turn that inner query to the equivalent sub-query in SQL.

Covert sql to entity framework

Accepted answer

P a u l 10,406 Reputation points

2021-09-08T01:38:48.247+00:00
You should be able to do something like this:

db.Events .Where(e => !db.Results .Where(r => r.UserId = 86) .Select(r => r.EventId) .Contains(e.Id) );

EF will turn that inner query to the equivalent sub-query in SQL.
Please sign in to rate this answer.

1 person found this answer helpful.
Esteban Peralta 26 Reputation points

2021-09-10T02:13:49.63+00:00

Thanks for the feedback, I took your suggestion and created my get method but it's returning a 500 internal server error when I run it on postman, here is the code I'm using:

[HttpGet("[action]/{UserId}")] public IActionResult AllEventsFinalFilter(int UserId) { var events = _dbContext.Events .Where(e => !_dbContext.Results .Where(r => r.UserId == UserId) .Select(r => r.EventId) .Contains(e.Id)); return Ok(events); }

P a u l 10,406 Reputation points

2021-09-10T02:24:03.117+00:00

Looks reasonable to me - do you have error logging enabled?

Esteban Peralta 26 Reputation points

2021-09-10T03:00:50.073+00:00

Looking at the error log I received this message: System.Text.Json.ThrowHelper.ThrowJsonException_SerializerCycleDetected(int maxDepth)

This article explains how to resolve it on the startup.cs file: https://www.thecodebuzz.com/jsonexception-possible-object-cycle-detected-object-depth/

P a u l 10,406 Reputation points

2021-09-10T03:09:44.367+00:00

Ah I see - Glad you got it fixed.
Sign in to comment

Covert sql to entity framework

0 additional answers