LINQ : Use of elements of string array

Again, using LINQPad:

string[] digits = { "zero", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine" };

var getDigits =
from d in digits
where d[0] == 't'
select d;

getDigits.Dump();

This selects all elements in the array where the first letter is "t".

Result is:

two
three

Enjoy!

LINQ : ToArray() and Take()

Using the usual LINQPad and the Northwind DB:

So we first get the last names from the Employees table and put them into a string array using the ToArray() construct and then we get the first 4 using the Take() construct.

string [] allEmployees1 = Employees.Select (e => e.LastName).Take(4).ToArray();

allEmployees1.Dump();

foreach (string s in allEmployees1)
Console.WriteLine(s);

Enjoy!

LINQ : Some string handling

Based on an example by Eric White, here's some simple string handling (running as always inside LINQPad).

// Per Eric White. The following is a query to split a string into words, only
// allow words > 1 character, eliminate the ones that contain the letter
// "y", convert the words to lower case, count the number of occurrences of
// each word, and return the ten most used words.

string text = "Debugging LINQ queries can be problematic. One of the reasons is that quite often, you write a large query " +
"as a single expression, and you can’t set a breakpoint mid-expression. Is LINQ really that problematic? " +
"Queries can be fun as is debugging!";

var uniqueWords = text
.Split(' ', '.', ',', '?', '!')
.Where (i => i != "")
.Where (i => i.Count() > 1)
.Where (i => !i.Contains ("y"))
.Select(i => i.ToLower())
.GroupBy(i => i)
.OrderByDescending(i => i.Count())
.Select(i => new { Word = i.Key, Count = i.Count() })
.Take(10);

uniqueWords.Dump();

Returns:

Word Count
is 3
debugging 2
linq 2
queries 2
can 2
be 2
problematic 2
that 2
as 2
one 1

Enjoy!

LINQ : Some more on foreign keys

Continuing the series of LINQ to SQL using the Northwind DB:

Note: Can only be done if table has "Entity Set" relationship with another table i.e. green left-pointing arrow in LINQPad

Let's start with a compiled query:

var products = CompiledQuery.Compile ((TypedDataContext dc, decimal minUnitPrice) =>
from prod in Products
where prod.OrderDetails.Any (c => c.UnitPrice > minUnitPrice)
select prod
);

products (this, 20).Dump ("Unit price > $20");

OK - let's try that as a simple lambda without the compiled query:

var products1 =
from prod in Products
where prod.OrderDetails.Any (c => c.UnitPrice > 20)
select prod;

products1.Dump();

OK - let's try writing that again the "SQL" way with foreign key x = foreign key y:

var products2 =
(from prod in Products
from od in OrderDetails
where od.UnitPrice > 20 && prod.ProductID == od.ProductID
select prod).Distinct();

products2.Dump();

Hang on! There's far too many rows. That's because the relationship is duplicated so we need to add the "Distinct" keyword. You'd think that the construct would be:

select prod.Distinct();

but that doesn't work. If you look closely at the code snippet above, you'll see that the "Distinct" is applied to the whole clause (look at the brackets) rather than just the "Select" element.

Enjoy!

LINQ : Compiled Queries

Continuing my playing with LINQPad, I found that you could put the query within a function that is precompiled (much like a stored procedure).

Using the Northwind DB,

var pp = CompiledQuery.Compile ((TypedDataContext dc, decimal minUnitPrice) =>
from p in Products
where p.UnitPrice > minUnitPrice
orderby p.UnitPrice
select p
);

pp (this, 10).Dump ("Products with unit price > 10");
pp (this, 100).Dump ("Products with unit price > 100");

This is equivalent to the "normal" form:

var pp1 =
from p in Products
where p.UnitPrice > 10
orderby p.UnitPrice
select p;

pp1.Dump();

Enjoy!

SQL Server : Installing the Northwind DB

As per previous posts, I'm playing with LINQPad and I needed a decent DB to run my queries on.

So I downloaded SQL Server 2008 Express but that doesn't come with any sample DB (apparently for security reasons).

The actual DB's are now hosted by Codeplex but they are just a pile of scripts - they don't actually include the DB.

So Mr. Google to the rescue and I eventually found the answer here.

The article is called "HOWTO: Install the Northwind and Pubs Sample Databases"
(by Brian Hart).

The article shows you how to install the DB using "SQL Server Management Studio Express" which is part of the Express download.

Enjoy!

LINQ : Some sample queries

Been paying around with LINQ to SQL using the excellent LINQPad and the Northwind DB.

Some samples I came up with:

(The "C# Expression" refers to the Type in LINQPad. Ensure the DB drop-down points to the Northwind DB. The lambda is generated during run time and can be viewed by clicking on the lambda symbol next to the Results tab. You can think of lambda as a function e.g. "a => a * 2" is a function that takes "a" as input and returns "a times 2".

The Dump statement just outputs the current object.)

// This works - C# Expression
// Get all rows in Products - sort by ProductName

from p in Products
orderby p.ProductName
select p

// This is the corresponding lambda - C# Expression

Products
.OrderBy (p => p.ProductName)

// C# Expression
// Only show rows that have an "A" in the ProductName

from p in Products
where p.ProductName.Contains("A")
select p

These are all C# Statement(s) - ";" are now required at the end of declarations.

// Return all rows of employees who live in the UK sorted by LastName

var employees =
from emp in Employees
where emp.Country == "UK"
orderby emp.LastName
select emp;

employees.Dump();

Some more complicated ones.

// Find all products that are in stock

var products =
from prod in Products
where prod.UnitsInStock > 0
orderby prod.UnitsInStock
select prod;

products.Dump();

// And the lambda

Products
.Where (prod => ((prod.UnitsInStock) > 0))
.OrderBy (prod => prod.UnitsInStock);

Products.Dump();

// Putting the lambda on one line

Products.Where (prod => ((prod.UnitsInStock) > 0)).OrderBy (prod => prod.UnitsInStock);
Products.Dump();

What about two tables with a foreign key relationship? Let's throw in two "where" statements for fun!

// Find all products that are in stock and that whose category is Beverages

var products =
from prod in Products
from cat in Categories
where prod.UnitsInStock > 0 && prod.CategoryID == cat.CategoryID && cat.CategoryName == "Beverages"
orderby prod.UnitsInStock
select prod;

products.Dump();

// Write the rows out individually line-by-line

foreach(var prod in products)
Console.WriteLine(prod.ProductID + " " + prod.ProductName);

// "products" is an object which we can simply re-sort without another DB look-up

var products2 =
from prod in products
orderby prod.ProductID
select prod;

Console.WriteLine();

foreach(var prod in products2)
Console.WriteLine(prod.ProductID + " " + prod.ProductName);

Enjoy!

Misc : Stackoverflow

Been meaning to blog about this for a while but if you have any programming-related questions, hop on over to stackoverflow.

There's a horde of really knowledgeable people waiting for your call. Now's good!

Ask, answer and get your reputation up.

Enjoy!

Misc : RSS reader

I think online RSS readers like Google Reader suck - I vastly prefer desktop ones. For many years, I used SharpReader. I liked it and it's free - what's to complain about?

But it's no longer maintained. The last update was "Changes in 0.9.7.0 - released August 2 2006" and there were more and more of my blogs that were just throwing feed errors that weren't actually errors.

So reluctantly, I look around for a new one. After a bit of research, I settled on FeedDemon. Took me a while to get used to its quirks but once I got over the initial learning curve, I'm quite happy with it.

And since I could export my subscriptions in opml and import them into FeedDemon, I didn't lose a single one.

Enjoy!

Excel : Putting the row number in the cell

Comparing two versions of a spreadsheet (old versus new), I wanted to have a column of the current row number on the old spreadsheet and the corresponding row number on the new one.

For the old spreadsheet, this would just be the row number e.g. column 3 has number 3.

An easy way to do this is to type two numbers in the first two rows (i.e. 1 and 2) and then highlight them as if to drag them which causes the little box to appear in the bottom right hand corner of the cell. Then drag the box down the rest of the column.

When you do this, Excel works out the relationship between the two numbers (i.e. row x = x; row x+1 = x+1) and then applies this to the rest of the column.

Done.

Enjoy!

Excel : Viewing two windows side by side

I have two spreadsheets; one an earlier version and one that reflects the current state after many alterations and updates and I wanted to document all the changes.

So I tried to put them side by side so I could manually compare them row-by-row.

There are two buttons on the task bar (one for each window) but clicking them loads each spreadsheet in exactly the same window. They effectively overlay each other.

After doing some research, I found you need to click Window / Arrange / Vertical and hey presto it's done.

Enjoy!

C# : T4 - the Text Template Transformation Toolkit

Been looking at code generation templates. Obviously CodeSmith is right up there but then I read an article by Scott Hanselman entitled "T4 (Text Template Transformation Toolkit) Code Generation - Best Kept Visual Studio Secret" which describes a Visual Studio add-in (I'm using VS 2005) which does pretty much the same thing.

There's a link in the article to another "How-To" by Oleg Sych entitled "
How to create a simple T4 template". You need to download DSL Tools and you may also want to install the T4 Editor by Clarius Consulting.

As an example, here's my template (Template.tt):

<#@ template language="C#" #>
<#@ assembly name="System.dll" #>

//
// This code was generated by a tool. Any changes made manually will be lost
// the next time this code is regenerated.
//
using System;

public class <#= this.ClassName #>
{
public static void HelloDude()
{
Console.WriteLine("Hello, Dude");

for (<#= this.Variable #> = <#= this.Start #>; <#= this.Variable #> < <#= this.End #>;
<#= this.Variable#>++)
{
}
}
}
<#+
string ClassName = "MyClass";
string Variable = "i";
string Start = "0";
string End = "10";
#>

and here's the class (TestClass.tt) that uses it:

<#
this.ClassName = "TestClass";
this.Variable = "i";
this.Start = "0";
this.End = "20";
#>

<#@ include file="Path to template\Template.tt" #>

which results in this code:

//
// This code was generated by a tool. Any changes made manually will be lost
// the next time this code is regenerated.
//
using System;

public class TestClass
{
public static void HelloDude()
{
Console.WriteLine("Hello, Dude");

for (i = 0; i < 20;
i++)
{
}
}
}

Enjoy!