LINQ to SQL Essentials

I know this topic might be a bit late now as LINQ to SQL has been there for sometime. I was a bit late as I was enjoying the functionalities I got from CodeSmith and netTiers combo which is a really good code generation tool, but with the new job, we are not using CodeSmith and netTiers combo so I have to find some free alternatives to CodeSmith (netTiers is free) and LINQ came into my mind. It might not be a full solution for code generation but it definitely helps a lot in minimizing the codes I type as it automatically generates the DataContext and Entities and making the database objects available through the Visual Studio IDE is just simply amazing, I still have to develop my proper layers but with LINQ things are a bit faster than doing it by old means. I have a play with this during the early days when it was released with .NET 3.5 in early 2008 but not really in a production environment until early this year.

So why am I writing this?

I just want to share basically my cheat sheet in LINQ to SQL which covers most of the basic stuff needed to operate on the database (CRUD). I had created this so that when I need to check some operations that I haven't memorized yet. I will just have to refer to this sheet.

So let's go in to it. Let's start with a SQL database structure like such:

Where you have an Employee Table which stores Employee information, Position Table with defines the Employee Position and Leave Request to store information regarding Leave Requests of an Employee.

Now add your LINQ to SQL Classes by adding it to your project and choosing the LINQ to SQL Classes on the Add New Item Window.

Give it a name (I named mine as Database.dbml), then once it is created, just drag the SQL tables you need to the Object Relational Designer, then build it.

Once it's there, all of the tables, methods you can use on that table and the table definition are already ready for use. The DataContext class in this case, Database.dbml created will have properties that represent each of the Table and Views we modeled earlier in the database. It also consists of the methods for each Stored Procedure if you have any. So the only thing you need to do to use it is to declare your Database Data Context like such:

private static DatabaseDataContext myDB = new DatabaseDataContext();

Now let's go to the LINQ Stuff.

Selecting a Record

On the sample below, we will select an Employee Record where his Employee ID is equal to 1.

var Employee = (from e in myDB.Employees
 where e.EmployeeID == 1
 select e).FirstOrDefault();
Console.WriteLine(Employee.FirstName + " " + Employee.LastName);

Selecting a Multiple Records

On the sample below, we will select an Employee Record where his Employee ID is greater than 1.

var Employee = (from e in myDB.Employees
 where e.EmployeeID > 1
 select e);

foreach (var x in Employee)
{
 Console.WriteLine(x.FirstName + " " + x.LastName);
}

Since this results is IEnumerable, you can directly use it as a Binding Source like such:

DatabaseDataContext myDB = new DatabaseDataContext();
GridView1.DataSource = (from results in myDB.Employees
 where results.Age > 20
 select results);
GridView1.DataBind();

Selecting a Multiple Record using Multiple Where Clause

On the sample below, we will select an Employee Record where his Age is greater than 26 and the Last Name starts with J:

var Employee = (from e in myDB.Employees
 where e.Age > 26 && e.FirstName.StartsWith("J")
 select e);
foreach (var x in Employee)
{
 Console.WriteLine(x.FirstName + " " + x.LastName);
}

As you've noticed, since the FirstName is of string type, you are able to use all string methods:

Inserting a Record

On the sample below, we will create a new Employee, assign properties to the Employee and save it into the database.

Employee newEmployee = new Employee();
newEmployee.FirstName = "Jim";
newEmployee.LastName = "Yu";
newEmployee.Age = 37;
newEmployee.Position = (from p in myDB.Positions
 where p.PositionName == "Developer"
 select p).FirstOrDefault();

myDB.Employees.InsertOnSubmit(newEmployee);
myDB.SubmitChanges();

As you can see, SQL Server objects are exposed on your intellisense:

Inserting a Record and related Sub Record

On the sample below, we will create an Employee Record at the same time, create a sub record for it which is a Leave Request. This is good for shopping cart scenarios where you can save new Customers as well as their orders at the same time.

Employee newEmployee = new Employee();
newEmployee.FirstName = "Kim";
newEmployee.LastName = "Park";
newEmployee.Age = 42;
newEmployee.PositionID = 2;

