CODEWITHSUNDEEP
entity-frameworklinqasp.net-core
Chris Hadfield
Chris Hadfield

Reputation: 524

LINQ - Conditional Join

I have a condition where joining table have a condition. Let's say I have a table called Mapper Student Teacher where Mapper table have a column named AcNoId which contains a id from both table Student and Teacher. The table structure is

Mapper Mapper

Student Student

Teacher Teacher

TestOption is a enum and is defined as a 

public enum TestOption
{
    Teacher = 1,
    Student = 2
}

Now I have a condition where if TestOption is a type of Student it should perform a join with Student table and if is a type of Teacher it should perform a join with Teacher table This is how I have tried so far

(from m in _context.Mapper
                   where m.TestOption == TestOption.Student
                       join s in _context.Student
                       on m.AcNoId equals s.Id into tempStudent
                       from st in tempStudent.DefaultIfEmpty()

                   where m.TestOption == TestOption.Teacher
                       join t in _context.Teacher
                       on m.AcNoId equals t.Id into tempTeacher
                       from ta in tempTeacher.DefaultIfEmpty()

                   select new
                   {
                        Type = m.TestOption.ToString(),
                        Student = st.StudentName ?? string.Empty,
                        Teacher = ta.TeacherName ?? string.Empty
                   }).ToList();

Instead of conditional join this query perform a following query on SQL Profiler

    exec sp_executesql N'SELECT [m].[TestOption], COALESCE([s].[StudentName], @__Empty_0) AS [Student], COALESCE([t].[TeacherName], @__Empty_1) AS [Teacher]
FROM [Mapper] AS [m]
LEFT JOIN [Student] AS [s] ON [m].[AcNoId] = [s].[Id]
LEFT JOIN [Teacher] AS [t] ON [m].[AcNoId] = [t].[Id]
WHERE ([m].[TestOption] = 2) AND ([m].[TestOption] = 1)',N'@__Empty_0 nvarchar(4000),@__Empty_1 nvarchar(4000)',@__Empty_0=N'',@__Empty_1=N''

How can I do this????

Upvotes: 0

Views: 105

Answers (1)

Ammar
Ammar

Reputation: 820

You can use the below code, with no need to use join or where statements on _context.Mapper:

(from m in _context.Mapper
    select new
    {
        Type = m.TestOption.ToString(),
        Student = _context.Student
            .FirstOrDefault(s => 
                m.TestOption == TestOption.Student && 
                s.Id == m.AcNoId) ?? string.Empty,
        Teacher = _context.Teacher
            .FirstOrDefault(t => 
                m.TestOption == TestOption.Teacher && 
                t.Id == m.AcNoId) ?? string.Empty,
    })
    .ToList();

Upvotes: 2

Related Questions