'EF Core complex query

this is the entity

public class Order
{
    public int Id {get;set;}
    public int ProductName {get;set;}
}

public class BuyerBill
{
    public BuyerBill()
    {
        BuyerBillItems = new List<BuyerBillItems>();
    }
    public int Id {get;set;}
    public int OrderId {get;set;}
    public int Status {get;set;}
    public List<BuyerBillItems> BuyerBillItems { get; set; }
    ....
}

public class BuyerBillItems
{
    public int Id {get;set;}
    public int BuyerBillId {get;set;}
    public int Fee {get;set;}
    ....
}

public class SellerBill
{
    public SellerBill()
    {
         SellerBillItems= new List<SellerBillItems>();
    }
    public int Id {get;set;}
    public int OrderId {get;set;}
    public int Status {get;set;}
    public List<SellerBillItems> SellerBillItems{ get; set; }
    ....
}

public class SellerBillItems
{
    public int Id {get;set;}
    public int SellerBillId {get;set;}
    public int Fee {get;set;}
    ....
}

select sum(e2.Fee) from order e 
join BuyerBill on e.Id=e1.OrderId 
join BuyerBillItems e2 on e1.Id=e2.BuyerBillId 
where e.Id=1

select sum(e2.Fee) from order e 
join SellerBill on e.Id=e1.OrderId 
join SellerBillItems e2 on e1.Id=e2.SellerBillId
where e.Id=1

There is only one row of order records in the SellerBill and SellerBill tables BuyerBillItems and SellerBillItems tables may have many rows

I can only query with 2 T-SQL statements now, how can I query with one T-SQL?

My project is using EF CORE, so you guys better help me to query with EF CORE, thanks!

The result I want to get is BuyerFee,SellerFee 200,100



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source