Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

LINQ to SQL Essentials

, 27 Sep 2010
Rate this:
Please Sign up or sign in to vote.
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 [...]

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

<span></span>DatabaseDataContext myDB = new DatabaseDataContext();
GridView1.DataSource = (from results in myDB.Employees
<span></span> where results.Age > 20
<span></span> 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.


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralPLINQO Pinmembergta428-Sep-10 0:51 
GeneralMy vote of 4 Pinmembermamta_m_d27-Sep-10 8:02 
GeneralRe: My vote of 4 PinmemberRaymund Macaalay27-Sep-10 8:55 
GeneralMy vote of 4 Pinmembermamta_m_d27-Sep-10 8:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 27 Sep 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid