Corey Roth [MVP] : LINQ

A simple way to programmatically create SharePoint security groups

CoreyRoth — Mon, 08 Mar 2010 17:12:50 GMT

When it comes to SharePoint deployments, I try to automate everything I can. I don’t like manual steps especially when it comes to setting up security. A common task when deploying any sites is setting up security in some manner. Today I am going to cover how to easily store definitions your SharePoint security groups in an XML file. We’ll use LINQ to XML to make reading the file a breeze, and then we’ll use the SharePoint object model to create the groups and add users (or AD groups). I’ve blogged on how to create a group before, but we’re going to take this a step further by giving you code that you can easily add to a feature receiver or console application. First let’s take a look at the XML file we’re going to use.

<?xml version="1.0" encoding="utf-8" ?>
<Groups>
  <Group Name="My Custom Read Group" Owner="SHAREPOINT\GroupOwner" Description="Readonly Permission Group" PermissionLevel="Read">
    <User Name="SHAREPOINT\TestUser1" />
    <User Name="SHAREPOINT\TestGroup1" />
    <User Name="SHAREPOINT\TestGroup2" />
  </Group>
  <Group Name="My Custom Contributors Group" Owner="SHAREPOINT\GroupOwner" Description="Contributors Permission Group" PermissionLevel="Contribute">
    <User Name="SHAREPOINT\TestGroup3" />
  </Group>
</Groups>

In this file, I am defining two SharePoint groups. One that will have read access and one that will have contribute access. I store the required information needed by the Add method on the SPGroupCollection object. I then have one or more User elements with the name of my Active Directory user or group. I tried to keep my XML schema pretty simple. You can customize it obviously how you want, you would just have to alter your LINQ queries.

Let’s take a look at the code we need to make this happen. I won’t go into as much detail of the object model since I went into it pretty well on my last post. We’ll just focus on how we use LINQ to XML to read the information we need and then have it create our groups. My method is called CreateGroups and it takes an SPWeb object and a string with the filename of the XML document.

private void CreateGroups(SPWeb currentSite, string groupsFilename)
{
    // get the xml document from the feature folder
    XDocument groupsXml = XDocument.Load(groupsFilename);
 
    // create a new anoynmous type with the group data
    var groups = from sharePointGroup in groupsXml.Root.Elements("Group")
                select new
                {
                    Name = sharePointGroup.Attribute("Name").Value,
                    Owner = sharePointGroup.Attributes("Owner").Any() ? sharePointGroup.Attribute("Owner").Value : null,
                    Description = sharePointGroup.Attributes("Description").Any() ? sharePointGroup.Attribute("Description").Value : string.Empty,
                    PermissionLevel = sharePointGroup.Attributes("PermissionLevel").Any() ? sharePointGroup.Attribute("PermissionLevel").Value : null,
                    Users = sharePointGroup.Elements("User").Any() ? sharePointGroup.Elements("User") : null
                };
 
    // iterate through the groups and create the groups
    foreach (var sharePointGroup in groups)
    {
        // only create the group if it does not exist
        if (!ContainsGroup(currentSite.SiteGroups, sharePointGroup.Name))
        {
            // add the owner to the web site users
            currentSite.EnsureUser(sharePointGroup.Owner);
 
            // add the group
            currentSite.SiteGroups.Add(sharePointGroup.Name, currentSite.SiteUsers[sharePointGroup.Owner],
                currentSite.SiteUsers[sharePointGroup.Owner], sharePointGroup.Description);
        }
 
        // add the users to the group
        AddUsersToGroup(sharePointGroup.Name, sharePointGroup.Users, currentSite, sharePointGroup.PermissionLevel);
    }
}

This seems like kind of a big method at first, but it’s really not that bad. To keep things simple, I haven’t included any exception handling code. We are really just querying the XML document, iterating through each group element inside of it, creating the groups, and then adding the users to the group. The first line of code just creates an XDocument object. We then construct a LINQ to XML query. What we want is to return data from each Group element in the document. The Add method doesn’t like nulls, so we check for them and use string.Empty if the value does not exist in the file. The one case where I don’t do this is for the Name of the group. If that is not present, I would rather the process throw an exception. As for the Users assigned to the group, I grab all of them and add them to our anonymous type like this.

Users = sharePointGroup.Elements("User").Any() ? sharePointGroup.Elements("User") : null

This gives us an IEnumerable<XElement> that we can pass to a method later to add each Active Directory user (or group) to the SharePoint group. Once we execute the query, we iterate through each group element. The first thing we have to do is make sure that the group does not exist. Of course there is no way to do that other than using the try/catch technique. I will usually wrap this in an extension method, but for today’s purpose, we’ll just call a method to check.

