Using LINQPad with SDL Tridion

LINQPad is a development tool that allows you to write C# scripts. This article explains how to use LINQPad in combination with Tridion.

I got started with LINQPad to write simple C# "scripts". So stuff that you would normally put in a small command line program in a Visual Studio project, is something you can also easily do in LINQPad.

LINQPad driver - 1 - Simple test program

The code in this example is a modified version of this Fibonacci program on Dot Net Perls.

Database connections

LINQPad can also connect to certain types of data sources. Similar to SQL Server Management Studio, you can connect LINQPad to your database server once and tell it to keep that connection across sessions. So when you restart LINQPad, the database connection will be there and ready to go.

LINQPad driver - 2 - Connection to MSSQL

Those persistent connections are one reason why some people have already proclaimed LINQPad the perfect replacement for SQL Server Management Studio. you can set up the connections to your regular databases once and then run queries against them in LINQPad whenever you need.

LINQPad driver - 3 - MSSQL Queries

Using LINQPad like this removes the need to Remote Desktop into the machine where the database is and fire up SQL Server Management Studio (or have that installed locally).

OData connections

But my use of LINQPad took flight when Tridion 2011 introduced OData.svc as part of its Content Delivery Services. Just like LINQPad can connecto to databases, it can also connect to OData services, including Tridion's.

LINQPad driver - 4 - OData collections

So with this, you can keep a persistent connection to your Content Delivery OData service open and quickly check the data that is present in the broker.

LINQPad driver - 5 - OData query

A very nice feature is that you can write your OData queries in LINQ and then check the URL that is being sent to the OData service.

LINQPad driver - 6 - OData query URL

If you're somewhat familiar with LINQ, you'll find that this is a very efficient way to write your OData queries.

Tridion connections

When a while ago somebody asked how to connect with LINQPad to the Core Service on Stack Overflow, I found my usage of LINQPad once again increasing. It turns out that the "plumbing" code required to connect to Tridion's Core Service is surprisingly small. Check my answer on Stack Overflow to see the code, or read on to see why you won't need it anymore.

After using the plumbing code fragment for a while, I created a custom Tridion Core Service driver for LINQPad. With this driver you get the following extra options when you click "Add connection" in LINQPad:

LINQPad driver - 7 - Tridion driver

When you click Next you get the following custom dialog:

LINQPad driver - 8 - Tridion connection dialog

Fill in the hostname or IP address, username and password of your connection and click OK. LINQPad will now show the connection to your Tridion Content Manager in the list of connections on the left. Just like with connections to databases and OData services, you can make these connections once and stay connected to all of your Tridion servers easily:

LINQPad driver - 9 - Tridion connections

And if you check the "Remember this connection" box in the dialog, these connections will be restored next time you start LINQPad.

Writing LINQ queries

When you write a query in LINQPad against a Tridion Core Service connection, you have an implicit Tridion property available. This property is the ICoreServiceClient you normally put in a client variable. For convenience it is also available as Client, Tcm or this, but I find that calling it Tridion results in the most readable code.

So let's start by getting a list of all Publications in the system:

Tridion.GetSystemWideListXml(new PublicationsFilterData())

Note: when writing code against the Core Service, I keep the API documentation (in CHM format) open at all times. The API is not very big and you'll soon find that you'll remember the most common types and methods. But it's very handy to go back and forth between the CHM and LINQPad with a single alt-tab.

The above fragment returns the XML document in the result area of LINQPad:

LINQPad driver - 10 - Get list of Publications

To allow us to continue processing the publications, we'll get the elements:

Tridion
    .GetSystemWideListXml(new PublicationsFilterData())
    .Elements()

This simply means that every item in the list XML becomes a separate item in the results, which makes further processing possible.

LINQPad driver - 11 - Get list of Publication Elements

For example let's say that we want to show the ID, title and parent for each publication:

