How to convert this SQL statement into a Lambda/Linq expression

Question

I'm looking for the equivalent LINQ Lambda expression for this SQL statement.

SELECT A.USER_ID, A.GIVEN_NAME, A.SURNAME, A.REGION, A.EMAIL 
FROM USER A
WHERE A.USER_ID IN (
    SELECT B.USER_ID_TXT 
    FROM TRAINING_COURSE B
    WHERE B.COURSE_ID_TXT IN 
        (SELECT C.MITT_COURSE_ID_TXT 
            FROM TRAINING_MITT C 
            WHERE C.TRAINING_ID =
                (SELECT D.TRAINING_ID 
                 FROM TRAINING_ROLE D 
                 WHERE D.ROLE_ID = 3011)))

Here are the entities. I've added some comments to illustrate the reference to the TABLES and FIELDS in the SQL query.

This table store users information and it is mounted as a Materialized view.

// TABLE USER 
public class TCUser
{
    // Field USER_ID
    public string UserId { get; set; }
    public string GivenName { get; set; }
    public string Surname { get; set; }
    public string OfficeBuilding { get; set; }
    public string Address { get; set; }
    public string FloorLocation { get; set; }
    public string FloorNumber { get; set; }
    public string Region { get; set; }
    public string Province { get; set; }
    public string PostalCode { get; set; }
    public string City { get; set; }
    public string Email { get; set; }
}

The next table store training course and it comes from an external source. It is basically an import of the data.

// TABLE TRAINING_COURSE - EXTERNAL SOURCE
public class TrainingCourse
{
    // Field USER_ID_TXT
    public String UserId { get; set; }

    // Field COURSE_ID_TXT
    public String CourseId { get; set; }
    public String CourseDescription { get; set; }
    public String ScheduleId { get; set; }
    public String ScheduleDescription { get; set; }
    public DateTime? ScheduleStartDate { get; set; }
    public DateTime? ScheduleEndDate { get; set; }
    public String Status { get; set; }
}

This table make association of training courses required to fulfill a role.

// TABLE TRAINING_ROLES - XREF between TRAINING_MITT and ICS_ROLE
public class IcsTrainingRole
{
     // Field ROLE_ID
     public int RoleId { get; set; }

     // Field TRAINING_ID
     public int TrainingId { get; set; }
     public virtual IcsTraining IcsTraining { get; set; }
     public virtual IcsRole IcsRole { get; set; }
}

This table do the association of Training with the external MITT Training DB

// TABLE TRAINING_MITT
public class IcsTrainingMitt
{
    // Field TRAINING_ID             
    public int TrainingId { get; set; }

    // Field MITT_COURSE_ID_TXT
    public string MittCourseId { get; set; }
    public virtual IcsTraining IcsTraining { get; set; }
}

public class IcsTraining
{
    public int TrainingId { get; set; }
    public string TrainingName { get; set; }
    public virtual ICollection<IcsTrainingRole> IcsTrainingRoles { get; set; }
    public virtual ICollection<IcsTrainingMitt> IcsTrainingMitt { get; set; }
}


public class IcsRole
{
    public int RoleId { get; set; }
    public int SectionId { get; set; }
    public string RoleName { set; get; }
    public virtual ICollection<IcsTrainingRole> IcsTrainingRoles { get; set; }
}

Don't. LINQ isn't SQL or a replacement to SQL. It's a language used with ORMs. Configure your ORM *entities* properly, add proper navigation relations and properties, and your ORM will generate the SQL statement from the LINQ query — Panagiotis Kanavos, Jan 11 '19 at 13:52
Given this query, I suspect the ORM will end up with a cleaner query too. There's no reason to use subqueries after the `WHERE A.USER_ID IN `, simple joins would produce the same execution plan. — Panagiotis Kanavos, Jan 11 '19 at 13:52
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) could help you? — NetMage, Jan 11 '19 at 16:55

score 0 · Answer 1 · answered Jan 11 '19 at 15:51

If you re-write your code using joins it will look like this:

SELECT A.USER_ID, A.GIVEN_NAME, A.SURNAME, A.REGION, A.EMAIL 
FROM USER A
JOIN TRAINING_COURSE B ON A.USER_ID = B.USER_ID_TXT
JOIN TRAINING_MITT C ON B.COURSE_ID_TXT = C.MITT_COURSE_ID
JOIN TRAINING_ROLE D ON C.TRAINING_ID = D.TRAINING_ID AND D.ROLE_ID = 3011

score 0 · Answer 2 · answered Jan 14 '19 at 07:43

Using Linq query syntax with your model classes by using joins (as suggested by Hogan):

var users = from u in TCUser
            join tc in TrainingCourse on u.UserId equals tc.UserId
            join tm in IcsTrainingMitt on tc.CourseId equals tm.MittCourseId
            join tr in IcsTrainingRole on tm.TrainingId equals tr.TrainingId
            where tr.RoleId == 3011
            select new
            {
                UserId = u.UserId,
                GivenName = u.GivenName,
                Surname = u.Surname,
                Email = u.Email
            };

I think the query syntax should be a lot more clear than using lambdas, since the model seems to lack proper navigation properties.

score 0 · Answer 3 · answered Jan 14 '19 at 12:53

Ok, using join is effectively simpler and based on the post of @kaffekopp, the equivalent expression using lambdas would then be:

var users = TCUser
    .Join(TrainingCourse, u => u.UserId, tc => tc.UserId, (u, tc) => new {u, tc})
    .Join(IcsTrainingMitt, @t => @t.tc.CourseId, tm => tm.MittCourseId, (@t, tm) => new {@t, tm})
    .Join(IcsTrainingRole, @t => @t.tm.TrainingId, tr => tr.TrainingId, (@t, tr) => new {@t, tr})
    .Where(@t => @t.tr.RoleId == 3011)
    .Select(@t => new
    {
        UserId = @t.@t.@t.u.UserId,
        GivenName = @t.@t.@t.u.GivenName,
        Surname = @t.@t.@t.u.Surname,
        Email = @t.@t.@t.u.Email
    });

How to convert this SQL statement into a Lambda/Linq expression

3 Answers3