LeaveRequest newLeaveRequest = new LeaveRequest();
newLeaveRequest.DateStart = DateTime.Now;
newLeaveRequest.DateEnd = DateTime.Now.AddDays(5);

newEmployee.LeaveRequests.Add(newLeaveRequest);
myDB.Employees.InsertOnSubmit(newEmployee);

myDB.SubmitChanges();

Updating a Single Record

On the sample below, we will select an Employee Record and update its details.

Employee myEmployee = (from e in myDB.Employees
 where e.LastName == "Yu"
 select e).FirstOrDefault();
myEmployee.FirstName = "Jimmy";

myDB.SubmitChanges();

Updating multiple records

LINQ to SQL does not give you a way to specify a set-based update so the only way to achieve this is either doing them one by one or do an Execute SQL Command on your Data Context.

myDB.ExecuteCommand("UPDATE Employees Set Age = " + 30);

Delete Record

On the sample below, we will Delete an Employee Record with an Employee ID of 9.

LeaveRequest myLeaveRequest = (from l in myDB.LeaveRequests
 where l.EmployeeID == 9
 select l).Single();

myDB.LeaveRequests.DeleteOnSubmit(myLeaveRequest);

myDB.SubmitChanges();

Joining Tables

On the sample below, we will join Employee and Position Tables and display information coming from both tables, in this case First Name and Last Name from Employees Table and Position Name from Position Table.

var EmployeeFullDetails = (from e in myDB.Employees
 join p in myDB.Positions on e.PositionID equals p.PositionID
 where p.PositionName == "Developer"
 select new
 {
 FullName = e.FirstName + " " + e.LastName,
 p.PositionName,
 });

foreach (var x in EmployeeFullDetails)
{
 Console.WriteLine(x.FullName + " - " + x.PositionName);
}

Grouping

On the sample below, we count all the Leave Requests by Employee ID, so in this case, we group the records by Employee ID and count how many entries they got in the Table.

var LeaveSummary = (from l in myDB.LeaveRequests
 group l by new { l.EmployeeID }
 into g
 where g.Count() > 0
 select new
 {
 GroupedEmployeeID = g.Key.EmployeeID,
 LeaveCount = g.Count()
 });

foreach (var x in LeaveSummary)
{
 Console.WriteLine("Employee ID : " + x.GroupedEmployeeID + " - Leave Count : " + x.LeaveCount);
}

Grouping while Joining

On the sample below, we will be Joining Employees and Positions Table and get the Maximum and Minumum age per position, count how many employees are there in the Position and Sum all the ages in their position.

var PositionEmployeeSummary = from e in myDB.Employees
 join p in myDB.Positions on e.PositionID equals p.PositionID
 group new
 {
 positions = p, employees = e
 }
 by new
 {
 p.PositionName,
 p.PositionID
 }
 into g
 select new
 {
 PositionID = g.Key.PositionID,
 PositionName = g.Key.PositionName,
 PositionCount = (Int64?)g.Count(),
 MaxAgeInPosition = (System.Int32?)g.Max(p => p.employees.Age),
 MinAgeInPosition = (System.Int32?)g.Min(p => p.employees.Age),
 SumOfAllAgeInPosition = (System.Int32?)g.Sum(p => p.employees.Age)
 };

foreach (var x in PositionEmployeeSummary)
{
 Console.WriteLine("PositionID : " + x.PositionID + ", PositionName : " + 
                    x.PositionName + ", PositionCount : " + x.PositionCount + ", 
                    Maximum Age in Position : " + x.MaxAgeInPosition + ", 
                    Minimum Age in Position : " + x.MinAgeInPosition + ", 
                    Age Sum in Position : " + x.SumOfAllAgeInPosition);
}

Basing on the samples above, I guess you can start and convert your old codes that might be used. Most of the needed operations have been discussed and if I missed anything that you need, let me know and I will try to help as much as I can.

Now go ahead and start coding, you will definitely learn more stuff as you go and LINQ to SQL is really easy to master.