1. LINQ to Object: Queries against plain old .NET objects in a collection that implements an IEnumerable<T> interface. This is an in-memory query interface to objects in memory.
2. LINQ to XML: Provides a way to query and modify XML documents using modification capabilities of Document Object Model. This is an in-memory query interface to an XML document in memory.
3. LINQ to SQL: Queries against .NET objects that implement IQueryable<T> interface. Behind the scenes, these .NET objects generate the requisite SQL to query the SQL data source and return the result set.
Note: There are at least two other ways LINQ works with databases. LINQ to Entities provides capability to query against databases using the Entity Framework model. LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object.
This guide is focused on LINQ to SQL.
Basics of LINQ
The syntax for LINQ is quite simple. Actually there are two different syntaxes for LINQ.
Let’s say we have IEnumerable object that contains list of countries.
class Country
{
}
SQL Style Comprehension Syntax
var query = from c in Countries
where c.Continent == "Asia"
orderby c.Name
select s;
Extension Methods and Lambda Expression
var query = Countries.Select(c => c.Continent == "Asia").OrderBy(c.Name);
Both of the above queries accomplish the same thing.
What is LINQ to SQL?
LINQ to SQL is an OR/M tool built right into Visual Studio and .NET 3.5+. LINQ supports several data sources and is extensible to support just about any data source.
LINQ to SQL allows you to generate and map .NET classes to objects in your SQL database schema. These generated classes represent the Data Model corresponding to your schema database objects. Once you have this data model generated, you can easily and efficiently perform queries, inserts, updates and deletes against your database using LINQ that leverages the generated classes. Instead of generating the classes, you can hand-write these classes but that is most likely unnecessary. The generated classes provide various extension points where you can insert your business validation code, and override default behavior for queries and updates.
LINQ to SQL is an OR/M tool built right into Visual Studio and .NET 3.5+. LINQ supports several data sources and is extensible to support just about any data source.
LINQ to SQL allows you to generate and map .NET classes to objects in your SQL database schema. These generated classes represent the Data Model corresponding to your schema database objects. Once you have this data model generated, you can easily and efficiently perform queries, inserts, updates and deletes against your database using LINQ that leverages the generated classes. Instead of generating the classes, you can hand-write these classes but that is most likely unnecessary. The generated classes provide various extension points where you can insert your business validation code, and override default behavior for queries and updates.
Even though you are accessing database objects (tables, views, stored procedures) and performing traditional CRUD (Create, Read, Update, Delete) operations, you are exclusively doing that using C# code. What this means that you can transform data query results into any data structure you desire using any legal C# code.
Since LINQ to SQL is part of Microsoft .NET, unlike other OR/M tools, you do not need to worry about including any third-party assemblies into your project.
Capabilities of LINQ to SQL
The following is broad overview of what you can accomplish with LINQ to SQL.
Useful References
Programming Guide: http://msdn.microsoft.com/en-us/library/bb399408.aspx
LINQ to SQL Samples: http://msdn.microsoft.com/en-us/vstudio/bb688085
Generating LINQ To SQL Classes
In Visual Studio, add a new LINQ to SQL class (.dbml). Drag entities, procedures, and functions on to the design surface of the .dbml file from the Server Explorer. The main class generated is a DataContext class that has members that reference the database objects. The DataContext class can be instantiated in a variety of ways (e.g. connection string, IDbConnection, SqlConnection).
Querying Data
InsertSince LINQ to SQL is part of Microsoft .NET, unlike other OR/M tools, you do not need to worry about including any third-party assemblies into your project.
Capabilities of LINQ to SQL
The following is broad overview of what you can accomplish with LINQ to SQL.
Useful References
Programming Guide: http://msdn.microsoft.com/en-us/library/bb399408.aspx
LINQ to SQL Samples: http://msdn.microsoft.com/en-us/vstudio/bb688085
Generating LINQ To SQL Classes
In Visual Studio, add a new LINQ to SQL class (.dbml). Drag entities, procedures, and functions on to the design surface of the .dbml file from the Server Explorer. The main class generated is a DataContext class that has members that reference the database objects. The DataContext class can be instantiated in a variety of ways (e.g. connection string, IDbConnection, SqlConnection).
Querying Data
- Data result can be projected into a custom or anonymous type within the select statement. Example: var q = from c in db.Customers select new myClassName { Name = c.FullName, Phone = c.Tel} in x order by x select x
- Data can be projected into Xml (using XElement)
- LINQ operators, such as sum, can be used within the projections and can operate on the entities contained within the queried entity
- Retrieved data can be paged by applying .Skip(startRow) and .Take(numRows) to the select statement
- Specific property of an entity (such large image, system.binary) can be lazy-loaded by setting DelayLoad property of the entity
- To load child objects, use the DataShape.LoadWith()
- Conditions can be specified based on the child objects (including collections)
- Since queries are deferred, we can build a base query and add more operations (e.g. where, orderby) to the query later and then execute it. For example, we can start with a simple select query and then append the where clause later
- The relationships defined in the database cause the joins to be automatically be carried out. To manually create a join for other relationships, use join keyword. Example: from S in db.Supplier join C in db.Customer on S.City equals C.City
- Given Customer, and Order entities, the data can be added in a number of ways. For example, create an Order and add Order Details to the Order. Then look up customer, and add the new Order to the Orders collection of the customer. Conversely, assign the customer id (if already know) to the new Order.
- Given Category, and Items entities, we can instantiate a new category, instantiate items, add items to the new category, and then insert the category. The category and the items are then inserted in one transaction.
Updating Data
- Use exception handler to handle ChangeConflictException
- Use DataContext.EntityName.Attach() to attach an on-the-fly object created with the one read from the database
- Update may fail if the orginal values read have been changed on the server by another user. In the absence of a row version, all fields of updated and original rows are compared. By setting UpdateCheck to "Never" for a field, the field can be excluded from the comparison.
Customizing Insert, Update, Delete
- In the properties of an entity, we can specify what stored procedure to call as well as which parameters of the stored proc bind to which members of an entity.
Executing Raw SQL
- Use DataContext.ExecuteQuery()
- Results can be projected to existing entity, anonymous type, or any POCO
- In implementation of partial methods, InsertEntityName, DeleteEntityName, UpdateEntityName, use ExecuteCommand()
- Property-level validation can be performed by implementing partial methods (e.g. partial void OnPropertyNameChanging(type arg))
- Entity-Level validation can be performed by implementing partial method OnValidate
- Operation-Level (Insert, Update, Delete) can be performed implementing partial methods InsertEntityName, DeleteEntityName, UpdateEntityName
- Simply drag and drop the desired stored procedures onto the .dbml design surface. The procedures become methods of the DataContext class.
- The return value of the stored procedure can be mapped to existing entity (drag the stored procedure method to the entity on the .dbml design surface)
- The OUT param is mapped to a ref argument in C# method
- It is possible to handling different shapes of data being returned from stored proc. For example, a stored proc may return customers or orders depending on the passed parameter. Use result.GetResult<entityType>()
SQL Functions
- Both scalar and table-valued (CREATE FUNCTION... RETURNS TABLE) functions are supported
- Drag desired functions to .dbml design surface
- Built-in functions are called when available. For example, Math.Floor() in LINQ to SQL executes as FLOOR on the database server
Transactions
- Upon SubmitChanges(), the SQL is executed in a transaction
- Change list can be found by calling DataContext.GetChangeList()
- TransactionScope can be used to commit other transactional operation along with LINQ to SQL transaction
Performance
- LINQ queries are deferred until enumerated on. To avoid multiple queries to the database, use q.ToList() or q.ToArray()
- It is possible to pre-compile a query
- For read operations, ObjectTracking can turned-off to boost performance
- Results can be bound to data-aware controls such as ASP.NET controls (e.g. GridView)
- Use DataContext.Log to print SQL to desired output (e.g. Console.Out)
- Use DataContext.GetCommand() to get SqlCommand
- Use DataContext.GetChangeSet() to get changed objects
- Use SQL Profiler trace to view the SQL being sent to the SQL Server
Code Samples
static public void AddNewAutomaker(Automaker automaker, List<Car> cars)
{
using (LINQExampleDataContext db = new LINQExampleDataContext(Read.ConnectionString))
{
automaker.Cars.AddRange(cars);
db.Automakers.InsertOnSubmit(automaker);
db.SubmitChanges();
}
}
// Insert Car with to a pre-existing automaker
Car newCar= new Car
{
...
AutomakerId = 123,
...
};
db.Cars.InsertOnSubmit(newCar);
db.SubmitChanges();
Update
static public void UpdateCarPrice(string carModelName, decimal newPrice)
{
using (LINQExampleDataContext db = new LINQExampleDataContext(Read.ConnectionString))
{
var car = db.Cars.Single(c => c.Model == carModelName);
car.Price = newPrice;
db.SubmitChanges();
}
}
// UPDATE WITH CHILD ROWS
// Update only what has changed
// Read originalAutomaker (must be in detached state)
// updatedAutomaker is passed in
// Attach() allows detection of changes
db.Automakers.Attach(updatedAutomaker, originalAutomaker);
// Make aware of Cars on the original
db.Cars.AttachAll(originalAutomaker.Cars, false);
// Delete Cars in the original
db.Cars.DeleteAllOnSubmit(originalAutomaker.Cars);
// Insert Cars in the updated
db.Automakers.Cars.InsertAllOnSubmit(updatedAutomaker.Cars);
// Uses column values to compare what has changed
// Update fails, if the original values have changed since read
// Can exclude columns from comparison by setting
// UpdateCheck to Never in the designer
// Alternatively, RowVersion or timestamp can be added
// to table row for update comparison
db.SubmitChanges();
Delete
static public void DeleteAutoMaker(string autoMakerName)
{
using (LINQExampleDataContext db = new LINQExampleDataContext(Read.ConnectionString))
{
var autoMaker = from a in db.Automakers
where a.Name == autoMakerName
select a;
db.Automakers.DeleteAllOnSubmit(autoMaker);
db.SubmitChanges();
}
}
// Deleting automaker and its cars
var autoMaker = from a in db.Automakers
where a.Id== autoMakerId
select a;
db.Automakers.DeleteOnSubmit(automaker);
db.Automakers.DeleteAllOnSubmit(autoMaker.Cars);
db.SubmitChanges();
Read
static public List<string> GetAllAutomakers()
{
using (LINQExampleDataContext db = new LINQExampleDataContext(ConnectionString))
{
var manufacturers = from a in db.Automakers
select a.Name.Trim();
return manufacturers.ToList<string>();
}
}
static public Automaker GetAutomaker(string automakerName)
{
using (LINQExampleDataContext db = new LINQExampleDataContext(ConnectionString))
{
return db.Automakers.Where(a => a.Name == automakerName).SingleOrDefault<Automaker>();
}
}
static public List<string> GetAllAutomakersSorted()
{
using (LINQExampleDataContext db = new LINQExampleDataContext(ConnectionString))
{
var manufacturers = from a in db.Automakers
orderby a.Name
select a.Name;
return manufacturers.ToList<string>();
}
}
static public List<Car> GetCarsByAutomaker(string automakerName)
{
using (LINQExampleDataContext db = new LINQExampleDataContext(ConnectionString))
{
var cars = from c in db.Cars
where c.Automaker.Name == automakerName
select c;
return cars.ToList<Car>();
}
}
static public Car GetCar(string carModelName)
{
using (LINQExampleDataContext db = new LINQExampleDataContext(ConnectionString))
{
return db.Cars.SingleOrDefault(c => c.Model == carModelName);
}
}
Paging
static public int TotalPages(int totalRows, int pageSize)
{
return (int) Math.Ceiling( (double) totalRows / (double) pageSize);
}
static public int TotalCars()
{
using (LINQExampleDataContext db = new LINQExampleDataContext(Read.ConnectionString))
{
return db.Cars.Count();
}
}
static public List<Car> GetCarsPaged(int pageNumber, int pageSize)
{
using (LINQExampleDataContext db = new LINQExampleDataContext(Read.ConnectionString))
{
int skipRows = (pageNumber - 1) * pageSize;
var cars = (from c in db.Cars
orderby c.Model
select c).Skip(skipRows).Take(pageSize);
return cars.ToList<Car>();
}
}
Stored Proc
static public List<Car> GetCarsForAutomaker(string automakerName)
{
using (LINQExampleDataContext db = new LINQExampleDataContext(Read.ConnectionString))
{
List<Car> ret = new List<Car>();
var cars = db.GetCarsByManufacturer(automakerName);
foreach (Car car in cars)
{
ret.Add(car);
}
return ret;
}
}
Transactions
using (TransactionScope txn = new TransactionScope())
using (LINQExampleDataContext db = new LINQExampleDataContext(Read.ConnectionString))
{
// Linq to Sql operations
// MSMQ operations
txn.Complete();
}
