LINQ - A brief overview

LINQ to SQL

I don't have a lot of experience with LINQ yet, but what I have read and played with is very impressive. Especially, when used with Visual Studio 2008. It is sooo easy to create a data access layer. If you want to create your own entities, then you can still do that just like you always have. You can even use stored procedures if you still want to use them. Though I think LINQ makes stored procedures not necessary since I usually use stored procs to prevent (dare I say) SQL injection, and I think LINQ handles that pretty well.

The question I always had was, why use LINQ. I think it is actually a very good thing now that I have read more about it. LINQ provides a uniform way to do Create, Read, Update, and Delete (CRUD) operations on data. Data could be Flickr, classes, any database by any vendor, or really anything if you extend or someone else has extended LINQ.

One of my initial thoughts was why do I need to learn LINQ I already know SQL and ADO.NET. That works really well. The simple answer is a consistent interface to other data as well. Really LINQ is just a conduit to allow you to do CRUD operations on nearly anything you can imagine. The advantage is that if the source of that data changes you code doesn't have to change much at all. It also means that if you are querying XML or the database, or whatever you can do it using the same syntax and same tool. Think of it just as you do a foreach statement. It is just another tool in your toolbelt. Instead of constantly having to implement different ways to do CRUD operations for each data source, now you really just need to learn or at least use one (LINQ).

Another argument I had initially was I like to use Custom entities that correspond to tables in the database. I used to use CodeSmith or MyGeneration to generate these entities. As it turns out that is what LINQ to SQL does. In fact, it produces very nice object model that I can use. The best part is that it is integrated into Visual Studio 2008, and I can visually see the model while in Visual Studio 2008, and can be edited there as well.

In the end, I think it is an excellent tool and worth learning. It is not difficult to learn, but it is a bit strange from a syntax point of view. Below are some of the basics to get you started. In this example, let's assume the name of the table is MyData.

Here is the SQL to create the table if you want to try these examples

CREATE TABLE [dbo].[MyData](
 [MyDataID] [int] IDENTITY(1,1) NOT NULL,
 [Test1] [nvarchar](50) NULL,
 [Test2] [int] NULL,
 [Test3] [datetime] NULL,
 CONSTRAINT [PK_MyData] PRIMARY KEY CLUSTERED
([MyDataID] ASC)
)

Assuming you created the table in some database, you need to add that database to the Server Explorer in Visual Studio 2008 (VS2008). 

Next we need to create a  DataContext. This is where LINQ objects live and communicate with the database. To create one of these, add a new item to your project called "LINQ to SQL Classes". I called my file MyDataClasses.dbml. Open this file in VS2008. You will see something similar to what you would see if you opened a DataSet. Find the MyData table in the Server Explorer that you added earlier. Simply drag the Table to the diagram. That is it! It is that simple! You now have your data access layer. You can add other tables and relationships just like DataSets, but we are just doing the one here.

NOTE: You may have to select the MyDataID field and change the Auto-Sync feature to OnInsert instead of Always. It should also have Primary Key set to true.

Continuing with example, assume you have a form of some kind that has four fields on it. One for each column in the database. Also, there are four buttons: Insert, Find, Update, and Delete.

Insert
To insert a new record, row, object, whatever you want to call it, you basically just create a new object, set the properties, insert it into the context, and tell the context to submit the changes. Here is the code to do that.

using (MyDataClassesDataContext ctx = new MyDataClassesDataContext())
{
MyData newRecord = new MyData();
newRecord.Test1 = txtString.Text;
newRecord.Test2 = Convert.ToInt32(txtInt.Text);
newRecord.Test3 = new DateTime?(Convert.ToDateTime(txtDateTime.Text));
ctx.MyDatas.InsertOnSubmit(newRecord);
ctx.SubmitChanges();
}

Find
Find is where the strange syntax is. There is a new kind of data type that LINQ makes use of and it is called var. It is more or less like Object, but not quite. In contrast you could use IEnumerable<MyData> instead of var. The choice is yours. All we do below is query the database. The first example is really for multiple rows being returned, and the second example is if you know exactly one will be returned. The second one is probably a better choice in this case since we are querying on the primary key.

using (MyDataClassesDataContext ctx = new MyDataClassesDataContext())
{
// this works
//var record2 = from d in ctx.MyDatas
//              where d.MyDataID == Convert.ToInt32(txtID.Text)
//             select d;

//MyData record = record2.ToList<MyData>()[0];

// This works better using a lymbda expression
MyData record = ctx.MyDatas.Single<MyData>(d => d.MyDataID == Convert.ToInt32(txtID.Text));

txtID.Text = record.MyDataID.ToString();
txtString.Text = record.Test1.ToString();
txtInt.Text = record.Test2.Value.ToString();
txtDateTime.Text = record.Test3.Value.ToShortDateString() + " " + record.Test3.Value.ToShortTimeString();
}

Update
For updating data you need to find the object you want to update, makes changes, and submit changes. This will by definition already be in the DataContext so you don't need to insert it on submit like you do when you insert. It is a mix between inserting a new object and finding an object.

using (MyDataClassesDataContext ctx = new MyDataClassesDataContext())
{
// this works
var record2 = from d in ctx.MyDatas
     where d.MyDataID == Convert.ToInt32(txtID.Text)
     select d;

MyData record = record2.ToList<MyData>()[0];

// This works better using a lymbda expression
//MyData record = ctx.MyDatas.Single<MyData>(d => d.MyDataID == Convert.ToInt32(txtID.Text));

record.Test1 = txtString.Text;
record.Test2 = Convert.ToInt32(txtInt.Text);
record.Test3 = new DateTime?(Convert.ToDateTime(txtDateTime.Text));

ctx.SubmitChanges();
}


A good places to start is:
http://msmvps.com/blogs/abu/archive/2008/06/30/introducing-linq-tutorial.aspx