how to write linq query of the below sql query

Sandeep Kumar

SELECT DISTINCT [RefNId]
,[NType]
-- ,[RoleId]
-- ,[DeptId]
-- ,[CourseId]
-- ,[UserId]
,[NotificationH]
,[NotificationB]
,[Comment]
,[Status]
,[ApproveBy]
,[CreatedBy]
,[CreatedOn]
,[ModifiedBy]
,[ModifiedOn]
,[ReadTag]
FROM [dbo].[Notification] N INNER JOIN MstStage S ON N.Status=S.Id where N.DeptId=2 ORDER By N.[CreatedOn] desc

6 Replies

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

Sandeep Kumar3y ago
It Is Not Working ,I did it, corrent date raw are notcoming first
+1

Naimish Makwana3y ago

Try below,

var _NotifyData = (from Notification in _dbContext.Notification
                   join MstStage in _dbContext.MstStage on Notification.Status equals MstStage.Id
                   where Notification.DeptId == (DeptId == 0 ? Notification.DeptId : DeptId) 
                   && Notification.Status == (Items.Status == 100 ? Notification.Status : Items.Status)
                   && Notification.CreatedOn >= (Items.FromDate == string.Empty ? Notification.CreatedOn : ApplicationData.AppStringToDatetime(Items.FromDate)) 
                   && Notification.CreatedOn <= (Items.ToDate == string.Empty ? Notification.CreatedOn : ApplicationData.AppStringToDatetime(Items.ToDate))
                   orderby Notification.CreatedOn descending
                   select new 
                   {
                       RefNId = Notification.RefNid,
                       NType = Notification.Ntype,
                       NotificationH = Notification.NotificationH,
                       NotificationB = Notification.NotificationB,
                       Comment = Notification.Comment,
                       StatusId = (int)MstStage.Id,
                       Status = MstStage.Mode,
                       CreatedBy = (int)Notification.CreatedBy,
                       CreatedOn = Notification.CreatedOn,
                       ModifiedBy = (int)Notification.ModifiedBy,
                       ModifiedOn = Notification.ModifiedOn
                   })
                   .Distinct()
                   .AsEnumerable()
                   .Select((k, index) => new NotifyData()
                   {
                       SrlNo = index + 1,
                       RefNId = k.RefNId,
                       NType = k.NType,                
                       NotificationH = k.NotificationH,
                       NotificationB = k.NotificationB,
                       Comment = k.Comment,                            
                       StatusId = k.StatusId,
                       Status = k.Status,
                       CreatedBy = k.CreatedBy,
                       CreatedOn = ApplicationData.AppDateToString(k.CreatedOn),
                       ModifiedBy = k.ModifiedBy,
                       ModifiedOn = ApplicationData.AppDateToString(k.ModifiedOn),
                       Can_Approve = GetRegistationResult(_authenticationManager.GetAuthenticatedUser().Result.UserId, UserManageAccess.NotificationPage, UserManageAccess.NotificationApproveAction).Result,

                   }).ToList();

Thanks

Sandeep Kumar3y ago
ORDER BY CREATEON DESCENDING,actually in listing latest date ont Coming in First raw
+1
Vishal Joshi3y ago
Hello sandeep

can you please tell me the issue/error ?

Thanks
+1
Sandeep Kumar3y ago
Can u tell where i have to change

var _NotifyData = (from Notification in _dbContext.Notification
join MstStage in _dbContext.MstStage on Notification.Status equals MstStage.Id
where Notification.DeptId == (DeptId == 0 ? Notification.DeptId : DeptId)
&& Notification.Status == (Items.Status == 100 ? Notification.Status : Items.Status)
&& Notification.CreatedOn >= (Items.FromDate == string.Empty ? Notification.CreatedOn : ApplicationData.AppStringToDatetime(Items.FromDate))
&& Notification.CreatedOn <= (Items.ToDate == string.Empty ? Notification.CreatedOn : ApplicationData.AppStringToDatetime(Items.ToDate))
orderby Notification.CreatedOn descending
select new
{
RefNId = Notification.RefNid,
NType = Notification.Ntype,
NotificationH = Notification.NotificationH,
NotificationB = Notification.NotificationB,
Comment = Notification.Comment,
StatusId = (int)MstStage.Id,
Status = MstStage.Mode,
CreatedBy = (int)Notification.CreatedBy,
CreatedOn = ApplicationData.AppDateToString(Notification.CreatedOn),
ModifiedBy = (int)Notification.ModifiedBy,
ModifiedOn = ApplicationData.AppDateToString(Notification.ModifiedOn)
}).Distinct().AsEnumerable().Select((k, index) => new NotifyData()
{
SrlNo = index + 1,
RefNId = k.RefNId,
NType = k.NType,
NotificationH = k.NotificationH,
NotificationB = k.NotificationB,
Comment = k.Comment,
StatusId = k.StatusId,
Status = k.Status,
CreatedBy = k.CreatedBy,
CreatedOn = k.CreatedOn,
ModifiedBy = k.ModifiedBy,
ModifiedOn = k.ModifiedOn,
Can_Approve = GetRegistationResult(_authenticationManager.GetAuthenticatedUser().Result.UserId, UserManageAccess.NotificationPage, UserManageAccess.NotificationApproveAction).Result,

}).ToList();
+1
Anurag Chauhan3y ago
var results = (from n in Notification join m in MstStage on n.Status equals m.Id where n.DeptId == 2 select

new

{

n.RefNId
,n.NType
,n.NotificationH
,n.NotificationB
,n.Comment
,n.Status
,n.ApproveBy
,n.CreatedBy
,n.CreatedOn
,n.ModifiedBy
,n.ModifiedOn
,n.ReadTag

}).Distinct().ToList().OrderByDescending(z=>z.CreatedOn);
+1