LINQ to SQL: Aggregations on multiple columns without any grouping

I think the title speaks for itself. I want to create a LINQ query with aggregations on multiple columns where no grouping is applied. This is just something that can be done very simple in plain SQL.

1 SELECT 2 MAX(HorsePower), 3 AVG(Weight) 4 FROM 5 Car

But when you want to create a construction that's similar in LINQ you will get an compilation error, because of a wrong syntax.

1 var result = from car in sut.Cars 2 select new 3 { 4 MaxHorsePower = car.Max(p => p.HorsePower), 5 AverageWeight = car.Average(p => p.Weight) 6 };

The trouble is that this doesn't work. You can do an aggregation on an complete set, like the following. This will result in just two queries.

1 var result2 = (from car in sut.Cars select car.HorsePower).Max(); 2 var result3 = (from car in sut.Cars select car.Weight).Average();

You can actually do multiple aggregations at once when using groups. The sad thing is, we don't always have something to group on. But that can be solved.

1 var result = (from car in 2 (from car in sut.Cars 3 select new 4 { 5 car.HorsePower, 6 car.Weight, 7 Dummy = string.Empty 8 }) 9 group car by new {car.Dummy} 10 into dummyGroup 11 select new 12 { 13 MaxHorsePower = dummyGroup.Max(p => p.HorsePower), 14 AverageWeight = dummyGroup.Average(p => p.Weight) 15 }).Single();

This solution adds a dummy column called 'Dummy' with the same value for every record. Because this value is the same for every record we can safely group on it and expect to have only one group. This solution makes sure we have one query for the database, but with the use of a workaround.

I would say yes this works, but for readability I would not suggest the use of this workaround for the trivial problem above. By using two queries we have a very manageable solution that's readable from code. The workaround needs at least some comments before we can totally understand it as someone new to the workaround.

But it in the end we can also say, we now know how we can fake the grouping. Use it with care, as I mentioned because of the manageability of your code.

Qash an interesting Banking 2.0 application for the Dutch people

A lot of people in the USA have heard of Mint or Wesabe. Those are so called Banking 2.0 applications that do advanced reporting on your incoming and outgoing money. This is an area where no bank I know of does the right thing. I have used ABN Amro internet banking and Postbank internet banking, and must say it works, but lacks a lot of features, specially reporting. But a dutch Banking 2.0 application is being built right now. It's called Qash. I subscribed for the beta and last week I my request was accepted. I must admit it's still beta but it worked at a glance. Take a watch how you can import your banking data.

 

I'm not sure how long I will be using Qash, but for now I like it. You can subscribe for the beta on the home page of Qash.

A few small changes to my weblog

It has been more than a year since the relounce of my weblog. Just last week I started adding advertisements from Google Adsense to my blog. I do this because I hope to earn a little bit from the ads to pay the hosting bill.

I now have two small ad-blocks in the side bar, between the tag-cloud and the archives and after the blog roll.

Ad-block in the sidebar

Also the search is now done through Google. This way there are also ads displayed in the search results.

Ad-block in the search

And finally I made ads active in the RSS-feed.

Ad-block in the RSS-feed

How difficult can a Windows Service be?

Not that difficult actually. But it took me some time to figure out what was wrong while running an application as a Windows Service that ran fine as a Windows Application. A little bit more context is necessary.

 

The application is based on a plugin mechanism that like most of the plugin mechanisms make use of Reflection to load the plugin dlls. In our solution we took every dll that's inside our current running directory to examine if there's a class that implements a certain interface. To determine the current directory we used:

string directory = Environment.CurrentDirectory;

This works fine under every Windows Application. It gives back the working folder. But the trouble began with the following message:

Could not load file or assembly 'file:///C:\WINDOWS\system32\6to4svc.dll' or one of its dependencies. The module was expected to contain an assembly manifest.

Very strange message. I was thinking there was a dependency to the 6to4svc.dll within one of our plugins, but that didn't really make sense. Because the 6to4svc.dll has something to do with translation of IPv6 to IPv4. The error was very strange because we would expect this kind of error to occur in both Windows Service and Windows Application environments. The next day I was fresh, and looked again at the message. I thought, was has C:\Windows\System32\ to do with our application? At that time I began to wonder are we loading dll's from the wrong directory? Yes we were, in Windows Services the Environment.CurrentDirectory is C:\Windows\system32\, a totally wrong directory for us. We changed our code a little bit to make it run in both Windows Services and Windows Application environments.

string directory = new FileInfo(Assembly.GetExecutingAssembly().Location).DirectoryName;

It took us a while to discover this. And while this solution isn't new at all, and a lot of people will have the same or alike solution for the same problem, I didn't find a solution while Googling on the error message, so it probably will be available soon.