LINQ Using inline subquery with min() in a left join where clause

Question

I am trying to get my head around how to generate the equivalent of this LEFT JOIN in Linq. It contains a subquery which gets the lowest service_id for the person in that row being joined, and then also restricts on service_code = "N" in both the subquery and the join). I just cant seem to get it to work in Linq.

SQL:-

LEFT OUTER JOIN SERVICE ON person.id_person = SERVICE.id_person 
    AND SERVICE.id_service = (SELECT MIN(id_service) FROM SERVICE WHERE id_person = person.id_person AND service_code = 'N')  
    AND SERVICE.service_code = 'N'

NB: I know how to do the left join correctly (DefaultIfEmpty() etc). Its the subquery that is the problem. How do I squeeze that subquery into the .Where clause in Linq?

Hi, you shoud check out https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins - the DefaultIfEmpty option is the key. — Hoarst, Sep 13 '21 at 09:16
its not left outer join that I am struggling with, its the subquery. I can do the left outer join with the where on service_code, on its own, but I cant seem to get the subquqery with the MIN() clause to work — Steve, Sep 13 '21 at 09:20
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Sep 13 '21 at 19:20

score 0 · Answer 1 · answered Sep 13 '21 at 09:34

This query should give you desired result and should be more performant. Also it shows how to express LEFT JOIN in alternative way.

var query = 
    from person in ctx.Person
    from service in ctx.Service
        .Where(service => service.service_code == 'N')
        .Where(service => service.id_person = person.id_person)
        .OrderBy(service => service.id_service)
        .Take(1)
        .DefaultIfEmpty()
    select new 
    {
        person,
        service
    };

LINQ Using inline subquery with min() in a left join where clause

1 Answers1