Corey Roth [MVP] : LINQ to SQL

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.

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.

ReflectionPermission Required for LINQ to SQL under Partial Trust

CoreyRoth — Mon, 28 Jan 2008 19:45:00 GMT

So by now, I can assume everyone has been listening to me and you are using partial trust in all of your SharePoint environments right? Ok, probably not, but for the two of you in the world that are, this post is for you. If you decide to use LINQ to SQL in any of your development, I have discovered that ReflectionPermission is needed by LINQ to SQL. I don't know the exact permissions it needs yet, but if you are in a bind to get it to work in your partial trust you can use a line like the following. I try to avoid giving any permission unrestricted access, but until you find out what the underlying class actually needs, it is dificult. To get you by, use a line like this.

Also some more obvious permissions LINQ to SQL will need is SqlClientPermission and probably ConfigurationPermission.

Returning a single object with LINQ to SQL

C-Dog's .NET Tip of the Day — Thu, 17 Jan 2008 22:42:25 GMT

I have been messing around with LINQ (both with SQL and XML) and am pretty familiar with the query syntax right now, but I wasn't sure at first how to get a single object. It's actually pretty simple using a lambda expression. Assume I have a...(read more)

LINQ to SQL Under the Covers

C-Dog's .NET Tip of the Day — Wed, 12 Dec 2007 23:12:48 GMT

With the release of the 3.5 extensions to the framework, there has been a lot of talk about LINQ and with it LINQ to SQL. Well I was interested in more how LINQ to SQL actually worked so I decided to take a look at some of the files it generates in the...(read more)

Cowboy Coding a Data Editor with ASP.NET Dynamic Data

C-Dog's .NET Tip of the Day — Tue, 11 Dec 2007 15:33:27 GMT

The ASP.NET 3.5 Extensions Preview came out this week. The biggest feature people are of course talking about is the MVC framework. I won't be talking about that today since ten thousand other people already are. Today I will be talking about a new...(read more)