Monthly Archives

Say I've got a database with about 5,000 blog posts from the last 2 years, I'm trying to create 'monthly' archives so I can display the data in a much more logical way.

For some reason I've never really had to work with date's all that much开发者_运维知识库 and my knowledge is lacking.

I'm using Linq/C#; can someone point me in the right direction to learn how to do this?

Cheers

Matt

Well, it's not much different from filtering based on an integer property:

DateTime beginDate, endDate;
// set beginDate and endDate as appropriate, based on the month you are displaying
// beginDate = new DateTime(2011, 1, 1);
// endDate = beginDate.AddMonths(1);
var query = from post in db.Entries
            where post.Date >= beginDate && post.Date < endDate
            orderby post.Date descending
            select post;

It really depends on your requirements and how the UI flows. In most cases, you could provide a range of dates, and query for posts within that range.

Formatting the resulting posts is entirely up to you, what tools you have to work with, and what you need it to do. However, you could use GroupBy to group posts under a certain date, and then iterate through each group to output individual links. My example below shows you this in a console app. You should be able to extrapolate how to apply it to your needs.

var postsByMonth = (from post in context.Posts
                            where post.Date >= start && post.Date < end
                            orderby post.Date descending
                            select post).GroupBy(post => new DateTime(post.Date.Year, post.Date.Month, 1));

            foreach (IGrouping<DateTime, Post> posts in postsByMonth)
            {
                Console.WriteLine("{0:MMM} {0:yyyy}", posts.Key);
                Console.WriteLine("======================");
                Console.WriteLine();

                foreach (Post post in posts)
                {
                    Console.WriteLine("Post from {0}", post.Date);
                }

                Console.WriteLine();
            }