Corey Roth [MVP] : C# 3.0

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.

Updating Data with LINQ to SQL and GridViews when using a View

CoreyRoth — Tue, 07 Oct 2008 18:27:59 GMT

Look! It's a post that isn't about SharePoint! Today, I want to talk about some of the issues I have run into when trying to update data using a GridView when bound to a LinqDataSource. This data source has an EnableUpdate property, but unfortunately there is more to it than just that. It works pretty well, but there are a few things that you have to do otherwise you will get the following error.

The Entity is not of the correct type.

In today's example, I required a stored procedure to do the update because I was updating multiple tables behind the scenes. Although LINQ can do this, the complexity of the update was simpler if I did it via stored procedure. The data I am displaying in my GridView is based off a database view. The first thing to do is drag the View into your LINQ to SQL class. You need to make sure your view includes a primary key. You then need to select the column and mark it as a Primary Key in the LINQ to SQL designer. After you have your view defined, drag the stored procedure for your update into the designer. Then on the properties of your view's data class, you can set a default method for Insert, Update, and Delete. In this case I am only worrying about Update. If you click on Update, it will bring up a Configure Behavior window which will allow give you two choices. Use Runtime will dynamically generate the CRUD logic at runtime. In our case though, we will choose Customize and then pick our stored procedure out of the list.

After you have selected an update method, your LINQ to SQL class is good to go. You are now ready to build your page. Start by dragging a GridView and LinqDataSource onto your page. On your LinqDataSource, set the EnableUpdate property to true. Use the wizard to bind your LINQ to SQL database. Pick the Entity named after your view but when configuring columns be sure and choose all columns (*). If you select individual columns, it populates the Select property on the LinqDataSource and you will not be able to make updates. At this point, you can bind your GridView to your LinqDataSource. If you have followed all of the steps so far, the Enable Editing option should be available for you to check. Check it and then the last thing you have to do is set the DataKeyNames property on the GridView. Set it to the name of your primary key and you should be ready to make updates using a GridView.

I always find that I spend more time than I care to setting up GridViews. Hopefully this will help if you ever need to use LINQ to SQL with a GridView. Also don't forget Tulsa TechFest this week where I will be talking about Code Access Security in SharePoint.

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.

Using an Extension Method to remove a Web Part from the Gallery

CoreyRoth — Tue, 15 Apr 2008 15:00:38 GMT

When you deactivate a feature that deployed a web part, it is a good idea to remove that web part from the gallery. This makes things much more likely to work when you upgrade said web part later. Like everything in SharePoint, this task is more complicated than it needs to be. You would think you could just call myWeb.WebParts.Delete("mywebpart.dwp"), but of course that is not the case. That would be too easy.

To work with the web parts in the gallery, you first have to know where they are. The Web Part Gallery is just a list, so it can be manipulated just like any other list. To get the list, we use the GetCatalog method on the SPWeb object. It takes an enum parameter of SPListTemplateType.WebPartGallery.

SPList webPartGallery = currentSite.GetCatalog(SPListTemplateType.WebPartCatalog);

Now we need to delete the web part from the gallery. Unfortunately, there is neither an indexer that uses the web part's name, nor a find method. This means we get to loop through the collection until we find the item we are looking for. This provides for another great place to use an Extension Method. I have found these to be highly useful to make up for the shortcomings in the SharePoint API.

public static class ExtensionMethods
{
    public static void Remove(this SPList myList, string key)
    {
        for (int i = 0; i < myList.Items.Count; i++)
        {
            if (string.Compare(myList.Items[i].Name, key, true) == 0)
            {
                myList.Items[i].Delete();
                return;
            }
        }
    }
}

The code is simple. Just loop through the collection and compare the name property to a key parameter on the method. The key parameter will contain the name of the web part's filename (i.e.: mywebpart.dwp). To use the extension method, it would be called as in the sample below.

using (SPWeb currentSite = SPContext.Current.Web)
{
    SPList webPartGallery = currentSite.GetCatalog(SPListTemplateType.WebPartCatalog);
    webPartGallery.Remove("mywebpart.dwp");
}

This seems like a lot of code to be able to do something that should only take one line of code, but that is what is required. My collection of SharePoint Extension Methods is getting huge.

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.

Using an Extension Method to check if an item exists in a SharePoint collection

CoreyRoth — Mon, 03 Mar 2008 16:13:00 GMT

As I have mentioned before, one of my biggest complaints about SharePoint is that none of the collections in the SharePoint API have any way to determine if an item exists. Extension Methods offer a slightly more elegant way to do this, although the underlying code still violated multiple best practice rules. Take a look at this example using the SPFileCollection.

public static bool Contains(this SPFileCollection fileCollection, string index)
{
    try
    {
        SPFile testFile = fileCollection[index];
        return true;
    }
    catch (SPException e)
    {
        return false;
    }
}

If you're not familiar with Extension Methods yet, they are an addition in C# 3.0 that allow you to add methods to existing classes without having to inherit from them. You prefix the first parameter with the keyword this followed by a type to specify what type you are extending. You can put your extension method in any class you want. Inside, the method, you see the typical way of checking to see if something exists in a SharePoint collection: try/catch. The syntax for using the extension method is below.

bool fileExists = fileCollection.Contains("SomeFile");

Extension methods are quite powerful and I think they can provide an excellent way to make many tasks easier and cleaner inside the SharePoint API.

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.

Using Object Initalizers yet?

CoreyRoth — Thu, 14 Feb 2008 15:03:00 GMT

Object Initalizers aren't talked about very much as one of the new features in C# 3.0, but I find them pretty useful from time to time (esepcially when dealing with the SharePoint API). They are great, when there isn't a constructor that has all of the arguments that you need to initialize the class with in it. Here is an example of how I used it lately with the KeywordQuery class.

KeywordQuery myQuery = new KeywordQuery(siteCollection)
{
    QueryText = string.Format("Color:\"{0}\"", myColor),
    ResultTypes = ResultType.RelevantResults
};

In this case I wanted to initialize the QueryText and ResultType properties. This makes the syntax much cleaner. Obviously this feature isn't life changing, but I find it a nice convenience.