private bool ContainsGroup(SPGroupCollection groupCollection, string index)
{
    try
    {
        SPGroup testGroup = groupCollection[index];
        return true;
    }
    catch (SPException e)
    {
        return false;
    }
}

Lame I know. I’m so happy there are ways to get around this in SharePoint 2010. Then this starts to look like code from the previous post. We call .EnsureUser to make sure the domain account of the group owner is registered with the site. We then just call the Add method with the Name, Owner, default user, and description. Again there is more info on the previous post about that method call. Assuming the group is created, we can then add the users to the group. We call a new method AddUsersToGroup which takes the groupName, the users element, an SPWeb, and the permission level.

The first thing we do is query the names of the Active Directory users (or groups). Here we are just grabbing it from the Name attribute of the User element. I probably could have condensed this query, but at least it’s easy to read. We then add each user (or group) from the User elements to the group. If you are curious about the empty parameters, take a look at the previous post. If you are going to run into an exception, it’s going to be here. If the group failed to be created or if the user does not exist (i.e.: you typed it in the XML file wrong), this line will throw an exception.

private void AddUsersToGroup(string groupName, IEnumerable<XElement> users, SPWeb currentSite)
{
    // select the username from the xml document
    var userList = from user in users
                   select new
                   {
                       Name = user.Attribute("Name").Value
                   };
 
    // add the users to the sharepoint group
    foreach (var user in userList)
    {
        currentSite.SiteGroups[groupName].AddUser(user.Name, string.Empty, user.Name, string.Empty);
    }
}

Now, we’re almost done. The last thing we need to do is set the permission level on the group. This is where we specify whether the group has readonly, contribute, full control, etc access to the site. Be sure and get the name on the permission level right otherwise you will get an exception. I’ve also blogged about how to assign permission levels before. Today’s post is really just a great practical use of putting together the things I have posted on before.

private void SetRoleDefinitionBinding(string groupName, SPWeb currentSite, string permissionLevel)
{
    // add the read role definition to the site group
    SPRoleAssignment roleAssignment = new SPRoleAssignment(currentSite.SiteGroups[groupName]);
    roleAssignment.RoleDefinitionBindings.Add(currentSite.RoleDefinitions[permissionLevel]);
    currentSite.RoleAssignments.Add(roleAssignment);
    currentSite.Update();
}

Effectively you create a new SPRoleAssignment by passing it a SPGroup object. You then add a binding using the existing RoleDefinitions on the site. You then add the assignment to the site and of course call .Update() so things get saved.

That’s really all there is to it. This is a great use of combining information from my previous posts into something that you can use everyday to set security on your sites. How you execute this code is up to you. I’ve used it in a feature receiver and in a console application before. Setting up security through the UI is very slow and painful. Once you create it on one server, there is no way to move it to another server and that’s not a lot of fun. This should help you with that and eliminate those nasty manual steps in your deployment process.

Checking to see if a list item exists in SharePoint 2010

CoreyRoth — Wed, 21 Oct 2009 16:02:00 GMT

This is another topic that is quite dear to most SharePoint developers. We’ve all been there. We want to know if a list item exists and the indexer is useless. It throws an exception should we attempt to access an item that does not exist. Well as we discovered this week there is a new TryGetList method that works great to determine if a list exists but there really isn’t anything new with a SPBaseCollection. We can do this a number of ways include spmetal.exe to generate strongly typed classes, but I was looking for a solution that didn’t require me to generate any classes. We can now use .OfType<T> to get an IEnumerable<T> that we can use. Once we have IEnumerable<T> it is quite easy to determine if a list item exists using the .Any() method. Look at the example below.

using (SPSite siteCollection = new SPSite("http://moss-server"))
{
    SPList myCustomList = siteCollection.RootWeb.Lists.TryGetList("MyCustomList");
 
    // doesn't throw exception!
    if (myCustomList != null)
    {
        var listEnumeration = myCustomList.Items.OfType<SPListItem>();
        return listEnumeration.Any(p => p.Name == "List Item 1");
    }
}

As you can see using the lambda expression p => p.Name we can do a comparison to see if a list item with that name exists. This will return us a boolean if the item does in fact exist. As you can see this isn't quite as good as a native method that checks for an item that exists, but it does work. If you want to get creative you can also check on other fields such as Title, Id, or whatever. Take a look at this sample.

listEnumeration.Any(p => p["Title"] == "List Item 1");
listEnumeration.Any(p => p.UniqueId == someGuid);

As you can see, LINQ makes it quite easy to determine if an item exists. This may not be the most efficient way to do it as it is rather brute force. It may only be arguably better than using a try / catch block, but it certainly looks cleaner.

Linq to XML and Deleting Lists on Feature Deactivation

CoreyRoth — Thu, 24 Sep 2009 20:04:04 GMT

A few months ago, I discussed how to use LINQ to XML to parse your elements.xml file to delete any files that you may have deployed on feature activation. Today I have decided to reuse this concept to delete any lists that I have created when I deactivate a feature? Now you may ask, “why would I want to delete a list when a feature is deactivated? I’ll lose all of my data in that list!” My answer is: Yes, of course you will, but sometimes when building a feature to deploy a list(s), you want to delete the list each time before you deploy a new version of it. During development this is a huge time saver. Right now, I am working on a feature that deploys four document libraries. This means I have to manually delete each one. That is a huge waste of time, but what is nice is that we can reuse the concept above with LINQ to XML and delete lists instead.

The code is quite similar. Have a look. We first, write some code to get the path to the elements.xml file. This code snippet assumes, it is always named Elements.xml. Maybe in the future, I will have it look in the feature.xml file, get all of the ElementManifest definitions and then delete whatever it finds in each file. For now though, we assume, you can change the path as needed.

public override void FeatureDeactivating(SPFeatureReceiverProperties properties)
{
    using (SPWeb currentSite = (SPWeb)properties.Feature.Parent)
    {
        string elementsPath = string.Format(@"{0}\FEATURES\{1}\Elements.xml", SPUtility.GetGenericSetupPath("Template"), properties.Definition.DisplayName);
        DeleteLists(currentSite, elementsPath);
    }
}

From here, it passes a reference to the current SPWeb and the path to the XML document. The path to the elements.xml file is used to populate an XDocument which we can then query with LINQ to XML.

private void DeleteLists(SPWeb currentSite, string elementsPath)
{
    try
    {
        XDocument elementsXml = XDocument.Load(elementsPath);
        XNamespace sharePointNamespace = "http://schemas.microsoft.com/sharepoint/";
 
        // get each URL to each list
        var listInstances = from module in elementsXml.Root.Elements(sharePointNamespace + "ListInstance")
                        select new
                        {
                            ListUrl = (module.Attributes("Url").Any()) ? module.Attribute("Url").Value : null,
                        };
 
        // iterate through each list and delete it
        foreach (var listInstance in listInstances)
        {
            try
            {
                SPList currentList = currentSite.GetList(string.Format("{0}/{1}", currentSite.Url, listInstance.ListUrl));
                currentList.Delete();
            }
            catch (System.IO.FileNotFoundException e)
            {
                // this exception is thrown if the list does not exist
            }
        }
 
        // update the site
        currentSite.Update();
    }
    catch
    {
    }
}

This code is quite similar to that used in my other post, however instead of looking for Module elements we are looking for ListInstance elements. It creates a list of those Urls to each list and iterates through them. For each list it finds, it deletes the list. I’ve written code in the past to manually delete lists, I create by name. That works fine, but the name of each list has to be maintained. Now, I just attach this to any feature I am working on and it deletes any list (or lists) that I throw at it. More than likely this isn’t something you want once you go to production, but it will save you a ton of time during development.

Code samples from my short talk about LINQ to XML

CoreyRoth — Wed, 29 Apr 2009 15:02:00 GMT

Today, I had the opportunity to give a short introduction on how to do some basic queries on XML documents with LINQ to XML. In the talk, I talked about how to create anonymous types from your XML document so that you can work with strong types. I also showed some uses some of the classes, methods, and properties, they would use most when working with LINQ to XML.

I also discussed how LINQ to XML can be used to make working with repeating tables in InfoPath easier. Consider the following XML of an InfoPath form. We have a repeating table called RepeatingTable and in the table there is element called Item.

<?xml version="1.0" encoding="utf-8"?>
<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-12-30T15:48:37"
             xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">
  <my:RepeatingTable>
    <my:Item>
      <my:Name>Polo Shirt</my:Name>
      <my:Color>26134</my:Color>
      <my:Price>19.99</my:Price>
    </my:Item>
    <my:Item>
      <my:Name>Jeans</my:Name>
      <my:Color>01823</my:Color>
      <my:Price>29.99</my:Price>
    </my:Item>
  </my:RepeatingTable>
  <my:Id>83123</my:Id>
</my:myFields>

If you needed to work with this repeating table when the InfoPath form was loaded again, you could use code like the following. You could also apply a similar technique to work with this data inside a workflow.

// use the MainDataSource to read the XML of the InfoPath Form
XDocument infoPathDocument = XDocument.Load(new System.IO.StringReader(MainDataSource.CreateNavigator().OuterXml));
// required to access my: namespace
XNamespace myNamespace = "http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-12-30T15:48:37";
// use descendants node to find any element with the name Item in the tree
var items = from item in infoPathDocument.Descendants(myNamespace + "Item")
            select new
            {
                Name = item.Element(myNamespace + "Name").Value,
                Color = item.Element(myNamespace + "Color").Value,
                Price = item.Element(myNamespace + "Price").Value
            };
