how to write linq query of the below sql query

Sandeep Kumar

3y1.5kLINQ

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();

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sandeep Kumar3y ago
Not EXPECTED RESULT Found as My Sql Result table
0

Jignesh Kumar3y ago

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 <= m.Id
                                    select subM).Count(),
                m.Id,
                m.Rack,
                m.Capacity,
                s?.BatchId,
                s?.SampleQty
            };

Tahir Ansari3y ago
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();
0