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#

Copy Code

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="O’Donnell", ID=112, Scores= new List<int> {75, 84, 91, 39}},

   new Student {First="Sven", Last="Mortensen", ID=113, Scores= new List<int> {88, 94, 65, 91}},

   new Student {First="Cesar", Last="Garcia", ID=114, Scores= new List<int> {97, 89, 85, 82}},

   new Student {First="Debra", Last="Garcia", ID=115, Scores= new List<int> {35, 72, 91, 70}},

   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="Garcia", ID=118, Scores= new List<int> {92, 90, 83, 78}},

   new Student {First="Lance", Last="Tucker", ID=119, Scores= new List<int> {68, 79, 88, 92}},

   new Student {First="Terry", Last="Adams", ID=120, Scores= new List<int> {99, 82, 81, 79}},

   new Student {First="Eugene", Last="Zabokritski", ID=121, Scores= new List<int> {96, 85, 91, 60}},

   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#

Copy Code

// 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#

Copy Code

// 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.

Copy 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:

Copy Code

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.

Copy Code

orderby student.Scores[0] descending

3.        Change the WriteLine format string so that you can see the scores:

Copy Code

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#

Copy Code

// 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#

Copy Code

// 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#

Copy Code

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#

Copy Code

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#

Copy Code

// 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#

Copy Code

    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#

Copy Code

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#

Copy Code

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.

Copy Code

 

class LINQQueryExpressions

{

    static void Main()

    {

 

        // 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."

 Copy Code

class FindSentences

{

    static void Main()

    {

        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.";

 

        // Split the text block into an array of sentences.

        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#

Copy Code

class QueryWithRegEx

{

    public static void Main()

    {

        // 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.

LINQ to SQL Object Model

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

NoteNote:

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

Kopiuj kod

<Table(Name:="Customers")> _

Public Class Customer

    Public CustomerID As String

    ' ...

    Public City As String

End Class

 

C#

Kopiuj kod

[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

Kopiuj kod

<Table(Name:="Customers")> _

Public Class Customer

    <Column(IsPrimaryKey:=True)> _

    Public CustomerID As String

 

    <Column()> _

    Public City As String

End Class

 

C#

Kopiuj kod

[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#

Kopiuj kod

    [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.

NoteNote:

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#

Kopiuj kod

    // 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:

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#: