Efcore: I cannot find a way to do this query using EF Core

[gurutcr]

I need to Join 2 tables on multiple columns, where one of the columns only exist in one table. The code looks something like this

context.Table1
    .GroupJoin(context.Table2,
        x => new { x.Id, SomeColumnOnlyInTable2},
        y => new { Id=y.Table1Id, y.SomeColumnOnlyInTable2},
        (x, y) => new { x, y })

But the compiler complaints that the SomeColumnOnlyInTable2 does not exist in the current context (on line 3). My specific SQL query need, requires this type of JOIN ON. Can anyone suggest how to make this work.

[chrsas]

In line 3 misses an x before SomeColumnOnlyInTable2.

[gurutcr]

But that column isn't available on Table1

[chrsas]

Please show me the Sql

[gurutcr]

I have the following 3 tables

Courses

Id, SortOrder, CourseName, CourseArea, CourseFor

Students

Id, FullName

CourseStudents

CourseId, StudentId, CollegeId

Requirement:

Get all course students from the 'Medical' area for 'foreign' students available in College '125'. Include courses even if there are no students enrolled in it.

Working SQL query:

SELECT cr.Id, 
       cr.CourseName, 
       st.Fullname 
FROM   dbo.Courses cr 
       LEFT JOIN dbo.CourseStudents cst 
              ON cr.Id = cst.CourseId 
                 AND cst.CollegeId = 125  --There is no key in Courses table matching this key, 
                         -- this condition is necessary to get even courses with NO enrolled students
       LEFT JOIN dbo.Students st 
              ON cst.StudentId = st.Id 
WHERE  cr.CourseArea = 'Medical' 
       AND cr.CourseFor = 'Foreigner' 
ORDER  BY cr.SortOrder, 
          st.Fullname 

With the GroupJoin and DefaultIfEmpty, I am very close to the final query, but that AND condition on JOIN ON is the problem now.

[chrsas]

var result = from cr in Courses
join cst from CourseStudents.Where(s => s.CollegeId == 125) on cr.Id equals cst.CourseId into csts
from cst in csts.DefaultIfEmpty()
join st from Students on cst.StudentId equals st.Id into sts 
from st in sts.DefaultIfEmtpy()
where cr.CourseArea == "Medical" && cr.CourseFor == "Foreigner"
orderby new {cr.SortOrder, st.Fullname}
select new {
       cr.Id, 
       cr.CourseName, 
       st.Fullname
}

[gurutcr]

Thanks chras for taking the time. I really appreciate it. I wanted to do it using lambda syntax. You think this kind of code cannot be done with lambda syntax? I was under the impression that lambda is more powerful than query syntax. I will shortly post my incomplete lamda code so may be you can fill in where I am having difficulty.

[chrsas]

Maybe this code can work for you.

context.Table1
    .GroupJoin(context.Table2.Where(y => /*  */),
        x => x.Id,
        y => y.Table1Id,
        (x, y) => new { x, y })

[gurutcr]

Here is my lamda query. Let me know if you need any clarifications.

            context.Courses
                .GroupJoin(context.CourseStudents,
                    x => new { x.Id },
                    y => new { Id = y.CourseId }, <-- Here I need to add the AND condition in my question
                    (x, y) => new { Courses = x, CourseStudents = y })
                .SelectMany(x => x.CourseStudents.DefaultIfEmpty(),
                    (x, y) => new { x.Courses, CourseStudents = y })
                .GroupJoin(context.Students,
                    x => x.CourseStudents.StudentId,
                    y => y.Id,
                    (x, y) => new { CoursesCourseStudents = x, Students = y }
                )
                .SelectMany(x => x.Students.DefaultIfEmpty(),
                (x, y) => new { x = x.CoursesCourseStudents, Students = y })
                .Select(x => new
                {
                    x.x.Courses.Id,
                    x.x.Courses.CourseName,
                    x.Students.FullName,
                    x.x.CourseStudents.CollegeId,
                    x.x.Courses.CourseFor,
                    x.x.Courses.CourseArea,
                    x.x.Courses.SortOrder
                })
                .Where(x => x.CourseFor == "Foreigner" && x.CourseArea == "Medical")
                .OrderBy(x => x.SortOrder)
                .ToList();

