SQL to LINQ with an IN clause

Question

BACKROUND:

I have a single table in my database that I use for logging calls to various web services.

Each log entry is linked by a GUID (CorrelationID) that links all log entries for a particular web request.

When searching the logs for a particular piece of data (eg. a persons name) I may get one log entry that has that piece of information, but I want to show all of the log entries related to that same request.

CURRENTLY:

To achieve the desired results in SQL I use this query:

SELECT * FROM Logging WHERE CorrelationId IN (SELECT DISTINCT CorrelationId FROM Logging WHERE Message like '%fred%') ORDER BY Datetime

PROBLEM:

I would like to convert this query to LINQ to Entities.

All I have at the moment is the main select

var records = db.Loggings.Where(x => x.Datetime >= fromDate && x.Datetime < toDate)
                .Where(x => x.Message.Contains("fred"))
                .OrderBy(x => x.Datetime).AsQueryable();

I can't work out the correct syntax for adding the IN part.

I have followed other SO answers to similar questions, but still cannot get this to work for my particular scenario.

Possible duplicate of [LINQ subquery IN](https://stackoverflow.com/questions/3477918/linq-subquery-in) — Hugo, Apr 02 '19 at 16:55
@Halhex yes, I tried that, it was painfully slow to complete the second query based on the results of the first. — Bandito, Apr 02 '19 at 17:01
You are using records twice (records = records). The entity database is somethings like dbEntity.TableName — jdweng, Apr 02 '19 at 17:02
That SQL doesn't even need that `IN`. You can just use the inner where clause on the outer query and you should get the same results. — juharr, Apr 02 '19 at 18:26

score 2 · Accepted Answer · answered Apr 02 '19 at 17:24

Do the job like this:

var subQuery = records.Where(x => x.Datetime >= fromDate && x.Datetime < toDate && x.Message.Contains("fred")).Select(x => x.CorrelationID).Distinct();

var result = records.Where(x => subQuery.Contains(x.CorrelationID)).OrderBy(x => x.Datetime).AsQueryable();

SQL to LINQ with an IN clause

1 Answers1