Hi ..

Intro

I had a table with Primary Key (let's call it: Subject; Primary Key: Id) respectively. In the other table, there's a column I want to retrieve its count and data. so let's call it (Teacher; Primary key: TeacherId, SubjectId, ClassroomName). So I want to retrieve the list of subjects from Subject Table by the classroom Name value.

Now the question is, how to use the variable of ClassroomName inside the Teacher Model while I don't have it in Subject Model.

Solution Logic

To solve such issue, I used the direct dbcontext to retrieve the list of Subjects Id from the TeachersList and store it in the runtime. and then I return the list of Subjects that contains a SubjectId in the Subjects Table.

Final Solution:

        public async Task<IEnumerable<Subject>> GetListByClassroomName(string classroom)
        {
            var idsList = await _context.Teachers.Where(t => t.ClassroomName == classroom).Select(t => t.SubjectId).ToListAsync();
            return await _context.Subjects.Where(s => idsList.Contains(s.Id)).ToListAsync();
        }

Conclusion:

The problem was retrieving list of data from Table A with a non-foreign key in table B using Table A primary key. The solution is to use to retrieve the list from table B and use ([dot]Select(A Primay Key)). Then, use Table A (WHERE) Query [List.Contain(A Primary Key)]

P.S. Thank you my dear mentor Abdullah Hashim for your guidance :)

Twitter: @MBahusayn

Update :

I received a feedback from Twitter: @rahulpnath There's an alternative solution of what I've shared above. Please have a look. Thanks, Rahul!