foreach (var item in items)
{
    // do something
}

In the code above, I use the CreateNavigator method of the MainDataSource as usual when working with an InfoPath form and I use a StringReader to load it into an XDocument. Since InfoPath form’s always use the my namespace it is necessary to declare it using the XNamespace object and use it before referring to any element or attribute in the document. In the example above, I create a new anonymous type with the Name, Color, and Price and then I can use a foreach loop to iterate through the items.

I have attached the code for the example above as well as the other examples I mentioned in the talk to this post. Please, let me know if you have any questions.

Follow me on twitter.

String[] in columns returned from KeywordQuery.Execute

CoreyRoth — Thu, 11 Dec 2008 16:17:51 GMT

I have posted in the past about how to use the KeywordQuery class to execute an Enterprise Search query. This makes it easy to get a DataTable containing search results. However, one caveat with this is that some columns get returned as a string array (string[]) instead of a string. This can make data binding a pain. If you wanted to bind your DataTable to a GridView, it simply will not display these columns. I used to think it had something to do with it only happening on custom managed properties that you create, but that is not actually the cast. It turns out this is actually related to the post yesterday, because it seems to happen for the same reason. If you have a managed property that has more than one crawled property mapped to it, you get a string[] instead of a string. I am sure there is a good reason for this, but I don't know what it is.

So how do we deal with this? Option 1 of course is to only map one crawled property to your managed properties. This is probably not going to be an ideal solution. Another option we have is to use LINQ to DataSets to coerce that string[] into something usable. First, we execute the query and load into a DataTable as usual.

// execute the query and put it in a dataTable
ResultTableCollection queryResults = keywordQuery.Execute();
ResultTable queryResultsTable = queryResults[ResultType.RelevantResults];
DataTable queryDataTable = new DataTable();
queryDataTable.Load(queryResultsTable, LoadOption.OverwriteChanges);

In this case, I want to bind the builtin Title managed property as well as two of my own managed properties. I use LINQ to DataSets to put these into a new anonymous type. The Field property is generic so you can specify the type that you want. In this case string[]. Notice the Title property doesn't require the use of the string array.

var formattedResults = from row in queryDataTable.AsEnumerable()
                       select new
                       {
                           Title = row.Field<string>("Title"),
                           MyManagedProperty1 = row.Field<string[]>("MyManagedProperty1")[0],
                           MyManagedProperty2 = row.Field<string[]>("MyManagedProperty2")[0]
                       };

