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 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 havent memorized yet I will just have to refer to this sheet.
So lets go in to it. Lets 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 th 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 its 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 represents 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 lets 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 IEnnumerable you can directly use is a 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 youve 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 geth 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 defintely learn more stuff as you go and LINQ to SQL is really easy to master.