Tridion
    .GetSystemWideListXml(new PublicationsFilterData())
    .Elements()
    .Select(elm => (PublicationData)Tridion.Read(elm.Attribute("ID").Value, null))
    .Select(pub => new {
        Publication=pub.Title+" ("+pub.Id+")",
        Parents=string.Join(",", pub.Parents.Select(parent => parent.Title+" ("+parent.IdRef+")"))
    })

At this stage be sure to set LINQPad to show the results as Rich Text. The Data Grid format unfolds objects. Although the additional information this displays is great for debugging, it may lead the query to time out when you use it on a large dataset like this.

On my system, this results in the following simple table:

LINQPad driver - 12 - Publications and parents

Just keep in mind that every call to Tridion. results in another roundtrip to you Tridion Content Manager server. So when the query takes more time than you expected, check what calls you are doing. In this last example we are loading every publication in our system. Although the information we can get from this is great, it makes sense that such a query may take some time to complete.

Writing regular Core Service client code

So far we've been writing pure LINQ queries. Just like with SQL queries, LINQ queries have a single result and (should) make no changes to the data on the server.

But we can also use LINQPad to write more regular C# code and make modifications to our Tridion Content Manager. For example: let's create a new Component. First we have to switch the Language dropdown in LINQPad from "C# Expression" to "C# Statement(s)":

LINQPad driver - 13 - CSharp statements

Then we can start by creating a new Component in the correct folder:

var folderId = "tcm:10-326-2";
var component = (ComponentData) Tridion.GetDefaultData(ItemType.Component, folderId);

If we run the code like this, Tridion will simply create a new ComponentData object in memory. Nothing is saved to the server yet, so you can just run this query (F5) without worrying. If the folder has a mandatory Schema set, the component's Schema property will be set to that.

But when we run the code, we don't see anything in the results window. Because we switched to "C# Statement(s)" LINQPad will no longer automatically output anything. If we want to output something, we have to do that from our code. Luckily LINQPad adds a great extension method called Dump() to every object. So if we just add that to our code, the results panels shows the new Component:

LINQPad driver - 14 - New empty Component

