LINQ has been out for around a year now, but I never have been able to get away from using traditional SQL queries and data-access classes. Today, I thought I would try to do the “Hello World” equivalent of a LINQ program and share that with you today.
So What’s LINQ?
LINQ stands for Language Integrated Query. It’s a technology developed for Microsoft’s .NET platform which make it easier to query databases and XML files. Here’s how Wikipedia describes LINQ:
Microsoft LINQ defines a set of proprietary query operators that can be used to query, project and filter data in arrays, enumerable classes, XML (XLINQ), relational database, and third party data sources. While it allows any data source to be queried, it requires that the data be encapsulated as objects. So, if the data source does not natively store data as objects, the data must be mapped to the object domain. Queries written using the query operators are executed either by the LINQ query processing engine or, via an extension mechanism, handed over to LINQ providers which either implement a separate query processing engine or translate to a different format to be executed on a separate data store (such as on a database server as SQL queries (DLINQ)). The results of a query are returned as a collection of in-memory objects that can be enumerated using a standard iterator function such as C#’s foreach.
Before we can do any LINQ queries, we must first have a data source to access it. To start out, I created a new ASP.NET/C# website in Visual Studio 2008. Then, I created a two table database using the built in version of SQL Express that comes with visual studio. One table called “Users” with an ID field, a username, a password, and then an “Account Type” feature which is a foreign key of the second table “AccountTypes”. The AccountTypes table simply has an ID field that’s the primary key then a “Title”. I went ahead and added some sample data into the database as well.
The LINQ to SQL Class:
After creating a database, the first step is to create a “LINQ to SQL” class. This class will map your class to the tables in the database. It’s actually an optional step, but doing so will allow you better control over the class. By doing this, you can rename tables and properties from within the class. To do this, Right click on the folder of your website in the Solution Explorer, and click “Add New Item”. Then pick out “LINQ to SQL Class”. Open the class after creating it.
The next step is to add your database tables to the “LINQ to SQL” class. Have your “LINQ to SQL” class file open in your main window, then in your database explorer, drag the tables you want to add to the class (in our case, users and accounttypes) over to the “LINQ to SQL” class.
At this point, you should have something that looks like this:
Before closing out of your DBML file / Linq to SQL class, I recommend that you give the class a new name. The default name is “DataClassesDataContext”, Let’s change that in the properties window to “UserContext”. At this point, you could implement a lot more of the sophisticated features that LINQ offers, but for now, we’ll leave it as is.
Creating the Query:
Now that we have our “LINQ to SQL” class made, The next thing we’ll want to do is create our query. We can just use the “Default.aspx” and “Default.aspx.cs” that came with our project for this part. Go to the PageLoad method of our Default.ASPX.CS file and we’ll start our coding there. The first thing we need to do is create an instance of the class we created:
UserContext thisContext = new UserContext();
The next thing we need to do is write our query. This will get all users from the users table that have an account type of “Admin”:
var UserResults = (from u in thisContext.Users
where u.AccountType1.Title == "User"
At this point, we have everything we need to do to make use of the database. The SQL isn’t generated and ran until we actually do something with the results, so let’s do something simple and write each username that is returned to the page:
foreach (var ThisUser in UserResults)
Response.Write(ThisUser.Username + "<br/>");
And that’s really all it takes to get started with LINQ. If all is well in the world and your code is as it should be you should get something that looks a lot like this:
You might also want to read: