Need help with converting this sql query

SELECT   c.studentId,  c.Name 
FROM  Classes c 
WHERE c.Class = 'Math' 
 AND c.Grade IN 'yoklama', '2')  
 AND c.studentId IN (  
   SELECT c2.studentId  
   FROM    Classes c2  
   WHERE c2.Class = 'Eng' 
    AND c.Grade IN ('yoklama', '2'))

I was trying like this but these keeps giving error that I am doing this wrong

var reportDtos = context.Classes.Where(c => pt.Class ==  'Math'  && c.Grade.HasValue 
&& c.Grade == '2' || c.Grade == 'yoklama' && c.studentId.Contains(context.ParticipTests
             .Where(x => x.Class ==  'Math'  &&x.Grade.HasValue && x.Grade == '2' || x.Grade == 'yoklama')
             .ToList()
             .Select(x => ))
             .Include("Area")
             .Include("Questions")
             .Include("Class.Test")
             .ToList()
             .Select(pt => new ReportDto
                {
                   ...
                }

CodePudding user response：

First off, strings must always be double quoted " and not single quoted ' like you have some.

If you write some code by splitting up the steps it makes it more understandable.

var validGrades = new List<string> { "Yoklama", "2" };

var studentsInEnglish = context.ParticipTests
   .Where(x => x.Class.Equals("Eng") && validGrades.Contains(x.Grade))
   .Select(x => x.studentId);
var studentsInMath = context.Classes
   .Where(x => x.Class.Equals("Math") && validGrades.Contains(x.Grade) && studentsInEnglish.Contains(x.studentId))
   .Select(x => (x.studentId, x.Name));

Now with this, (if you really still want to) you can write a single query by plugging in and replacing.

var reportDtos = context.ParticipTests
    .Where(
        x => x.Class.Equals("Math") &&
        new List<string> { "Yoklama", "2" }.Contains(x.Grade) &&
        context.Classes
            .Where(
                y => y.Class.Equals("Eng") &&
                new List<string> { "Yoklama", "2" }.Contains(y.Grade))
            .Select(y => y.studentId)
            .Contains(x.studentId))
    .Select(x => (x.studentId, x.Name))
    .ToList();

...But I like the first way better.