Querying Relational Databases with LINQ to SQL
LINQ to SQL is a built-in ORM (Object Relational Mapper) tool in .NET that allows us to query relational databases using LINQ (Language Integrated Query) syntax. In this tutorial, we'll look at how to use LINQ to SQL to query a relational database.
Syntax
// Creating DataContext instance
var dataContext = new DataContext(connectionString);
// Querying database using LINQ to SQL syntax
var query = from t in dataContext.TableName
where t.ColumnName == value
select t;
Example
Suppose we have a database named "TestDB" with a table named "Employees" containing two columns "EmployeeID" (int) and "Name" (varchar). Now, let's see how to use LINQ to SQL to retrieve all the employees whose Name
column values start with "J"
.
using System.Linq;
using System.Data.Linq;
class Program
{
static void Main(string[] args)
{
// Creating DataContext instance
var dataContext = new DataContext("Data Source=(local);Initial Catalog=TestDB;Integrated Security=True");
// Querying database using LINQ to SQL syntax
var query = from e in dataContext.GetTable<Employee>()
where e.Name.StartsWith("J")
select e;
// Outputting the results
foreach (var employee in query)
{
Console.WriteLine(employee.Name);
}
Console.ReadLine();
}
}
// Model Class representing the Employee Table
public class Employee
{
public int EmployeeID { get; set; }
public string Name { get; set; }
}
Output
After running the above code, the output will display the names of all employees whose names start with "J":
John
James
Explanation
In the above syntax, a DataContext
object is created using a connection string to specify the database to connect to. LINQ to SQL syntax is used to query the Employee
table, where the name column value starts with "J"
. The results are then displayed using a foreach
loop.
Use
LINQ to SQL is commonly used to retrieve data from a relational database, using a familiar syntax that's similar to querying in-memory collections with LINQ. It is used to abstract the database and manipulate data using strongly-typed objects, with the help of a Model Class that represents the table structure.
Important Points
- To use LINQ to SQL, a DataContext object must be created using the necessary connection string parameters.
- A Model Class should be created, representing the table structure, to map the results with strong type objects.
- LINQ to SQL queries are executed when the query is enumerated by calling a method like
ToList
or by using aforeach
loop.
Summary
In this tutorial, we learned about querying relational databases using LINQ to SQL in .NET. We saw how to create a DataContext
object, query a table using LINQ to SQL syntax, and then use a Model Class to represent the table structure. LINQ to SQL simplifies the querying process and makes it easy to manipulate relational databases, using a familiar syntax.