We then can bind this to a GridView as usual (if that's what you need it for).

SearchGridView.DataSource = formattedResults;
SearchGridView.DataBind();

I am sure there is a good reason that these fields get returned as a string[]. If you don't like these options, you can always call the web service and get the results as XML.

My 500th Post! Left Outer Joins with LINQ

CoreyRoth — Wed, 15 Oct 2008 19:43:46 GMT

Today, this blog reaches an important milestone. This is my 500th post. It's just a number, but I thought I would use it as an opportunity to talk about the history of the blog and where it is heading. On December 1st, 2004, I wrote my first .NET Tip of the Day about a great new method on primitives called TryParse in the upcoming release of ASP.NET 2.0. This blog started out as a simple announcements list built on a SharePoint intranet site so that I could educate a team of developers on emerging technology (at the time it was .NET 2.0 and SQL Server 2005). When I left that company, I wanted to keep the concept going so I exported all of the content and created a home-grown blogging engine at dotnettipoftheday.com. Since then, I have been committed to giving back to the community by providing useful information that will help developers do their every day jobs. My blog has always focused on how to do something not to give my opinion on something that I may or may not know anything about. Admittedly, I haven't been able to keep up with the daily thing of posting (although I did a pretty good job back then), but I still make an effort to post a few things a week.

Sometime in 2007, the term DotNetMafia was coined by the godfather himself Kyle Kelin. Although the original idea for the mafia has evolved, it turned out to be a great way to get blogs from several of us onto one web site, dotnetmafia.com, which ran on DotNetNuke. The site worked ok for us for a while, but it was lacking. Later that year, I imported everything (well almost everything) into a new Community Server site where we are today. In case you haven't seen the rest of the DotNetMafia bloggers, be sure and check out Tony Kilhoffer, James Ashley, Cory Robinson, and Kevin Williams. They post on a variety of topics (including some outside of .NET).

In nearly 4 years, I have brought you 500 posts. 112 of those posts have been on SharePoint. Obviously you can see that I have shifted focus some. The main reason is I blog about the things I am working with on a daily basis. Right now, that has been SharePoint. Although I still find time to blog about LINQ and other things from time to time. Let's face it though, the SharePoint community needs all the help it can get. Sites like SharePointBlogs.com (which this feed is syndicated too), have made a huge difference in helping people find the right information. This is why I am committed to posting every nuance I find in the product, so that the next SharePoint developer coming along doesn't have to spend time trying to figure it out.

I am pleased with how the site has grown to date. In four years, we have gone from having a couple of internal users a month, to 200 - 300 unique visitors a day. 65% of that traffic each day comes from search. The rest are from RSS subscriptions, referrals, and my CodePlex project. For now, I plan to keep the content coming and encourage others to blog too. I am committed to helping the community even more and hope to continue to grow the site and organize meetups. I want to thank all of my friends, all two of my readers, Copy Source as HTML, and everyone that has left a comment. I really appreciate it.

Enough fluff for now though, let's get to some content. I am going to step away from SharePoint for today's post and try to hit a broader audience talking about LINQ.

Left Outer Joins with LINQ

The topic I am talking about today is doing left outer joins with LINQ. Doing a join in LINQ is pretty simple, however doing an outer join is a bit more tricky because the way you do it doesn't follow the way you would think of it using T-SQL. The documentation on LINQ covers how to do this, but it doesn't do a great job explaining what you are doing and why. For this example let's say we have two classes Product and Inventory. The Product class has the following properties Name, ProductId, and Price. The Inventory class has the properties ProductId and Count. We want to get a list of all products and know how many we have in inventory but unfortunately the Inventory table isn't complete and it doesn't have data on all products. We still want the product to return in a query though.

First, for the purpose of example, I populate two lists. This could just as easily come from an XML document or SQL. In this case we have three products and two items containing inventory, but info is missing for one particular product.

List<Product> productList = new List<Product>();
productList.Add(new Product() { Name = "Product 1", Price = 10.99f, ProductId = 2 });
productList.Add(new Product() { Name = "Product 2", Price = 1.99f, ProductId = 5 });
productList.Add(new Product() { Name = "Product 3", Price = 3.99f, ProductId = 9 });
 
List<Inventory> inventoryList = new List<Inventory>();
inventoryList.Add(new Inventory() { ProductId = 2, Count = 54 });
inventoryList.Add(new Inventory() { ProductId = 9, Count = 31 });

If this was a T-SQL query it would probably look something like this:

SELECT p.Name, i.Count FROM Products p LEFT OUTER JOIN Inventory i ON p.ProductId = i.ProductId

In LINQ it is going to look a bit different though.

var productInventory = from product in productList
                       join inventory in inventoryList
                       on product.ProductId equals inventory.ProductId into productInventoryGroup
                       from item in productInventoryGroup.DefaultIfEmpty(new Inventory() { ProductId = product.ProductId, Count = 0 })
                       select new
                       {
                           Name = product.Name,
                           ProductId = product.ProductId,
                           Count = item.Count
                       };

The first thing we do is get a reference to an instance of the class (product) out of the productList with the from clause. We then can join that to the inventoryList. Remember in LINQ, you always have to have a reference to the class itself and not the collection when performing join and where operations, so we also get a reference to an instance of the class (inventory). Once we have that we can use the on clause to relate the two lists using the ProductId field. When doing a join you must use the keyword equals to relate the two collections.

Here is where things really differ from what you may be used to. The first thing that is different is that the result of the join is stored in a new variable using the into clause (in this case productInventoryGroup). This is because in LINQ you use the DefaultIfEmpty() method to specify default values to return when there are no matching values on the right side of the join. You can specify no parameter on this method to use the default when it is empty (usually null or 0 for an int), or you can pass a value for it to use. In this case I created a new instance of Inventory and set Count to 0. You then use another from clause to reference an item from that join. Once you get to this point, it is just a matter of creating a new anonymous type, with product.Name and item.Count as values. Remember, item in this case is of type Inventory and will contain the value from that list or the default if there is no match.

You can then iterate through the result of the join like usual.

foreach (var item in productInventory)
{
    Console.WriteLine(string.Format("{0}: {1}<br />", item.Name, item.Count));
}

Which would return the following results.

Product 1: 54
Product 2: 0
Product 3: 31

It really isn't that bad, but it's just different than what you might be used to coming from the T-SQL world. Hopefully, this explanation helps and will be of use to you.

Of course, don't forget that tomorrow night is the SharePint meetup at CrawPappy's at 6:00pm. Thanks again.

Select Where In with LINQ

CoreyRoth — Tue, 01 Jul 2008 22:14:40 GMT

I can't talk about SharePoint all the time, so I thought I would talk about how to perform a type of query with LINQ. In T-SQL you might have wrote something like this at one point.

SELECT Title, Id FROM Table1 WHERE Id IN (SELECT Id FROM Table2)

Basically, I am looking for all rows in Table1 where there is a matching Id in Table2. Effectively I want a contains or exists type comparison between tables or lists. I recently ran into a scenario where I wanted to do this and the syntax wasn't immediately obvious to me so I thought I would post something on it. Let's start by defining a simple class.

public class MyClass
{
    public string Title
    {
        get;
        set;
    }
 
    public int Id
    {
        get;
        set;
    }
}

We'll then start by adding some test data to a list of this class.

List<MyClass> myClassList = new List<MyClass>();
myClassList.Add(new MyClass() { Title = "Title 1", Id = 1 });
myClassList.Add(new MyClass() { Title = "Title 2", Id = 2 });
myClassList.Add(new MyClass() { Title = "Title 14", Id = 14 });

In this case I want to compare it to a list of integers to find which items of MyClass match.

List<int> subQueryList = new List<int> { 1, 14, 97, 3, 11 };

Now, to perform the LINQ query. The key to this kind of query makes use of the contains extension method on the list.

var filteredList = from myClass in myClassList
                    where subQueryList.Contains(myClass.Id)
                    select myClass;

Enumerating this query would return MyClass objects with an Id of 1 and 14. This works well given a simple list of integers but what if we have two different lists of MyClass? Here is our second list.

List<MyClass> myClassList2 = new List<MyClass>();
myClassList2.Add(new MyClass() { Title = "Title 5", Id = 5 });
myClassList2.Add(new MyClass() { Title = "Title 2", Id = 2 });
myClassList2.Add(new MyClass() { Title = "Title 14", Id = 14 });

One way that comes to mind to handle this is to write a LINQ query to get a list of the Ids for the second list and then query in a similar manner.

// get a list of ids from the other list of classes
var idList = from myClass in myClassList2
            select myClass.Id;
 
// subquery using the idList
var filteredList2 = from myClass in myClassList
                   where idList.Contains(myClass.Id)
                   select myClass;

Enumerating filteredList2 would return MyClass objects with an Id of 2 and 14. Instead of using a subquery to get a list of Ids, what about something like this?

var filteredList3 = from myClass in myClassList
                    where myClassList2.Contains(myClass.Id)
                    select myClass;

This will compile just fine, but as expected it returns no results. Although the myClass in each list with Ids of 2 and 14 have identical values, they are different objects. If you wanted to exert some additional effort, you could get this to work, but I am not going to cover that today.

Checking for empty EnumerableRowCollections when using LINQ

CoreyRoth — Thu, 24 Apr 2008 15:18:03 GMT

After you query something with LINQ, you may want to know if any results were returned before your start enumerating or working with the EnumerableRowCollection (that's most likely the type behind that var you are using in most cases). Consider the following example.

DataTable myDataTable = new DataTable();
 
var queryResults = from queryResult in myDataTable.AsEnumerable()
                   where queryResult.Field<DateTime>("ItemDateTime") < DateTime.Now
                   select new
                   {
                       intColumn1 = queryResult.Field<int>("IntColumn1"),
                       stringColumn1 = queryResult.Field<string>("StringColun1")
                   };

At first you might try something like this.

if (queryResults != null)

However this will just tell you if the collection is null (and it seems that the collection always has some value regardless of the results of the query). There are two ways that I can think of off the top of my head to check for this. First, you can use the Count() method.

if (queryResults.Count() > 0)

If you don't like that, you can also use the more eloquent Any() method. I've mentioned this method in the past as a way to check for nulls. Any() can be used to check to see if just about anything exists including elements, attributes, and rows. Here is what it looks like.

if (queryResults.Any())
{
    var result = queryResults.First();
 
    Console.WriteLine("Results: {0}", result.intColumn1);
}

The reason I bring this up is that, you have to check if something exists in the collection before calling the First() method. Although, you really don't have to check to see if rows exists before enumerating this collection, you do have to use it before calling First() because it will throw an exception if no rows exist. Btw, First() returns the first item in the EnumerableRowCollection if that wasn't obvious already.

How to: Use the Group Clause in LINQ

CoreyRoth — Wed, 26 Mar 2008 14:29:37 GMT

As you know, LINQ is similar in a lot of ways to T-SQL, but as you go to start doing things with grouping or joining, you will find that that there are some syntactical differences. There are two ways you can use the group clause in LINQ, which way you will use it will be based upon your particular needs. I'll start by explaining the way I typically use it. In this scenario, I want to group something by a particular column and then also get a count of how many rows have each value. Basically, we are creating the equivalent of the following T-SQL statement.

SELECT ProductName, COUNT(*) AS ProductCount FROM MyTable GROUP BY ProductName

When using the group clause it has to be the last statement in the query unless you use the into keyword. Basically, it replaces the select statement. I'll talk about that in a minute. To support a scenario more similar to the T-SQL syntax, you make use of the into keyword. Here is what the LINQ query would look like. In this case we are using LINQ to DataSet as the source.

var productGroups = from row in myDateTable.AsEnumerable()
                    group row by row.Field<string>("ProductName") into rowGroup
                    select new
                    {
                        Name = rowGroup.Key,
                        Count = rowGroup.Count()
                    };

The syntax of the group clause specifies what to group (row), how to group it (row.Field<string>("ProductName")) and where to put that grouping (rowGroup) so that you can use it. The difference between this and T-SQL is the into clause which is required in order for you to do anything with the grouping in an anonymous type. The variable rowGroup is actually of type of IGrouping<string, DataRow> with string being the type we grouped on. Once the data is grouped into rowGroup, this variable can be used to store the name and count in a new anonymous type. The Key property contains the value of what we are grouping on and the Count() method gives us our count. So far when I have grouped in LINQ, I have found this to be the most common scenarios. Typically I want an IEnumerable<> of some sort of anonymous type that I can iterate over or bind to.

If you don't want an anonymous type at this point, you can use the other use of the group clause without the into keyword. In that case the result of the LINQ query would return the IGrouping<string, dataRow>. You can then either query it again with LINQ or use a nested loop to work with the data.

var productGroups = from row in myDateTable.AsEnumerable()
                    group row by row.Field<string>("ProductName");
 
foreach (IGrouping<string, DataRow> productGroup in productGroups)
{
    foreach (var row in productGroup)
    {
        // do something with dataRow here
    }
}

I don't find this scenario nearly as usable but some may find it better. Hopefully this will help the next time you got to group something with LINQ. It's not really that complicated but I thought it would be worth it to point some of the differences. I'll probably cover left outer joins pretty soon, because I have done it a few times now and the syntax still gets me tripped up every time.

Using LINQ to make Enterprise Search Results more usable

CoreyRoth — Tue, 25 Mar 2008 14:58:37 GMT

In my previous post about using the KeywordQuery class, I had one small omission. The fact is that when you add your own managed properties using the SelectProperties collection of the keyword class, the data type you get back is a string[] containing 1 element instead of a string. This can be quite annoying when you are attempting to do data binding as well as additional filtering. I have found this to really only be the case when dealing with custom managed properties. Default properties such as title and content source always return a string. To combat this use LINQ to read the data into a new anonymous type. You can then bind the data and filter as needed.

var results = from queryResult in queryDataTable.AsEnumerable()
              select new
              {
                  Title = queryResult.Field<string>("ContentSource"),
                  Size = (queryResult.Field<string[]>("Size").Any()) ? queryResult.Field<string[]>("Size")[0] : null                        
              };

In the above example, I specify ContentSource as a string just like normal. However, for my custom manager property Size, I have to cast to a string[] and then I simply return the first element. Technically, I should probably check to make sure that the element exists as well (but I am checking for null). Once everything is copied into the anonymous type, you can bind, group, or filter it as needed.

How to: Use LINQ to SQL without using the Object Relational Designer

CoreyRoth — Tue, 18 Mar 2008 00:54:20 GMT

LINQ to SQL has already proved to be extremely easy to use to create object relational mappings when you have an existing database schema using the Object Relational Designer. This designer is good, but you may not want something that is autogenerating your domain classes. You may want to generate your domain classes yourself. This is actually quite easy and works in a similar manner to other OR/Ms such as ActiveRecord. The thing I like about it is that your domain objects do not have to inherit from some base class that has all of the underlying logic to access the database. Instead you create a custom class separate from your domain objects that inherits from DataContext.

We'll create a simple example of a products table for an e-Commerce web site. Let's start by looking at the domain object. Before you create your domain object start by adding a reference to System.Data.Linq to your class library if it is not already present. You will then need to add a using statement in each domain class for System.Data.Linq.Mapping.

[Table(Name="Products")]
public class Product
{
    [Column]
    public string Name;
 
    [Column(IsPrimaryKey=true, Name="Id")]
    public int ProductId;
 
    [Column(Name="Price")]
    public double Price;
}

The first thing you do in your domain class is decorate it with a Table attribute. An optional parameter here specifies the name of the underlying database table. In this case my domain object it Product but my database table is named Products. I then defined three properties representing columns in the table. The Column attribute specifies that the property will have a corresponding column in a database table. The IsPrimaryKey parameter specifies that the column is a primary key in the database. The Name parameter here also allows you to specify a different column name in the database.

That is really all that is required to create a domain object. You can define a class for each domain object you want and you can also create relations between them (but I won't be covering that here today). Once you have your domain object created, you will need to create a DataContext class to actually be able to query your domain objects. This is also pretty simple. You just expose a property with the generic type of Table<> for each one of your domain objects. The name of the property is what you will use with the DataContext when you are querying with LINQ.

public class StoreDataContext : DataContext
{
    public Table<Product> Products;
 
    public StoreDataContext(string connection)
        : base(connection)
    {
    }
}

Now that you have your domain objects written you will need to create the SQL tables that they represent. You can do this manually, or you can have LINQ create the whole database for you. Just create an instance of your DataContext and call the CreateDatabase method. This method infers the name of the database given the connection string you used. If you did not specify the database, you need to add a Database attribute with the name to your class.

StoreDataContext myDataContext = new StoreDataContext(myConnectionString);
myDataContext.CreateDatabase();

Alright, so now your domain objects and database are created, now you just need to query something with it.

var products = from product in myDataContext.Products
               where product.Price > 49.99f
               select product;

This simple query simply returns any product with a price greater than 49.99. So, LINQ to SQL doesn't have to be completely domain driven. This gives you a lot of flexibility and makes it easy to add additional things to your domain logic if you want to. The downside to this of course it that, when your database schema changes, your domain object is not going to get updated at the click of a button. If you are building your domain objects in this manner though, this is probably not a concern to you though.

LINQ to SQL Logging

CoreyRoth — Thu, 21 Feb 2008 15:24:41 GMT

The other day I was reading Charlie Calvert's excellent post on LINQ and Deferred Execution and stumbled upon a logging feature inside LINQ to SQL. By setting the Log property to a TextWriter (in this case Console.Out), LINQ to SQL will log the query and what parameters it sent to the database server. Assign it before you execute your query like this.

MyDataContext.Log = Console.Out;

Obviously you don't have to log to the console, you can log to any text writer. This provides a great way to log what queries are going to the database and helps eliminate the mystery of what queries have been executing in your application. Obviously, you can view these queries in the debugger, but once your application is in production, this is a good solution.

Making DataSets tolerable using LINQ to DataSet

CoreyRoth — Mon, 18 Feb 2008 16:01:00 GMT

Unfortunately, most of us aren't working in a perfect world, so it is bound to happen that you run into a dataset or two. Whatever the reason (the developer was lazy, you're maintaining legacy code, someone didn't know any better, or you're just working with the SharePoint API), it would be nice if there was a way to make dealing with datasets easier. LINQ to DataSet does this by allowing you to perform queries or move the data easily into a domain object.

To work with LINQ to DataSet, an extension method called AsEnumerable() is tacked onto the DataTable class making it queryable by LINQ This extension method is provided by adding System.Data.DataSetExtensions.dll to your project. Then, it is just a matter of knowing the syntax to get individual columns of data. Using a generic xtension method called Field, we can get the value of a column with the appropriate type.

In this example, we are going to filter the datatable on the ItemDateTime field and return a new anonymous type. We are assuming the datatable contains columns IntColumn1, StringColumn1, and ItemDateTime.

var queryResults = from queryResult in myDataTable.AsEnumerable()
                   where queryResult.Field<DateTime>("ItemDateTime") < DateTime.Now
                   select new
                   {
                       intColumn1 = queryResult.Field<int>("IntColumn1"),
                       stringColumn1 = queryResult.Field<string>("StringColun1")
                   };

This really makes it easy to perform subqueries on things presented to you via datasets. What if you want to get away from that nasty dataset and work with a domain object? LINQ makes that pretty easy as well. Just assign the values into a domain object. Here is a simple domain object (note: that it uses automatic properties).

public class MyDomainObject
{
    public DateTime ItemDateTime
    {
        get;
        set;
    }
 
    public int IntColumn1
    {
        get;
        set;
    }
 
    public string StringColumn1
    {
        get;
        set;
    }
}

Here is how you would assign it to the domain object.

var queryResults2 = from queryResult in myDataTable.AsEnumerable()
                    select new MyDomainObject
                    {
                        ItemDateTime = queryResult.Field<DateTime>("ItemDateTime"),
                        IntColumn1 = queryResult.Field<int>("IntColumn1"),
                        StringColumn1 = queryResult.Field<string>("StringColun1")
                    };

Who knows how efficient this is, but it is quite simple. If you are curious what type queryResults2 is in this, it is an EnumerableRowCollection<MyDomainObject>. If you want it as a list, you can use the ToList() method, there is also ToArray() and ToDictionary() if that's what you want. That's all on LINQ and datasets for today. Hopefully, this will make your next experience with them better.

How I used LINQ to XML to Create Document Library Folders

C-Dog's .NET Tip of the Day — Thu, 17 Jan 2008 23:29:24 GMT

I have been doing a lot of document library work lately and in my work I had a need to create a series of subfolders in the document library using a particular custom content type. This meant I was going to need to write a little code. I decided to keep...(read more)