LINQ TO ADO.NET

LINQ to ADO.NET provides tools that let your applications apply LINQ-style queries to objects used by ADO.NET to store and interact with relational data.

LINQ to ADO.NET includes three components: LINQ to SQL, LINQ to Entities, and LINQ to DataSet. The following sections briefly give additional details about these three pieces.

LINQ to SQL and LINQ to Entities

LINQ to SQL and LINQ to Entities are object-relational mapping (O/RM) tools that build strongly typed classes for modeling databases. They generate classes to represent the database and the tables that it contains. LINQ features provided by these classes allow a program to query the database model objects.

For example, to build a database model for use by LINQ to SQL, select the Project menu’s Add New Item command and add a new “LINQ to SQL Classes” item to the project. This opens a designer where you can define the database’s structure.

Now you can drag SQL Server database objects from the Server Explorer to build the database model. If you drag all of the database’s tables onto the designer, you should be able to see all of the tables and their fields, primary keys, relationships, and other structural information.

LINQ to SQL defines a DataContext class to represent the database. Suppose a program defines a DataContext class named dcTestScores and creates an instance of it named db. Then the following code selects all of the records from the Students table ordered by first and last name:

Dim query = From stu In db.Students
    Order By stu.FirstName, stu.LastName

Microsoft intends LINQ to SQL to be a quick tool for building LINQ-enabled classes for use with SQL Server databases. The designer can quickly take a SQL Server database, build a model for it, and then create the necessary classes.

The Entity Framework that includes LINQ to Entities is designed for use in more complicated enterprise scenarios. It allows extra abstraction that decouples a data object model from the underlying database. For example, the Entity Framework allows you to store pieces of a single conceptual object in more than one database table.

Building and managing SQL Server databases and the Entity Framework are topics too large to cover in this book so LINQ to SQL and LINQ to Entities are not described in more detail here. For more information, consult the online help or Microsoft’s website. Some of Microsoft’s relevant websites include:

LINQ to DataSet

LINQ to DataSet lets a program use LINQ-style queries to select data from DataSet objects. A DataSet contains an in-memory representation of data contained in tables. Although a DataSet represents data in a more concrete format than is used by the object models used in LINQ to SQL and LINQ to Entities, DataSets are useful because they make few assumptions about how the data was loaded. A DataSet can hold data and provide query capabilities whether the data was loaded from SQL Server, from some other relational database, or by the program’s code.

The DataSet object itself doesn’t provide many LINQ features. It is mostly useful because it holds DataTable objects that represent groupings of items, much as IEnumerable objects do.

The DataTable class does not directly support LINQ either, but it has an AsEnumerable method that converts the DataTable into an IEnumerable, which you already know supports LINQ.

WHERE’S IENUMERABLE?
Actually, the AsEnumerable method converts the DataTable into an EnumerableRowCollection object but that object implements IEnumerable.

Example program LinqToDataSetScores, which is available for download on the book’s website, demonstrates LINQ to DataSet concepts. This program builds a DataSet that contains two tables. The Students table has fields StudentId, FirstName, and LastName. The Scores table has fields StudentId, TestNumber, and Score.

The example program defines class-level variables DtStudents and DtScores that hold references to the two DataTable objects inside the DataSet.

The program uses the following code to select Students records where the LastName field comes before “D” alphabetically:

Dim before_d =
    From stu In DtStudents.AsEnumerable()
    Where stu!LastName < "D"
    Order By stu.Field(Of String)("LastName")
    Select First = stu!FirstName, Last = stu!LastName
dgStudentsBeforeD.DataSource = before_d.ToList

There are only a few differences between this query and previous LINQ queries. First, the From clause calls the DataTable object’s AsEnumerable method to convert the table into something that supports LINQ.

Second, the syntax stu!LastName lets the query access the LastName field in the stu object. The stu object is a DataRow within the DataTable.

Third, the Order By clause uses the stu object’s Field(Of T) method. The Field(Of T) method provides strongly typed access to the DataRow object’s fields. In this example the LastName field contains string values. You could just as well have used stu!LastName in the Order By clause, but Visual Basic wouldn’t provide strong typing.

Finally, the last line of code in this example sets a DataGrid control’s DataSource property equal to the result returned by the query so the control will display the results. The DataGrid control cannot display the result directly so the code calls the ToList method to convert the result into a list, which the DataGrid can use.

The following list summarizes the key differences between a LINQ to DataSet query and a normal LINQ to Objects query:

  • The LINQ to DataSet query must use the DataTable object’s AsEnumerable method to make the object queryable.
  • The code can access the fields in a DataRow, as in stu!LastName or as in stu.Field(Of String)("LastName").
  • If you want to display the results in a bound control such as a DataGrid or ListBox, use the query’s ToList method.

If you understand these key differences, the rest of the query is similar to those used by LINQ to Objects. The following code shows two other examples:

' Select all students and their scores.
Dim joined =
    From stu In DtStudents.AsEnumerable()
    Join score In DtScores.AsEnumerable()
    On stu!StudentId Equals score!StudentId
    Order By stu!StudentId, score!TestNumber
    Select
        ID = stu!StudentId,
        Name = stu!FirstName & stu!LastName,
        Test = score!TestNumber,
        score!Score
dgJoined.DataSource = joined.ToList
 
' Select students with average scores >= 90.
Dim letter_grade =
    Function(num_score As Double)
        Return Choose(num_score  10,
            New Object() {"F", "F", "F", "F", "F", "D", "C", "B", "A", "A"})
    End Function
 
' Add "Where Ave >= 90" after the Group By statement
' to select students getting an A.
Dim grade_a =
    From stu In DtStudents.AsEnumerable()
        Join score In DtScores.AsEnumerable()
        On stu!StudentId Equals score!StudentId
    Group score By stu Into
        Ave = Average(CInt(score!Score)), Group
    Order By Ave
    Select Ave,
        Name = stu!FirstName & stu!LastName,
        ID = stu!StudentId,
        Grade = letter_grade(Ave)
dgAverages.DataSource = grade_a.ToList

The first query selects records from the Students table and joins them with the corresponding records in the Scores table. It displays the results in the dgJoined DataGrid control.

Next, the code defines an inline function and saves a reference to it in the variable letter_grade. This function returns a letter grade for numeric scores between 0 and 100.

The next LINQ query selects corresponding Students and Scores records, and groups them by the Students records, calculating each Student’s average score at the same time. The query orders the results by average and selects the students’ averages, names, IDs, and letter grades. Finally, the code displays the result in the dgAverages DataGrid.

LINQ to DataSet not only allows you to pull data out of a DataSet but also provides a way to put data into a DataSet. If the query selects DataRow objects, then its CopyToDataTable method converts the query results into a new DataTable object that you can then add to a DataSet.

The following code selects records from the Students table for students with last name less than “D.” It then uses CopyToDataTable to convert the result into a DataTable and displays the results in the dgNewTable DataGrid control. It sets the new table’s name and adds it to the DsScores DataSet object’s collection of tables.

' Make a new table.
Dim before_d_rows =
    From stu In DtStudents.AsEnumerable()
    Where stu!LastName < "D"
    Select stu
Dim new_table As DataTable = before_d_rows.CopyToDataTable()
dgNewTable.DataSource = new_table
 
new_table.TableName = "NewTable"
DsScores.Tables.Add(new_table)

The LinqToDataSetScores example program displays a tab control. The first tab holds a DataGrid control that uses the DsScores DataSet as its data source, so you can see all of the DataSet’s tables including the new table. Other tabs show the results of other queries described in this section.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.191.200.242