Writing Queries in C#
(LINQ)
This walkthrough will guide you through the new C# 3.0
language features and show how they are used to write LINQ query expressions.
After completing this walkthrough you will be ready to move on to the samples
and documentation for the specific LINQ provider you are interested in, such as
LINQ to SQL, LINQ to DataSets, or LINQ to XML.
To create a C# project that targets
version 3.5 of the .NET Framework
1. Start Visual Studio.
2.
On the File menu, point
to New, and then click Project.
3.
The upper-right corner
of the New Project dialog box has three icons. Click the left icon and make
sure that .NET Framework Version 3.5 is checked.
4.
Click the Console
Application icon under Visual Studio Installed Templates.
5.
Give your application a
new name, or accept the default name, and click OK.
6.
Notice that your project
has a reference to System.Core.dll and a using directive for the System.Linq namespace.
Create an in-Memory Data Source
The data source for the queries is a simple list of
Student objects. Each Student record has a first name, last name, and an array
of integers that represents their test scores in the class. Copy this code into
your project. Note the following characteristics:
- The Student class consists of auto-implemented
properties.
- Each student in the list is initialized with an
object initializer.
- The list itself is initialized with a collection initializer.
This whole data structure will be initialized and
instantiated without explicit calls to any constructor or explicit member
access. For more information about these new features, see Auto-Implemented Properties (C# Programming
Guide) and Object and Collection Initializers
(C# Programming Guide).
To add the data source
- Add the Student class and the initialized list of
students to the Program class in your project.
C#
public class Student
{
public
string First { get;
set; }
public
string Last { get;
set; }
public
int ID { get; set; }
public
List<int>
Scores;
}
// Create a data source by
using a collection initializer.
static
List<Student> students = new
List<Student>
{
new
Student {First="Svetlana", Last="Omelchenko",
ID=111, Scores= new List<int> {97, 92, 81, 60}},
new
Student {First="Claire", Last=
new
Student {First="Sven", Last="Mortensen", ID=113, Scores= new List<int> {88, 94, 65, 91}},
new
Student {First="Cesar", Last=
new
Student {First="Debra", Last=
new
Student {First="Fadi",
Last="Fakhouri",
ID=116, Scores= new List<int> {99, 86, 90, 94}},
new
Student {First="Hanying",
Last="Feng",
ID=117, Scores= new List<int> {93, 92, 80, 87}},
new
Student {First="Hugo", Last=
new
Student {First="Lance", Last="Tucker", ID=119, Scores= new List<int> {68, 79, 88, 92}},
new
Student {First="Terry", Last=
new
Student {First="
new
Student {First="Michael", Last="Tucker", ID=122, Scores= new List<int> {94, 92, 91, 91} }
};
To add a new Student to the Students
list
- Add a new Student to the Students list and use a
name and test scores of your choice. Try typing all the new student information
in order to better learn the syntax for the object initializer.
Create the Query
To create a simple query
- In the application's Main method, create a simple
query that, when it is executed, will produce a list of all students whose
score on the first test was greater than 90. Note that because the whole
Student object is selected, the type of the query is IEnumerable<Student>.
Although the code could also use implicit typing by using the var keyword, explicit typing is used to clearly
illustrate results. (For more information about var, see Implicitly Typed Local Variables (C#
Programming Guide).)
Note
also that the query's range variable, student, serves as a reference to each
Student in the source, providing member access for each object.
C#
// Create the query.
// The first line could also be written as
"var studentQuery ="
IEnumerable<Student> studentQuery =
from student in
students
where student.Scores[0] >
90
select student;
class="LibC_o"> Execute
the Query
To execute the query
1. Now write the foreach loop that will cause the query to execute. Note the following about the code:
o
Each element in the
returned sequence is accessed through the iteration variable in the foreach loop.
o
The type of this
variable is Student, and the type of the query variable is compatible, IEnumerable<Student>.
2.
After you have added
this code, build and run the application by pressing Ctrl + F5 to see the
results in the Console window.
C#
// Execute the query.
// var could be used here also.
foreach (Student student in
studentQuery)
{
Console.WriteLine("{0}, {1}", student.Last, student.First);
}
To add another filter condition
- You can combine multiple Boolean conditions in the where clause in order to further refine a query. The following code adds a condition so that the query returns those students whose first score was over 90 and whose last score was less than 80. The where clause should resemble the following code.
where student.Scores[0] > 90 && student.Scores[3] < 80
For
more information, see where clause (C# Reference).
Modify the Query
To order the results
1.
It will be easier to
scan the results if they are in some kind of order. You can order the returned
sequence by any accessible field in the source elements. For example, the
following orderby clause orders the results in
alphabetical order from A to Z according to the last name of each student. Add
the following orderby clause to your query, right
after the where statement and before the select statement:
orderby student.Last ascending
2.
Now change the orderby clause so that it orders the results in reverse
order according to the score on the first test, from the highest score to the
lowest score.
orderby student.Scores[0] descending
3.
Change the WriteLine format string so that you can see the scores:
Console.WriteLine("{0}, {1} {2}", student.Last, student.First, student.Scores[0]);
For
more information, see orderby clause (C# Reference).
To group the results
1.
Grouping is a powerful
capability in query expressions. A query with a group clause produces a
sequence of groups, and each group itself contains a Key and a sequence that
consists of all the members of that group. The following new query groups the
students by using the first letter of their last name as the key.
C#
// studentQuery2 is an IEnumerable<IGrouping<char, Student>>
var studentQuery2 =
from student in students
group student by student.Last[0];
2.
Note that the type of
the query has now changed. It now produces a sequence of groups that have a
char type as a key, and a sequence of Student objects. Because the type of the
query has changed, the following code changes the foreach
execution loop also:
C#
// studentGroup is a IGrouping<char, Student>
foreach (var studentGroup in
studentQuery2)
{
Console.WriteLine(studentGroup.Key);
foreach (Student student
in studentGroup)
{
Console.WriteLine(" {0}, {1}",
student.Last, student.First);
}
}
3.
Press Ctrl + F5 to run
the application and view the results in the Console window.
For
more information, see group clause (C# Reference).
To make the variables implicitly
typed
- Explicitly coding IEnumerables of IGroupings can quickly become tedious. You can write the same query and foreach loop much more conveniently by using var. The var keyword does not change the types of your objects; it just instructs the compiler to infer the types. Change the type of studentQuery and the iteration variable group to var and rerun the query. Note that in the inner foreach loop, the iteration variable is still typed as Student, and the query works just as before. Change the s iteration variable to var and run the query again. You see that you get exactly the same results.
C#
var studentQuery3 =
from student in students
group student by student.Last[0];
foreach (var groupOfStudents in
studentQuery3)
{
Console.WriteLine(groupOfStudents.Key);
foreach (var student in groupOfStudents)
{
Console.WriteLine(" {0}, {1}",
student.Last, student.First);
}
}
For
more information about var,
see Implicitly Typed Local Variables (C#
Programming Guide).
To order the groups by their key
value
- When you run the previous query, you notice that
the groups are not in alphabetical order. To change this, you must provide
an orderby clause after the group clause. But to
use an orderby clause, you first need an
identifier that serves as a reference to the groups created by the group
clause. You provide the identifier by using the into keyword, as follows:
C#
var studentQuery4 =
from student in students
group student by student.Last[0] into studentGroup
orderby
studentGroup.Key
select studentGroup;
foreach (var groupOfStudents in
studentQuery4)
{
Console.WriteLine(groupOfStudents.Key);
foreach (var student in groupOfStudents)
{
Console.WriteLine(" {0}, {1}",
student.Last, student.First);
}
}
When
you run this query, you will see the groups are now sorted in alphabetical
order.
To introduce an identifier by using
let
- You can use the let keyword to introduce an
identifier for any expression result in the query expression. This
identifier can be a convenience, as in the following example, or it can
enhance performance by storing the results of an expression so that it
does not have to be calculated multiple times.
C#
// studentQuery5 is an IEnumerable<string>
// This query returns those
students whose
// first test score was higher than their
// average score.
var studentQuery5 =
from student in students
let totalScore =
student.Scores[0] + student.Scores[1] +
student.Scores[2] + student.Scores[3]
where totalScore
/ 4 < student.Scores[0]
select student.Last
+ " " + student.First;
foreach (string s in
studentQuery5)
{
Console.WriteLine(s);
}
For
more information, see let clause (C# Reference).
To use method syntax in a query
expression
- As described in LINQ Query Syntax versus Method Syntax (C#), some query operations can only be expressed by using method syntax. The following code calculates the total score for each Student in the source sequence, and then calls the Average() method on the results of that query to calculate the average score of the class. Note the placement of parentheses around the query expression.
C#
var studentQuery6 =
from student in students
let totalScore =
student.Scores[0] + student.Scores[1] +
student.Scores[2] + student.Scores[3]
select
totalScore;
double averageScore
= studentQuery6.Average();
Console.WriteLine("Class
average score = {0}", averageScore);
To transform or project in the
select clause
1.
It is very common for a
query to produce a sequence whose elements differ from the elements in the
source sequences. Delete or comment out your previous query and execution loop,
and replace it with the following code. Note that the query returns a sequence
of strings (not Students), and this fact is reflected in the foreach loop.
C#
IEnumerable<string> studentQuery7 =
from student in students
where student.Last
== "Garcia"
select student.First;
Console.WriteLine("The Garcias in the class are:");
foreach (string s in
studentQuery7)
{
Console.WriteLine(s);
}
2.
Code earlier in this
walkthrough indicated that the average class score is approximately 334. To
produce a sequence of Students whose total score is greater than the class
average, together with their Student ID, you can use an anonymous type in the
select statement:
C#
var studentQuery8 =
from student in students
let x = student.Scores[0] +
student.Scores[1] +
student.Scores[2] + student.Scores[3]
where x > averageScore
select new { id = student.ID,
score = x };
foreach (var item in studentQuery8)
{
Console.WriteLine("Student ID: {0}, Score:
{1}", item.id, item.score);
}
LINQ Query Expressions (C#
Programming Guide)
Language-Integrated Query (LINQ) is the name for a set
of technologies based on the integration of query capabilities directly into
the C# language (also in Visual Basic and potentially any other .NET
language). With LINQ, a query is now a first-class language construct, just
like classes, methods, events and so on.
For a developer who writes queries, the most visible
"language-integrated" part of LINQ is the query expression. Query
expressions are written in a declarative query syntax introduced in C# 3.0. By
using query syntax, you can perform even complex filtering, ordering, and
grouping operations on data sources with a minimum of code. You use the same
basic query expression patterns to query and transform data in SQL databases,
ADO.NET Datasets, XML documents and streams, and .NET collections.
The following example shows the complete query
operation. The complete operation includes creating a data source, defining the
query expression, and executing the query in a foreach
statement.
class LINQQueryExpressions
{
static void
{
// Specify the data source.
int[] scores = new int[] { 97, 92, 81, 60 };
// Define the query
expression.
IEnumerable<int> scoreQuery =
from score in scores
where score > 80
select score;
// Execute the query.
foreach
(int i in scoreQuery)
{
Console.Write(i + "
");
}
}
}
// Output: 97 92 81
Query for Sentences that Contain a
Specified Set of Words (LINQ)
This example shows how to find sentences
in a text file that contain matches for each of a specified set of words.
Although the array of search terms is hard-coded in this example, it could also
be populated dynamically at runtime. In this example, the query returns the
sentences that contain the words "Historically," "data,"
and "integrated."
class FindSentences
{
static void
{
string text = @"Historically, the world of data and the world of objects " +
@"have not been well integrated. Programmers work in C# or Visual Basic " +
@"and also in SQL or XQuery.
On the one side are concepts such as classes, " +
@"objects, fields, inheritance, and
.NET Framework APIs. On the other side
" +
@"are tables, columns, rows, nodes, and separate languages for
dealing with " +
@"them. Data types often require translation between the two worlds; there are " +
@"different standard functions. Because the object world has no notion of query, a " +
@"query can only be represented as a string without compile-time
type checking or " +
@"IntelliSense support in the IDE. Transferring data from SQL tables or XML trees to " +
@"objects in memory is often tedious and error-prone.";
//
string[]
sentences = text.Split(new char[] { '.', '?', '!' });
// Define the search terms. This list could also
be dynamically populated at runtime.
string[] wordsToMatch = { "Historically", "data", "integrated" };
// Find sentences that contain all the terms in the wordsToMatch
array.
// Note that the number of terms to match is not specified at compile
time.
var sentenceQuery = from sentence in sentences
let w = sentence.Split(new char[]
{ '.', '?', '!', ' ', ';', ':', ',' },
StringSplitOptions.RemoveEmptyEntries)
where w.Distinct().Intersect(wordsToMatch).Count() == wordsToMatch.Count()
select sentence;
// Execute the query. Note that you can
explicitly type
// the iteration variable here even though sentenceQuery
// was implicitly typed.
foreach (string str in sentenceQuery)
{
Console.WriteLine(str);
}
// Keep the console window open in debug mode.
Console.WriteLine("Press any key to exit");
Console.ReadKey();
}
}
/* Output:
Historically, the world of data and the
world of objects have not been well integrated
*/
The query works by first splitting the text into
sentences, and then splitting the sentences into an array of strings that hold
each word. For each of these arrays, the Distinct method removes all duplicate words,
and then the query performs an Intersect operation on the word array and the wordstoMatch array. If the count of the intersection
is the same as the count of the wordsToMatch array, all words were found in the words
and the original sentence is returned.
In the call to Split, the punctuation marks are used as
separators in order to remove them from the string. If you did not do this, for
example you could have a string "Historically," that would not match
"Historically" in the wordsToMatch array. You may have to use additional
separators, depending on the types of punctuation found in the source text.
Compiling the Code
·
Create a Visual
Studio project that targets the .NET Framework version 3.5. By default,
the project has a reference to System.Core.dll and a using
directive (C#) or Imports statement (Visual Basic)
for the System.Linq namespace. In C# projects, add a using directive for the System.IO
namespace.
·
Copy this code into your project.
·
Press F5 to compile and run the
program.
·
Press any key to exit the console
window.
LINQ Queries with Regular
Expressions
This example shows how to use the Regex class to create a regular expression for
more complex matching in text strings. The LINQ query makes it easy to filter
on exactly the files that you want to search with the regular expression, and
to shape the results.
C#
class QueryWithRegEx
{
public static void
{
// Modify this path as necessary.
string startFolder = @"c:\program
files\Microsoft Visual Studio 9.0\";
// Take a snapshot of the file system.
IEnumerable<System.IO.FileInfo>
fileList = GetFiles(startFolder);
// Create the regular expression to find all
things "Visual".
System.Text.RegularExpressions.Regex searchTerm
=
new System.Text.RegularExpressions.Regex(@"Visual (Basic|C#|C\+\+|J#|SourceSafe|Studio)");
// Search the contents of each .htm file.
// Remove the where clause to find even more matches!
// This query produces a list of files where a
match
// was found, and a list of the matches in that file.
// Note: Explicit typing of "Match" in select clause.
// This is required because MatchCollection
is not a
// generic IEnumerable collection.
var queryMatchingFiles =
from file in fileList
where file.Extension == ".htm"
let fileText = System.IO.File.ReadAllText(file.FullName)
let matches = searchTerm.Matches(fileText)
where searchTerm.Matches(fileText).Count > 0
select new
{
name = file.FullName,
matches = from System.Text.RegularExpressions.Match
match in matches
select match.Value
};
// Execute the query.
Console.WriteLine("The term \"{0}\" was found in:", searchTerm.ToString());
foreach
(var v in queryMatchingFiles)
{
// Trim the path a bit, then write
// the file name in which a match was found.
string s = v.name.Substring(startFolder.Length
- 1);
Console.WriteLine(s);
// For this file, write out
all the matching strings
foreach
(var v2 in v.matches)
{
Console.WriteLine(" " +
v2);
}
}
// Keep the console window open in debug mode
Console.WriteLine("Press any key to exit");
Console.ReadKey();
}
//
This method assumes that the application has discovery
//
permissions for all folders under the specified path.
static IEnumerable<System.IO.FileInfo> GetFiles(string path)
{
if (!System.IO.Directory.Exists(path))
throw new System.IO.DirectoryNotFoundException();
string[] fileNames = null;
List<System.IO.FileInfo> files = new
List<System.IO.FileInfo>();
fileNames = System.IO.Directory.GetFiles(path,
"*.*", System.IO.SearchOption.AllDirectories);
foreach (string name in fileNames)
{
files.Add(new System.IO.FileInfo(name));
}
return files;
}
}
Note that you can also query the MatchCollection object that is returned by a RegEx
search. In this example only the value of each match is produced in the
results. However, it is also possible to use LINQ to perform all kinds of
filtering, sorting, and grouping on that collection. Because MatchCollection is a non-generic IEnumerable collection, you have to explicitly
state the type of the range variable in the query.
The LINQ to SQL Object Model
In LINQ to SQL, an object model
expressed in the programming language of the developer is mapped to the data
model of a relational database. Operations on the data are then conducted
according to the object model.
In this scenario, you do not issue
database commands (for example, INSERT) to the
database. Instead, you change values and execute methods within your object
model. When you want to query the database or send it changes, LINQ to SQL
translates your requests into the correct SQL commands and sends those commands
to the database.
The most fundamental elements in the LINQ
to SQL object model and their relationship to elements in the relational
data model are summarized in the following table:
|
LINQ to SQL Object Model |
Relational Data Model |
|
Entity class |
Table |
|
Class member |
Column |
|
Association |
Foreign-key relationship |
|
Method |
Stored Procedure or Function |
|
|
|
|
The following descriptions assume that you have a basic knowledge of
the relational data model and rules. |
|
LINQ to SQL Entity Classes and
Database Tables
In LINQ to SQL, a database table is represented by an entity class. An entity class is like any other class
you might create except that you annotate the class by using special
information that associates the class with a database table. You make this
annotation by adding a custom attribute (TableAttribute) to your class declaration, as in the
following example:
Example
Visual Basic
<Table(Name:="Customers")> _
Public Class Customer
Public CustomerID As String
'
...
End Class
C#
[Table(Name = "Customers")]
public class Customerzz
{
public string CustomerID;
//
...
public string
City;
}
Only instances of classes declared as
tables (that is, entity classes) can be saved to the database.
For more information, see the Table
Attribute section of Attribute-Based Mapping (LINQ to
SQL).
LINQ to SQL Class Members and
Database Columns
In addition to associating classes with tables, you
designate fields or properties to represent database columns. For this purpose,
LINQ to SQL defines the ColumnAttribute attribute, as in the following
example:
Example
Visual Basic
<Table(Name:="Customers")> _
Public Class Customer
<Column(IsPrimaryKey:=True)> _
Public CustomerID As String
<Column()>
_
End Class
C#
[Table(Name = "Customers")]
public class Customer
{
[Column(IsPrimaryKey = true)]
public string CustomerID;
[Column]
public string
City;
}
Only fields and properties mapped to
columns are persisted to or retrieved from the database. Those not declared as
columns are considered as transient parts of your application logic.
The ColumnAttribute attribute has a variety of properties
that you can use to customize these members that represent columns (for
example, designating a member as representing a primary key column). For more
information, see the Column Attribute section of Attribute-Based Mapping (LINQ to
SQL).
LINQ to SQL Associations and
Database Foreign-key Relationships
In LINQ to SQL, you represent database
associations (such as foreign-key to primary-key relationships) by
applying the AssociationAttribute attribute. In the following segment of
code, the Order class contains a Customer property that has an AssociationAttribute attribute. This property and its
attribute provide the Order class with a relationship to the Customer class.
The following code example shows the Customer property from the Order class.
C#
[Association(Name="FK_Orders_Customers",
Storage="_Customer", ThisKey="CustomerID", IsForeignKey=true)]
public Customer Customer
{
get
{
return this._Customer.Entity;
}
set
{
Customer previousValue = this._Customer.Entity;
if (((previousValue != value)
|| (this._Customer.HasLoadedOrAssignedValue ==
false)))
{
this.SendPropertyChanging();
if ((previousValue != null))
{
this._Customer.Entity = null;
previousValue.Orders.Remove(this);
}
this._Customer.Entity =
value;
if ((value != null))
{
value.Orders.Add(this);
this._CustomerID = value.CustomerID;
}
else
{
this._CustomerID = default(string);
}
this.SendPropertyChanged("Customer");
}
}
}
For more information, see the Association
Attribute section of Attribute-Based Mapping (LINQ to
SQL).
LINQ to SQL Methods and Database
Stored Procedures
LINQ to SQL supports stored procedures and
user-defined functions. In LINQ to SQL, you map these database-defined
abstractions to client objects so that you can access them in a strongly typed
manner from client code. The method signatures resemble as closely as possible
the signatures of the procedures and functions defined in the database. You can
use IntelliSense to discover these methods.
A result set that is returned by a call to a mapped
procedure is a strongly typed collection.
LINQ to SQL maps stored procedures and functions to
methods by using the FunctionAttribute and ParameterAttribute attributes. Methods representing stored
procedures are distinguished from those representing user-defined functions by
the IsComposable property. If this property is set to false (the default), the method represents a stored
procedure. If it is set to true, the method
represents a database function.
|
|
|
If you are using Visual Studio, you can use the Object Relational
Designer to create methods mapped to stored procedures and user-defined
functions. |
C#
//
This is an example of a stored procedure in the Northwind
//
sample database. The IsComposable property defaults
to false.
[Function(Name="dbo.CustOrderHist")]
public ISingleResult<CustOrderHistResult> CustOrderHist([Parameter(Name="CustomerID", DbType="NChar(5)")] string customerID)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
customerID);
return ((ISingleResult<CustOrderHistResult>)(result.ReturnValue));
}
For more information, see the Function
Attribute, Stored Procedure Attribute, and Parameter Attribute sections of Attribute-Based Mapping (LINQ to
SQL) and Stored Procedures (LINQ to SQL).
Using LINQ to SQL
(Part 1)
Over the last few months I wrote a series of blog posts that covered some
of the new language features that are coming with the Visual Studio and
.NET Framework "Orcas" release. Here are pointers to the
posts in my series:
- Automatic Properties, Object Initializer and Collection Initializers
- Extension Methods
- Lambda Expressions
- Query Syntax
- Anonymous Types
The above language features help make querying data a first class programming concept. We
call this overall querying programming model "LINQ" - which stands
for .NET Language Integrated Query.
Developers can use LINQ with any data source. They can express
efficient query behavior in their programming language of choice, optionally
transform/shape data query results into whatever format they want, and then
easily manipulate the results. LINQ-enabled languages can
provide full type-safety and compile-time checking of query expressions,
and development tools can provide full intellisense, debugging, and rich
refactoring support when writing LINQ code.
LINQ supports a very rich extensibility model that facilitates
the creation of very efficient domain-specific operators for data
sources. The "Orcas" version of the .NET Framework ships with
built-in libraries that enable LINQ support against Objects, XML, and Databases.
What Is
LINQ to SQL?
LINQ to SQL is an O/RM
(object relational mapping) implementation that ships in the .NET
Framework "Orcas" release, and which allows you to model a
relational database using .NET classes. You can then query the
database using LINQ, as well as update/insert/delete data from it.
LINQ to SQL fully supports transactions, views, and stored
procedures. It also provides an easy way to integrate data validation and
business logic rules into your data model.
Modeling
Databases Using LINQ to SQL:
Visual Studio "Orcas" ships with a LINQ to SQL designer that
provides an easy way to model and visualize a database as a LINQ to SQL object
model. My next blog post will cover in more depth how to use this
designer (you can also watch this video I made in January to see me build a
LINQ to SQL model from scratch using it).
Using the LINQ to SQL designer I can easily create a representation of the
sample "Northwind" database like below:
My LINQ to SQL design-surface above defines four entity classes: Product,
Category, Order and OrderDetail. The properties of each class map to the
columns of a corresponding table in the database. Each instance of a
class entity represents a row within the database table.
The arrows between the four entity classes above represent
associations/relationships between the different entities. These are
typically modeled using primary-key/foreign-key relationships in the
database. The direction of the arrows on the design-surface indicate
whether the association is a one-to-one or one-to-many relationship.
Strongly-typed properties will be added to the entity classes based on this.
For example, the Category class above has a one-to-many relationship with the
Product class. This means it will have a "Categories" property
which is a collection of Product objects within that category. The
Product class then has a "Category" property that points to a
Category class instance that represents the Category to which the Product
belongs.
The right-hand method pane within the LINQ to SQL design surface
above contains a list of stored procedures that interact with our database
model. In the sample above I added a single
"GetProductsByCategory" SPROC. It takes a categoryID as an
input argument, and returns a sequence of Product entities as a result.
We'll look at how to call this SPROC in a code sample below.
Understanding the DataContext Class
When you press the "save" button within the LINQ to SQL
designer surface, Visual Studio will persist out .NET classes that represent
the entities and database relationships that we modeled. For each LINQ to
SQL designer file added to our solution, a custom DataContext class will also
be generated. This DataContext class is the main conduit by which we'll
query entities from the database as well as apply changes. The
DataContext class created will have properties that represent each Table we
modeled within the database, as well as methods for each Stored Procedure we
added.
For example, below is the NorthwindDataContext class that is
persisted based on the model we designed above:
LINQ to SQL Code Examples
Once we've modeled our database using the LINQ to SQL designer, we can then
easily write code to work against it. Below are a few code examples that
show off common data tasks:
1) Query Products From the Database
The code below uses LINQ query syntax to retrieve an IEnumerable sequence
of Product objects. Note how the code is querying across the
Product/Category relationship to only retrieve those products in the
"Beverages" category:
C#:
2) Update a Product in the Database
The code below demonstrates how to retrieve a single product from the
database, update its price, and then save the changes back to the database:
C#:
3) Insert a New Category and Two New Products into the Database
The code below demonstrates how to create a new category, and then create
two new products and associate them with the category. All three are then
saved into the database.
Note below how I don't need to manually manage the primary key/foreign key
relationships. Instead, just by adding the Product objects into the
category's "Products" collection, and then by adding the Category
object into the DataContext's "Categories" collection, LINQ to
SQL will know to automatically persist the appropriate PK/FK relationships for
me.
C#
4) Delete Products from the Database
The code below demonstrates how to delete all Toy products from the
database:
C#:
5) Call a Stored Procedure
The code below demonstrates how to retrieve Product entities not using LINQ
query syntax, but rather by calling the "GetProductsByCategory"
stored procedure we added to our data model above. Note that once I
retrieve the Product results, I can update/delete them and then call
db.SubmitChanges() to persist the modifications back to the database.
C#:
6) Retrieve Products with Server Side Paging
The code below demonstrates how to implement efficient server-side database
paging as part of a LINQ query. By using the Skip() and Take() operators
below, we'll only return 10 rows from the database - starting with row 200.
C#:
