If you're new to LINQ, I highly recommend reading Introduction to LINQ before reading this article.

LINQ to SQL is a mapping between the Relational Database Schema and Objects. The relational data can be manipulated using LINQ by its mapped objects. When an object is linked to relational data, it receives attributes of the relational data. The mapping is done by translating the relational database schemas into object definitions and this can be done automatically by using the LINQ to SQL Tools in Visual Studio, which are called Object Relational Designer (O/R Designer). The Object Relational Designer is an editor which is used to create the LINQ to SQL mapping. The Object Relational Designer has two panels. The Left side panel is the designer panel where we can drag and drop the required tables from the Server explorer and the right side panel is used to perform advanced configurations like mapping to stored procedures or functions from the database. Following are the basic steps to add LINQ to SQL:
  1. Open a new project.

  2. Add a new database connection using the Server Explorer.

  3. From Server Explorer, right click on Data Connections and click the Add Connection option. Following is the snapshot of the Server Explorer,

    LINQ to SQL
  1. From Add Connection window, select the Server name where the SQL server is installed or enter the complete path of the target SQL server instance name in the Server Name text box.

  2. Now select the target database name from the Select menu or enter a database name dropdown list and click Ok. Following is the snapshot of the Add Connection window,

    LINQ to SQL
  1. Now to add LINQ to SQL, right click on the project name from the Solution Explorer and click the Add option then click the New Item option. Following is the snapshot of the Add New Item window,

    LINQ to SQL
  1. Now from Add New Item window, click on LINQ to SQL classes. The default name of the LINQ to SQL classes is DataClasses1.dbml. We can also change the default name to any other name. Following is the snapshot,

    LINQ to SQL
  1. Now select the required tables from the server Explorer and drag into the DataClasses1.dbml left side panel

  2. The LINQ to SQL classes (DataClasses1.dbml) has a toolbox which we can use to set the associations or relationships between the tables.

  3. When the LINQ to SQL Classes is created and the required tables are added then it creates a DataContext DataClasses1DataContext. The DataClasses1DataContext is the default name of the DataContext and it depends on the name of the DataClasses1.dbml. Following is the snapshot of the DataClasses1DataContext.dbml,

    LINQ to SQL

Now LINQ to SQL is ready to use. Just declare an object from the created DataContext DataClasses1DataContext and performs LINQ query on this object.

Programming Examples of LINQ to SQL

The following programming examples explain LINQ to SQL and demonstrate how to display the Data Model of the .dbml, how to display data from the database using LINQ to SQL and how to manipulate the database data.

Program # 1

Write a program that retrieves the tables list of the DataContext .dbml class.
C# Program
  1. using System;
  2. using System.Linq;
  3. namespace ConsoleApplication1 {
  4. class Program {
  5. static void Main(string[] args) {
  6. DataClasses1DataContext dbcontext = new DataClasses1DataContext();
  7. var dtm = dbcontext.Mapping;
  8. foreach(var t in dtm.GetTables()) {
  9. Console.WriteLine(t.TableName);
  10. }
  11. Console.ReadKey();
  12. }
  13. }
  14. }

Program # 2

Write a program that retrieves data from a database table using LINQ to SQL.
C# Program
  1. using System;
  2. using System.Data.Linq;
  3. using System.Linq;
  4. namespace ConsoleApplication1 {
  5. class Program {
  6. static void Main(string[] args) {
  7. //Declare an object of the DataContext
  8. DataClasses1DataContext dbContext = new DataClasses1DataContext();
  9. //Retrieve data from StudentsInfo table
  10. var result = from d in dbContext.GetTable < StudentsInfo > ()
  11. select d;
  12. foreach(var std in result) {
  13. Console.WriteLine("Student ID = " + std.StdRegNumber);
  14. Console.WriteLine("Student Name = " + std.StdName);
  15. Console.WriteLine("Student Date of Birth = " + std.StdDoBirth);
  16. }
  17. Console.ReadKey();
  18. }
  19. }
  20. }
Visual Basic Program
  1. Imports System
  2. Imports System.Data.Linq
  3. Module Module1
  4. Sub Main()
  5. 'Declare an object of the DataContext
  6. Dim dbContext As DataClasses1DataContext = New DataClasses1DataContext()
  7. ' Retrieve data from StudentsInfo table
  8. Dim result = From d In dbContext.GetTable(Of StudentsInfo)()
  9. Select d
  10. For Each std In result
  11. Console.WriteLine("Student ID = " & std.StdRegNumber)
  12. Console.WriteLine("Student Name = " & std.StdName)
  13. Console.WriteLine("Student Date of Birth = " & std.StdDoBirth)
  14. Next
  15. Console.ReadKey()
  16. End Sub
  17. End Module

Program # 3

Write a program that inserts new data into a database table using LINQ to SQL.

C# Program
  1. using System.Linq;
  2. DataClasses1DataContext dbContext = new DataClasses1DataContext();
  3. //Declare an object from the StudentsInfo Table and assign values
  4. //to its attributes
  5. StudentsInfo obj = new StudentsInfo();
  6. obj.StdRegNumber = "008";
  7. obj.StdName = "Hassan Shah";
  8. DateTime dob = new DateTime(1991, 11, 09);
  9. obj.StdDoBirth = dob;
  10. try {
  11. //Insert new student into StudentsInfo table
  12. dbContext.GetTable < StudentsInfo > ().InsertOnSubmit(obj);
  13. //Save changes to the Database
  14. dbContext.SubmitChanges();
  15. Console.WriteLine("The record has been inserted");
  16. } catch (Exception e) {
  17. Console.WriteLine(e.Message);
  18. }
Visual Basic Program
  1. Imports System.Linq
  2. Dim dbContext As DataClasses1DataContext = New DataClasses1DataContext()
  3. 'Declare an object from the StudentsInfo Table and assign values to its attributes
  4. Dim obj As StudentsInfo = New StudentsInfo()
  5. obj.StdRegNumber = "008"
  6. obj.StdName = "Hassan Shah"
  7. Dim dob As DateTime = New DateTime(1991, 11, 9)
  8. obj.StdDoBirth = dob
  9. Try
  10. 'Insert new student record into StudentsInfo table
  11. dbContext.GetTable(Of StudentsInfo)().InsertOnSubmit(obj)
  12. 'Save changes to the Database
  13. dbContext.SubmitChanges()
  14. Console.WriteLine("The record has been inserted")
  15. Catch e As System.Data.SqlClient.SqlException
  16. Console.WriteLine("The Record already Exist")

InsertOnSubmit() Method

This method is used to add a record or entity to the current table of the database. It is used in conjunction with the SubmitChanges() method of DataContext class. The SubmitChanges() method submits and saves the inserted record or entity of the InsertOnSubmit() method.