How to filter DateTime string, no sqlserver commands

Question

I'm working on my exam project and I'm struggling with creating a method which returns filtered list of orders. I'm trying to filter the by DateTime func which I previously saved in database with code first variant and after filtering it to call the method in WindowsForm gridview.

I tried multiple ways which I saw around the internet but nothing worked

    public IQueryable<Order> GetOrderFiltered(IQueryable<Order> OrdersQuery, Order order)
        {

            if (!string.IsNullOrWhiteSpace(order.Time))
            {
                OrdersQuery = OrdersQuery.Where(p => p.Time.Contains(DateTime.Today.ToString("dd/MM/yyyy HH:mm")));
            }

            var list = new List<Order>(OrdersQuery);
            return list;
        }
    }

namespace Data.Model
{
    public class Order
    {
        [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        public string Product { get; set; }
        public string PhoneNumber { get; set; }
        public string Location { get; set; }
        public DateTime Time { get; set; }
    }
}

I would be very thankful if someone could help me.

How exactly do you wish do filter the list? Only receive orders where Time is today? — MaxB, Apr 05 '19 at 08:38
You could use linq to sort it, without the sql. See this - https://stackoverflow.com/questions/5344805/linq-orderby-descending-query — ConstXobs, Apr 05 '19 at 08:43
if your datetime saved in database as string you should do : p => p.Time.Contains(DateTime.Today.ToString("dd-MM-yyyy HH:mm") you need to verify the sql datetime format ( dd-MM-yyyy ) or (MM-dd-yyyy) — Alaaeddine HFIDHI, Apr 05 '19 at 08:49
Do you have the opportunity to change `Order.Time` to `DateTime` type ? (also in your DB) — Spotted, Apr 05 '19 at 08:55

score 0 · Answer 1 · answered Apr 05 '19 at 09:06

public IQueryable<Order> GetTodayOrders(IQueryable<Order> OrdersQuery)
{
    return OrdersQuery.Where(o => o.Time >= DateTime.Today && o.Time < DateTime.Today.AddDays(1));
}

Order order parameter looked useless (since you only want orders from today): I removed it.

I renamed the method to better express the intent.

I assumed that since the return type is still IQueryable it is intended to returns directly without any intermediary structure (List).

The where condition allows to cover orders with time from Today at 00:00:00 to 23:59:59.

MaxB · Accepted Answer · 2019-04-05T09:12:54.337

I can't know for sure how exactly you want it to be filtered, but basically you can do the following if you wish to get the list of Orders whose time is equal to a given order.

public IQueryable<Order> GetOrderFiltered(IQueryable<Order> OrdersQuery, Order order)
{
    return OrdersQuery.Where(x => x.Time == order.Time);
}

Here's a sample class I wrote to check it:

class Program
{
    public class Order
    {
        public Order(int id, string time)
        {
            this.Id = id;
            this.Time = time;
        }

        public int Id { get; set; }
        public string Time { get; set; }
    }

    static void Main(string[] args)
    {
        List<Order> list = new List<Order>();
        list.Add(new Order(0, "1"));
        list.Add(new Order(1, "2"));
        list.Add(new Order(2, "1"));
        list.Add(new Order(3, "2"));
        list.Add(new Order(4, "1"));


        IQueryable<Order> test = GetOrderFiltered(list.AsQueryable(), new Order(121, "1"));
        // test has orders 0,2,4
    }

    public static IQueryable<Order> GetOrderFiltered(IQueryable<Order> OrdersQuery, Order order)
    {
        return OrdersQuery.Where(x => x.Time == order.Time);
    }
}

Edit. After reading your new comment, the function you're looking for is:

public IQueryable<Order> GetOrderFiltered(IQueryable<Order> OrdersQuery, Order order)
{
    return OrdersQuery.Where(x => x.Time == DateTime.Today.ToString("dd/MM/yyyy"));
}

Only in case that your Time looks like this - "01/02/2005" of course.

You can play with your Time property however you want, it depends on you. If it has minutes and seconds after the dd/mm/yyyy, as per your code in question, then you could use:

return OrdersQuery.Where(x => x.Time.StartsWith(DateTime.Today.ToString("dd/MM/yyyy")));

or "Contains" instead of "StartsWith"

How to filter DateTime string, no sqlserver commands

2 Answers2