Having used Linq2Umbraco for a while now to retrieve Umbraco data in custom DALs I really needed a way to build queries and execute them against the Umbraco web site’s data without each time compiling the application and maybe even firing up the debugger. Enter LINQPad by by Joseph Albahari, a fantastic program which has pretty much replaced SQL Server Management Studio on my workstation for querying databases. Although you can query the Umbraco database of your web site LINQPad can’t natively operate on an UmbracoDataContext, which is what Linq2Umbraco is using when you export the Umbraco document types to .Net and which you will be using when querying the data in the DAL. So it would be really helpful if you can use the UmbracoDataContext directly in LINQPad as you can use these queries 1:1 in your code. Thankfully LINQPad can be extended to work with custom DataContexts, all that needs to be done is to write a custom data context driver as described here. The documentation is exceptionally good and after a short while the first Linq2Umraco driver for LINQPad was ready and is now free to download:

Download TheFarm Linq2Umbraco Driver for LINQPad 1.0 (16.08kb)

The driver has been compiled against LINQPad 2.31 using .Net 3.5, so it should work fine on both current versions of LINQPad (.Net 3.5 and 4). The umbraco.Linq.Core.dll, which the driver needs as it contains the UmbracoDataContext, will be dynamically loaded. The driver assumes that it exists in the same directory as the dll which contains the custom Linq2Umbraco DataContext. The dynamic loading has 2 advantages:

• The size of the driver is only 16kb! No need to pack all the dlls in there as they are all present in your Umbraco installation anyway. And this way the driver isn’t bound to one specific Umbraco version.
• LINQPad requires it’s driver assemblies to be strongly signed, and .Net requires every referenced assembly to be strongly signed as well. However umbraco.Linq.Core is not strongly signed, and although I managed to strongly sign the assembly by using a little tool it did create havoc to the Umbraco installation, so dynamically loading the assembly magically resolves all that headache!

Following is a quick demonstration of the driver in use. I’ve created a small test Umbraco installation with a couple of document types and content nodes:

The doc types contain just a couple of standard fields like text string, data,  plus one Ultimate picker instance on the Products page (checkbox list, saved as comma delimited string). Now I’ll export the document types to .Net by right clicking on ‘Document Types’, the popup window will be populated like this:

It doesn’t matter if you choose POCO with or without abstractions. It is also very advisable to install Matt Brailford’s fantastic AutoExport2DotNet package, it will perform an export to .Net every time you modify the document types.

After downloading and renaming the UmbracoDataContext files I’ll include them in the custom DAL like so:

As you can see at the moment the DAL project doesn’t contain anything else but the two automatically generated files plus the necessary link to umbraco.Linq.Core. The Umbraco 4.6.1 test installation is showing up right next to it, I need the /App_Data/umbraco.config file from it later.

Now let’s start up LINQPad and add a connection using the custom driver:

Clicking on View more drivers… will reveal the following dialog, where Browse… let’s you select the custom LINQPad driver:

Opening the driver will shortly lead to the message ‘Driver successfully loaded’, the driver will now appear in the list of drivers from above:

After hitting ‘Next’ the following dialog will appear:

Here the properties for the Linq2Umbraco connection are set.

• Path to custom assembly: This is the assembly that contains the exported UmbracoDataContext, in this case it’s TheFarm.DAL.dll
• Full name of custom type: A popup window will present you a list of all UmbracoDataContexts in the assembly, in 99% of the cases there will only be one and in this case it’s the TheFarmDemoDataContext as specified above
• /Appdata/umbraco.config: The umbraco.config file of the Umbraco installation found in /App_Data.
• Surpress lazy secondary queries: UmbracoDataContext uses the AssociationTree class to list children, e.g. the class Products will contain a list of ProductCategories. When querying Products you will most likely not want to generate a list of all ProductCategories for each Product, Surpress lazy secondary queries will tell the program to stop evaluating these expressions as well.

After hitting OK the connection will be added to LINQPad and you can start querying the custom UmbracoDataContext:

Notice here the output of TopProducts (Ultimate picker), Introduction (Richtext editor) and PricesValidUntil (date). ProductCategorys and Productss are the above mentioned children and grand children which are here surpressed with ‘Surpress lazy secondary queries’. Another example:

You can also easily create custom Linq Extension methods e.g. in the DAL and use them in your queries like so:

[Credits for the ToCSV extension go to Muhammad Mosa.]

TheFarm Linq2UmbracoDataContextDriver 1.0.lpx (16.08 kb)