Why Order By Creation Desc Not Working

Sandeep Kumar

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 NotifyData
          {
              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,
              ApproveBy = k.ApproveBy,
              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();

4 Replies

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

Tuhin Paul3y ago

If the CreatedOn field contains null values, they will be treated as the smallest value and will be sorted first, regardless of the order by clause. You can add a where Notification.CreatedOn is not null condition to exclude null values from the result set. Also if you have implemented a custom sorting logic using a SQL trigger or stored procedure, it may override the order by clause and return the results in a different order.
Tuhin Paul3y ago
The CreatedOn field in the Notification table has the correct data type (i.e., datetime, datetime2, or datetimeoffset). If the data type is different, it may not sort the results as expected.

Sandeep Kumar3y ago

This is My Table Class .model of tables

public partial class Notification
{
    public int Id { get; set; }
    public string RefNid { get; set; }
    public string Ntype { get; set; }
    public int? RoleId { get; set; }
    public int? DeptId { get; set; }
    public int? CourseId { get; set; }
    public int? UserId { get; set; }
    public string NotificationH { get; set; }
    public string NotificationB { get; set; }
    public string Comment { get; set; }
    public int? Status { get; set; }
    public int? ApproveBy { get; set; }
    public int CreatedBy { get; set; }
    public DateTime? CreatedOn { get; set; }
    public int? ModifiedBy { get; set; }
    public DateTime? ModifiedOn { get; set; }
    public int? ReadTag { get; set; }
}

Naimish Makwana3y ago
In the SQL query, the CreatedOn field is implicitly converted to a datetime data type, whereas in the LINQ query, the CreatedOn field is being converted to a string using the AppDateToString method. If the CreatedOn field in the Notification entity is not already a datetime data type, it's possible that the conversion to a string is affecting the ordering of the results.

Thanks