MSDN VB Dev Center Related Posts

In the first post of this series, we parsed our CSV file by simply splitting each line on a comma. While this works for simple files, it becomes problematic when consuming CSV files where individual fields also contains commas. Consider the following sample input:

CustomerID,COMPANYNAME,Contact Name,CONTACT_TITLE
ALFKI,Alfreds Futterkiste,Maria Anders,"Sales Representative"
ANATR,Ana Trujillo Emparedados y helados,Ana Trujillo,"Owner, Operator"
ANTON,Antonio Moreno Taqueria,Antonio Moreno,"Owner"

Typically when a field in a CSV file includes a comma, the field is quote escaped to designate that the comma is part of the field and not a delimiter. In the previous versions of this parser, we didn’t handle these cases. As a result the following unit test would fail given this sample data:

    <TestMethod()>
    Public Sub TestCommaEscaping()
        Dim data = New DynamicCsvEnumerator("C:\temp\Customers.csv")
        Dim query = From c In data
                    Where c.ContactTitle.Contains(",")
                    Select c.ContactTitle

        Assert.AreEqual(1, query.Count)
        Assert.AreEqual("Owner, Operator", query.First)
    End Sub

We could add code to handle the various escaping scenarios here. However, as Jonathan pointed out in his comment to my first post there are already methods that can do CSV parsing in the .Net framework. One of the most flexible ones is the TextFieldParser in the Microsoft.VisualBasic.FileIO namespace. If you code in C# instead of VB, you can simply add a reference to this namespace and access the power from your language of choice.

Retrofiting our existing implementation to use the TextFieldParser is fairly simple. We begin by changing the _FileStream object to being a TextFieldParser rather than a FileStream. We keep this as a class level field in order to stream through our data as we iterate over the rows.

In the GetEnumerator we then instantiate our TextFieldParser and set the delimiter information. Once that is configured, we get the array of header field names by calling the ReadFields method.

    Public Function GetEnumerator() As IEnumerator(Of Object) _
        Implements IEnumerable(Of Object).GetEnumerator

        _FileStream = New Microsoft.VisualBasic.FileIO.TextFieldParser(_filename)
        _FileStream.Delimiters = {","}
        _FileStream.HasFieldsEnclosedInQuotes = True
        _FileStream.TextFieldType = FileIO.FieldType.Delimited

        Dim fields = _FileStream.ReadFields
        _FieldNames = New Dictionary(Of String, Integer)
        For i = 0 To fields.Length - 1
            _FieldNames.Add(GetSafeFieldName(fields(i)), i)
        Next
        _CurrentRow = New DynamicCsv(_FileStream.ReadFields, _FieldNames)

        Return Me
    End Function

    Public Function MoveNext() As Boolean Implements IEnumerator.MoveNext
        Dim line = _FileStream.ReadFields
        If line IsNot Nothing AndAlso line.Length > 0 Then
            _CurrentRow = New DynamicCsv(line, _FieldNames)
            Return True
        Else
            Return False
        End If
    End Function

While we are at it, we also change our MoveNext method to call ReadFields to get the parsed string array of the parsed values in the next line. If this is the last line, the array is empty and we return false in the MoveNext to stop the enumeration. We had to make one other change here because in the old version, we passed the full unparsed line in the constructor of the DynamicCsv type and did the parsing there. Since our TextFieldParser will handle that for use, we’ll add an overloaded constructor to our DynamicCsv DynamicObject accepting the pre parsed string array:

Public Class DynamicCsv
    Inherits DynamicObject

    Private _fieldIndex As Dictionary(Of String, Integer)
    Private _RowValues() As String

    Friend Sub New(ByVal values As String(),
                   ByVal fieldIndex As Dictionary(Of String, Integer))
        _RowValues = values
        _fieldIndex = fieldIndex
    End Sub

With these changes, now we can run our starting unit test including the comma in the Contact Title of the second record and it now passes.

If you like this solution, feel free to download the completed Dynamic CSV Enumerator library and kick the tires a bit. There is no warrantee expressed or implied, but please let me know if you find it helpful and any changes you would recommend.

In the last post, I showed how to use DynamicObject to make consuming CSV files easier. In that example, we showed how we can access CSV columns using the standard dot (.) notation that we use on other objects. Using DynamicObject, we can refer to item.CompanyName and item.Contact_Name rather than item(0) and item(1).

While I’m happy about the new syntax, I’m not content with replacing spaces with underscores as that doesn’t agree with the coding guidelines of using Pascal casing for properties. Because we have control on how the accessors work, we can modify the convention. Let’s reconsider the CSV file that we’re working with. Here’s the beginning:

CustomerID,COMPANYNAME,Contact Name,CONTACT_TITLE,Address,City,Region,PostalCode,Country,Phone,Fax
ALFKI,Alfreds Futterkiste,Maria Anders,Sales Representative,Obere Str. 57,Berlin,NULL,12209,Germany,030-0074321,030-0076545
ANATR,Ana Trujillo Emparedados y helados,Ana Trujillo,Owner,Avda. de la Constituci¢n 2222,Mexico D.F.,NULL,5021,Mexico,(5) 555-4729,(5) 555-3745
ANTON,Antonio Moreno Taqueria,Antonio Moreno,Owner,Mataderos  2312,Mexico D.F.,NULL,5023,Mexico,(5) 555-3932,NULL

Notice here that the header row contains values with a mix of mixed case, all upper, words with spaces, and underscores. To standardize this, we could parse the header and force an upper case at the beginning of each word. That would take a fair amount of parsing code. As a fan of case insensitive programming languages, I figured that if we just strip the spaces and underscores and work against the strings in a case insensitive manner, I’d be happy. In the end, we’ll be able to consume the above CSV with the following code:

Dim data = New DynamicCsvEnumerator("C:\temp\Customers.csv")
Dim query = From c In data
            Where c.City = "London"
            Order By c.CompanyName
            Select c.ContactName, c.CompanyName, c.ContactTitle

To make this change, we change how we parse the header row and the binder name when fetching properties. In our DynamicCsvEnumerator, we already isolated the parsing of the header with a GetSafeFieldName method. Previously we simply returned the input value replacing a space with an underscore. Extending this is trivial:

    Function GetSafeFieldName(ByVal input As String) As String
        'Return input.Replace(" ", "_")
        Return input.
            Replace(" ", "").
            Replace("_", "").
            ToUpperInvariant()
    End Function

That's it for setting up the header parsing changes. We don't need to worry about spaces in the incoming property accessor because it's not legal to use spaces in a method name. I'll also assume that the programmer won't use underscores in the method names by convention. Thus, the only change we need to make in our property accessor is to uppercase the incoming field name to handle the case insensitivity feature. Here's the revised TryGetMember implementation.

    Public Overrides Function TryGetMember(ByVal binder As GetMemberBinder,
                                           ByRef result As Object) As Boolean
        Dim fieldName = binder.Name.ToUpperInvariant()
        If _fieldIndex.ContainsKey(fieldName) Then
            result = _RowValues(_fieldIndex(fieldName))
            Return True
        End If
        Return False
    End Function

All we do is force the field name to upper case and then we can look it up in the dictionary of field indexes that we setup last time. Simple yet effective.

When we wrote LINQ in Action we included a sample of how to simply query against a CSV file using the following LINQ query:

