Convert sql query to EF

Can anybody please help with this SQL query to transform it to EF Linq Expression?

select MI.Id, B.Count, B.Cost, Name, Price, Unity, I.Count, I.Cost, BOL.Date, BOL.type, BOL.Number
        from Balance B inner join MaterialsInfo MI on Mi.Id = B.MaterialsId
        inner join Income I on MI.Id = I.MaterialsId
        inner join BillOfLading BOL on I.BillOfLadingId = BOL.Id

        where B.Id in (select Id from (select max(Date), Id from Balance group by Balance.MaterialsId))

        and I.Id in (select Id from(select max(Date), Income.Id as Id from Income inner join BillOfLading BOL on Income.BillOfLadingId = BOL.Id group by Income.MaterialsId))

I’ve been trying to do this for days, so I could really use some help.

Do you need extra info?

Thank you in advance.

Edit Here is a little part of the diagram, maybe it helps

Answer

It seems the subqueries in both of your conditions in where clause is incorrect. Because your

group by column is missing in the select statement

And it is very unclear what you trying to achieve from the subqueries.

Without a clear understanding of your subqueries, all I can prepare for you is this.

var result = await (from balance in yourDBContect.Balances
            join material in yourDBContect.MaterialsInfos on balance.MaterialsId equals material.Id
            join income in yourDBContect.Incomes on material.Id equals income.MaterialsId
            join bill in yourDBContect.BillOfLadings on income.BillOfLadingId equals bill.Id
            where Balances.GroupBy(g => g.MaterialsId)
                          .Select(s => new
                          {
                               MaterialsId = s.Key,
                               MaxDate = s.Max(x => x.Date)
                          }).ToList().Contains(new { balance.MaterialsId, MaxDate = balance.Date })
            && Incomes.Join(BillOfLadings,
                            p => p.BillOfLadingId,
                            e => e.Id,
                            (p, e) => new { p, e })
                      .GroupBy(g => g.p.MaterialsId)
                      .Select(s => new
                      {
                          MaterialsId = s.Key,
                          MaxDate = s.Max(s => s.e.Date)
                      }).ToList().Contains(new { income.MaterialsId, MaxDate = balance.Date })
            select new
            {
                material.Id,
                balance.Count,
                balance.Cost,
                balance.Name,
                balance.Price,
                balance.Unity,
                ICount = income.Count,
                ICost = income.Cost,
                bill.Date,
                bill.Type,
                bill.Number
            });

Notes:

• Alias for Income.Count and Income.Cost are changed because an object cannot contain exactly same nenter code hereamed property.
• Linq for a proper subquery will replace new List<int>().