none
How to get Empty Month Data in LINQ ? RRS feed

  • Question

  • I have written a query :

    var value = from c in results.AsEnumerable()
                            group c by new
                            {
                                years = Convert.ToDateTime(c.Field<string>("Posted")).Year,
                                months = Convert.ToDateTime(c.Field<string>("Posted")).Month
                            }
                                into d
                                orderby d.Key.years,d.Key.months
                                select new
                                {
                                    Date = String.Format("{0}/{1}",d.Key.months,d.Key.years),                             
                                    Count = d.Count()
                                };

    this yields result like :

    Date Count

    3/2011     45

    5/2011     68

    9/2011     72

    i want like this :

    DateCount

    3/2011     45

    4/2011

    5/2011     68 

    6/2011 0

    7/2011 0

    8/2011 0

    9/2011     72

    Please Help !!!

    Friday, March 16, 2012 9:37 AM

Answers

  • Hi LINK_me_with_LINQ,

    I think you can create a new table, insert all the months into it and join it with the existing query result.

    DataTable MonthTable = new DataTable();
                DataColumn month = new DataColumn("Month", typeof(string));
                DataColumn count = new DataColumn("Count", typeof(int));
                MonthTable.Columns.Add(month);
                MonthTable.Columns.Add(count);
                for (int i = 1; i < 13; i++)
                {
                    DataRow row = MonthTable.NewRow();
                    row["Month"] = string.Format(i + "/2011");
                    row["Count"] = 0;
                    MonthTable.Rows.Add(row);
                }

    Join this datatable with the result of your query.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 21, 2012 6:09 AM
    Moderator
  • Hi LINK_me_with_LINQ,

    Allen_Li1988 is right in the regard that you'll need to populate a collection with all the months you want and then assign the count for each one. If I understood correctly from your example what you're wanting is to include all months between the min "Posted" date and the max "Posted" date. Here is a solution using the Enumerable.Range() method to generate the collection entirely with LINQ:

                List<DateTime> dates = results.AsEnumerable()
                                        .Select(c => Convert.ToDateTime(c.Field<string>("Posted")))
                                        .Select(d => new DateTime(d.Year,d.Month,1))
                                        .ToList();
    
                var value = (from month in Enumerable.Range(1, 12)
                             from year in Enumerable.Range(dates.Min().Year,dates.Max().Year)
                             let date = new DateTime(year,month,1)
                             where date >= dates.Min() && date <= dates.Max()
                             select new
                                        {
                                            Date = String.Format("{0}/{1}", date.Month, date.Year),
                                            Count = dates.Count(d => d == date),
                                        });

    Regards,

    Tyler

    Wednesday, March 21, 2012 9:41 AM

All replies

  • Hi LINQ_me_with_LINQ,

    Welcome to MSDN Forum.

    Could you please post the table schema here?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, March 19, 2012 4:29 AM
    Moderator
  • SharePoint is in the Backend.

    Posted is a DateTime column in the Sharepoint.

    I am using LINQ to OBJECT.

    Monday, March 19, 2012 10:33 AM
  • Hi LINK_me_with_LINQ,

    I think you can create a new table, insert all the months into it and join it with the existing query result.

    DataTable MonthTable = new DataTable();
                DataColumn month = new DataColumn("Month", typeof(string));
                DataColumn count = new DataColumn("Count", typeof(int));
                MonthTable.Columns.Add(month);
                MonthTable.Columns.Add(count);
                for (int i = 1; i < 13; i++)
                {
                    DataRow row = MonthTable.NewRow();
                    row["Month"] = string.Format(i + "/2011");
                    row["Count"] = 0;
                    MonthTable.Rows.Add(row);
                }

    Join this datatable with the result of your query.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 21, 2012 6:09 AM
    Moderator
  • Hi LINK_me_with_LINQ,

    Allen_Li1988 is right in the regard that you'll need to populate a collection with all the months you want and then assign the count for each one. If I understood correctly from your example what you're wanting is to include all months between the min "Posted" date and the max "Posted" date. Here is a solution using the Enumerable.Range() method to generate the collection entirely with LINQ:

                List<DateTime> dates = results.AsEnumerable()
                                        .Select(c => Convert.ToDateTime(c.Field<string>("Posted")))
                                        .Select(d => new DateTime(d.Year,d.Month,1))
                                        .ToList();
    
                var value = (from month in Enumerable.Range(1, 12)
                             from year in Enumerable.Range(dates.Min().Year,dates.Max().Year)
                             let date = new DateTime(year,month,1)
                             where date >= dates.Min() && date <= dates.Max()
                             select new
                                        {
                                            Date = String.Format("{0}/{1}", date.Month, date.Year),
                                            Count = dates.Count(d => d == date),
                                        });

    Regards,

    Tyler

    Wednesday, March 21, 2012 9:41 AM
  • You guys are really awesome............. thanks a lot both of you -  Allen & Tyler
    Wednesday, March 21, 2012 1:12 PM