From line In File.ReadAllLines(“books.csv”) 
Where Not Line.StartsWith(“#”) 
Let parts = line.Split(“,”c) 
Select Isbn = parts(0), Title = parts(1), Publisher = parts(3)

While this code does make dealing with CSV easier, it would be nicer if we could refer to our columns as if they were properties where the property name came from the header row in the CSV file, perhaps using syntax like the following:

From line In MyCsvFile
Select line.Isbn, line.Title, line.Publisher

With strongly typed (compile time) structures, it is challenging to do this when dealing with variable data structures like CSV files. One of the big enhancements that is coming with .Net 4.0 is the inclusion of Dynamic language features, including the new DynamicObject data type. In the past, working with dynamic runtime structures, we were limited to using reflection tricks to access properties that didn't actually exist. The addition of dynamic language constructs offers better ways of dispatching the call request over dynamic types. Let's see what we need to do to expose a CSV row using the new dynamic features in Visual Studio 2010.

First, let's create an object that will represent each row that we are reading. This class will inherit from the new System.Dynamic.DynamicObject base class. This will set up the base functionality to handle the dynamic dispatching for us. All we need to do is add implementation to tell the object how to fetch values based on a supplied field name. We'll implement this by taking a string representing the current row. We'll split that based on the separator (a comma). We also supply a dictionary containing the field names and their index. Given these two pieces of information, we can override the TryGetMember and TrySetMember to Get and Set the property based on the field name:

Imports System.Dynamic

Public Class DynamicCsv
    Inherits DynamicObject

    Private _fieldIndex As Dictionary(Of String, Integer)
    Private _RowValues() As String

    Friend Sub New(ByVal currentRow As String,
                   ByVal fieldIndex As Dictionary(Of String, Integer))
        _RowValues = currentRow.Split(","c)
        _fieldIndex = fieldIndex
    End Sub

    Public Overrides Function TryGetMember(ByVal binder As GetMemberBinder,
                                           ByRef result As Object) As Boolean
        If _fieldIndex.ContainsKey(binder.Name) Then
            result = _RowValues(_fieldIndex(binder.Name))
            Return True
        End If
        Return False
    End Function

    Public Overrides Function TrySetMember(ByVal binder As SetMemberBinder,
                                           ByVal value As Object) As Boolean
        If _fieldIndex.ContainsKey(binder.Name) Then
            _RowValues(_fieldIndex(binder.Name)) = value.ToString
            Return True
        End If
        Return False
    End Function
End Class

With this in place, now we just need to add a class to handle iterating over the individual rows in our CSV file. As we pointed out in our book, using File.ReadAllLines can be a significant performance bottleneck for large files. Instead we will implement a custom Enumerator. In our customer enumerable, we initialize the process with the GetEnumerator method. This method opens the stream based on the supplied filename. It also sets up our dictionary of field names based on the values in the first row. Because we keep the stream open through the lifetime of this class, we implement IDisposable to clean up the stream.

Imports System.Collections

Public Class DynamicCsvEnumerator
    Implements IEnumerator(Of Object)
    Implements IEnumerable(Of Object)

    Private _FileStream As IO.TextReader
    Private _FieldNames As Dictionary(Of String, Integer)
    Private _CurrentRow As DynamicCsv
    Private _filename As String

    Public Sub New(ByVal fileName As String)
        _filename = fileName
    End Sub

    Public Function GetEnumerator() As IEnumerator(Of Object) _
        Implements IEnumerable(Of Object).GetEnumerator

        _FileStream = New IO.StreamReader(_filename)
        Dim headerRow = _FileStream.ReadLine
        Dim fields = headerRow.Split(","c)
        _FieldNames = New Dictionary(Of String, Integer)
        For i = 0 To fields.Length - 1
            _FieldNames.Add(GetSafeFieldName(fields(i)), i)
        Next
       
        Return Me
    End Function

    Function GetSafeFieldName(ByVal input As String) As String
        Return input.Replace(" ", "_")
    End Function

    Public Function GetEnumerator1() As IEnumerator Implements IEnumerable.GetEnumerator
        Return GetEnumerator()
    End Function

    Public ReadOnly Property Current As Object Implements IEnumerator(Of Object).Current
        Get
            Return _CurrentRow
        End Get
    End Property

    Public ReadOnly Property Current1 As Object Implements IEnumerator.Current
        Get
            Return Current
        End Get
    End Property

    Public Function MoveNext() As Boolean Implements IEnumerator.MoveNext
        Dim line = _FileStream.ReadLine
        If line IsNot Nothing AndAlso line.Length > 0 Then
            _CurrentRow = New DynamicCsv(line, _FieldNames)
            Return True
        Else
            Return False
        End If
    End Function

    Public Sub Reset() Implements IEnumerator.Reset
        _FileStream.Close()
        GetEnumerator()
    End Sub

#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls

    ' IDisposable
    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                _FileStream.Dispose()
            End If
            _CurrentRow = Nothing
        End If
        Me.disposedValue = True
    End Sub

    ' This code added by Visual Basic to correctly implement the disposable pattern.
    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class

VB:

Public Sub OpenCsv()
    Dim data = New DynamicCsvEnumerator("C:\temp\Customers.csv")
    For Each item In data
        TestContext.WriteLine(item.CompanyName & ": " & item.Contact_Name)
    Next

End Sub

C#:

[TestMethod]
public void OpenCsvSharp()
{
    var data = new DynamicCsvEnumerator(@"C:\temp\customers.csv");
    foreach (dynamic item in data)
    {
        TestContext.WriteLine(item.CompanyName + ": " + item.Contact_Name);
    }
}

In addition, since we are exposing this as an IEnumerable, we can use all of the same LINQ operators over our custom class:

VB:

Dim query = From c In data
            Where c.City = "London"
            Order By c.CompanyName
            Select c.Contact_Name, c.CompanyName

For Each item In query
    TestContext.WriteLine(item.CompanyName & ": " & item.Contact_Name)
Next

C#:

[TestMethod]
public void LinqCsvSharp()
{
    var data = new DynamicCsvEnumerator(@"C:\temp\customers.csv");
    var query = from dynamic c in data 
                where c.City == "London"
                orderby c.CompanyName
                select new { c.Contact_Name, c.CompanyName };

    foreach (var item in query)
    {
        TestContext.WriteLine(item.CompanyName + ": " + item.Contact_Name);
    }
}

Note: This sample makes a couple assumptions about the underlying data and implementation. First, we take an extra step to translate header strings that contain spaces to replace the space with an underscore. While including spaces is legal in the csv header, it isn't legal in VB to say: " MyObject.Some Property With Spaces". Thus we'll manage this by requiring the code to access this property as follows: "MyObject.Some_Property_With_Spaces".

Second, this implementation doesn't handle strings that contain commas. Typically fields in CSV files that contain commas are wrapped by quotes (subsequently quotes are likewise escaped by double quotes). This implementation does not account for either situation. I purposely did not incorporate those details in order to focus on the use of DynamicObject in this sample. I welcome enhancement suggestions to make this more robust.

While debugging in Visual Studio 2010, I noticed that the DataTip now has a new feature. At the right hand side of the variable window, there is now a pin icon.

Clicking on this pin icon adds the DataTip to the code window allowing it to float over the existing text.

In addition to allowing you to drill into the variable’s values as you would in the watch, locals, or autos windows, You can also add comments which remain with the pinned DataTip.

When you stop debugging, the DataTip will disappear. However when you debug into this method again, it will re-appear as long as it is pinned. As a bonus, it will persist even after closing and re-opening Visual Studio.

While Visual Studio 2010 definitely has some rough edges, I continue to be amazed by some of the new UX features that the next version will bring.

LINQ to SQL offers a quick mechanism to build a data tier in a n-Tier application. There’s a challenge when using the DBML designer in a Class Library. The designer stores the connection string in the Settings file. Although it appears that you can change it in the config file, any changes you make there will be ignored because they are actually retained in the generated Settings file.

While you could go into the DataContext’s .Designer file and change the location of the connection string, any changes you make there will be overwritten if you make changes to the DBML file. So what are you to do?

Remember, one of the nice features added to VB 9 and C# 3 was partial properties. With the DataContext, the code generators add a OnCreated method that is called as part of the context’s constructors. As a result, we can implement the partial method in a separate partial DataContext class that is not changed when the DBML is regenerated. Here’s a sample to do that for the context on this site (LinqBlogDataContext):

Imports System.Configuration

Public Class LinqBlogDataContext
  Private Sub OnCreated()
    MyBase.Connection.ConnectionString = _
      ConfigurationManager.ConnectionStrings("LinqBlogConnectionString") _
         .ConnectionString
    End Sub
End Class

When you do this, you can change the connection string in the app.config or web.config and it will be picked up in the business tier correctly. Realize that the design surface will still use the value in the settings in& the Class Library project instead of the config file.

There are a number of options you can consider when testing to see if a record exists using LINQ to SQL. Which one should you use? It depends… In general, check the generated SQL for various options in SQL Management Studio to see the how the various execution plans compare. For example, each of the following can tell you if a record exists.

Dim q1 = Customers.FirstOrDefault(Function(c) c.City="London") 

Dim q2 = Customers.Count(Function(c) c.City="London") 

Dim q3 = Customers.Any(Function(c) c.City="London") 

If we take a look at the generated SQL, we'll see that these produce the following SQL Statements:

-- Region Parameters
DECLARE @p0 NVarChar(6) = 'London'
-- EndRegion
SELECT TOP (1) [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], 
   [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], 
   [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [Customers] AS [t0]
WHERE [t0].[City] = @p0
GO

-- Region Parameters
DECLARE @p0 NVarChar(6) = 'London'
-- EndRegion
SELECT COUNT(*) AS [value]
FROM [Customers] AS [t0]
WHERE [t0].[City] = @p0
GO

-- Region Parameters
DECLARE @p0 NVarChar(6) = 'London'
-- EndRegion
SELECT 
    (CASE 
        WHEN EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [Customers] AS [t0]
            WHERE [t0].[City] = @p0
            ) THEN 1
        ELSE 0
     END) AS [value]

Analyzing this in SQL Management Studio shows that the second and third options take roughly 30% of the total time where the first takes 40%. As a result, it would appear that the first last two options were faster than the first. Realizing that the first has to hydrate a full object (and throw it away), we can recognize that there is additional data being transferred across the wire which can slow things down as well.

That doesn't tell the whole story. In general, I would expect that the last option would perform best because SQL Server could stop as soon as it finds the first matching record, where-as, it has to process the entire list (or index) to do a count. Usually with large volumes of data, Exists will out perform Count.

If we check the overall performance of the second two options, we can see that typically the last option performs slower than the second. I suspect this is due to the time it takes to process the expression tree and generate the SQL for these two methods. Also, the underlying data (Northwind) doesn't have that many records so the processing time may be unrealistic as compared to results from a larger database.

Also consider whether you need to work with the results or just want to know if they exist. If you need to work with them once you've determined that they exist, then using the .Any/Exists option would cause you to need a separate query to fetch the actual objects. In that case, FirstOrDefault would be better as you only need a single query to the database.

In general, there is no silver bullet. You need to test your options and determine which is best for your current situation.

Thanks' to Steve Smith’s Project Euler with LINQ, I’ve recently begun playing with the Project Euler questions seeing how far I can push my algorithm skills along with LINQ and LINQPad. LINQPad makes it easy to slap together some code samples and output results, including performance metrics, so I don’t need to worry with that plumbing code and can focus on creating fast code.

While working on one of the problems I realized the solution would offer a good opportunity to demonstrate a nuance of .Net iterators. Understanding this nuance may help some of you to grok how they work inside LINQ to Objects. In this case, the problem is Euler 10: Find the sum of all primes less than 2,000,000. We can begin by putting this into a LINQ query which elegantly reflects the desired outcome:

Dim query = _
  Aggregate num In Enumerable.Range(2, 1999998) _
  Where IsPrime(num) _
  Into Sum(CLng(num))

So far, so good. We just need to identify which numbers are prime and we’re finished. Easier said than done. I started by brute forcing this, which is often not a good idea with Euler which requires that your results take less than a second to process. My first stab was to set-up a list of found prime numbers. Then as I was iterating over my source data, I would iterate over each of the previously found primes to see if this number is divisible by that number. If it is, I would return that it was not prime. Otherwise, I would add it to the prime list and return that it is a prime. Here’s some code:

Private FoundPrimes As New List(Of Long)
  Function IsPrime(ByVal num As Long) As Boolean
  For Each prime In FoundPrimes
    If num Mod prime = 0 Then
      Return False
    End If
  Next

  Return AddToPrimes(num)
End Function 

Function AddToPrimes(ByVal num) As Boolean
  FoundPrimes.Add(num)
  Return True
End Function

Ok, so this works, but is quite slow (several minutes for 2 million numbers). We can’t speed this up using the PLINQ here because we need to move through our list in order, otherwise we will be adding numbers to our prime list that aren’t actually primes.

We can easily modify this by limiting the set of primes that we test a given number against. We know from mathematical proofs that the highest number we need to test any given number to see if it is a prime, is the square root of that given number. Thus, we modify the IsPrime method as follows:

Function IsPrime(ByVal num As Long) As Boolean
    Dim top = math.sqrt(num)
    For Each prime In FoundPrimes
        If prime > top Then Return AddToPrimes(num)
        If num Mod prime = 0 Then
            Return False
        End If
    Next
    Return AddToPrimes(num)
End Function

Running this through LINQPad now returns a respectable performance of 2.003 seconds. This is pretty good, but still doesn’t fit Euler’s guideline of sub-second performance. Back to the grindstone to find a better way. The performance hog here is obviously repeated iterating over the prime list for each given number. Perhaps if we could somehow flag multiples of each prime in our list as we find a prime, we could eliminate this iteration process. Thus instead of using a list of primes, we’ll create an array of bits (boolean) the size of the range we want to test:

Const numCount As Integer = 2000000
Dim allNums(numCount - 1) As Boolean

So, how do we find the primes in this list. First, realize that the Where and Select LINQ operators have overloads that include not only the input value, but also the index for the current value. To use this, we will need to modify our query because we can’t access this index using query expressions (at least not in VB). We’ll have to change our query to something like the following:

Dim query = allNums.Where(Function(num, index) IsPrime(index)) _
                   .Select(Function(num, index) index) _
                   .Sum(Function(num) num)

This would work, but the index for our Select method is not the index of the underlying data source, but rather the index for the item returned in the Where clause. As a result, we’ll need to process this index through our Where function and expose that in a scope wide enough that we can call back into it in our Select method. Here’s our revised code to this point, including a delegate pointer to a PrimeSieve method that will do the grunt work:

Const numCount As Integer = 2000000
Dim allNums(numCount - 1) As Boolean
Dim foundPrime As Integer 

Sub Main()
    Dim query = allNums _
            .Where(AddressOf PrimeSieve) _
            .Select(Function(ignore) CLng(foundPrime)) _
            .Sum(Function(num) num)
    Console.WriteLine(query)
End Sub

One thing to point out here is that we could have eliminated the Select clause if we were dealing with smaller numbers, but we need to widen our results to a long because the sum will overflow an integer type otherwise.

Now, on to our revised Prime algorithm. In this one, we pass in the current bit which we ignore and the index. The definition of Where requires that this returns a Boolean, so we’ll return false if this number has already been marked as a prime multiple. Otherwise, we’ll mark all multiples of this number as no longer prime and return true:

Private Function PrimeSieve(ByVal num1 As Boolean, ByVal index As Integer) As Boolean
    If index < 1 Then Return False
    If allNums(index) Then Return False 

    foundPrime = index + 1
    For num = index To (numCount - 1) Step foundPrime
        allNums(num) = True
    Next 

    Return True
End Function

At this point, you may be questioning if the underlying query will work if we keep referring back to a single FoundPrime variable. Here is where understanding iterators becomes important. Let’s begin by considering the basic definition of Where and Select (We’ll have to use C# here because VB doesn’t have a syntax for iterators yet):

static IEnumerable<t> Where<t>(IEnumerable<t> source, Func<t , bool> predicate) {
    foreach (T element in source) {
        if (predicate(element)) yield return element;
    }
}

static IEnumerable<s> Select<t , S>(IEnumerable<t> source, Func<t , S> selector) {
    foreach (T element in source) {
        yield return selector(element);
    }
}

What these are basically saying is: as some outside method calls us to move through our list, return values that meet the appropriate results. We are NOT doing a full iteration over our list in the Where method and returning the results to the next method (Select). If we did, select would indeed be using the same foundPrime value. Instead, we start moving through the results getting the first value that meets our criteria and passing control of our application on to the Select method. Select operates over it and passes control on to the next level - Sum. Sum then aggregates the result and pulls the next value returning control to the Where clause to find the next matching result.

Let’s step through the process with the first several numbers. The operation in our query doesn’t start until we actually start walking through the results. Thus Sum causes us to fetch the first number in our list. Where passes index 0 to the predicate PrimeSieve which returns false because it is under 1 (0 and 1 are not considered primes). Where continues to the next bit (index 1).

Since 2 is a prime, we then mark all multiples of 2 (4, 6, 8, 10, etc) true and return true. Because the predicate evaluated true, we yield that on to the Select method which pulls the foundPrime value and passes that on to Sum.

Sum then asks for the next number (3). Now, we re-enter the Where clause after the yield (internally using goto) and continue the iteration. We now do a PrimeSieve for index 2. This bit is still false, so we mark all multiples (6,9,12) as true. Of course 1/2 of these values are already true. I suspect that checking the bits before setting them would only slow our operation down, so I just go ahead and set it. We now pass operation on to select which pulls the new foundPrime and passes that value on to Sum to aggregate.

In the next iteration, we find that allNums(3) (the fourth number) is already marked true, thus we return false and to where and continue on to index 4 which is not yet marked true because this is the prime value 5. Rinse and repeat and we can efficiently evaluate as many numbers as we need.

So after all of this, what’s the performance difference?

A couple things to mention here:

• When dealing with small sets of iterations the amount of improvement is relatively small. This points to the reminder that you shouldn’t over optimize code if you don’t need to. The benefits as we increase the iterations becomes dramatic.
• The order in which the results are pulled is important. Thus, you can’t parallelize this algorithm (using PLINQ). In this example, the prime check has side effects of changing the underlying data source and setting an external property.
• This version relies on the way iterators work. You would not be able to substitute an observer pattern (like in LINQ to Events and the Reactive Framework). That is a push model rather than a pull model. As a result, it could be possible that you are processing the number 4 before it has been marked as not prime and your results would thus be invalid.

At DevLink I had the pleasure of presenting a session on LINQ to SQL Tricks and Tips. The slides and demos for LINQ to SQL Tricks and Tips are available on my download page if you are interested. Following the session, an attendee voiced the desire for LINQ to SQL to create interfaces while it creates the class definitions in order to make it easier to mock the entities in unit testing scenarios.

As part of the presentation, I showed Damien Guard’s L2ST4 code generation template. The great thing about these templates is that they are fully customizable.  If you’ve been following this blog, you may remember my post showing adding property Get logging to LINQ to SQL with T4. In this post, I’m going to show you how to add the ability to generate and implement interfaces for the table’s columns. I’m only going to show implementing the table’s columns and not the associations. Additionally, you will need to modify this if you use inheritance in your LINQ to SQL models. I’m using the VB Template in this example, but the C# changes are very similar. Hopefully, you will see how easy it is to make these kinds of changes and can add these extensions yourself if necessary.

Ok, so let’s get started. First off, we will set up a flag in the options so that we can toggle creating the interfaces in our code generation. At the top of the file, change the declaration of the options anonymous type adding the CreateInterfaces = true as follows:

var options = new {
	DbmlFileName = Host.TemplateFile.Replace(".tt",".dbml"), // Which DBML file to operate on (same filename as template)
	SerializeDataContractSP1 = false, // Emit SP1 DataContract serializer attributes
	FilePerEntity = false, // Put each class into a separate file
	StoredProcedureConcurrency = false, // Table updates via an SP require @@rowcount to be returned to enable concurrency	
	EntityFilePath = Path.GetDirectoryName(Host.TemplateFile), // Where to put the files	
	CreateInterfaces = true // Add interfaces for each table type
};

Next, we define the interfaces. To make things easier, we’ll just declare the interface for each table just before we define the table itself. This will keep the interfaces in the same namespace and the same file as the tables (if you use the FilePerEntity option). The biggest trick is to figure out where to insert this code.  Search for the following text in the existing template: “if (data.Serialization && class1.IsSerializable) {“. Change the template between the Namespace declaration and the serialization settings as follows:

Namespace <#=data.EntityNamespace#>	

<#		}
#>		

<#
if (options.CreateInterfaces) { #>
	'Interface
	<#=code.Format(class1.TypeAttributes)#>Interface I<#=class1.Name#>
	<#			foreach(Column column in class1.Columns) {#>
	<# if (column.IsReadOnly) {#>ReadOnly <#}#>Property <#=column.Member#> As <#=code.Format(column.Type)#>
	<# } 
	#>
	End Interface
<# } 
#>
<#		if (data.Serialization && class1.IsSerializable) {

Here we create an interface named IClass where Class is the actual name of the class we are going to generate.  Once we have the interface created, we iterate over each of the columns defining the properties that correspond to the table’s columns.

Next, we need to alter the Class definition to have it implement our new interface. Scrolling down about 15 lines, find the line where we declare that the class implements the INotifyPropertyChanging and INotifyPropetyChanged interfaces. Change this line to read as follows:

	Implements INotifyPropertyChanging, INotifyPropertyChanged<#
if (options.CreateInterfaces) {#>, I<#=class1.Name#><#}#>

If we were using C#, our job would be done. However, VB requires that interfaces be implemented explicitly.  Since we are generating this code, making this last change is relatively easy as well. Scroll down to the definition of the property getter and setter (in my copy, this is line 334). Change the property definition to read as follows:

#>		<#=code.Format(column.MemberAttributes)#><# if (column.IsReadOnly) {#>ReadOnly <#}#>Property <#=column.Member#> As <#=code.Format(column.Type)#><# 
			if (options.CreateInterfaces) {#> Implements I<#=class1.Name#>.<#=column.Member#><#}#>

			Get

Ok. We’re done. All we need to do is save our changes to the TT file and it will regenerate the classes from our DBML assuming you have already set your project up to use the TT files rather than the default LINQ to SQL generator.

As I continue re-writing this site to use MVC, I find more substructures that I can refactor easily. In the original implementation for the menu, I used the asp:Menu control. As I was working to theme the site, I had problems getting it to work acceptably with CSS styles. I also didn't like the reliance on tables.

In an effort to improve on it, I found the method of using unordered lists with list items for the menus (<ul> <li>). I then moved to a modified version of the UL SiteMap menu discussed by Byant Likes.

In moving to MVC, I was looking for a better option and found one on the MVC tutorial site. This builds the menu with a StringBuilder. I had a couple problems with this implementation however:

• By using a StringBuilder, you don't get compiler assistance in validating the markup.
• The implementation doesn't handle security trimming.
• It only handles a single level of menu items.
• For Each loops seem to be an anti-pattern for me with my LINQ experience.

To fix these issues, I figured we could re-write this relatively easily using XML Literals and VB with a recursive call to handle the potential n-levels of SiteMenuItems possible in the SiteMap XML specification. Even without adding any of the additional functionality, we can drastically simplify the implementation in the MVC Tutorial by re-writing it in XML Literals in VB:

Dim xMenu1 = <div class="menu">
                <ul id="menu">
                   <%= From node In SiteMap.RootNode.ChildNodes _
                                    .OfType(Of SiteMapNode)() _
                       Select <li class=<%= if(SiteMap.CurrentNode Is node, _
                                               "active", _
                                               "inactive") %>>
                                 <a href=<%= % node.Url>>
                                    <%= helper.Encode(node.Title) %></a>
                     </li> %>
                 </ul>
              </div>
Return xMenu1.ToString()

There are a couple things to point out here. First, the SiteMap.RootNode.ChildNode property returns a list of Object rather than SiteMapNode items. We can fix that by purposely limiting the results and strongly typing it at the same type using the .OfType(Of T) extension method.

Second, We eliminate the for each loop by projecting the new li items in the select clause. In this, we use the ternary If to determine if the node in the iteration is the same as the one selected. If it is, we apply the "active" style. The rest is relatively straight forward.

At this point, we have fixed issues 1 and 4 from my objection list above. Next, let's deal with the n=levels of menu items. To do this, we will replace the LINQ query with a recursive function call. The Menu helper extension now looks like the following:

Imports System.Runtime.CompilerServices

Namespace Helpers
    Public Module MenuHelper
        <Extension()> _
        Public Function Menu(ByVal helper As HtmlHelper) As String
            Dim xMenu = <div class="menu">
                            <ul id="menu">
                                <%= AddNodes(SiteMap.RootNode, helper) %>
                            </ul>
                        </div>
    
            Return xMenu.ToString()
        End Function
    End Module
End Namespace

Now we have an extremely clean and concise XML building of the basic structure. The hard work comes in the AddNodes method.

Private Function AddNodes(ByVal currentNode As SiteMapNode, ByVal helper As HtmlHelper) _
                 As IEnumerable(Of XElement)

   Return From node In currentNode.ChildNodes.OfType(Of SiteMapNode)() _
          Select <li class=<%= If(SiteMap.CurrentNode Is node, "active", "inactive") %>>
                     <a href=<%= node.Url %>><%= helper.Encode(node.Title) %></a>
                     <%= If(node.ChildNodes.Count > 0, _
                          <ul class="child">
                              <%= AddNodes(node, helper) %>
                          </ul>, Nothing) %>
                  </li>
End Function

Essentially this function moves the LINQ projection we did in the original re-write into a separate method. This method takes a SiteMapNode and returns rendered lists of XElements. In addition to generating the HTML for the current node's children, we also check to see if the respective child in turn has  are any child nodes and if so, we create a new unordered list and recursively call back into AddNodes to render those children as well. By using a recursive function, we can handle any level of children nodes that the SiteMap throws at us.

In order to add security trimming, we simply need to add a where clause. The SiteMap contains a non-generic IList that happens to contain strings. To use LINQ on this, we use the .Cast method to turn it into a generic IEnumerable(Of String). With that in place, we can use the .Any extension method to find if any of the roles specified in the SiteMap source meet the criteria where the HttpContext's current user is in at least one of those roles. We'll also check to see if there are no roles specified for that node (which means that it is not trimmed and all users can access the node). Here is the revised body of the AddNodes method:

Return From node In currentNode.ChildNodes.OfType(Of SiteMapNode)() _
       Where node.Roles.Count = 0 OrElse _
             node.Roles.Cast(Of String).Any(Function(role) _
                                            HttpContext.Current.User.IsInRole(role)) _
       Select <li class=<%= If(SiteMap.CurrentNode Is node, "active", "inactive") %>>
                  <a href=<%= node.Url %>><%= helper.Encode(node.Title) %></a>
                  <%= If(node.ChildNodes.Count > 0, _
                      <ul class="child">
                          <%= AddNodes(node, helper) %>
                      </ul>, Nothing) %>
              </li>

That's it. The only thing left is to manage the css styles to handle the fly-outs. You should be able to find plenty of sites that demonstrate how to set that up. If you can thinq of any enhancements to this, let me know.

While translating this site over to MVC, I ran into a challenge when converting the RSS feed implementation. Currently I'm using XML Literals to generate the RSS and I could certainly continue to use that track from the Controller similar to the Sitemap implementation on Mikesdotnetting. However, putting the XML generation in the controller directly conflicts with the separation of concerns that MVC embraces. If I were only displaying one RSS feed, I might be willing to break this here. However, I'm rendering a number of different RSS feeds here: Posts, Posts by Category, and Files.

Since it would be good to have a reusable view, I decided to create a single view which various controllers can use. I was dynamically generating the XML in the past so my queries would now need to project into a type that the view can consume. Here we have several alternatives:

1. Create a strongly typed object structure which is strictly used to shape our results for the shared Rss view.
2. Project into a list of System.ServiceModel.SyndicationItem and then bind to that.
3. Project into an anonymous type and figure out a way to bind to that projection in our view.

I initially thought I would go down the second route similar to the implementation discussed on the DeveloperZen post. However, I wanted to support some of the RSS extensions including comments and enclosures that aren't directly supported in that implementation.

At first I was unsure how to bind an anonymous projection in a View, so I eliminated option 3 and implemented option 1 similar to the strongly typed implementation discussed on Mikesdotnetting blog. To do this, I needed to build the following set of strongly typed structures:

Public Structure RssElement
    Public Title As String
    Public Link As String
    Public PubDate As DateTime
    Public PermaLink As String
    Public TrackBackUrl As String
    Public CommentRss As String
    Public CommentUrl As String
    Public CommentCount As Integer
    Public Description As String
    Public Categories() As Category
    Public Enclosures() As Enclosure
End Structure

Public Structure Category
    Public Url As String
    Public Title As String
End Structure

Public Structure Enclosure
    Public Url As String
    Public Length As Integer
    Public Type As String
End Structure

If I were using VB 10, this would have been done with auto-implemented properties. However I went with structures at this point because I didn't want to type that much for something that was going to be view only anyway.

With this structure in place, I could go ahead and implement the controller and view. The controller simply projected into this new object structure in the Select clause of a LINQ query. The view then was able to consume this as we could strongly type the view as a ModelView(Of IEnumerable(Of RssElement)). Here's the view that I created:

<%@ Page Language="VB" ContentType="application/rss+xml"
         Inherits="System.Web.Mvc.ViewPage(Of IEnumerable(Of RssElement))" %>
<rss version='2.0' xmlns:dc='http://purl.org/dc/elements/1.1/' 
    xmlns:slash='http://purl.org/rss/1.0/modules/slash/' 
    xmlns:wfw='http://wellformedweb.org/CommentAPI/'
    xmlns:trackback='http://madskills.com/public/xml/rss/module/trackback'>
   <channel>
       <title>Thinq Linq</title>
       <link><%=Url.Action("Post") %></link>
       <description>LINQ and related topics.</description>
       <dc:language>en-US</dc:language>
       <generator>LINQ</generator>
       <% For Each item In Model%>
            <item>
                      <title><%=item.Title%></title>
                      <link><%=item.Link%></link>
                      <pubDate><%=item.PubDate%></pubDate>
                      <guid isPermaLink="false"><%= item.PermaLink %></guid>
                      <dc:creator>jwooley</dc:creator>
                      <slash:comments><%=item.CommentCount%></slash:comments>
                      <trackback:ping><%=item.TrackBackUrl%></trackback:ping>
                      <comments><%=item.CommentUrl%></comments>
                      <wfw:commentRss><%=item.CommentRss%></wfw:commentRss>
                      <wfw:comment><%=item.CommentUrl%></wfw:comment>
                      <description><%=Html.Encode(item.Description)%></description>
                      <% if Not item.Categories is Nothing then %>
                           <%For Each c In item.Categories%>
                              <category domain="<%= c.Url %>"><%=c.Title%></category> 
                          <% Next %>
                      <% End If%>
                      <%If Not item.Enclosures is Nothing then %>
                          <% For Each e In item.Enclosures%>
                                <enclosure 
                                    url='<%=e.Url %>'
                                    length='<%=e.length %>'
                                    type='<%=e.type %>' />
                          <% Next%>
                      <% end if %>
                  </item>
       <% Next%>
   </channel>
</rss>

In comparing this code with the XML Literal implementation, they are amazingly similar. With MVC, I may be able to live without XML Literals in the views as we simply replace a LINQ projection with a For Each loop. Notice here I check to see if the Categories and Enclosures objects exist before I enumerate over each of those arrays. This is because the Post feed doesn't include enclosures and the File feed doesn't need Categories. This flexibility allows us to create a reusable view for all of our needs.

But, I'm not quite happy with this implementation. I would prefer not to have to declare the additional structure layer just to pass the view something to consume. In this case, it feels like we are having the Controller consume the data Model and create a ModelView (RssElement) to be consumed by the View. We don't really need a new pattern (M-C-MV-V), do we? Instead, I would like to be a bit more "Dynamic" in my implementation so that I didn't need this class and could simply project into an anonymous type and eliminate the RssElement structures entirely.

After a bit of reflection, I realized that this is a case where VB is uniquely positioned crossing the bridge between strong typing and dynamic languages. Normally, I do not recommend using the Option Strict Off option, but this is one case where it does come in useful. To begin, we'll remove those pesky structures. Next, we'll change the controllers to project into anonymous types. Here's the revised code for the Post Rss Controller:

    Function ShowPosts() As ActionResult
        Dim posts = From p In (From post In Context.PostItems _
                    Order By post.PublicationDate Descending _
                    Take 20).AsEnumerable _
                    Select New With { _
                        .Description = p.Description, _
                        .Link = Url.Action("Title/" & p.TitleUrlRewrite & ".aspx", "Post"), _
                        .PubDate = p.PublicationDate.ToString("r"), _
                        .Title = p.Title, _
                        .TrackBackUrl = Url.Action("Trackback/" & p.Id, "Seo"), _
                        .PermaLink = "42f563c8-34ea-4d01-bfe1-2047c2222a74:" & p.Id, _
                        .Categories = (From c In p.CategoryPosts _
                                    Select New With { _
                                         .Title = c.Category.Title, _
                                         .Url = Url.Action("Category/" & c.CategoryID, _
                                                           "Post")}).ToArray, _
                        .Commentrss = Url.Action("Comment/" & p.Id, "Rss"), _
                        .CommentUrl = Url.Action("Title/" & p.TitleUrlRewrite, "Post"), _
                        .CommentCount = p.Comments.Count, _
                        .Enclosures = Nothing}

        Return View("ShowFeed", posts.ToList)
    End Function

Notice here, that we have to be very careful with our property naming and can't leave off anything. This is why we have to initialize our .Enclosures property to Nothing because we can't initialize it to an empty collection. Since our view checks to see if the object is null before binding it, we are fine here.

Now back to the view. How do we tell the view what type of data the Model contains if we can't name it? Here's where option strict off comes in handy. However, in a View page, we can't simply state Option Strict Off at the top of our code. Instead, we need to set the CompilerOptions to set optionstrict- as follows:

<%@ Page Language="VB" ContentType="application/rss+xml" 
         CompilerOptions="/optionstrict-" Inherits="System.Web.Mvc.ViewPage" %>

In this case, we are not only setting the CompilerOptions, but removing the generic type definition in the Inherits clause. The rest of the view remains intact. Now, we can consume our anonymous type (because we aren't typing the view) and let the Option Strict setting dynamically resolve our method and type names. Notice here, if we were using C# 4.0, we wouldn't be able to use the Dynamic option and state that the page inherits ViewPage<Dynamic> because we can't project into a Dynamic type in our LINQ query.

Now that we have modified our view, we can reuse it. First move it to the Shared folder so that the view will be accessible regardless of which controller tries to consume it. Next, we create other controllers making sure that all of the properties are projected correctly in our LINQ query.

    Function Rss() As ActionResult
        Return View("ShowFeed", _
            From f In GetFiles() _
            Select New With { _
                .Description = f.Description, _
                .Link = "http://www.ThinqLinq.com/" & f.URL, _
                .PubDate = f.LastWriteTime.ToString("r"), _
                .PermaLink = "42f563c8-34ea-4d01-bfe1-2047c2222a74:" & f.Name, _
                .TrackBackUrl = "", _
                .CommentRss = "", _
                .CommentUrl = "", _
                .CommentCount = 0, _
                .Categories = Nothing, _
                .Title = f.Name, _
                .Enclosures = New Object() {New With { _
                                            .Length = f.Length, _
                                            .Type = "application/x-zip-compressed", _
                                            .Url = "http://www.ThinqLinq.com/" & f.URL}}})
    End Function

Be aware. Here we are playing with the dangerous part of dynamic languages. We no longer get the compiler to ensure that our type includes all of the necessary properties. If we forget a property or mis-type the property name, we will only know about it when a run-time exception is thrown. Of course, since this is MVC, we can use unit tests to check our type. With dynamic programming, think of the compiler as just another unit test. You need to write the rest of them by hand.

While I like the flexibility that the new dynamic option provides, I miss the comfort that comes from strong typing. Also, I haven't checked the performance differences between these implementations and suspect that the previous strongly typed option may out perform this one. With optimizations in VB 10 around Option Strict Off, I suspect that the performance differences may shrink, but would need to test this as well.

I'll also admit to being relatively new to MVC and welcome better alternatives from those who have been using it longer. What do you Thinq?

At CodeStock, I attended Rod Paddock's intro to JQuery session since I hadn't played with JQuery yet. As often happens when I go to conferences, being in the different environment starts to get the mind thinking in different ways. Sometimes the benefit of the conference isn't necessarily something stated directly, but rather a thought when the mind wanders. One such thought occurred during Rod's presentation where I thought that it might be interesting to "query" sets of text over a larger document and apply formatting to selected words (similar to how Visual Studio colors keywords and other code elements).

A quick search on syntax highlighting found that I was not alone thinking that JQuery might be a good option for syntax highlighting. Chili is a JQuery based code highlighter that already supports a number of languages. It is relatively easy to incorporate into the site. First, you need to add a script reference to JQuery by adding the following:

<script type="text/javascript" src=http://jquery.com/src/jquery-latest.pack.js" />

Next, we add a link to the chili script code:

<script type="text/javascript" src="jquery/chili/jquery.chili-2.2.js" />

Third, we designate the path that contains the various language specific implementation details in a script block:

<script id="setup" type="text/javascript"> 
    ChiliBook.recipeFolder = "jquery/chili/";
</script>

Now, when we want to add highlighting to our code, we include it inside a <code> tag that is assigned to the class name of the language we want to colorize. Unlike the popular SyntaxHighlighter, Chili doesn't require you to specify the location of each language individually. It loads it dynamically based on matching up the file name with the class name.

To see how I added colorization to the source on the above code, see how the actual code is wrapped by a <code class="js"> … </code> tag. In this case, there is a js.js file in the recipeFolder that Chili uses to highlight this code. In addition, I'm wrapping the code tag inside a pre tag to eliminate otherwise unnecessary markup (like &nbsp; and <br />). This makes copying and pasting the code easier.

<pre><code class="js">

  <script id="setup" type="text/javascript">
     ChiliBook.recipeFolder = "jquery/chili/";
  </script>

</code></pre>

There's a problem with directly integrating Chili into this site however. Chili does not include a native VB syntax highlighter. However, adding new code definitions is as simple as adding a new .js file containing a collection of JSON objects defining what terms are to be colorized and how the styles should be applied. For the current VB implementation, I've added the following colorizations:

• Comments are green
• String literals are red
• Processing instructions (like #Region and #If) are silver
• Keywords and LINQ keywords are #4040c2
• XML Literal expression hole symbols (<%= and %>) are a bit different as they use a yellow background with dark gray foreground, but we can easily set this through the style tag.

To do this, we set up a JSON structure to contain the various Regular Expression match patterns and the corresponding styles:

{
      _name: "vb"
    , _case: true
    , _main: {
          com    : { 
              _match: /'.*/ 
            , _style: "color: green;"
        }
        , string : { 
              _match: /(?:\'[^\'\\\n]*(?:\\.[^\'\\\n]*)*\')|(?:\"[^\"\\\n]*(?:\\.[^\"\\\n]*)*\")/ 
            , _style: "color: red;"
        }
        , preproc: { 
              _match: /^\s*#.*/ 
            , _style: "color: silver;"
        }
        , keyword: { 
              _match: /\b(?:AddHandler|AddressOf|AndAlso|Alias|And|Ansi|As|Assembly|Auto|
Boolean|ByRef|Byte|ByVal|Call|Case|Catch|
CBool|CByte|CChar|CDate|CDec|CDbl|Char|CInt|Class|CLng|CObj|
Const|CShort|CSng|CStr|CType|Date|Decimal|Declare|Default|Delegate|
Dim|DirectCast|Do|Double|Each|Else|ElseIf|End|Enum|Erase|Error|Event|Exit|
False|Finally|For|Friend|Function|Get|GetType|GoSub|GoTo|Handles|
If|Implements|Imports|In|Inherits|Integer|Interface|Is|Let|Lib|Like|Long|Loop|
Me|Mod|Module|MustInherit|MustOverride|MyBase|MyClass|
Namespace|New|Next|Not|Nothing|NotInheritable|NotOverridable|
Object|On|Option|Optional|Or|OrElse|Overloads|Overridable|Overrides|
ParamArray|Preserve|Private|Property|Protected|Public|
RaiseEvent|ReadOnly|ReDim|REM|RemoveHandler|Resume|Return|
Select|Set|Shadows|Shared|Short|Single|Static|Step|Stop|String|Structure|
Sub|SyncLock|Then|Throw|To|True|Try|TypeOf|Unicode|Until|Variant|
When|While|With|WithEvents|WriteOnly|Xor)\b/ 
            , _style: "color: #4040c2;"
        }
        , linqkeyword: { 
              _match: /\b(?:From|Select|Where|Order By|Descending|Distinct
|Skip|Take|Aggregate|Sum|Count|Group|Join|Into|Equals)\b/ 
            , _style: "color: #4040c2;"
        }
        , xmlexpressionhole: {
              _match: /\<%=|\%>/
            , _style: "background: #fffebf; color: #555555;"
        }
    }
}

If you want, you can download this file at http://thinqlinq.com/jquery/chili/vbasic.js. Now, to use the new definition, simply add the file to the path you defined as the chiliBook.RecipeFolder above. Then add code to your page like the following:

<pre><code class="vbasic">
   Private Function FormatCategories(ByVal post As PostItem) As String
        If post.CategoryPosts.Count > 0 Then
            'Categories found. Return them
            Dim response = _
                From catPost In post.CategoryPosts _
                Select val = _
                    <span>
                        <a href=<%= "default.aspx?CategoryId=" & _
                        catPost.Category.CategoryId %>>
                           <%= catPost.Category.Description.Trim %></a>
                    </span>.ToString()
            Return String.Join(", ", response.ToArray)
        Else
            Return ""
        End If
    End Function
</code></pre>

There are a couple issues with this implementation: First, the highlighting only works if you view the page on the site. If you are viewing this through an aggregator, you won't see the syntax highlighting. Personally, I find this to be an acceptable tradeoff to the alternative--injecting the styles inline with the code as is done with the CopySourceAsHtml project or the Windows Live Writer VSPaste plug-in. Although the code is correctly highlighted when viewed from an aggregator, it is horrendous when consumed by a reader for the blind with screen reader systems.

The second issue with this implementation is that it doesn't take context into account. As a result, if you have an object with the same name as one of the keywords, it will be highlighted  incorrectly. This will become more of an issue in VS 2010 when we (finally) get type colorization in VB. To do type colorization, we need access to the object symbols which are unavailable outside of the compiler's environment.

The third issue is that this version doesn't correctly colorize the XML Literals. While I'm sure it is possible, I'm not enough of a Regular Expression expert to figure out all of the options required to enable syntax highlighting for the XML Literals. If someone wants to add that, I would love to try out your suggestions.

With SQL Server, you can use the For Xml clause (read more in BOL). The quickest option is to add For XML Auto at the end of a SQL statement. You can do this with dynamic SQL or inside a stored proc. If you use a stored proc, the DBML tool doesn't recognize this as XML (and return it as an XElement as it does for XML Data type columns).

Regardless of whether you are using stored procs or dynamic SQL, the server returns the result as an array of strings broken up into 4000 character chunks. It is your responsibility to piece this back together. You can concatenate the strings and parse the XML, however there is no true root node in this return set, only a series of XML elements.

Since you are not going to be able to rely on the generated method stub for the procedure, you may want to consider using ExecuteQuery directly and handle the string parsing. If you define this in a partial class for your context, it will appear to calling code as if it came directly from the database pre formatted.  For example, here is some code that returns the customers from Northwind as an XElement:

Public Function CustomerAsXml() As XElement
    Dim returnVal = Me.ExecuteQuery(Of String)("Select * from Customers For XML Auto")
    Dim fullString = String.Concat((From x In returnVal Select x).ToArray)
    Dim xml = XElement.Parse("<root>" & fullString & "</root>")
    Return Xml
End Function

You could substitute the name of your stored proc with parameters in the place of this dynamic SQL and it should work equally well.

Dim returnVal = Me.ExecuteQuery(Of String)("CustomersXml", New Object() {})
 

When I added the Pingbacks and Trackbacks, I changed the implementation of the Comments to use an inheritance model (TPH) where Comments, Trackbacks, and Pingbacks all inherit from the abstract CommentBase class. To refresh your memory, here's the appropriate part of the DBML designer surface:

While this works fine with minimal changes when viewing data, it can cause problems if you are using the LinqDataSource for editing values. When trying to update or delete a record, you may encounter a message similar to the following:

Cannot create an abstract class.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.MissingMethodException: Cannot create an abstract class.

So what's happening here? When the page is posted back, the LinqDataSource tries to re-create the object based on the incoming values so that it can reattach it to the context to perform the update. In the case of our polymorphic CommentBases collection, the data source doesn't know how to recreate the necessary object and thus throws the exception.

Normally when trying to override CRUD behaviors with the LinqDataSource, you would use the Deleting, Inserting, Updating, and Selecting methods. However in this case, the datasource has already tried to hydrate the object in order to pass it into the method handler as part of the event args. Thus handling it here is too late.

As an alternative, we can intercept the request earlier in the process. In the case of the ListView control, we can intercept this in the ItemDeleting, ItemUpdating and ItemInserting event handlers. The key here is that when we're done intercepting the request on this level that we need to keep the LinqDataSource from receiving the request by setting the Cancel property of the EventArgs and cleaning up ourselves.

When deleting a record, this is a simple process. First, we grab the key of the record being updated from the e.Keys collection. We then use that to search for the comments that have that ID passing the results to DeleteAllOnSubmit. Once the change is saved, we block future notifications setting the cancel. Here's a sample implementation:

Protected Sub ListView1_ItemDeleting(ByVal sender As Object, _
                  ByVal e As System.Web.UI.WebControls.ListViewDeleteEventArgs) _
                  Handles ListView1.ItemDeleting
    If e.Keys.Count > 0 Then
        Using dc As New LinqBlog.BO.LinqBlogDataContext
            dc.CommentBases.DeleteAllOnSubmit(From c In dc.CommentBases _
                                              Where c.CommentId = CInt(e.Keys(0)))
            dc.SubmitChanges()
            'Suppress the default behavior of the binding source
            'The binding source doesn't know how to instantiate the
            'original value type on post-back due to the MustInherit inheritance
            ListView1.EditIndex = -1
            e.Cancel = True
        End Using
    End If
End Sub

Updating follows a similar process. In this case, we get the object we are editing based on the value in the Keys and then replay the changes based on the arg's NewValues collection.

Protected Sub ListView1_ItemUpdating(ByVal sender As Object, _
                   ByVal e As System.Web.UI.WebControls.ListViewUpdateEventArgs) _
                   Handles ListView1.ItemUpdating
    If e.Keys.Count > 0 Then
        Using dc As New LinqBlog.BO.LinqBlogDataContext
            Dim id As Integer = CInt(e.Keys(0))
            Dim item = dc.CommentBases.Where(Function(c) c.CommentId = id).FirstOrDefault
            If Not item Is Nothing Then
                'Set the values
                For i = 0 To e.NewValues.Count - 1
                    CallByName(item, e.NewValues.Keys(i), CallType.Set, e.NewValues(i))
                Next
                dc.SubmitChanges()

                'Suppress the default behavior of the binding source
                'The binding source doesn't know how to instantiate the
                'original value type on post-back due to the MustInherit inheritance
                ListView1.EditIndex = -1
                e.Cancel = True
            End If
        End Using
    End If
End Sub

Since we're not implementing inserting from the grid for comments, we don't need to code that piece. I'll leave it to you, the reader to try that one. Realize that you will need to determine the CommentType prior to creating the appropriate instance object before setting the values on insert. Otherwise, the process is basically the same.

One other thing to keep in mind is handling concurrency. Because we are refetching the object as part of the CUD operation, we are effectively throwing away the true original values. Of course we can take care of that if we use a database issued timestamp (rowversion) for concurrency.

Recently, I discussed Sending and Receiving TrackBacks on this blog. The TrackBack API is not the only mechanism which blog engines use to communicate between each other about the links that are included in individual posts. Wikipedia lists three methods to keep track of which articles are cross linked: Refback, Trackback, and Pingback. Of these, perhaps the trickiest to implement is the Pingback because it uses the xml-rpc style of communication rather than SOAP or REST that most .Net programmers are familiar with.

Thankfully, Cook Computing has released an open source implementation for xml-rpc which makes programming for it similar to programming .Net services by relying on attributes to specify the methods and contracts that a class consumes. The source and documentation can be found at http://www.xml-rpc.net/. Once we add a reference to the CookComputing.XmlRpcV2.dll, we can begin coding our Pingback implementation.

Unlike WCF, we don't need to perform a lot of configuration steps. We simply add a Generic Handler (.ashx) file and point it to the class that will perform the implementation. In this case, our handler will be called PingbackService.ashx and consists of the following:

<%@ WebHandler Language="VB" Class="LinqBlog.BO.Services.PingbackService" %>

As they say in the Staples advertisement, "That was easy!" Next, we implement the PingbackService class in our business tier. Similar to the Trackback, we'll separate this process into the sending operation and the receiving operation.

Sending Pingbacks

The steps to send a pingback are similar to those for a Trackback:

• Find the links in our post.
• Check the post's to see if the hosting server supports the Pingback API
• Send the pingback to the hosting server's URI

The first step is identical to the TrackBack, so refer to the ParseAndSendTrackbacks method from my previous post for that code. Before we can send the ping, we need to check the server for our link to see if it supports Pingbacks. The Pingback Specification allows for two options for server discovery: based on the presence of a X-Pingback HTTP header looking like:

X-Pingback: http://www.ThinqLinq.com/Api/PingbackService.ashx

Or a <link> tag in the web page as follows:

<link rel="pingback" href="http://www.ThinqLinq.com/Api/PingbackService.ashx" />

Thus we need to check for the presence of either of these auto discovery mechanisms in our check to find the URI for the pingback server. Here's a sample implementation which takes a post's url and returns the url of the server's service (or an empty string if the server doesn't support Pingbacks).

Private Function GetPingbackServer(ByVal destination As String) As String
    Dim destUri As Uri = Nothing
    If Not Uri.TryCreate(destination, UriKind.Absolute, destUri) Then
        'Make sure we have a valid uri and that it isn't from this site (relative uri)
        Return ""
    End If

    Dim server As String = ""
    Dim req = DirectCast(WebRequest.Create(destination), HttpWebRequest)
    req.Referer = "http://www.thinqLinq.com"
    Using resp = DirectCast(req.GetResponse, HttpWebResponse)
        'Check headers for x-Pingback
        server = resp.Headers.Get("x-Pingback")
        If server <> "" Then Return server

        'Check for link element
        If resp.Headers.AllKeys.Contains("Content-Type") AndAlso _
            resp.Headers("Content-Type").StartsWith("text/html") Then

            Dim client As New WebClient()
            client.UseDefaultCredentials = True
            Dim page = client.DownloadString(destination)
            Dim regexString = _
                 String.Format("<link rel={0}pingback{0} href={0}[a-z0-9:\.\/_\?\-\%]*{0}", _
                               ControlChars.Quote)

            Dim match = Text.RegularExpressions.Regex.Match(page, regexString, _
                                                RegexOptions.IgnoreCase).Value
            If Not String.IsNullOrEmpty(match) Then
                Dim startIndex As Integer = match.IndexOf("href=") + 6
                Dim ret = match.Substring(startIndex, match.Length - startIndex - 1)
                Return ret
            End If
        End If
    End Using
    Return ""
End Function

In this case, checking the headers is easy. Finding the <link> link tag takes a combination of Regular Expressions and string parsing since the <link> tag can either be HTML or XHTML compliant (and thus we can't use XML parsing on it. Now that we know the address of our post, the address that we're linking to and the address of the linking site's pingback server, we can issue the request to the server using Xmlrpc.Net. Here's the code:

Public Function SendPing(ByVal source As String, ByVal destination As String) As String

    Dim server = GetPingbackServer(destination)
    If Not server = "" Then
        Dim proxy As IPingbackPing = _
            DirectCast(XmlRpcProxyGen.Create(GetType(IPingbackPing)), _
                       IPingbackPing)
        proxy.Url = server
        Return proxy.Ping(source, destination)
    End If
    Return ""
End Function

Typically with the XMLRPC.Net, we specify the server's address in a static attribute on the service type. However, in our case the URL isn't known at compile time. As a result, we use the XmlRpcProxyGen.Create method to create a proxy for the RPC service at runtime. The Create method takes a type as an interface. We define the type with the required attributes as follows:

<XmlRpcUrl("http://ThinqLinq.com/SetAtRuntime.aspx")> _
 Public Interface IPingbackPing
    Inherits IXmlRpcProxy

    <XmlRpcMethod("pingback.ping")> _
    Function Ping(ByVal source As String, ByVal destination As String) As String

End Interface

Notice that the interface does specify a XmlRpcUrl. This is just a place holder which we replace in the SendPing method by setting the proxy.Url to the actual server's address. The act of calling the Ping method is trivial thanks to the generated proxy.

Receiving a Pingback

Switching gears to the server side now, receiving a ping is actually easy to do with the xml-rpc.net implementation. On our class, we inherit from the CookComputing.XmlRpc.XmlRpcService. This takes care of the core handler code and wiring up our method with the RPC call. To associate our method with the RPC method name, we add the CookComputing.XmlRpc.XmlRpcMethod specifying a method name of "pingback.ping" as required by the Pingback specification. This method takes two string parameters: source url and destination url; and returns a message indicating the outcome of the request.

      <XmlRpcMethod("pingback.ping")> _
      Public Function Ping(ByVal source As String, ByVal destination As String) As String
          Using dc As New LinqBlogDataContext
              'Get the post's ID based on the destination url from 
              'the custom URL Rewriting scheme.
              Dim postId As Integer = GetPostId(destination, dc)
              If postId > 0 Then
                  'Make sure we haven't already added this pingback
                  If (From c In dc.CommentBases _
                      Where c.CreatorLink = source And _
                          c.PostId = postId).Count = 0 Then

                      dc.CommentBases.InsertOnSubmit( _
                          New Pingback With {.CreatorLink = source, _
                                             .Description = "Pingback from " & source, _
                                             .EnteredDate = Now, _
                                             .PostId = postId, _
                                             .Creator = "Pingback", _
                                             .CreatorEmail = ""})
                      dc.SubmitChanges()

                      Return "pingback registered successfully"
                  Else
                      Return "pingback already registered"
                  End If
              Else
                  Return "pingback not registered, no post found"
              End If
          End Using
     End Function

In this case, we need to make sure that a) the destination location does include a reference to a post (through the id in the querystring or via URL Rewriting). If we have a valid ID, we then check to see if there is already a linkback associated with this post and the given source address. We don't want to register duplicate linkbacks. This is particularly important since we are implementing both Postbacks and Trackbacks and the calling site could send both requests. We only want to register one. Assuming we have a new Pingback for this post, we will create a new Pingback object (which inherits from CommentBase) and set the necessary values. With LINQ to SQL, applying the change is the standard SubmitChanges call on the context. We finish by letting the client know what happened in our call. This is mostly a courtesy as the specification doesn't require a specific response outside of exception codes.

Feel free to pingback (or trackback) to this post to let me know if this series of posts has been helpful to you.

Last week, I announced that my translations of the Entity Framework samples were available in VB. Today the ADO.Net team announced that next set have been posted. These are part of the ADO.Net Entity Framework Documentation Samples. These are the projects that are used in the EF quick start and walkthroughs that come with the .Net documentation. They are a set of mini applications demonstrating using EF within the context of an application.

The Course Manager sample was previously translated, but the HR Skills, Adventureworks data binding and research and collaboration tool were just updated today. Unlike the other samples, these don't have separate downloads for each sample, but rather have both C# and VB versions included with each download. Here's the description of each of these projects as taken from the MSDN site:

• CourseManager.zip
  The CourseManager Windows forms application created by completing the Entity Framework quickstart.
• HRSkillsCombined.zip
  This is a Visual Studio 2008 solution that contains both a Windows forms project and an ASP.NET project. Both samples demonstrate data binding to entity objects. The ASP.NET sample uses the EntityDataSource control for data binding.
• AdWksSalesWinDataBind.zip
  The AdventureWorks Data Binding sample demonstrates data binding that uses the Entity Framework. This application displays and modifies SalesOrderDetail entities associated with SalesOrderHeader entities.
• ResearchCollaborationAssistant.zip
  The Annotation and Research Collaboration Tool aids research and collaboration by creating reference annotations and contact entities that can be searched for both relevant Web pages and people associated with topics or search texts.

I hope you find these samples helpful. I'm not sure that I would recommend using the manual databinding that the team used when creating these samples as there are quite a few cases where they could have relied on native databinding rather than manually adding items to text boxes. These translations were fairly literal translations on purpose.

If you're just wanting to learn the query syntax and see the capabilities of EF, the Entity Framework Query Samples are a better source of information.

Thanks to both the VB Team and Data Team for recognizing the need for these samples in VB.

Yesterday, I showed how we can receive trackbacks from other sites using the TrackBack API. Today, we'll look at the other side of this picture: sending TrackBacks to other sites based on links in the post. Sending a TrackBack entails several steps:

1. Parsing the post to find links to other sites.
2. Checking the target site to see if it supports TrackBacks.
3. Formatting and sending the TrackBack to the target's service.
4. Checking for error responses.

Thus with every post I create now, I include a method to parse the post and send the trackbacks as necessary. Parsing the post is a relatively painless process. In it, I query the raw HTML of the post finding anything that looks like href="….". Although I typically use LINQ for querying, in this case the best tool for the job is a regular expression. I admit that I'm not the best at regular expressions, so if anyone has a better alternative, let me know. Here's the method that starts the parsing process:

Public Shared Sub ParseAndSendTrackbacks(ByVal post As PostItem)
    'find references that support trackbacks
    Dim pattern As String = String.Format("href={0}[a-z0-9:\.\/_\?\-\%]*{0}", _
                                          ControlChars.Quote)
    Dim matches = Regex.Matches(post.Description, pattern, RegexOptions.IgnoreCase)
    For Each Link In matches.OfType(Of RegularExpressions.Match)()
        Try
            Dim startIndex = Link.Value.IndexOf(ControlChars.Quote) + 1
            Dim urlPart As String = Link.Value.Substring(startIndex, _
                                        Link.Value.Length - startIndex - 1)
            Dim svc As New TrackbackService
            svc.SendTrackback("http://www.ThinqLinq.com/Default/" & _
                              post.TitleUrlRewrite & ".aspx", post, urlPart)
        Catch ex As Exception
            Trace.Write(ex.ToString)
        End Try
    Next
End Sub

As you can see, this consists mostly of the Regex match and some string parsing to get down to the url that we are referring to. We then send that url into a method which will send the trackback.

Public Sub SendTrackback(ByVal postUrl As String, ByVal post As PostItem, ByVal externalUrl As String)

    Dim server = GetTrackbackServer(externalUrl)
    If server <> "" Then
        'Send the trackback to the sever
    End If
End Sub

Here, the first step is to see if the post that we are referencing in our post supports the trackback API. The trackback API has an auto-discovery mechanism whereby the page needs to include an embedded RDF which specifies the URI that a client should ping when it wants to issue a trackback. On this site, the trackback server URI for my Receiving Trackbacks post is:

<!--
    <rdf:RDF xmlns:rdf=http://www.w3.org/1999/02/22-rdf-syntax-ns#
      xmlns:dc=http://purl.org/dc/elements/1.1/
      xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/">
      <rdf:Description rdf:about=http://www.ThinqLinq.com/About.aspx
       dc:identifier="http://www.foo.com/Default.aspx" dc:Title="Thinq Linq"
       trackback:ping="http://ThinqLinq.com/Trackback.aspx?id=22050" />
    </rdf:RDF>
-->

In this case, notice that the XML is included inside of a comment block. The TrackBack Technical Specification indicates that this is required by some validators. Since we know that the site needs to include a node like this, we can issue a request to the page of the post that we are wanting to send a post to and see if it includes the appropriate rdf response information. We can't assume that the page will be XHTML compliant, so we will use another regular expression to find the <rdf:RDF></rdf:RDF> node and then use XML literals with VB 9 to parse it to get the value of the trackback:ping. Since the XML is strongly typed with namespaces, we'll start by including the appropriate imports to our class file. If we don't include these imports, our LINQ query will fail because the namespaces are required when querying XML.

Imports <xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#">
Imports <xmlns:dc="http://purl.org/dc/elements/1.1/">
Imports <xmlns:trackback="http://madskills.com/public/xml/rss/module/trackback/">

With the namespaces in place, we can proceed with the rest of our code to find the TrackBack server's URI. This method will return the server's address or an empty string if the server is not found for any reason.

Private Function GetTrackbackServer(ByVal externalUrl As String) As String
    Try
        'Make sure that we have a valid external link
        Dim linkUri As Uri = Nothing
        If Not Uri.TryCreate(externalUrl, UriKind.Absolute, linkUri) Then
            Return ""
        End If
        Dim server As String = ""

        'Set up the HTTP request
        Dim req = DirectCast(WebRequest.Create(linkUri), HttpWebRequest)
        req.Referer = "http://www.thinqLinq.com"
        Dim client As New WebClient()
        client.UseDefaultCredentials = True

        'Get the page's contents
        Dim page = client.DownloadString(externalUrl)

        'Find the rdf tag
        Dim regexString = String.Format("<rdf:rdf(.*?)rdf:rdf>")
        Dim match = Text.RegularExpressions.Regex.Match(page, regexString, _
                         RegexOptions.IgnoreCase Or RegexOptions.Singleline).Value
        If Not String.IsNullOrEmpty(match) Then
            'Use LINQ to XML to fet the trackback:ping attribute's value
            Dim rdf = XDocument.Parse(match)
            Dim url = rdf.Root.<rdf:Description>.FirstOrDefault.@trackback:ping
            If Not String.IsNullOrEmpty(url) Then
                Return url
            End If
        End If
    Catch ex As Exception
        Diagnostics.Trace.WriteLine(ex.ToString())
    End Try
    'Something didn't work right, or the site doesn't support TrackBacks.
    Return ""
End Function

Now that we know the server, we can finish off the process of sending our request to the server that we started earlier. In this case, we create the request setting the method to "POST" and the ContentType to "application/x-www-form-urlencoded". We then build our form's values that we make sure to UrlEncode and place in the request's content stream.

Public Sub SendTrackback(ByVal postUrl As String, ByVal post As PostItem, ByVal externalUrl As String)

    Dim server = GetTrackbackServer(externalUrl)
    If server <> "" Then
        Dim req = DirectCast(WebRequest.Create(server), HttpWebRequest)
        req.Method = "POST"
        req.ContentType = "application/x-www-form-urlencoded"
 
        Dim content As New StringBuilder()
        content.AppendFormat("url={0}", HttpUtility.UrlEncode(postUrl))
        content.AppendFormat("&title={0}", HttpUtility.UrlEncode(post.Title))
        content.Append("&blog_name=ThinqLinq")
        content.AppendFormat("&excerpt={0}", _
                             HttpUtility.UrlEncode(StripHtml(post.Description, 200)))

        Dim contentBytes() = System.Text.Encoding.ASCII.GetBytes(content.ToString())
        req.ContentLength = contentBytes.Length

        Using reqStream = req.GetRequestStream
            reqStream.Write(contentBytes, 0, contentBytes.Length)
        End Using

        Dim resp = req.GetResponse()
        Using respStream = resp.GetResponseStream()
            Dim reader As New StreamReader(respStream)
            Dim response = XDocument.Parse(reader.ReadToEnd())
            'Check for errors
            If CDbl(response...<error>.FirstOrDefault()) > 0D Then
                Throw New InvalidOperationException(response...<message>.FirstOrDefault().Value)
            End If
        End Using
    End If
End Sub

Once we send the request, we make sure to check the response to see if there were any errors. As we mentioned yesterday, the TrackBack Technical Specification requires that the response be XML in the following format:

<?xml version="1.0" encoding="utf-8"?>
<response>
   <error>1</error>
   <message>The error message</message>
</response>

Since it is XML, it is easy to parse this using LINQ to XML. If an error is found, we raise an exception including the contained error message from the server.

If you've been following along, I've been working on enhancing this site a bit recently. A couple of the most recent enhancements can be found in the following posts:

• Paging with AJAX WCF and LINQ
• Adding Gravatar support to comments

Continuing in this tradition, I wanted to include the ability to be notified when other sites post links to my posts. There are several such API's that support this kind of notification, including Trackbacks and Pingbacks. In this post, we'll look at receiving Trackbacks and saving them to our database (using LINQ of course).

The technical specification for Trackbacks is hosted at http://www.sixapart.com/pronet/docs/trackback_spec. To implement the listener part of the trackback, we simply need to be able to accept a HTTP Post which includes the post id in the URI and the trackback's details as form parameters. For example, if I wanted to send a trackback for my Paging with AJAX post, I would issue the following request:

POST http://ThinqLinq.com/Trackback.aspx?id=22040
Content-Type: application/x-www.form-urlencoded; charset=utf-8

title=Test+Postback&url=http://www.fansite.com/&excerpt=Good+post&blog_name=Fansite

In this request, the URI specifies the name of the trackback server including the ID of the post we are tracking: http://ThinqLinq.com/Trackback.aspx?id=22040. The trackback API specifies that the Content-Type should always be application/x-www.form-urlencoded; charset=utf-8. The body specifies the values we want the trackback server to know about. Each of these are optional. In this case, we want to send the server a trackback for a post called "Test Postback" which can be found at http://www.fansite.com on the blog that is named "Fansite". To be nice, we'll include an excerpt of our post with the value of "Good post". Because the content type needs to be urlencoded, we need to make sure that each value is encoded properly. To summarize, following parameter values would be sent for our test request:

• title=Test Postback
• url=http://www.fansite.com/
• excerpt=Good Post
• blog_name=Fansite

One tool to issue a raw HTTP request to test this is Fiddler. Inside Fiddler, we can select the Request Builder tab. Select "POST" as the method and enter the URI that we wish to post to (http://ThinqLinq.com/Trackback.aspx?id=22040). We then set the content type in the Request Headers and our post content in the Request Body. Below is a screen shot to send this request using Fiddler.

We'll discuss what we need to do to send this request from code in a later post. For now, we'll focus on receiving this request. While I wanted to do this with WCF, implementing it as a standard Web page is the easiest. As a result, we'll create a page called Trackback.aspx. Since there is no real UI on this, we'll remove everything from the page except for the pointer to the page's code file:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Trackback.aspx.vb" Inherits="Trackback" %>

On the page load, we need to clear our buffer because we're going to just send a simple XML response indicating any errors we may experience.

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Response.Buffer = True
        Response.Clear()
        Response.Cache.SetCacheability(HttpCacheability.NoCache)
        Response.ContentType = "text/xml"

Next, we need to create the object that we want to persist in our database. We already have a Comment table which maps to our Comments. In many ways Trackbacks are like Comments, so we'll use the same table. Since they do have different behaviors, we'll use LINQ to SQL's ability to support inheritance. Thus we'll add a discriminator column on our Comment table called CommentType and default the value to "C" since most of the items in that table will be comments.

Next, in our DBML we need to indicate the new mapping. Our existing comment class will now be an abstract base class called CommentBase. We'll change it's Inheritance Modifier to MustInherit as a result. We'll then add three derived types for Comment, Trackback, and Pingback to model the behaviors we'll be implementing now. We'll also need to make sure to Inheritance Discriminator values of each of the inheritance arrows. When we're done, this piece of our model now looks as follows:

Now that we have our new Trackback type, we can instantiate it on our Page_Load with the form values that we get from our page's request fields.

      Dim trackback As New LinqBlog.BO.Trackback
      Dim id As Integer
      If Not Integer.TryParse(Request("id"), id) Then
          Return CreateFailureMessage("Id invalid")
      End If

      trackback.PostId = ID
      trackback.Creator = If(Request.Params("blog_name"), "")
      trackback.CreatorEmail = String.Empty
      trackback.Description = _
          String.Format("Trackback from <a href={0}{1}{0}>{2}</a&gt", _
                        ControlChars.Quote, _
                        If(Request.Params("url"), ""), _
                        HttpUtility.UrlDecode(If(Request.Params("title"), "")))
      trackback.EnteredDate = Now
      trackback.CreatorLink = If(Request.Params("url"), "")

We do need to decode the Url encoded values. We also need to watch out for missing values because each of the form values are optional. In this case, we'll use VB 9's support for ternary If. Also notice here that we aren't setting the CommentType field. Since we are using the discriminator column and set that in LINQ to SQL's metadata, when we save Trackback objects, it will automatically assign the type to "T" for us.

Now that we have our trackback object, adding it to the database with LINQ is trivial. However, we do need to make one additional check before saving the trackback. We don't want to save multiple trackbacks for a single post. Thus we'll check to see if there are any trackbacks for this post in the database already before saving this one:

            Using dc As NewLinqBlogDataContext
                'Make sure we don't already have a comment for this post from this url
                If(Aggregatetb Indc.CommentBases.OfType(OfTrackback)() _
                   Wheretb.PostId = id And_
                        tb.CreatorLink = values.Url _
                    IntoCount()) = 0 Then

                    'Add it
                    dc.CommentBases.InsertOnSubmit(trackback)
                    dc.SubmitChanges()

                End If
            End Using

We're almost done. The last step is to send the appropriate response to the sending system as required by the Trackback API specification. This is a simple XML response containing any error messages. With XML Literals, this is a simple copy paste operation from the Trackback API:

    Private Function CreateSuccessMessage() As String
        Return <?xml version="1.0" encoding="utf-8"?>
               <response>
                   <error>0</error>
               </response>.ToString()
    End Function

    Private Function CreateFailureMessage(ByVal description As String) As String
        Return <?xml version="1.0" encoding="utf-8"?>
               <response>
                   <error>1</error>
                   <message><%= description %></message>
               </response>.ToString()
    End Function

With this in place, we just add the following inside of our If statement after we save the value to the database:

    Response.Write(CreateSuccessMessage)
    Response.End()

If there were any issues in creating or saving our trackback, we can send the Response.Write(CreateFailureMessage(error)).

Feel free to test this by tracking back to this post. Be aware that I am moderating the trackbacks to avoid comment spam that can be inevitable when exposing public known API's like this. Please don't abuse the ability to send trackbacks as I don't want to have to remove the functionality.

For those Visual Basic users out that that have struggled with the fact that the samples were only available in C#, you can now rejoice. There are a number of projects that have now been translated into Visual Basic for your learning pleasure. You can find these samples on the MSDN Code Gallery’s Entity Framework page. At this point the following projects have been translated.

• Entity Framework Query Samples Compatible with .NET Framework 3.5 SP1 and Visual Studio 2008 SP1 (Visual Basic and C# versions available)
  The Entity Framework Query Samples is a small Windows Forms program that contains several basic Entity SQL and LINQ to Entities queries against that NorthwindEF Entity Data Model (based on a modified version of Northwind). Its goal is to help you learn the features of the two query languages supported by EF and visualize how the results and the translated store query look like.
• Entity Framework Lazy Loading Compatible with .NET Framework 3.5 SP1 and Visual Studio 2008 SP1 (Visual Basic and C# versions available)
  This sample shows how to use code generation to add support for transparent lazy loading to Entity Framework. It includes code generator (EFLazyClassGen), supporting library (Microsoft.Data.EFLazyLoading) and sample test applications.
• Persistence Ignorance (POCO) Adapter for Entity Framework V1 Compatible with .NET Framework 3.5 SP1 and Visual Studio 2008 SP1 (Visual Basic and C# versions available)
  EF POCO Adapter enables Plain Old CLR Objects (POCOs) to be tracked using released version of Entity Framework V1 using automatically generated adapter objects. It consist of a code generator, supporting library and a test suite and examples.

There are several more on the way. I’ll try to update this post when they become available.

5/22: Update: Two more samples went online today:

• EF Extensions Compatible with .NET Framework 3.5 SP1 and Visual Studio 2008 SP1 (Visual Basic and C# versions available)
  The ADO.NET Entity Framework Extensions library includes utilities that make querying stored procedures, creating typed results from DB data readers and state tracking external data much easier in the Entity Framework. A sample application demonstrates several patterns using these utilities, including stored procedures with multiple result sets, materialization of CLR types, and registering entities in the Entity Framework state manager.
• ADO.NET Data Services IUpdateable implementation for Linq to Sql
  Sample implementation of ADO.NET Data Services IUpdateable interface for Linq to Sql Data Sources.

These days, it seems that every web site needs to have some use of gratuitous AJAX in order to stay on the bleeding edge. Since we didn't have any here yet, I thought I would throw some in for good measure. I liked the lazy loading of records instead of paging found on some sites, including the Google RSS reader and thought I would see what it would take to add something like that here.

If you're not familiar with this paging option, instead of loading a new page of records each time the user gets to the end, they simply add to the end of the list via an AJAX (Asynchronous JavaScript and XML) call to get more records. My implementation is not nearly as fancy, but it gets the job done.

To try out the AJAX implementation we're going to discuss, browse to the AjaxPosts.aspx page. The implementation consists of three parts:

• The web page that hosts the AJAX ScriptManager and provides the foundation of the page itself.
• A JavaScript file which performs the client side paging functionality.
• A WCF service which fetches the data and formats it for the client.

We'll start with the hosting page. The page itself is very simple. We'll make it easy to handle the formatting by continuing to use the same MasterPage that we use elsewhere on this site. That makes the content section rather concise.

The content section includes a ScriptManager which serves to push the AJAX bits down to the client. It also contains knowledge of our service using the asp:ServiceReference tag, and the client side JavaScript using the asp:ScriptReference tag. In addition, we include a blank div element which we will use to insert the content fetched from our service, and a button which is used to initiate requests for more posts from our service.

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="AjaxPosts.aspx.vb" 
    Inherits="AjaxPosts" MasterPageFile="~/Blog.master" Title="ThinqLinq" %>
<%@ Register 
    Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
    Namespace="System.Web.UI" TagPrefix="asp" %>

<asp:Content ID="content" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
    <asp:ScriptManager ID="ScriptManager1" runat="server">
        <Services>
            <asp:ServiceReference Path="~/API/AjaxServices.svc" />
        </Services>
        <Scripts>
            <asp:ScriptReference Path="~/AjaxServiceClient.js" />
        </Scripts>
        
    </asp:ScriptManager>

    <div id="Posts">
    </div>
    
    <button id="More" onclick="FetchPosts(); return false;">More Posts</button>
</asp:Content>

With the plumbing out of the way, we can start to focus on the meat of the AJAX implementation. First off, we need to have a way to access our data. My first inclination was to use ADO.Net Data Services to serve up dynamic data from our LINQ sources. However in this case, I decided that the functionality was quite limited in scope and a dedicated service would be the better option. A dedicated service would also offer some additional level of caching should scalability become an issue on the site. Also, I wasn't sure my JavaScript skills would be quite suited to doing the necessary client side string parsing that would be necessary to build dynamic HTML content from the raw data. Thus in this case I decided to use a more standard WCF service.

Our WCF service takes as parameters the page number that we want to retrieve and the number of records that make up a page. We'll have our JavaScript pass these values. In response, it will send back a string containing the already formatted HTML that we will display. First we need to set up our class and method and decorate them with the necessary WCF attributes. When adding services that you want to AJAX enable, make sure to use the  "AJAX Enabled WCF Service" template from the Add New Item dialog box rather than the standard "WCF Service". This will set up the web.config differently to allow the service to expose a way to access a JavaScript client proxy. (Thanks goes to Wally for helping me figure that one out. For our service, we'll put it in the ThinqLinq namespace and call it AjaxServices. It will have one service method (indicated by the OperationContract attribute) called LoadFormattedPosts.

Namespace ThinqLinq
    <ServiceContract(Namespace:="ThinqLinq")> _
    <AspNetCompatibilityRequirements(RequirementsMode:=AspNetCompatibilityRequirementsMode.Allowed)> _
    Public Class AjaxServices

        <OperationContract()> _
       Public Function LoadFormattedPosts(ByVal page As Integer, ByVal pagesize As Integer) As String
       End Function
    End Class
End Namespace

Inside the LoadFormattedPosts we'll get to use our LINQy goodness. In this case, we know that we are going to need our posts with their associated categories and comments. As an optimization, we'll add the appropriate load options to fetch all of those at the same time. We'll also include the appropriate Skip and Take methods to do the paging. Our resulting LINQ to SQL query is fairly standard.

Using dc As New LinqBlogDataContext
    Dim LoadOptions As New DataLoadOptions
    LoadOptions.LoadWith(Of PostItem)(Function(p) p.Comments)
    LoadOptions.LoadWith(Of PostItem)(Function(p) p.CategoryPosts)
    LoadOptions.LoadWith(Of CategoryPost)(Function(cp) cp.Category)
    dc.LoadOptions = LoadOptions

    Dim posts = From p In dc.PostItems _
                Order By p.PublicationDate Descending _
                Skip pagesize * page _
                Take pagesize

    Dim response As String = FormatPosts(posts)
    Return HttpUtility.HtmlDecode(response)
End Using

This query would be fine if we only wanted to send the raw data back to the AJAX client. However, we'll take an extra step in this implementation and do the formatting in a separate FormatPosts method. We'll actually add a separate method to format the Categories as well.

Private Function FormatPosts(ByVal posts As IEnumerable(Of PostItem)) As String
  Dim response = _
      From p In posts _
      Select val = <div class="post">
                     <h2>
                        <a href=<%= "Default/" & p.TitleUrlRewrite %>><%= p.Title %></a>
                     </h2>
                     <div class="story"><%= p.Description %></div>
                     <div class="meta">Posted on <%= p.PublicationDate %> - 
                        <a href=<%= "Default/" & p.TitleUrlRewrite %>>
                        Comments (<%= p.Comments.Where(Function(c) c.IsApproved).Count() %>)</a>
                        <br/>
                        <%= If(p.CategoryPosts.Count > 0, _                               "Categories:" & FormatCategories(p), _                               "") %>
                     </div>
                   </div>.ToString() _
        Select val  Return "<div class='posts'>" & String.Join("", response.ToArray) & "</div>"
End Function
Private Function FormatCategories(ByVal post As PostItem) As String
  If post.CategoryPosts.Count > 0 Then
    Dim response = _
       From catPost In post.CategoryPosts _
       Select val = <span>
                      <a href=<%= "http://www.ThinqLinq.com/Default.aspx?CategoryId=" & _                              catPost.Category.CategoryId %>>
                         <%= catPost.Category.Description.Trim %></a>
                    </span>.ToString()

    Return String.Join(", ", response.ToArray())
  Else
    Return ""
  End If
End Function

I'm not going to take the time to explain all of the LINQ to XML implementation here. I've discussed LINQ to XML in past posts. I do want to point out here a couple extra steps that need to be taken with this implementation.

First, we need to be careful in cases where we have posts without associated comments or categories. If we don't, we will run into issues with null results and the query will fail. Second, we have to watch how and when we are casting to strings and arrays. The easiest way to make sure that we are casting properly is to make sure you have Option Strict turned On at the file or project level.

Now that we have our service set-up, we can write the client side JavaScript code. Because we're using the AJAX ScriptManager and an AJAX enabled WCF service, we don't have to write any fancy plumbing code to access this service. To test this, we can browse to the service passing a /js or /jsdebug flag as follows: http://ThinqLinq.com/Api/AjaxServices.svc/js. That leaves just the task of writing the client side to access the service and place it in the AjaxServiceClient.js file for the ScriptManager to find.

var svcProxy;
var currentPage;
function pageLoad() {
    currentPage = 0;
    svcProxy = new ThinqLinq.AjaxServices();
    svcProxy.set_defaultSucceededCallback(SucceededCallback);
  
    FetchPosts();
}

function FetchPosts() {
    svcProxy.LoadFormattedPosts(currentPage, 5);
}

function SucceededCallback(result) {
    var postTag = document.getElementById("Posts");
    postTag.innerHTML += result;
    currentPage += 1;
}

if (typeof (Sys) !== "undefined") Sys.Application.notifyScriptLoaded();

In this JavaScript, we set up two global variables: currentPage to manage the paging functionality and svcProxy to act as the instance of the proxy that accesses our service. In the pageLoad function, we initialize these values. Once initialized, we then set the asynchronous callback function for when values are retrieved. Finally, we invoke the method to fetch the posts for the first time that the page is loaded.

To fetch the posts, we simply call the LoadFormattedPosts method of our proxy which sends the request to our WCF service. Because this is performed asynchronously, we will just fire the request and let the callback handle the response.

In the SucceededCallback method, we grab the return value from the WCF service in the result parameter. Once we have that, we get a reference to the placeholder "Posts" div in the AjaxPosts.aspx document. To add the new results to the client, we concatenate the current contents with the new result value using +=. Finally, we increment the currentPage number so that the next request will fetch the next page of posts.

That's it. We're done. Jump over to http://ThinqLinq.com/AjaxPosts.aspx to see the result in action. There are a number of things that can be improved on this implementation, but it is a start. One definite drawback on this implementation is that it is not SEO friendly. You can see this by viewing the source to the resulting page. Notice that none of the post contents are included in the source.

I don't claim to be a JavaScript or AJAX expert and I'm sure there are other more elegant solutions. I'd love to learn from your experience, so feel free to post your recommendations and we'll see what we can do to improve this.

Having a bit more free time than expected, I thought I would take a bit of time and add some features to this site based on some things I've seen at other sites. A quick one is adding Gravatar support to the comments. If you're not familiar with gravatar's, www.Gravatar.com describes them as a globally recognized avatar, is quite simply an image that follows you from site to site appearing beside your name when you do things.

To get a Gravatar, go to Gravatar.com and let them know your email address and the picture you want associated with your picture. Once you are done, any site that supports gravatar's will then show your picture to the world. That's your task.

My task is to enable this site to access your picture. They have a number of add-ins for various blogging engines, but since this one is custom, we'll have to implement it ourself. Luckly it is as simple as adding a img tag to our site with the source being a URI which includes information about the user that we want to display. Here's a sample URI which would show my picture:

<img src="http://www.gravatar.com/avatar.php?gravatar_id=58d453f6449cc9125948bd153bc4272b&rating=G&size=40" alt="Gravatar" />

Let's break the source attribute down a bit. Essentially it is a URI to a PHP site on the Gravatar server: http://www.gravatar.com/avatar.php. It includes three query string parameters: the gravatar_id, rating and size.

The rating and size are easy enough. For this site, we're going to keep it clean (although we do get Kinq-y at times) so we'll keep the site rated G. Other sites could use one of their other rating levels (g, pg, r, or x).

For the size, you can specify that the image be anywhere between 0 and 512 pixels. To keep the page load small here, I'll ask for images 40 px by 40 px and set the size=40.

With that out of the way, we need to generate the value for the gravatar_id paramter. In a nutshell, the id is just a MD5 hash of the commentor's email address. When we set-up the ability to add comments to this site, we made the email address a required field, so we are already storing that. All we need to do is convert it and bind to it in our custom img tag. To encapsulate that functionality and keep it with the comments themselves, we will add a partial class for Comments and put our new property in there. We don't add it directly to the Comment class that the dbml file generates as it will be deleted in the future if we ever decide to regenerate that file. With partial classes, we can retain parts of the class definition in multiple physical files and the compiler will combine them within the generated assembly. Here's the definition of this class and our new property:

Public Class Comment
    Public ReadOnly Property GravatarSource() As String
        Get
        End Get
    End Property
End Class

Notice here, we don't need to specify that this is a partial class because the one generated by our DBML designer already includes the partial destinction. As long as we're in the same namespace, in VB, we're fine. I should point out however that there are some limitations on how we can use this partial property in LINQ queries (see http://thinqlinq.com/Default/Projecting-into-an-unmapped-property-from-a-LINQ-to-SQL-query.aspx).

Now for the getting this value. To make binding simple, we'll just format the entire URI in this method (we're using a property here due to limitations in data binding to methods). Using the String.Format method, we can insert the hash into our uri using the following:

Return String.Format("http://www.gravatar.com/avatar.php?gravatar_id={0}&rating=G&size=40", GetEmailHash())

The body of the GetEmailHash function is where the meat of our work happens. In this, we will encode the value of the Commentor's email address which we can access from the other part of the partial class as the CreatorEmail property. To do that, we need to encode the string into a byte array. Then, using a MD5CryptoServiceProvider instance, we can compute the hash into a new byte array.

Dim enc As New UTF8Encoding()
Dim hashProvider As New MD5CryptoServiceProvider
Dim bytes() As Byte = hashProvider.ComputeHash(enc.GetBytes(Me.CreatorEmail))

Finally, we need to piece the encrypted array back into a string. In the case of the Gravatar system, each byte needs to be converted back to the HEX represntation and lower cased. We could use a for-each loop and iterate over the results building it up dynamically, but this is a great case of using LINQ to Objects to replace an iteration:

Imports System.Security.Cryptography
Imports System.Text
Imports System.IO 

Public Class Comment
    Public ReadOnly Property GravatarSource() As String
        Get
            'We need the MD5 hash of the email address
            Return String.Format("http://www.gravatar.com/avatar.php?gravatar_id={0}&rating=G&size=40", GetEmailHash())
        End Get
    End Property
    Private Function GetEmailHash() As String
        Dim enc As New UTF8Encoding()
        Dim hashProvider As New MD5CryptoServiceProvider
        Dim bytes() As Byte = hashProvider.ComputeHash( _
                           enc.GetBytes(Me.CreatorEmail))
        Return String.Join("", _
                           (From b In bytes _
                            Select b.ToString("X2").ToLower()) _
                            .ToArray())
    End Function
 End Class

Yesterday, I had the pleasure of demonstrating Delegates, Lambdas and Expressions at the Alabama Code Camp. This is not the first time I presented this. The original demo project is still available in the Downloads section of this site. This time however, I was able to round off the VB set of the demos to demonstrate all of the abilities available in C# 3.0 due to the new Statement and Multi-line Lambda language enhancements coming in VB 10. The complete VB 10 version of the project is available to download now as well.

Here are some of the added methods. (Note, this has been tested against the October 2008 beta release of Visual Studio 2010. Be aware of the evaluation time bomb in this release if you intend to use it.) The DoTrick method takes an Action delegate which means that it takes no parameters and returns no values. In VB this equates to a Sub and in VB 10 is refered to as a Statement Lambda.

'Anonymous delegate
animal.DoTrick(Sub() Console.WriteLine("Anonymous Method"))

' Lambda expression as a variable
Dim LiftOneLeg As TrickAction =
   Sub()
      animal.LegsOnGround -= 1
   End Sub
animal.DoTrick(LiftOneLeg)

'Lambda expression inline
animal.DoTrick(Sub()
      animal.LegsOnGround += 3
   End Sub)

'Multi-line lambda
animal.DoTrick(Sub()
      animal.LegsOnGround = 0
      Console.WriteLine("I'm Jumping now!")
      animal.LegsOnGround = 4
   End Sub)

I also included a nice consise combination of VB 9 function lambdas with VB 10 Statement lambdas. In this case, I find the animals that have broken business rules (Predicates which are delegates that return a boolean) and then iterate over them using the ForEach method that takes an Action delegate which can be fulfilled with a statement lambda.

animals _
   .Where(Function(item) Not item.IsValid).ToList _
   .ForEach(Sub(item) Console.WriteLine(item.ToString))

For this update, I also include a demonstration of a common use of Lambda's, particularly in Silverlight applications where all data access is asynchronous. Since all delegates actually inherit from MultiCastDelegate, all delegates by default offer asynchronous BeginInvoke/EndInvoke possibilities in addition to the Synchronous Invoke method.

With BeginInvoke, we use a lambda that takes an IAsynchResult object as a parameter. In the past we had to declare the call back as a separate method. With the inclusion of multi-line statement lambdas, we can now declare the callback directly inline with the BeginInvoke request as follows: 

If you missed this presentation, I'll be doing it again at the South Florida Code Camp next week. Come on down and check it out.

When we were writing LINQ in Action, we weren't able to specify all of the possible methods and functions that have supported query translations for a couple reasons.

1. There were too many to be included in the scope of the book.
2. The book was being written at the same time that LINQ was evolving and more comprehensions were being supported, thus giving us a moving target that we couldn't ensure the accuracy of when the product shipped.
3. We realized that over time, translations for more functions may be added and enumerating the list in the book might not reflect the current methods supported with a given framework version.

As I was searching for an answer to a recent question, I happened upon a listing on MSDN showing the functions and methods which are and are not supported. The full list of LINQ to SQL supported and unsupported members is available online at http://msdn.microsoft.com/en-us/library/bb386970.aspx.

As an example the following methods are shown as having translations for DateTime values: Add, Equals, CompareTo, Date, Day, Month, Year. In contrast methods like ToShortDateString, IsLeapYear, ToUniversalTime are not supported.

If you need to use one of the unsupported methods, you need to force the results to the client and evaulate them using LINQ to Objects at that point. You can do that using the .AsEnumerable extension method at any point in the query comprehension. Any portion of the query that follows AsEnumerable will be evaluated on the client side.

A couple months ago I added a feature to this site to build a Site Map for this site dynamically based on the information from the database for posts and files for the downloads. If your not familiar with how Sitemap files can help your site searchability, Google has a good documentation about Sitemaps in their Webmaster tools.

The SiteMap Protocal is a rather simple XML document consisting of a set of url nodes that consist of the following:

• loc - URL for the page link
• lastmod - Date the page was last modified
• changefreq - How often the page is changed
• priority - How high you think the page should be ranked relative to other pages on your site.

For this site, I decided to index the main (default.aspx) File, about and contact pages. In addition, I indexed each post as a separate url node. If you want to view the resulting data, browse to http://www.thinqlinq.com/sitemap.aspx. To do this, I used LINQ to XML with VB XML Literals. To begin, we need to add the XML Namespaces. At the top of our file, we enter the following imports:

Imports <xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
Imports <xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

We continue by adding the root urlset node and one child node representing the main page:

Dim map = _

    <?xml version='1.0' encoding='UTF-8'?>

    <urlset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

        xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9

            http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd"

        xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">

        <url>

            <loc>http://www.ThinqLinq.com/default.aspx</loc>

            <lastmod>

               <%= (From p In dc.PostItems _

                    Order By p.PublicationDate Descending) _

            .First.PublicationDate.ToString("yyyy-MM-dd") %>

            </lastmod>

            <changefreq>daily</changefreq>

            <priority>1.0</priority>

        </url>

    </urlset>

Most of this is standard XML. The main difference is the use of a LINQ query to show the last modification date based on the most recent post from our database. In this case we just want the First date when the dates are ordered descending. We do need to format it properly so that our search engine (Google) will be able to recognize it.

Next up, we need to add the link for the Downloads page. We'll do this much the same way that we added the url for the default page. However, in this case the modification date won't come from the database, but rather use a LINQ to Objects query to get the most recent file in the downloads directory on this site.

The About and Contact pages are relatively straight forward. The remaining url nodes are generated based on the records in the PostItems from our database. To populate them, we'll create a LINQ query pulling the data from the database using LINQ to SQL and projecting (Select) out individual url nodes for each row in the database:

As you can see, there isn't much here that is overly complex. It's just a series of LINQ queries filling the data from various sources. For reference purposes, Here's the complete code:

It's been a while since I posted some serious content for which I apologize. I started this site a year and a half ago as a proof of concept around LINQ. When the site went live, I included the download version of the site from the presentations I've been doing on LINQ and Asp.Net. In all this time, I've made significant updates to the site, but haven't made them available, until now. If you're interested, you can download the updated bits from the file downloads here and play with them. I did take the liberty of changing the actual production database, removing some of the site metrics, and other sensitive personal information.

As the site has evolved, I tried to document the enhancements. Here's a copy of some of the related links to help you step through some of the changes.

• ThinqLinq in Multiple Languages
• Implementing Search
• Filtering the RSS feed by category
• Adding an RSS feed for file downloads
• Binding Many-To-Many relationships with nested repeaters
• Filtering items in the aggregation feed

For those that attended my session at the Philly Code Camp 2008.3, this is the version of the project that I was discussing. Of course, there's always more enhancements that I would like to do, including centralizing access to a more common DataContext, using CompiledQueries, and other performance enhancements. If you have any recommendations, I'm all ears to hear what you thinq.

There are times where you wish you didn't have to worry about legacy code. This is particularly true with programming languages where constructs need to be supported even if they have long outlived their usefullness. Consider the following code that many of us "old-timers" learned to make the TRS-80 in the computer store go into an infinate loop (because the sales person didn't know how to break out of the loop).

Public Shared Sub Main()
     10: Dim x As String = "This is a test" : Console.Write(x) : GoTo 10
End Sub

Ok, if you're paying attention, this isn't old VB code, but VB.Net (any version). Yes, you can still use line numbers, GoTo and the colon to put multiple statements in a single line. Repeat after me, "Just because you can doesn't mean you should."  Not only is this ugly code to maintain, but it's just sloppy. Please don't code like this.

A couple months ago, I was convinced to try Twitter. In the process, I found www.TweetBeep.com which sends me notifications whenever someone tweets the word LINQ. Today I saw the following:

"my visual studio crashed on retrieving 39,450 records via Linq.. what a shame.. looking for a workaround and a reason.." (name withheld to protect the guilty).

In some ways, this falls into the category of, "Just because you can doesn't mean you should." In this case, the fault lies in the business requirement. There shouldn't be any reason why you should fetch 39,000 records. What user in their right mind would page through that many results?

While admitedly, many demos (including some that I present) show queries such as the following, we do that knowing that the resulting rows will be relatively small.

Dim custs = From c In Customers _
                   Order By c.Name _
                   Select c

One of the great things about LINQ is the compositionality. In this, you can add qualifications to your query (like paging and filtering) without affecting the underlying query. If you are using queries that don't utilize paging and or filtering on your data, make sure you know the underlying data and that returning that many records is reasonable (and isn't likely to grow substantially in the future). As an example, we can extend the above query and add paging as follows:

Dim custs = From c In Customers _
                   Order By c.Name _
                   Select c

Dim pageSize = 10
Dim paged = custs _
                    .Skip(currentPageNumber * pageSize) _
                    .Take(PageSize)

Additionally, I strongly recommend limiting the results prior to fetching using a where clause. There are a number of sites out there that show how to progressivly filter results (for a ComboBox, a TextBox's AutoCompleteSource or the AJAX AutoComplete extender). Extending the functionality so that the suggested items don't appear until the results are sufficiently (depending on your data) filtered is equally easy with LINQ:

If SearchString.Length > 2 Then
     Dim custs = From c In Customers _
                   Where c.Name.StartsWith(SearchString)
                   Order By c.Name _
                   Select c _
                   Take 25
     CustName.AutoCompleteSource = custs
Else
     'Not sufficiently filtered. Keep the suggestion list blank.
     CustName.AutoCompleteSource = New String() {}
End If

In the case of the original question. LINQ isn't at fault here. It is a tool for you to use. Fetching 39,000 records using LINQ or ADO.Net are equally bad ideas. Use the tools to their best effect. Don't get sloppy in your coding practice.

A while back, I showed you how to use an XML Mapping file to enable POCO support with LINQ to SQL instead of requiring you to clutter your class libraries with LINQ to SQL attributes. It turns out, the schema for the XML Mapping files (xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007") may not be included in your install of Visual Studio 2008. It was missing from mine. Luckily, the file is available on MSDN. If you add it to the schemas recognized by Visual Studio, you will get instant intellisense when editing your XML Mapping files. Here's how:

1. Copy the schema from the Visual Studio 2008 Development Center available here.
2. Paste it into a new XSD file and save it to your hard drive. If you want to stay consistent with the other schema files, locate your VS 2008 program directory and put it in the \Xml\Schemas directory. On my computer, this is located in the C:\Program Files\Microsoft Visual Studio 9.0\Xml\Schemas directory.
3. In Visual Studio, Click on XML on the main menu and then select Schemas. This will open the XML Schemas editor window.
4. Click the "Add..." button. Locate and select the XSD file that you saved in step 2.
5. Double check that there is a check in the Use column for your newly added schema.

That's it. Now, the next time you're editing your XML Mapping files, you will get the intellisense goodness that Visual Studio has to offer.

In LINQ in Action, we discuss how to add the LINQ to SQL Query visualizer into the Visual Studio 2008 environment. This tool allows you to open a window during debug time to view the TSQL that is generated from the LINQ expression tree. It also allows you to run the query and view the results. If you're not familiar with it, check out this post by Scott Guthrie.

In addition to the query visualizer, you can also build and install the Expression Tree visualizer, not only as a separate application, but also as an integrated visualizer within Visual Studio 2008. To do this, download the Linq Samples from MSDN Code gallery. Inside of that, you can find a project for the ExpressionTreeVisualizer.  To use it as a stand alone utility, build and run the ExpressionTreeVisualizersApplication. This is the method most people are familiar with.

Building the solution will also build the ExpressionTreeVisualizer library. This is the one you need to use to enable it in Visual Studio natively, copy the generated ExpressionTreeVisualizer.dll library and paste it into your ..\Program Files\Microsoft Visual Studio 9.0\Common7\Packages\Debugger\Visualizers directory.

Once you have placed the library in the visualizers directory, let's see what you can do to use the new visualizer . First, let's build a LINQ to SQL query:

Dim query = From cust In dc.Customers _
            Where cust.City = "London" _
            Order By cust.CompanyName _
            Select cust

Dim queryExpression = query.Expression

Now, that we have our code set-up, set a breakpoint in your code after you have instantiated this queryExpression variable and debug your project. Now, if you hover over the query.Expression method, you'll see a new magnifying glass as shown below:

Clicking on the visualizer icon, will launch the visualizer tool revealing the following screen:

Sure, there's lots of information in there. The expression trees are quite complex. This tool helps you decipher them in cases where you need to either parse or dynamically create expression trees in your applications.

When we wrote LINQ in Action, we took a bit of time to explain how the identity tracking system worked with LINQ to SQL to make sure that changed objects were retained when subsequent queries are requested from a data context. In a nutshell, when you issue a query, the data context translates the LINQ query into TSQL and sends that to the database. The database returns the rowsets to LINQ to SQL. The provider checks the returned rows against those that it is already tracking from previous fetches and, rather than instantiating the object again, returns the object in its internal store. This is done primarily to ensure that changes a user has made in the course of his context's lifetime are retained rather than being overwritten.

We also discussed (p. 258 if you're following along) how there is a special optimization wherein if you are querying for a single result, the pipeline would check the internal cache first before looking at the database, thus reducing the overhead of repeated hits to the database. An astute reader checked out our claim, and sure enough that optimization did not make it into the RTM bits of VS 2008. We considered fixing this in the second printing, but consulted with the product teams first. It turns out that the intended behavior was indeed to include this optimization, but due to a last minute bug, it didn't make it in.

As Dinesh points out, this oversight has been fixed in SP1. Now, if you try to fetch a single object (using Single, SingleOrDefault, First, or FirstOrDefault), the in memory object cache will be checked based on the identity columns declared in the entity's structure. If a matching object is found, it will be returned, otherwise the record will be requested from the database.

There are subtle differences between VB and C# in terms of nullability. This caused a significant difference in the TSQL generated on even simple LINQ queries. Consider the following query from Northwind's Orders table where the Freight column is generated as a Nullable(Of Integer) type:

 Dim filtered = _
       From o In dc.Orders
       Where o.Freight > 100 
       Select o

First the bad news: with the RTM of VB 2008, this query resulted in the following TSQL:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE (COALESCE(
    (CASE
        WHEN [t0].[Freight] > @p0 THEN 1
        WHEN NOT ([t0].[Freight] > @p0) THEN 0
        ELSE NULL
     END),@p1)) = 1

In this case, I've highlighted the relevant portion of the Where clause. Notice here the simple comparison translates into a COALESCE(CASE WHEN... construct. This was done due to VB's handling of nullable type comparisons under the covers.

AND NOW THE GOOD NEWS:

With the VB 2008 SP1, the difference in nullablity was removed by the SQLProvider used by LINQ to SQL. As a result the above LINQ query now generates the following TSQL:

SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE [t0].[Freight] > @p0

Notice in this case the where clause direcly reflects the statement ffrom the LINQ query and the performance from SQL Server's perspective is GREATLY improved! Who says LINQ to SQL is dead?

The VS 2008 SP1 includes lots of new features (more than a typical service pack, but that's another matter). There are a number of smaller enhancements that could easily go un-noticed otherwise. One of these is to fix a bug in the way the VB compiler generates anonymous types.

In most cases, you will only notice this if you are binding an anonymous projection to a DataGridView or the ASP GridView. With these controls, you will find that the columns used to be generated alphabetically rather than in the order in which they were declared in the anonymous type projection. Consider the following query (using the Northwind Customers table of course):

With VS 2008 RTM, a grid bound to these results would display the columns in the following order unless you explicitly set the columns: Address, CompanyName, ContactName, ContactTitle. Notice here that they are listed alphabetically. Interestingly if you use the same query in C#, the columns would be displayed retaining the order of the projection.

To determine the cause for the difference, one must view the actual IL using ILDASM. Comparing the C# with VB IL, you can see that the C# compiler retains the order where-as VB alphabetized it. No, you can't use Lutz's Reflector this time because he too alphabetizes the properties from the IL.

This was true until VS 2008 SP1 which fixed this bug. Now, the anonymous types are generated in the order in which they were declared and the column ordering is retained. Running the same query we had above results in the following column ordering with VB 2008 SP1: CompanyName, ContactTitle, ContactName, Address.

Thanks to the VB team for fixing this bug in the compiler.

One of the key things that LINQ to SQL does for us is offers a quick way to fill a set of objects with data from a database. Typically this is done by setting up some mapping and calling the GetTable method on the DataContext. There are cases, particularly when you already have an infrastructure set-up to populate objects using a DbDataReader, where it would be nice if you could just populate the columns without the need to set-up a mapping.

The DataContext has a little known method called Translate which can take a DBDataReader as a parameter and fill an IEnumerable list of objects without the need for mapping structures. To use it, specify the type you want to populate and pass the instance of the reader and let it do it's magic. Here's a sample:

Using cn As New SqlClient.SqlConnection(My.Settings.NorthwindConnectionString)
  cn.Open()
  Using cmd As New SqlClient.SqlCommand("SELECT * FROM Region", cn)
    Dim reader = cmd.ExecuteReader()
    Using nwind As New NorthwindDataContext

      Dim RegionList = nwind.Translate(Of Region)(reader)

      For Each item In RegionList
        Console.WriteLine(" {0}: {1}", item.RegionId, item.RegionDescription)
      Next
    End Using
  End Using
End Using

In this case, we tell the context to translate the resultset of the reader into a generic IEnumerable(Of Region). There are number of caveats to keep in mind here:

1. The column name in the result set must correspond to the property name.The translation is quite forgiving however. It is not case sensitive.
2. If you have a row in the result set but no corresponding property, that row will be ignored (no exception will be thrown).
3. If you have a property in your object with no corresponding row in the result set, that property's value will not be set.
4. The translation must be done to a known type. You can not project into an anonymous type. You can return an object or simple IEnumerable if the type is not known and then use reflection to work with the results.
5. Since the translate method returns an IEnumerable, make sure not to close the connection or the reader before iterating over the results.

The Translate method supports 3 overloads as follows:

    Public FunctionTranslate(Of TResult)(ByVal reader As DbDataReader) As IEnumerable(Of TResult)
    Public Function Translate(ByVal reader As DbDataReader) As IMultipleResults
    Public Function Translate(ByVal elementType As Type, ByVal reader As DbDataReader) As IEnumerable

Translate does not require any mappings to be set. It simply sets the public property values based on the column names. If you have business logic in your Property Set implementations, that logic will run. As far as I can see, there is no way to instruct Translate to use the private storage field instead of the public property.

You can consume stored procs rather than the standard dynamic sql for accessing child objects. To do this, set up your fetch stored procs and make sure that they return the correct data type (not the standard custom generated type for stored procedures). To load a child collection, create a method on the partial implementation of your context. Name the function "LoadCs" where "C" is the name of the child property accessor from the parent object in the designer. This function will take a type as the parent type as a parameter and return an IEnumerable of the Child type. The names you use must agree with the names of the types and properties in your entities in order for this to work.

      Public Class CustomDataContext

     ‘Load a single child

At TechEd Developers 2008 in Orlando, I had the pleasure of competing in Speaker Idol. In that competition, we had the opportunity to present a topic in 5 minutes. Unfortunately, the topic I choose really needed 10 minutes to cover at the level of detail it needed. Instead of limiting the topic, I decided to go ahead and present it a bit too fast.

If you want to see the video, or see how to use VB 9's XML Literals and LINQ to XML to fetch data from a web page (that must be XHtml compilant), manipulate it and insert it into a Word 2007 file, it is now available on the  Developer Landing page, and the Library page. of the TechEd site. If you prefer, you can jump right to the video in either WMV or MP4 file formats. If you're not familiar with LINQ to XML, go ahead and download the video and just watch it at half speed ;-)

Many people have noticed when binding an anonymous type to a grid in VB that the order of the properties does not reflect the order that they were specified in the projection (Select) clause. Instead, they appear alphabetized. Consider the following query:

Dim query = From c In Customers _
                    Select c.LastName, c.FirstName, c.BirthDate

If you bind this query to a DataGrid or DataGridView and allow the columns to be generated automatically, the results will be displayed with the following column ordering: BirthDate, FirstName, LastName. Notice that the columns are ordered alphabetically based on the property name.

I just received word that this behavior is changing in the next update to VB. After this release, the property order should be retained from the projection clause, just as it is in C#.

The Author class has separate fields for the first and last name. Each of these is mapped to the corresponding fields in the Author table. In the book, we show how you can create a read only property in a partial class (so that it won't get clobbered when we regenerate our classes in the designer). The new property is trivial:

   Partial Public Class Author

        Public ReadOnly Property FormattedName() As String

            Get

                Return Me.FirstName & " " & Me.LastName

            End Get

        End Property

    End Class

Notice here that there are no mapping attributes to this property. In part, that is because there is no corresponding field in the table. As we show in the book, you are free to query the author table and return Author objects. From there, you can display the FormattedName as follows:

           Dim authors = From a In context.Authors _

                          Select a

            For Each a In authors

                Console.WriteLine(a.FormattedName & "; " & a.WebSite)

            Next

This works fine because we are projecting the complete Author type. However, in early builds, we couldn't project the unmapped properties into an anonymous type like this:

            Dim authors = From a In context.Authors _

                          Select a.FormattedName, a.WebSite

If you tried to use this projection, you would get a runtime exception. In the RTM bits, the behavior was modified. Now, if you try to run the above query (sample 8.25 in the book samples for anyone following along). You will see that the query succeeds and the anonymous type is used. So how can they know how to populate the FormattedName when it is not mapped and doesn't exist in the table itself? No, the provider doesn't look inside the property, determine the mapped properties that are used, and fetch them. While that could work in our simple example, many unmapped properties  would use significantly more resources, many of which may not be members of our class, or methods without direct translation in TSQL. If you look at the generated SQL that is issued when the query is consumed, you might be able to figure out what is happening in this case.

SELECT [t0].[ID], [t0].[LastName], [t0].[FirstName], [t0].[WebSite], [t0].[TimeStamp]
FROM [dbo].[Author] AS [t0]

Notice here, our select clause to the database is not optimized to only return the fields we requested. Instead, all of the fields are returned. So what's going on? They discovered in evaluating the Select clause that there were unmapped properties. At that point, they just turned around and populated a full author object. Using this object, the provider turns around and generates the anonymous type from the object rather than directly from the underlying data store directly. It's a bit of smoke and mirrors at this point.

So the question that came up asks if the next printing of the book needs to be adjusted to remove the statement that you can't project an unmapped property. While you can indeed project these properties, you can't use them elsewhere in the query. Thus if you wanted to sort the data based on the unmapped property, the exception would be thrown. Consider the following query./p>

            Dim authors = From a In context.Authors _

                          Order By a.FormattedName _

                          Select  a.FormattedName, a.WebSite

In this case when we try to run it, we get the following error:

"System.NotSupportedException: The member 'LinqInAction.LinqBooks.Common.VB.SampleClasses.Ch8.Author.FormattedName' has no supported translation to SQL."

Because of this, I plan to leave the note in the chapter warning you of using the unmapped property in your query. Unfortunately, I don't have enough space in the book to insert this complete explanation at this time. I hope this explanation helps some of you who are confused at this point.

LINQ makes working with data in its various guises easier. By intergating it into the language, we have rich integrated support for working with data. However, there are times where the syntax is slighly different from what you would typically expect with TSQL. Once case where this occurs is when trying to join two data sources that are related by more than one field (also know as a composite key). This differs from standard joins where one table has a primary key and the other table has a foreign key id. Here's a sample table structure for a standard join in Northwind between the Products and Categories:

With Linq, this join could be represented with the following query:

Dim query = _
     From p In dc.Products _
     Join c In dc.Categories _
     On p.CategoryID Equals c.CategoryID _
     Select p.ProductName, c.CategoryName

So far, there's not much new in this query. Here, "p" is the outer variable and "c" is the inner variable of the join. Likewise, p.CategoryID is the outerKeySelector and c.CategoryID is the InnerKeySelector in the Join extension method.

This works fine when we have single values that can be compared easily. However, how can we specify multiple fields for the KeySelectors on the join? In my work with reverse mortgages, we have a situation where the loan amount is based in part on the lending limits set forth by HUD. In their limits, they specify the State and County for each limit. In this case, I need to join those values against the loan property's state and county to come up with the limit amount. Let's consider the following partial table schemas.

In this case, we could join the tables in TSQL with the following query:

SELECT LendingLimits.Amount
FROM LendingLimits
INNER JOIN Property
ON LendingLimits.State=Property.State AND
      LendingLimits.County=Property.County
WHERE PropertyId=@SearchValue  AND
      EffectiveDate = @TargetDate

Unfortunately, the Join extension method does not support the ability to provide the InnerKeySelector/OuterKeySelector as a series of expressions. However, when dealing with objects, we can compare objects to see if they equal each other. Therefore, the solution in this case is to join two anonymous types and compare them against each other. Here's the corresponding LINQ query. Notice the difference in the On clause. If you understand working with objects, this syntax should make sense.

Dim query1 = _
    From l In dc.LendingLimits _
    Join p In dc.Properties _
    On New With {l.County, l.State} Equals _
          New With {p.County, p.State} _
    Where p.PropertyId = SearchValue And _
          l.EffectiveDate = TargetDate _
    Select l.Amount

I was working to come up with some creative uses of LINQ to XML for my new talk I'm giving at the Huntsville, AL Code Camp. I figured it would be good to include a sample which queries a large XML document. Remembering that the complete works of Shakespeare were available in XML form, I did a quick search and found a version at http://metalab.unc.edu/bosak/xml/eg/shaks200.zip. This file separates each play out into separate XML files. Since I wanted to find out which parts had the most lines across all plays, I wanted to put them into a single XML file. Rather than doing this manually, I went ahead and whipped up a quick LINQ query to fetch the xml documents and load them up into an array of XElements:

Dim plays = _
    From file In New System.IO.DirectoryInfo("C:\projects\ShakespeareXml").GetFiles() _
    Where file.Extension.Equals(".xml", StringComparison.CurrentCultureIgnoreCase) _
    Let doc = XElement.Load(file.FullName) _
    Select doc

Ok, now that out of the way, I really wanted to load up a single XML file with these resulting nodes. Pretty easy using XML Literals. Just wrap the query with a new root element:

Using a single self referencing table is a common database pattern for trees of data. As an example of this concept, we can use the Employee table in Northwind. It has a self referential relationship set up using the ReportsTo field. If we drag this into the LINQ to SQL designer, it will infer the self relation and create the appropriate EntitySet/EntityRef relationship exposing it with the Employees (for subordinates of a given employee) and Employee (for the reference to the boss). The ReportsTo property contains the ID of the related property.

Inspecting the attributes used for Employees, we see that the association is mapped as follows:

<Association(Name:="Employee_Employee", Storage:="_Employees", OtherKey:="ReportsTo")> _
Public Property Employees() As EntitySet(Of Employee)

Similarly, Employee is mapped as follows:

<Association(Name:="Employee_Employee", Storage:="_Employee", ThisKey:="ReportsTo", IsForeignKey:=true)> _
Public Property Employee() As Employee

If we wish to traverse the subordinates of a given employee (boss), we can use the following:

Private Sub Main()
   Dim dc As New NWindDataContext
   Dim StartingEmp = (From emp In dc.Employees Where emp.ReportsTo Is Nothing).FirstOrDefault
   Console.WriteLine("{0}{1} {2}", Space(indentLevel * 5), StartingEmp.FirstName, StartingEmp.LastName)
   DisplaySubordniates(StartingEmp)
End Sub

Private indentLevel As Integer
Private Sub DisplaySubordniates(ByVal emp As Employee)
   If emp.Employees.Count > 0 Then
      indentLevel += 1
      For Each emp In emp.Employees
         Console.WriteLine("{0}{1} {2}", Space(indentLevel * 5), emp.FirstName, emp.LastName)
         DisplaySubordniates(emp)
      Next
      indentLevel -= 1
   End If
End Sub

Realize that using this implementation does have a cost. Each time we iterate over the Employee or Employees, we lazy load the resulting set. This can cause a rather chatty interaction with the data source. Because we are working with n levels of heirarchies, we can not optimize this interaction using the load options. If we were to try to add a LoadOptions, we get the following run-time exception: "Cycles not allows in LoadOptions.LoadWith type graph."

So, you're thinqing about convering existing code to use LINQ? Here are a couple quick tricks to get you started:

1) Have your regression tests ready. No warrantee is implied as to your success once completing the following.

2) Find any imports to System.Data.SqlClient including global imports and remove them. See what breaks in your application and re-write it using LINQ to SQL instead of ADO.

3) Search for iteration loops (For Each/foreach) or explicit calls to enumerator.MoveNext and see if you can replace them with declarative queries.

As an example of this last point, consider this bit of code from the Personal Web Starter Kit (in the Admin/Photo.aspx.vb file):

Dim d As IO.DirectoryInfo = New IO.DirectoryInfo(Server.MapPath("~/Upload"))
Dim enumerator As System.Collections.IEnumerator = CType(d.GetFiles("*.jpg"), System.Collections.IEnumerable).GetEnumerator
Do While enumerator.MoveNext
  Dim f As IO.FileInfo = CType(enumerator.Current, IO.FileInfo)
  Dim buffer() As Byte = New Byte((f.OpenRead.Length) - 1) {}
  f.OpenRead.Read(buffer, 0, CType(f.OpenRead.Length, Integer))
  PhotoManager.AddPhoto([Convert].ToInt32(Request.QueryString("AlbumID")), f.Name, buffer)
Loop
GridView1.DataBind()

If your source offers an ability to pass an array of items rather than requiring an explicit iteration, you may be able to simplify the process. In this case, I also chose to refactor out the logic inside the loop to further assist the maintainability. Here's the refactored code:

Sub Button1_Click(ByVal sender As Object, ByVal e As ImageClickEventArgs)
  Dim d As IO.DirectoryInfo = New IO.DirectoryInfo(Server.MapPath("~/Upload"))
  PhotoManager.AddPhotoList( _
     From f In New IO.DirectoryInfo(Server.MapPath("~/Upload")).GetFiles("*.jpg") _
     Select PhotoManager.CreatePhoto(CInt(Request.QueryString("AlbumID")), _
                f.Name, FileBytes(f)))
     GridView1.DataBind()
End Sub

Private Function FileBytes(ByVal source As IO.FileInfo) As Byte()
  Dim length As Integer = CInt(source.OpenRead.Length)
  Dim buffer() As Byte = New Byte(length - 1) {}
  source.OpenRead.Read(buffer, 0, length)
  Return buffer
End Function

Is the resulting code better? In this case, it is a toss-up. I'm just trying to show the possibility. It's up to you to test the applicability in your own applications. If you want more migration hints, check out my presentation at http://www.thinqlinq.com/Downloads/LINQMigrationStrategies.zip.

In case anyone is interested, I have put together a sample port of the original Personal Web Starter Kit using LINQ rather than the standard ADO data tier in the PhotoManager.vb class. With this version, we can eliminate all of the stored procedures and rely on LINQ for our entire data access. In this implementation, I intentionally attempted to retain the original method signatures where possible to make migration more seamless. The project site is at http://code.msdn.microsoft.com/LinqPersonalWeb.

This is one of the sample projects I use in my LINQ Migration strategies talks, so if you attended that talk, check out the sample project for some more concrete examples. Because the original example is fairly basic and the tiers are separated out neatly, doing a migration really only requires replacing code in one file: PhotoManager.vb. Let's take a look at a couple of the refactorings we did for this example.

The meat of the original project is a solution to group images into albums and store them in a database. To start, we create a new mapping file by creating a new LINQ to SQL Classes file. Onto this surface, drag the Album and Photo tables to generate the entity classes and associated mappings. With that in place, we can move our attention to the PhotoManager class which abstracts all of the data access.

The PhotoManager class has separate methods to GetPhoto, GetPhotos, AddPhoto, EditPhoto and RemovePhoto. The same is true for albums. Each of these maps to corresponding stored procedures. Let's compare the original implementation of GetPhoto with the LINQ enabled version. In the original, we see familiar code to create a datareader and Fetch a scalar result from the function.

Public Overloads Shared Function GetPhoto(ByVal photoid As Integer, ByVal size As PhotoSize) As Stream
  Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("Personal").ConnectionString)
    Using command As New SqlCommand("GetPhoto", connection)
      command.CommandType = CommandType.StoredProcedure
      command.Parameters.Add(New SqlParameter("@PhotoID", photoid))
      command.Parameters.Add(New SqlParameter("@Size", CType(size, Integer)))
      Dim Filter As Boolean = Not (HttpContext.Current.User.IsInRole("Friends") Or HttpContext.Current.User.IsInRole("Administrators"))
      command.Parameters.Add(New SqlParameter("@IsPublic", Filter))
      connection.Open()
      Dim result As Object = command.ExecuteScalar
      Try
        Return New MemoryStream(CType(result, Byte()))
      Catch
        Return Nothing
      End Try
    End Using
  End Using
End Function

The corresponding Stored Procedure is as follows:

For those unfamiliar with the sample solution, realize that the photos table contains multiple copies of each image rendered in different resolutions. The procedure is responsible for determining which field to return based on the size parameter that is passed in. By limiting the fields returned, we can optimize the IO requirements, but this does mean that any time we want to access the photos, we need to repeat the same logic. Indeed, the same If block used in the GetPhoto procedure is copied and reused in the GetFirstPhoto procedure. This does not lead for the kind of code maintainability we would like to see.

Naturally, since your reading about it here, I'm sure you would like to see how LINQ may offer a better alternative. Starting with the GetPhoto method, we can eliminate the late bound ADO code and provide a more strongly typed version of the same method. We will also be able to refactor and reuse more pieces of the query through-out the application. Here is the code for our GetPhoto method.

Public Overloads Shared Function GetPhoto(ByVal photoid As Integer, ByVal size As PhotoSize) As Stream
  Dim dc As New PersonalDataContext
  Dim query = From p In dc.Photos _
                      Where (p.PhotoID = photoid) And _
                                 (p.Album.IsPublic Or IsFriend)
  Return GetPhotoBytes(query, size)
End Function

Get photo greatly reduces the amount of plumbing code and allows us to focus on the desired results. GetPhoto returns the actual image that corresponds to the requested ID and size. It also checks to see if the user is allowed to see that photo by checking to see if the album is marked as a public album, or if the user is considered a friend based on their login credentials. Here's the implementation of the IsFriend method.

Public Shared Function IsFriend() As Boolean
  Return (HttpContext.Current.User.IsInRole("Friends") Or _
              HttpContext.Current.User.IsInRole("Administrators"))
End Function

Since the user's credentials are already cached for the current user, there is no need to requery that part of the database on every fetch.  The real key to this implementation lies in the GetPhotoBytes method. In this method, we evaluate the size parameter and dynamically extend our query to project just the field we want to consume.

Last time, we added categories to the web view of the ThinqLinq site. This time, we're going to add them to the RSS feed. Because RSS is "Really Simple", adding the categories is fairly easy. According to the RSS specification, <category> is an optional sub-element of <item>. It can additionally contain the domain that contains that category. In our case, we will point the domain to our implementation that displays all posts for a given category by passing the category id to the query string of our default page. Thus we would want to insert a category like follows:

<category domain="http://www.ThinqLinq.com/default.aspx?CategoryId=1">LINQ</category>

To refresh your memory, we created last discussed creating RSS in the following post: http://www.thinqlinq.com/Default/Projecting_XML_from_LINQ_to_SQL.aspx. To add our comments, we need to inject an additional query before inside of each <item> element for the categories. To generate each of the categories for a post, we add a query to project (using the select statement) our new <category> nodes as follows:

     <%= From c In post.CategoryPosts _
              Select <category domain=<%= "Default.aspx?CategoryId" & c.CategoryID %>>
              <%= c.Category.Title %></category> %>

Notice here, when we are creating the domain, we can use the CategoryID of the CategoryPosts object from the many-to-many table. To display the actual post's title, we need to drill through the CategoryPost to the child Category object's Title property (c.Category.Title). Putting this all together, Using the magic of LINQ to XML and VB, we arrive at the following statement:

Response.Write(<?xml version="1.0" encoding="UTF-8"?>
  <rss version='2.0' xmlns:dc='http://purl.org/dc/elements/1.1/' xmlns:slash='http://purl.org/rss/1.0/modules/slash/' xmlns:wfw='http://wellformedweb.org/CommentAPI/'>
  <channel>
    <title>Thinq Linq</title>
    <link>http://www.ThinqLinq.com/default.aspx</link>
    <description>Thoughts relating to LINQ and Language Integrated Query related topics.</description>
    <dc:language>en-US</dc:language>
    <generator>LINQ</generator>
    <%= From post In query.ToArray _
            Select <item>
                         <title><%= post.Title %></title>
                         <link><%= "http://www.thinqlinq.com/Default.aspx?Postid=" & post.Id.ToString %></link>
                         <pubDate><%= post.PublicationDate.ToString("ddd, dd MMM yyyy hh:mm:ss GMT") %></pubDate>
                         <guid isPermaLink="false">42f563c8-34ea-4d01-bfe1-2047c2222a74:<%= post.Id %></guid>
                         <description><%= post.Description %></description>
                         <%= From c In post.CategoryPosts _
                                  Select <category domain=<%= "Default.aspx?CategoryId" & c.CategoryID %>>
                                  <%= c.Category.Title %></category> %>
                         </item> %>
    </channel>
  </rss>)

If you want to view the resulting feed, browse directly to http://www.thinqlinq.com/rss.aspx rather than using the standard RSS feed link which is managed through FeedBurner to allow me to gather additional statistics for this site.

Ok, so I've been a bit busy this weekend adding some nice stuff for this site. One thing that I wanted to add was another RSS feed, this time for the file upload section. If you want to subscribe to the File RSS feed, direct your aggregator to the following link: http://www.thinqlinq.com/Files.aspx/Rss

Of course, since this is a learning site, I'll let you in on the code needed to accomplish the task. As you may guess, LINQ makes serving up XML from an object collection using a heterogeneous join to a database fairly easy. If you haven't seen how we display the files for this site, check out the podcast I did for Wally last year where we go over it in more detail.

To begin the task, we will set-up our initial query We will use two data sources for this task. The first data source is the object collection containing the FilInfo objects from the download directory for this site. (System.IO.DirectoryInfo(...).GetFiles). The second source is the FileClassic table returned using LINQ to SQL. LINQ allows us to easily join these two data sources and work with it as we would any other data source.

Dim fileDescriptions = dc.GetTable(Of FileClassic)()
Dim files = (From f In New System.IO.DirectoryInfo(Server.MapPath("~/Downloads")).GetFiles _
                  Join desc In fileDescriptions On f.Name Equals desc.FileName _
                  Order By f.LastWriteTime Descending _
                  Select URL = "Downloads/" & f.Name, _
                  Name = System.IO.Path.GetFileNameWithoutExtension(f.Name), _
                  f.LastWriteTime, f.Length, _
                  Description = Server.HtmlDecode(desc.Description), desc.Id).ToArray

With our query prepared, we can now use LINQ to XML with VB Literals to generate the RSS feed and output it to the response stream as follows:

Response.Write(<?xml version="1.0" encoding="UTF-8"?>
  <rss version="2.0" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:slash="http://purl.org/rss/1.0/modules/slash/" xmlns:wfw="http://wellformedweb.org/CommentAPI/">
    <channel>
    <title>ThinqLinq samples and presentations</title>
    <link>http://www.ThinqLinq.com/Files.aspx</link>
    <description>Here are some of my presentation files available for you to use as you wish.  Just let me know if you like what you see and what you want to see more of.</description>
    <%= From f In files _
            Select <item>
                        <title><%= f.Name %></title>
                        <link><%= "http://www.ThinqLinq.com/" & f.URL %></link>
                        <pubDate><%= f.LastWriteTime.ToString("ddd, dd MMM yyyy hh:mm:ss GMT") %></pubDate>
                        <guid isPermaLink="false"><%= "42f563c8-34ea-4d01-bfe1-2047c2222a74:" & f.Id %></guid>
                        <description><%= f.Description %></description>
                       <enclosure url=<%= "http://www.ThinqLinq.com/" & f.URL %>
                                         length=<%= f.Length %>
                                         type="application/x-zip-compressed"/>
                      </item> %>
    </channel>
  </rss>)

The main difference between this example and the ones we are using for blog posts is the addition of the <enclosure> node. With enclosure, we specify the url to the file download, the file length, and the file type. In this site, I plan to always use ZIP files so I just hard code that. The other values come from our starting query. If you subscribe to this new feed, you should see each item including an enclosure icon so that you can download it.

When I first released ThinqLinq, the only filtering I applied was to only select the top 20 posts. I was recently asked if I could extend the implementation so that it the aggregation feed could be filtered based on the categories. Since ThinqLinq uses LINQ for the data interaction, it is relatively easy to add filtering to the existing query.

However, in this case, the filtering is not a simple Where clause on the underlying table. That is because the table structure uses a Many to Many relationship between the Categories and PostItems. Here's the object relationships as created by the LINQ to SQL designer:

Typically when querying a database in a many to many relationship, we start on one end and work our way to the other. LINQ offers another option. Consider the following LINQ Query:

From catPost In dc.CategoryPosts _
Where catPost.Category.Title = Request.QueryString("Category") _
Select catPost.PostItem

In this query, we actually start in the middle. We can do it because we can use object trees rather than having to rely on joins. With LINQ, the many-to-many table contains a reference to the related objects on both sides. This way, we can use one side (the Category) in the Where clause and the other side (PostItem) for the Select clause. Naturally, we could also mix and match the results.

For the rss feed, we need to return the full results if no query string is supplied, but filter it if there is one. In addition, we will replace the spaces with underscores so that we can include them in the query string. With VB's Replace method, we can easy convert back from the underscores to the spaces. Here's the first query that the example will use:

Dim query As IQueryable(Of PostItem)
If Request.QueryString("Category") <> "" Then
    query = From catPost In dc.CategoryPosts _
           Where catPost.Category.Title = Replace(Request.QueryString("Category"), "_", " ") _
           Select catPost.PostItem
Else
    query = dc.PostItems
End If

We declare the query as IQueryable(Of PostItem) so that we can further refine the query results later. Regardless of whether we use the filtered or unfiltered version, we still want to limit the results to the most recent 20 items. The composability of IQueryable allows us to further refine our query as follows:

In the LINQ in Action forum, a user asked about returning multiple result sets from a single stored procedure. Below is one way of dealing with this issue.

In the procedure, we used multiple results rather than a result with a return value (through RETURN or an OUTPUT parameter). Here we need to use the IMultipleResult rather than the default ISingleResult implementation. It appears that the designer does not map IMultipleResult in the final build, so we are going to need to do it ourselves. We mention this interface in chapter 8 but didn't have a chance to include a sample. Here's a sample implementation on returning the Subjects and Books from the Book sample database. First the stored proc:

CREATE PROCEDURE dbo.GetSubjectAndBooks
AS

 Select * from subject
 
 IF @@RowCount>0 BEGIN
  Select * from Book
 END

Now for the function mapping. We want to create a function that can return both the Subjects and the Books. To do this, we will create a function that returns the MultipleResult. Simlar to the standard stored procedure mapping, you create a function in a custom partial for the DataContext. The function will return a value of type IMultipleResults. Decorate the function with the FunctionAttribute including the name of the function. Here's the implementation in VB:

    <FunctionAttribute(Name:="dbo.GetSubjectAndBooks")> _
    <ResultType(GetType(Book))> _
    <ResultType(GetType(Subject))> _
    Public Function GetSubjectAndBooks() As IMultipleResults
        Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo))
        Dim results As IMultipleResults = DirectCast(result.ReturnValue, IMultipleResults)
        Return results
    End Function

Notice, the main difference here is the addition of two attributes identifying the possible ResultTypes (Book and Subject). The rest of the function should be self explanitory. To consume the function, we call the GetResult method of IMultipleResults passing in the generic type we want to return as follows:

    Dim context As New LinqBooksDataContext

    ObjectDumper.Write(context.GetSubjectAndBooks.GetResult(Of Subject))
    ObjectDumper.Write(context.GetSubjectAndBooks.GetResult(Of Book))

Today, I'm not looking at sending mass spam using LINQ to pull a list of recipients. I'm actually referring to the ability to generate the message body using XML Literals. Using the System.Net.Mail.MailMessage object, we can easily send emails to an SMTP server.

The body of the email can either be plain text or HTML. Dynamically generating the text is often a laborious task involving a string builder and lots of method calls. The body corresponds to the body portion of a HTML page. If you use well formed XHTML in the body, you are actually generating a specialized version of XML. Once we are working with XML, we can use XML Literals in VB to format our output.

I recently had to do this on a project to send lists of updated values from an external source. In the body, I needed to dynamically fill a HTML table with the new values. The table consists of 4 columns: State, County, Limit, Effective Date. I begin by laying out the content in a HTML editor (like Visual Studio 2008...) Here's the results:

<body>Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur eros purus, suscipit ac, pulvinar vel, aliquet vehicula, pede. Duis eros dolor, iaculis non, aliquam sed, tincidunt ac, diam.
    <table>
      <tr><th>State</th>
            <th>County</th>
            <th>New Limit</th>
            <th>Effective Date</th>
      </tr><tr><td>XX</td>
                     <td>Foo</td>
                     <td>$123</td>
                     <td>1/1/2000</td>
               </tr>
    </table>
  </body>

I know what you must be thinking by now: Gee Jim, how could you come up with such a beautiful page. As Bones would say, "D@mmit Jim, I'm programmer not a designer." We'll keep it clean for now to focus on what is yet to come.

Realize that our body tag is actually the root of a well-formed XML document. As such, we can copy it as a template directly into our tool (which is a console application by the way), add a reference to System.Linq and System.Xml.Linq, and paste it into our VB module assigning a variable, let's call it "body" to the XML.

While we're at it, we'll go ahead and insert new rows into the table based on the results of an object query. In this query, we'll iterate over the records we are adding which is an IEnumerable(Of Limit). We'll project a new row (<tr>) for each object in our iteration. Rather than imperatively iterating, we'll use LINQ's declarative syntax In addition, we'll insert our values using the <%= %> place holders. Here's the resulting declaration:

Dim message As New System.Net.Mail.MailMessage("from@ThinqLinq.com", "to@ThinqLinq.com", "Limits Updated", body.ToString)
message.IsBodyHtml = True
Dim server As New System.Net.Mail.SmtpClient(My.Settings.SmtpServer)
server.Send(message)

There you go, a quick and painless way to create HTML emails using VB 9 and LINQ. Let me know what you Thinq.

In my VB 9 language enhancements talks, I do them withalmost all coding on the fly as I find people often can comprehend the code. I start by building a quick class that is used throughout the demos. To assist, I do use the snippet functionality in VB. For example, if you type "property" and then tab twice, the designer will generate a private field with public property accessors. The if you change the highlighted values, any associated names will be changed as well.

Private newPropertyValue As String
Public Property NewProperty() As String
    Get
        Return newPropertyValue
    End Get
    Set(ByVal value As String)
        newPropertyValue = value
    End Set
End Property

Personally, if you are not doing anything within your properties, there isn't much that you buy in using properties as compared to just exposing the field publically. I want a bit more functionality built into my properties. In the very least, I want to be able to include some change tracking. Once nice feature of the snippets is the fact that they are quite easy to modify and create your own.

To begin, we need to find where the supplied snippets are located on your disk. We can find this by clicking "Tools" and then  the "Code Snippets Manager". We can find the location by drilling into the "Code Patterns" then "Properties, Procedures, Events" and find the "Define a Property" snippet. The location window will show you where this one is located. In the default install, it will be in your c:\Program Files\Microsoft Visual Studio 9.0\Vb\Snippets\1033\common code patterns\properties and procedures\ folder. Navigate to this folder and copy the DefineAProperty.snippet. Paste it as a new file and name it whatever you want keeping the .snippet extension.

The snippet file is just a XML document. Open it with visual studio to edit it. The top is a Header which includes the description information that will show up in the snippet manager. One change you will need to make is to alter the "Shortcut" tag so that it will use the key combination you want to use to invoke your custom snippet. In my demos, I use "propertycc", thus I change the header as follows: