Convert T-SQL to LINQ using the WHERE IN clause with a Nested Subquery

Question

How would the following T-SQL query be written in LINQ using the WHERE IN clause and a subquery:

SELECT a.IncidentID,
                 a.OccurWhen,
                 a.OccurWhere,
                 a.Description
FROM buIncidentDetail a
WHERE a.IncidentID IN (SELECT IncidentID
                              FROM buPerson
                              WHERE IsDeleted = 0
                              AND (NameFirst LIKE '%%'
                                    OR NameLast LIKE '%%')
                                    )
OR a.IncidentID IN (SELECT IncidentID
                        FROM buInjury
                        WHERE IsDeleted = 0
                        AND (TimeLossEstimateTerms LIKE '%%'
                              OR ResultOtherDesc LIKE '%%')
                              )

Before marking this as a possible duplicate, all the other examples I found were using an array, i.e. – (“1”, “2”, “3”). I’m looking for one that specifically demonstrates the use of a subquery.

Answer 1

You turn the queries inside out and start with the inner queries first, and then applying the result of those to the outer query.

int? incidentID;                  
var person = buPerson.Where( 
    p => p.IsDeleted == 0 
        && (!string.NullOrWhitespace(p.NameFirst) 
        ||  !string.NullOrWhitespace(p.NameLast)).FirstOrDefault();                   
if ( person != null )
    incidentID = person.IncidentID;
else {
    var incident = buInjury.Where( 
        i => i.IsDeleted == 0 
            && (!string.NullOrWhitespace(i.TimeLossEstimateTerms) 
            ||  !string.NullOrWhitespace(i.ResultOtherDesc)).FirstOrDefault();  
    if ( incident != null )
        incidentID = incident.IncidentID;
}
if ( incidentID.HasValue() )
{
    var detail = buIncident
                    .FirstOrDefault(j=>j.IncidentID.Value)
                    .Select(j => new { k => new { 
                            ID = k.IncidentID, 
                            When = g.OccurWhen, 
                            Where = g.OccurWhere, 
                            Description = g.Description };
    if ( detail != null )
        Console.WriteLine( "{0}, {1}, {2}, {3}",  
                         detail.IncidentID, 
                         detail.OccurWhen, 
                         detail.OccurWhere, 
                         detail.Description);
}
else
    Console.WriteLine("No incident found");