CODEWITHSUNDEEP
c#sqlentity-frameworklinq
Bandito
Bandito

Reputation: 329

SQL to LINQ with an IN clause

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.

Upvotes: 2

Views: 88

Answers (1)

NaDeR Star
NaDeR Star

Reputation: 655

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

Upvotes: 2

Related Questions