My ASP.NET adventure is about 18 months in now and I continue to learn. The more I learn the more I love it. One of the things I've learnt to love just recently is LINQ-To-SQL.

At first I was dismissive of LINQ. Mainly because I didn't really get it and thought it was too advanced, so I'd come back to to it once I'd mastered the traditional approach of using raw SQL.

What I found with the old approach was that apps I was building soon became a mess. The DataSets had their own SQL stored in them and then lots of references to Stored Procedures in the database itself. Before long they became unmanageable.

Now that I've switched to LINQ-To-SQL I've not written any actual SQL code and my database no longer have Stored Procedures. All my data-access logic is written in LINQ (C# code) and stored in repository classes in the app's source code. It feels so, so much better this way.

I wish I'd taken note of LINQ from the off!

What is LINQ?

From memory I think it means Language Integrated Natural Queries. Even if it I got that wrong it does kind of explain (at least in my mind) what it is. Firstly that the queries you write feel more natural to a programmer ("this"=="that" rather than SQL's "this"="that" etc) and that it's integrated in the language you're using (C#).

Basically it's a programmer-friendly way of doing SQL queries. You don't need to write any SQL at all. It does all that for you by converting your code in to SQL.

Here's a basic example of using LINQ (it doesn't have to be used to access SQL!):

string[] names = {"Tom", "Dick", "Harry", "Jake"};

IEnumerable<string> namesWithAIn = names.Where(n=>n.Contains("a"));

//The namesWithAIn "list" has two strings in it -- Harry and Jake

We define an array of strings and then pick out those containing the letter "a". The result is an IEnumerable object. Don't worry about that though. It's just a class that lets you loop its member. Like a fancy List.

Don't worry about the The "n=>n." bit either. It confused me at first but is quite simple really once you get used to it. It's called a "Lambda expression" and, in my mind, I read it as "using n". So, what you're doing is saying "from here on in 'n' refers to the objects in the list I'm querying. In our case 'n' would be each of the strings in the array. In reality it would refer to rows of a table or the classes C# is using to represent those rows.

LINQ-to-SQL

Let's say you have a SQL table in a database like this and it's called "Kids":

In Visual Studio you can add LINQ-to-SQL classes to create objects to represent tables. You would then be able to do this:

IEnumerable<Kid> girls = dataContext.Kids.Where(
  k=>k.Sex.Equals("Female")
 ).OrderBy(k=>k.Age);

foreach(Kid kid in girls){
 Console.WriteLine("Found a girl called "+kid.Name);
}

Pretty cool, no?

No need for any SQL and all the objects are strongly-typed. Like it promises it just feels natural.

Note the class called "Kid" we're referring to. I didn't create this. Visual Studio did. And for each column in the table it added the appropriate property to the class. Such as an integer called Age and a string called Name etc.

You can make the Lambda expressions in the Where() "clause" as complex as you like:

IEnumerable<Kid> girlsTwoOrOlder = dataContext.Kids.Where(
  k=>k.Sex.Equals("Female") && k.Age>=2
 ).OrderBy(k=>k.Age);

foreach(Kid kid in TwoOrOlder){
 Console.WriteLine("Found a girl called "+kid.name);
}

Notice we're using "&&" rather than "AND", which is what SQL expects us to use. Again, more natural.

LINQ gives us lots of cool methods to use. Two examples are Skip() and Take(), which come in handy when paging through large tables.

If you wanted entries for page 3 of a view that showed 5 rows per page, you would write:

IEnumerable<Kid> paginated = dataContext.Kids.OrderBy(k=>k.Age)
        .Skip(10)
        .Take(5);

Obviously this is merely scratching the surface of what LINQ-to-SQL can do.

Hopefully this is enough to whet your appetite? What I'm trying to get across is that there's no need to be scared of it (like I was). Embrace it from the off! I only regret that I didn't.