[chrsas]

You can try the method that I posted above. https://github.com/aspnet/EntityFrameworkCore/issues/15877#issuecomment-497712115

[gurutcr]

The query I was looking for

SELECT cr.Id, 
       cr.CourseName, 
       st.Fullname 
FROM   dbo.Courses cr 
       LEFT JOIN dbo.CourseStudents cst 
              ON cr.Id = cst.CourseId 
                 AND cst.CollegeId = 125  --There is no key in Courses table matching this key, 
                         -- this condition is necessary to get even courses with NO enrolled students
       LEFT JOIN dbo.Students st 
              ON cst.StudentId = st.Id 
WHERE  cr.CourseArea = 'Medical' 
       AND cr.CourseFor = 'Foreigner' 
ORDER  BY cr.SortOrder, 
          st.Fullname 

The query it generated

SELECT [x].[Id], 
       [x].[CourseName], 
       [y].[Fullname], 
       [t].[CollegeId], 
       [x].[CourseFor], 
       [x].[CourseArea], 
       [x].[SortOrder] 
FROM   [dbo].[Courses] AS [x] 
       LEFT JOIN (SELECT [x0].* 
                  FROM   [dbo].[CourseStudents] AS [x0] 
                  WHERE  [x0].[CollegeId] = 125) AS [t] 
              ON [x].[Id] = [t].[CourseId] 
       LEFT JOIN [dbo].[Students] AS [y] 
              ON [t].[StudentId] = [y].[Id] 
WHERE  ( [x].[CourseFor] = 'Foreigner' ) 
       AND ( [x].[CourseArea] = 'Medical' ) 
ORDER  BY [x].[SortOrder]

Both queries retrieve the same results, but I really wanted to see if lambda syntax can generate the query I want. Is there any way, we can tweak the lamda to generate the exact query I wanted?

[chrsas]

As far as I know, ef core can't generate code like that.

[gurutcr]

Thanks Chen for your help. So looks like query syntax is better than method syntax.

[sdanyliv]

@gurutcr, I'm almost always write queries using query syntax - it is closer to SQL.
As a benefit it limits inappropriate linq methods usage and it easier to reassembly query, move parts up/down.
Also you can always transform query syntax to method chain using ReSharper, but you will be surprised how many transparent objects are automatically created by compiler to reduce boilerplate. x.x.x.x.x.x... is not a limit and even is not closer to real complex queries :)

[gurutcr]

Thanks Svyatoslav. In stackoverflow I saw lots of devs backing lambda syntax over query syntax.

[gurutcr]

As far as I know, ef core can't generate code like that.

Hi Chen, I misunderstood your statement. I now understand what you mean. Neither query syntax nor lambda syntax can generate that kind of SQL query. Got it.

So lambda syntax is still not inferior to query syntax :)

[sdanyliv]

@gurutcr, what can i say, they just do not write complex queries ;)
Simple Where filter is much readable in lambda syntax, but when you write grouping, a lot of joins - method chain (lambda whatever) syntax become unreadable and looks like a mess.
Just check one issue in our repository and find method chain syntax of the same query at the bottom, nightmare isn’t?

[gurutcr]

Wow.... I hope I don't ever have to write such lengthy code

[smitpatel]

C# context.Table1 .GroupJoin(context.Table2, x => new { x.Id, SomeColumnOnlyInTable2 = 125 }, // <-- just use the constant value you need y => new { Id=y.Table1Id, y.SomeColumnOnlyInTable2}, (x, y) => new { x, y })
Above should generate SQL join condition you want in your query.

[gurutcr]

Smit, I didn't see your comment and ended up doing the same myself (trial and error). But thanks for confirming that it is the correct way of doing it.