how to write linq query of the below sql query

Question

select
ROW_NUMBER() over (partition by m.Rack order by m.Id) RackWiseSerialNo,
m.Id,
m.Rack,
m.Capacity,
s.BatchId,
s.SampleQty
from [MstStorage] M LEFT JOIN [tblStorageBatch] S ON M.Id=S.StorageId

-------

My Code is like

_TestRetensionList = (from MstStorage in _dbContext.MstStorage.Where(k => k.IsActive == ApplicationData.IsActive)
join TblStorageBatch in _dbContext.TblStorageBatch on MstStorage.Id equals TblStorageBatch.StorageId into TblStorageBatch
from m1 in TblStorageBatch.DefaultIfEmpty()
select new
{
Id = MstStorage.Id,
Row = MstStorage.Row,
Shelf = MstStorage.Shelf,
Tray = MstStorage.Tray,
Capacity = MstStorage.Capacity,
LabId = MstStorage.LabId,
BatchId = m1.BatchId,
SampleQty = m1.SampleQty,
Rack = MstStorage.Rack

}).AsEnumerable().Select((k, index) => new TestRetensionList()
{

Id = k.Id,
Row = k.Row,
Shelf = k.Shelf,
Tray = k.Tray,
Capacity = k.Capacity,
LabId = k.LabId,
BatchId = k.BatchId,
SampleQty = k.SampleQty,
Rack = k.Rack,
LeftQty = (k.Capacity - k.SampleQty),
RackWiseSerailNo= ??
Statuscolor = Statuscolor((int)(k.Capacity - k.SampleQty), k.SampleQty).Result,
btnvisible = BtnVisibility((int)(k.Capacity - k.SampleQty), k.SampleQty).Result,
}).ToList();

Sandeep Kumar · Answer

Not EXPECTED RESULT Found as My Sql Result table

Jignesh Kumar · Answer

Hello Please use this query will help you out var query = from m in context.MstStorage join s in context.tblStorageBatch on m.Id equals s.StorageId into storageBatches from s in storageBatches.DefaultIfEmpty() orderby m.Rack m.Id select new { RackWiseSerialNo = (from subM in context.MstStorage where subM.Rack == m.Rack && subM.Id

Tahir Ansari · Answer

Try this one. var result = (from m in context.MstStorage join s in context.tblStorageBatch on m.Id equals s.StorageId into storageBatches from s in storageBatches.DefaultIfEmpty() orderby m.Rack m.Id select new { RackWiseSerialNo = SqlFunctions.RowNumber().Over( PartitionBy(m.Rack).OrderBy(m.Id) ) m.Id m.Rack m.Capacity s.BatchId s.SampleQty }).ToList();