Accessing data using Language Integrated Query (LINQ) in ASP.NET web pages – Part 1

• Download source code - 186 KB

Introduction

This article comprises of two parts. Part 1 deals with the introduction to LINQ and the LinqDataSource control in ASP.NET and describes how to define and retrieve an in-memory data collection and display data in a web page. Part 2 explains how to create entity classes to represent SQL Server databases and tables using the Object Relational Designer and display data in a web page using the LinqDataSource control.

Introduction to LINQ

Language Integrated Query (LINQ) is a query syntax that defines a set of query operators that allow traversal, filter, and projection operations to be expressed in a declarative way in any .NET-based programming language. It provides a unified programming model for querying and updating data from different types of data sources, and extends data capabilities directly into the C# and Visual Basic languages. LINQ simplifies the interaction between object-oriented programming and relational data by applying the principles of object-oriented programming to relational data.

With the advent of LINQ, a ground-breaking, new concept of a query has been introduced as a first-class language construct in C# and Visual Basic. LINQ simplifies the way you work with data queries. LINQ offers you a unified, declarative syntax model to query any data source, including XML documents, SQL Server databases, ADO.NET Datasets, in-memory collections, or any other remote or local data source that chooses to support LINQ. Language Integrated Queries are strongly typed, and designer tools can be used to create object-relational mappings. It is easier now for developers to catch errors during compile-time; it also supports Intellisense and debugging.

Sample query

A query is an expression that retrieves data from a data source. All LINQ query operations consist of three essential actions: Obtain the data source, Create the query, and Execute the query.

In LINQ, the execution of the query is isolated from the query itself, and hence data cannot be retrieved just by creating a query.

In the following sample, a query retrieves even numbers from an array of integers. The output will be: 0,2,4,6,8,10.

// Data source. 
int[] numbers = new int[10] { 0, 1, 2, 3, 4, 5, 6 ,8,9,10}; 
// Query creation. 
IEnumerable<int> numQuery = 
  from num in numbers where (num % 2) == 0 select num; 
// Query execution. 
foreach (int j in numQuery) 
{ 
    Console.Write("{0,1} ", j); 
}

LinqDataSource control in ASP.NET

As many of you familiar with various DataSource controls in ASP.NET 2.0 know, the 2008 version of ASP.NET includes a new DataSource control called LinqDataSource. The LinqDataSource control enables us to retrieve and update data using Language Integrated Queries (LINQ) from an in-memory collection of data or SQL Server database tables. It automatically generates data commands for select, update, delete, and insert operations, and you need not have to create them manually.

This control has two main properties:

• ContextTypeName that represents the name of the type object that contains the data collection.
• TableName that represents the name of the public field or property that returns a data collection, or a table name in the case of database access.

Walkthrough 1: Connecting an in-memory data collection using the LinqDataSource control and performing queries to display data in an ASP.NET page

Open Visual Studio 2008 and create a New WebSite Project.

Add a new class file to the App_Code folder of the project. Define classes that supply data to the control and write LINQ queries to retrieve data from them.

public class Student { 
    public string First { get; set; } 
    public string Last { get; set; } 
    public int ID { get; set; } 
    public List<int> Scores; 
}

public class StudentData 
{ 
    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} } 
    };

You have just added a Student class and initialized a list of students in the class file. 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.

Creating queries

You can create a number of queries to retrieve data from the data source, and in this article, I have created a few queries for demonstration purposes. Each query is assigned to a public property or field so that it would be used in the LinqDataSource controls in the web page.

Queries of IEnumerable<StudentAverage> type

public class StudentAverage 
{ 
    public int ID; 
    public string First; 
    public string Last; 
    public double ScoreAverage; 
}

public IEnumerable<StudentAverage> studentAverageMarks = 
       from student in StudentData.students 
       where student.Scores[0] > 90 
select new StudentAverage { ID=student.ID, First=student.First, 
       Last=student.Last, ScoreAverage = student.Scores.Average()}; 

Declare a new type StudentAverage that consists of ID, First, Last, and ScoreAverage fields. This query returns the list of students with their average marks.

Queries of IEnumerable<int> type

