How to perform an SQL “IN” query in Linq

By pcurd

Using Linq to achieve a query that in SQL that uses the “IN” keyword – i.e. to check a value against a range of values – requires use of the .Contains method on a new array of the range of values.

For example, if I need to exclude from reports the following customers:

  1. Internal
  2. Demo
  3. Sample
I would in SQL use something like:
CustomerName NOT IN ('Internal', 'Demo', 'Sample')
In Linq I need something like:
!(new[] {"Internal", "Demo", "Sample"}).Contains(CustomerName))

Appropriately wired into EF or your ORM of choice.

I think this is confusing since it is written backwards to a SQL programmer, so it reads “does this list of things contain the value?” instead of “is the value in this list of things?” as we are used to in SQL.



SWDevelopments Website launched

By pcurd

For the last few months I have been rebranding my personal projects and side work to “SW Developments” and today I have launched a website for this venture – http://swdevelopments.co.uk.

I will be developing this website and the venture with more details in the coming months.

SW Developments will consider any project and can produce cost effective, modern Content Management System driven websites, and more powerful custom development driven websites or any IT consultancy services for local companies.



Powershell to run Get-MessageTrackingLog on multiple machines

By pcurd

I have an infrastructure with multiple Exchange 2007 servers and wanted to compile a list of all emails sent by two email addresses. Whilst Get-MessageTrackingLog did what I wanted, it only ran on one server at a time and would only search for one email address.

To get around this I wrote a PowerShell script to run Get-MessageTrackingLog for each sending email address and each computer. This is then combined into one CSV file for transfer to Excel.

$computers = "server1", "server2"
$senders = "Email1@domain.com", "Email2@domain.com"
$outputfile = "C:\output.csv"

$start = "DD/MM/YYYY HH:MM:SS"
$end = "DD/MM/YYYY HH:MM:SS"

$(foreach ($sender in $senders) {
$(foreach ($computer in $computers) {
get-messagetrackinglog -Sender $sender -Start $start -End $end -EventID "RECEIVE" -Server $computer | select Timestamp, @{Name="Recipients";expression={$_.Recipients}}, MessageSubject, Sender

})}) | sort-object -property timestamp | Export-CSV -path $outputfile -notype



A forage into Ruby

By pcurd

I’ve been experimenting with Ruby (and with the Rails framework) the last few days after a need came up for a console based application with a large degree of string manipulation. I have been using Python and Perl for similar jobs before – in fact I run a number of Python scripts that have proved to be extremely useful. A lot of the legacy code I work with is written in VBA (Visual Basic for Applications) within Access and Excel so this was another possible choice.

I wanted to avoid hosting my code within Access due to a possible rollout of Access 2003 soon and the fact that I needed to access mailstores – the thought of using ADO scared me and I don’t have a machine I can dedicate a copy of Outlook to so this effectively ruled out VBA.

Perl has loads of libraries available on CPAN and Python seems to be going the same way but the quality of community code can be suspect – I’ve never run into a problem with it myself, I use CPAN libraries all over the place – especially on my web server – and I didn’t want to be supporting third party libraries as well as my own code.

We are effectively a C# shop for new development – just myself and my boss keeping the legacy code up to date really – so I had to consider this as an option. .Net provides a nice SMTP library but not POP3 or IMAP… so that brought me back to the same problem I had with Perl and Python. I spent about half a day with a CodeProject IMAP library but it was not feature complete and after scoping out the changes I’d need decided I might as well have written the library myself.

I’ve been looking into Ruby and Rails recently for a personal database project and knew that Rails provided a lot of functionality. Now, I know that technicaly Rails is a community third party library but it is so much more refined and integrated than a hodge podge of CPAN modules thrown together. And, Rails provides IMAP straight “out of the box”. I loaded up Ruby.Net only to find that neither them or IronRuby supports Rails yet! This project was proving more complicated than I had expected – and I hadn’t written a line of code yet.

So I made the decision to write this application in neat Ruby on Rails without linking to .Net binary in the hope that Ruby.Net will catch up eventually! (Rails support is expected in the next version apparently)

My first surprise was that Rails just works. I did not have to go through any rigmarole to get Net::IMAP to work – just

require 'net/imap'

and I was off. As I didn’t have access to the rails scaffold I used DBI to access a Microsoft SQL Server instance with the amazingly complicated

require 'dbi'

which is shorter even than PHP’s database linking code. Opening a handle with

dbh = DBI.connect('DBI:ODBC:MY_DSN')

gave me immediate access to the ability to run SQL with sensible data returns.

Suffice to say, I was impressed. A few helper functions later, and some hastily learnt Ruby conditional statements for error catching

var = var ? var : " "

to remove nils from strings – this technique even works when passing parameters – amazing. Features available in Perl like

run_my_long_complex_function(variable) unless defined?(use_simple_mode)

are so much cleaner and clearer to read in Ruby – plus the excellent “PickAxe” (Programming Ruby – possibly the best Ruby textbook) not only encourages this technique – it’s the one it teaches you.

More impressive and powerful functions that let you pass in chunks of code (‘blocks’) using “yields” are likely to be my next excitement but so far I haven’t needed them.

The only thing I can say is that I had some 22 lines of error checking code using if then else etc – Ruby first impressed with the ability to use ? : notation which reduced it to about 8 lines. Then I realised that I could use conditional formatting like the unless example above. Then Ruby amazed me with the ability to use them both together – did you realise that if blocks are expressions, not statements – Ruby did. That code is now 1 line – and it works just as well.

I muchly look forward to developing the rest of the feature set in Ruby as I’m sure it’ll keep impressing me with it’s ability to make programming interesting again!