How to convert SQL subquery to linq lambda?

Question

I have following SQL subqueries and want to convert them to linq lambda.

Count:

select count(*)
from ParentA
where orderStatus in ('Status1','Status2')
  and orderNumer in (select orderNumber
                     from ChildB
                     where approval = 0)

Entities:

public class ParentA
{
    public long ProductID { get; set; }
    public string ProductName { get; set; }
    public ChildB Order { get; set; }
    public string orderStatus { get; set; }
    public long orderNumer { get; set; }
}

public class ChildB
{
    public long Id { get; set; }
    public string Name { get; set; }
    public long orderNumer { get; set; }
    public bool approval { get; set;}
}

All columns:

select *
from ParentA
where orderStatus = 'Status1'
  and orderNumer in (select orderNumber
                     from ChildB
                     where approval = 1)

Eg:

var query =_dbcontext.ParentA.Include().Where().Count();
                

Answer 1

After looking at your posted classes, it looks more like ChildB is the parent and ParentA is the child, with one ChildB per ParentA.

The following should be the method LINQ equivalents:

var result1 = db.ParentA
    .Where(a =>
        (a.orderStatus == "Status1" || a.orderStatus == "Status2")
        && !a.ChildB.approval
    )
    .Count();

var result2 = db.ParentA
    .Where(a =>
        (a.orderStatus == "Status1")
        && a.ChildB.approval
    )
    .ToList();

If you ChildB class has a ParentA collection not shown in your post, this could also be written as:

var result1 = db.ChildB
    .Where(b => !b.approval)
    .SelectMany(b => b.ParentA
        .Where(a => a.orderStatus == "Status1" || a.orderStatus == "Status2")
    )
    .Distinct()
    .Count();
    
var result2 = db.ChildB
    .Where(b => b.approval)
    .SelectMany(b => b.ParentA
        .Where(a => a.orderStatus == "Status1")
    )
    .Distinct()
    .ToList();