none
How to get all null items greater than 4 months from current date. RRS feed

  • Question

  • I am trying to get all null items for a table column greater than 4 months from today's date using Linq. Below is my query. Although there are items, my query returns 0. I am not sure what I am doing wrong.

    var FinalizedDate = DateTime.Now.AddMonths(- 4);
    var test = EmpTable.Include(x => x.admintable.registers.Select(wt => wt.adminSurnames))  
    .Where(x => x.admintable.Id == 45 
    && x.FinalizedDateTime == null && x.FinalizedDateTime > FinalizedDate).ToList();


    Ebenezer



    • Edited by denkyira Thursday, August 1, 2019 7:37 PM
    Thursday, August 1, 2019 7:35 PM

Answers

  • I have finally got it working. The issue was having was wrong comparison. My query should look like this:

    var FinalizedDate = DateTime.Now.AddMonths(- 4);
    var test = EmpTable.Include(x => x.admintable.registers.Select(wt => wt.adminSurnames)) 
    .Where(x => x.admintable.Id == 45 
    && x.FinalizedDateTime == null && x.InsertDate > FinalizedDate).ToList();
    


    Ebenezer

    • Marked as answer by denkyira Friday, August 2, 2019 10:58 AM
    Friday, August 2, 2019 10:58 AM

All replies

  • Hi denkyira,

    Here is a simple demo that filter date that "item is null and greater than 4 month" in DataTable.

        DataTable dt = new DataTable("Table");
    
        private void Form1_Load(object sender, EventArgs e)
        {
            dt.Columns.Add("column1", System.Type.GetType("System.String"));
            dt.Columns.Add("column2", System.Type.GetType("System.DateTime"));
    
            DataRow dr1 = dt.NewRow();
            dr1["column1"] = null;
            dr1["column2"] = new DateTime(2019, 6, 1);
            dt.Rows.Add(dr1);
            DataRow dr2 = dt.NewRow();
            dr2["column1"] = null;
            dr2["column2"] = new DateTime(2019, 1, 1);
            dt.Rows.Add(dr2);
            DataRow dr3 = dt.NewRow();
            dr3["column1"] = "ssss";
            dr3["column2"] = new DateTime(2019, 1, 1);
            dt.Rows.Add(dr3);
        }
    
        private void btFilter_Click(object sender, EventArgs e)
        {
            var query = from t in dt.AsEnumerable()
                        where (t.Field<string>("column1") == null && DateTime.Now.Year * 12 + DateTime.Now.Month - t.Field<DateTime>("column2").Year * 12 - t.Field<DateTime>("column2").Month > 4)
                        select t;
            foreach (DataRow dr in query)
            {
                Console.WriteLine(dr["column1"]);
                Console.WriteLine(dr["column2"]);
            }
        }

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 2, 2019 2:53 AM
  • Hi Kyle

    Thanks for your response. I think your demo and my code are the similar with the on;y difference is you are using DataTables which I am not. So the question is still what makes my query not working?


    Ebenezer

    Friday, August 2, 2019 10:45 AM
  • I have finally got it working. The issue was having was wrong comparison. My query should look like this:

    var FinalizedDate = DateTime.Now.AddMonths(- 4);
    var test = EmpTable.Include(x => x.admintable.registers.Select(wt => wt.adminSurnames)) 
    .Where(x => x.admintable.Id == 45 
    && x.FinalizedDateTime == null && x.InsertDate > FinalizedDate).ToList();
    


    Ebenezer

    • Marked as answer by denkyira Friday, August 2, 2019 10:58 AM
    Friday, August 2, 2019 10:58 AM