In this case I can see in the results (scroll down in the result window if you're following along) that the Folder apparently doesn't specify a mandatory Schema. So we have to set one ourselves in the code.

To get a quick overview of the Schemas in a Publication, I run this LINQ query:

Tridion
    .GetListXml("tcm:0-10-1", new RepositoryItemsFilterData {
        Recursive=true,
        ItemTypes = new[] { ItemType.Schema },
        SchemaPurposes= new [] { SchemaPurpose.Component }
    })
    .Elements()
    .Select(elm => new {
        ID=elm.Attribute("ID").Value,
        Title=elm.Attribute("Title").Value,
        Fields=String.Join(", ", Tridion.ReadSchemaFields(elm.Attribute("ID").Value).Fields.Select(field => field.Name))
    })

Don't forget that you'll need to set LINQPad's Language dropdown to "C# Expression" for this query.

LINQPad driver - 15 - Schemas and fields

Let's use the Article schema for now:

var folderId = "tcm:10-326-2";
var component = (ComponentData) Tridion.GetDefaultData(ItemType.Component, folderId);
component.Schema.IdRef = "tcm:10-1664-8";
component.Title = "Article created from LINQPad at "+DateTime.Now.ToString();
component.Dump(1);

We limit the output of Dump to a single level here, to make the output less verbose.

LINQPad driver - 16 - New component with title set

Next up we have to set the Content of the new Component. Normally when using the Core Service you only get access to the Content as raw XML. But the Core Service driver for LINQPad comes with a built-in copy of the Fields collection from the Tridion practice wiki. With that in place and the list of fields we saw earlier, we can easily fill in the Content property of our article:

var folderId = "tcm:10-326-2";
var component = (ComponentData) Tridion.GetDefaultData(ItemType.Component, folderId);
component.Schema.IdRef = "tcm:10-1664-8";
component.Title = "Article created from LINQPad at "+DateTime.Now.ToString();
var schemaFields = Tridion.ReadSchemaFields(component.Schema.IdRef, true, null);
var fields = TcmFields.ForContentOf(schemaFields);
fields["title"].Value = "Tridion driver for LINQPad released";
fields["summary"].Value = "A driver for accessing Tridion from LINQPad was released on SDL Tridion World";
fields["description"].Value = "...";
component.Content = fields.ToString();
component.Dump(1);

Be sure to check the XML in the result window at this stage:

LINQPad driver - 17 - New component with content set

This looks good to me, so we can save the component and write its TCM URI to the result window.

var folderId = "tcm:10-326-2";
var component = (ComponentData) Tridion.GetDefaultData(ItemType.Component, folderId);
component.Schema.IdRef = "tcm:10-1664-8";
component.Title = "Article created from LINQPad at "+DateTime.Now.ToString();
var schemaFields = Tridion.ReadSchemaFields(component.Schema.IdRef, true, null);
var fields = TcmFields.ForContentOf(schemaFields);
fields["title"].Value = "Tridion driver for LINQPad released";
fields["summary"].Value = "A driver for accessing Tridion from LINQPad was released on SDL Tridion World";
fields["description"].Value = "...";
component.Content = fields.ToString();
component.Metadata = TcmFields.ForMetadataOf(schemaFields, component).ToString(); 
component = (ComponentData)Tridion.Create(component, new ReadOptions());
component.Id.Dump();

LINQPad driver - 18 - New component created

And now we can quickly head off to a browser window to check on our new creation. Or if by now you're hooked on LINQPad, just open a new window and:

Tridion.Read("tcm:10-5475", null)

So by now we've seen that we can use the Tridion driver for LINQPad to execute LINQ queries against Tridion's Core Service and to write small programs that create content or perform more complex queries.

Built-in collections

When we had just created our first connection to Tridion in LINQPad you no doubt saw some familiar item types under our new connection: Publications, Users, Groups and PublishTransactions.

If you right-click on one of them, you get the option to fire some common LINQ queries against them, such as counting the number of Publications in the system.

LINQPad driver - 19 - Publications Count menu

These properties in the UI are just shortcuts to some of the items that you can normally get from the Core Service using a call to GetSystemWideList.

In fact, the Publications property that shows in the LINQPad UI is quite similar to the code we had in our first example:

public IEnumerable<PublicationData> Publications
{
    get
    {
        return GetSystemWideList(new PublicationsFilterData()).Cast<PublicationData>();
    }
}

The Tridion driver for LINQPad adds some of these convenience properties to the CoreServiceClient that it makes available to your code. So instead of writing your own call to GetSystemWideList to get the publications, you can also simply code:

Tridion.Publications.Count

Or

Publications.Count

That last fragment is precisely what LINQPad itself generates for you. But this time around, you also know how it works.

Context Organizational Item

We left one field blank: Context Organizational Item. If we fill in the TCM URI of an organizational item into this field, the connection will start working in that context. So if for example we specify tcm:0-10-1, it will start working on the Publication we've been using for our last few examples:

LINQPad driver - 20 - Collections on Publication

The screenshot shows that we now have more properties available. The list will show a different set if properties based on whether the context is a Publication, a Structure Group, a Folder or empty, which is what we started with.

Not all properties are probably equally useful. Most often I leave the Context Organizational Item field empty in my connections and instead just get to where I need to work/look in the code window.

Summary

In this article we've covered what LINQPad is and how you can use it to interact with the Tridion Content Manager through its Core Service API.

You can find the Tridion driver for LINQPad here.

About the Author
Frank van Puffelen
Technical Director

Frank joined SDL in 2004 and as a principal developer worked on many of the company's releases until the end of 2012. He is now the technical director of ZyLAB USA.

ZyLAB