Shane Mitchell’s Weblog

LINQ-to-SQL Grouping By Week

I came across the problem of requiring to group items from my SQL database by week yesterday.  After a quick Google search, I found a few other people were having the same problem.  Although I must admit this was not hard, it might not seem immediately obvious to everyone.

The following code example will group items from a database by week, and is supported by SQL Server:

var byWeek = from t in ctx
             where t.Date >= startWeek && t.Date < endWeek
             group t by t.DateFieldInDb.Date.AddDays(-(int)t.DateDateFieldInDb.DayOfWeek) into tw
             orderby tw.Key
             select new
             {
                 Date = tw.Key,
                 Count = tw.Count()
             };

I should note that this groups weeks starting with Sunday.  To start with Monday, you could try the following code (untested, but should work):

var byWeek = from t in ctx
             where t.Date >= startWeek && t.Date < endWeek
             group t by t.DateFieldInDb.Date.AddDays(-((int)t.DateFieldInDb.DayOfWeek == 0 ? 6 : (int)t.DateFieldInDb.DayOfWeek - 1)) into tw
             orderby tw.Key
             select new
             {
                 Date = tw.Key,
                 Count = tw.Count()
             };

And that’s it, simple as that.  Let me know if you find this useful, or find a better way!

Tags: C#, Group By, LINQ to SQL

Don’t use Internet Solutions Fraud

Last week I had a terrible time dealing with a director from a company called Internet Solutions, based in Northern Queensland.  These guys run the following companies:

• Yourhub
• Internetlounge
• Yourdomains
• Yourhosts
• Yourcity

I had this server colocated in a Brisbane data-centre with these guys, in exchange for offering my services to them at ungodly hours of the morning.  I did this - and everything was fine for 15 months.  Then, out of the blue, my server gets turned off.  I panic trying to get this guy’s phone number, and when I finally do, he tells me he never knew about the arrangement (lie!) and will work out an arrangement for me to pay him monthly.  This I could live with.  What happens next should disturb you.

As you might know, the cost of colocation in Brisbane is about $150/month for a decent deal.  This guy sends me a quote, saying he wants to charge me $1700/month PLUS backcharge me for 15 months at this rate.  He ended up wanting to charge me $15000.  That’s how much he would pay to run his whole ISP there!

I told him I couldn’t do that, and he held my server equipment at RANSOM.   Meanwhile my clients were absolutely freaking out about how they couldn’t access their data and company records, and one threatened to sue the guy $125,000 a week that he held it at RANSOM.

After 2 days lawyers were involved, and he eventually surrendered the hardware.  I had to quickly organise more colocation, which ended up being OK.

I hear he’s doing this to other people too.

Point of the story is, I recommend you think twice before using:

• Internet Solutions
• Yourhub
• Internetlounge
• Yourdomains
• Yourhosts
• Yourcity

~Shane

Tags: Fraud, Internet Solutions, Yourhub