public static int studentID; 
public IEnumerable<int> GetStudentTotal = from student 
       in StudentData.students where student.ID == 
       StudentData.studentID select student.Scores.Sum(); 

Declare another public property that returns an int type of collection that holds the result from the query. This query retrieves the sum total of all marks for a particular student and it uses a condition in the where clause. The user will supply data to the parameter studentID during runtime.

public IEnumerable<int> 
  GetStudentList = from student in StudentData.students 
  select student.ID; 

GetStudentList is another property added to the class that stores the result of a query. This query returns a collection of Student.ID.

Queries of implicit type (anonymous type)

public IEnumerable GetData 
{ 
    get 
    { 
        var studentMarks = from student in StudentData.students where 
            student.ID > 110 select new { ID = student.ID, 
            First = student.First, Last = student.Last, 
            ScoreTotal = student.Scores.Sum() }; 
        foreach (var s in studentMarks) 
        yield return s.ID + ", " + s.Last + ", " + 
                     s.First + ", " + s.ScoreTotal; 
    } 
}

Now, you have just added a public property that returns a query result as a collection of students marks data. Note that the query involves an anonymous type that contains fields such as ID, First, Last, and ScoreTotal. The query returns an implicit type var, and it uses iterators to return the elements in the collection.

By this time, you have your data sources and queries ready for access by the data-bound controls of a web page.

Designing the web page to view data

To demonstrate the usage of LINQ data sources and to view data on a web page, the layout of the page has been designed in three sections.

Section 1: View student details such as ID, First Name, Last Name, and Average Score. This view executes query 1, studentAverageMarks, defined earlier in this article.

Drag a LinqDataSource control (ID=LinqDataSource1) into the page from the data tab in the toolbox and configure it to access the data sources and queries you have just created in the project, as shown below, in the Configure Data Source Wizard.

1. Choose “StudentData” as the context object.



2. Choose “studentAverageMarks” as table in the Data Selection, and Check all the fields appearing in the Select list.



3. Click the “Finish” button to complete the configuration of the LinqDataSource control.

Drag a GridView control (ID=GridView1) into the page from the data tab in the tool box, and set DataSource property to LinqDataSource1.

Section 2: View the total score for a selected student ID listed in the drop-down list. This section executes the query 2, GetStudentTotal, defined earlier in this article.

Drag a LinqDataSource control (ID=LinqDataSource2) into the page from the data tab in the toolbox, and configure it to access the data sources and queries you have just created in the project, as shown below, in the Configure Data Source Wizard.

1. Choose “StudentData” as a context object.
2. Choose “GetStudentList” as table in the Data Selection, and check the field appearing in the select list. (Pic3)



3. Click the “Finish” button to complete the configuration of the LinqDataSource control.

Drag a DropDownList control (ID=DropDownList1) into the page and set the DataSource property to LinqDataSource2. Remember to check the box that enables auto-postback behaviour to this control.

Place TextBox and Label controls in the page, and add the following code to the SelectedIndexChanged event handler of DropDownList1.

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e) 
{ 
    StudentData sd = new StudentData(); 
    StudentData.studentID = Convert.ToInt32(DropDownList1.SelectedItem.Text); 
    TextBox2.Text = Convert.ToString(sd.GetStudentTotal.ToList<int>()[0]); 
}

Section 3: View student details such as Student ID, First Name, Last Name, and Total Score. This view executes the query 3, studentMarks, returned from the public property GetData defined earlier in this article.

Drag a LinqDataSource control (ID=LinqDataSource3) into the page from the data tab in the toolbox and configure it to access the data sources and queries you have just created in the project.

1. Choose “StudentData” as a context object.
2. Choose “GetData” as table in Data Selection and check all the fields appearing in the Select list.
3. Click the “Finish” button to complete the configuration of the LinqDataSource control.

Drag a ListBox control (ID=ListBox1) into the page from the data tab in the tool box and set the DataSource property to LinqDataSource3.

That’s it. Now, run the webpage and see the output as displayed below.

Summary

Part-I of this article has demonstrated how to define and connect to in-memory data sources and explained various ways of defining and executing queries on that data collection. You are now invited to walk-through the Part-II of this article that defines the entity classes to database objects and write and execute LINQ queries on database table objects.