Querying Databases with LINQ-to-SQL

This chapter will explain how to perform database queries with LINQPad. The default LINQ-to-SQL driver will be used to run those queries. All samples explained in this chapter use a database that will be populated with all United States postal codes. The name given to this database will be uspostalcodes.

Creating a database from LINQPad

A SQL database can be created from LINQPad, as explained in the “Managing SQL connections” section of Chapter 4. 2.

To create the uspostalcodes database, the user should click on the Add connection hyperlink, and then select the LINQ-to-SQL driver from the Choose Data Context dialog box. Click Next to bring up the LINQ to SQL Connection dialog box, where you can create the database.

To create the uspostalcodes database (after the rest of connection parameters are chosen), select the Specify new or existing database option from the Database options group, and then type the name uspostalcodes into the combo box located below this option. The Create database button will be enabled after name’s typing is finished; click this button to make LINQPad create the database. LINQPad will display the message “Created uspostalcodes” beside the Create database button after database creation. Click OK to save the connection info and make it appear in the user interface.

Figure 33: LINQPad Connection dialog box appears after database creation

Creating database’s entities

If you look at the connection displayed in the user interface, you will notice that no entity appears in the tree view. This means that LINQPad created the database only. Entities creation for the database is up to the user. Unfortunately, there’s no easy way to create database’s entities with LINQ-to-SQL. For this book, we’ll use Microsoft SQL Server Management Studio.

Figure 34: LINQPad user interface with uspostalcodes connection

You can launch SQL Server Management Studio from the Run dialog by typing ssms. Then, you should log in into SQL Server Management Studio and select the uspostalcodes database in the Object Explorer. Then, click the New Query button located in the toolbar to open a query tabbed window. Type and execute the following code.

Code Listing 9

CREATE TABLE states
(state_id INT IDENTITY NOT NULL,
 state_name VARCHAR(100) NOT NULL DEFAULT '',
 state_abbr VARCHAR(4) NOT NULL DEFAULT '');
ALTER TABLE states ADD CONSTRAINT pk_states PRIMARY KEY (state_id);
CREATE INDEX idx01_states ON states (state_name);
CREATE TABLE counties
(county_id INT IDENTITY NOT NULL,
 county_name VARCHAR(100) NOT NULL DEFAULT '',
 state_id INT NOT NULL DEFAULT 0);
ALTER TABLE counties ADD CONSTRAINT pk_counties PRIMARY KEY (county_id);
CREATE INDEX idx01_counties ON counties (county_name);
ALTER TABLE counties ADD CONSTRAINT fk01_counties FOREIGN KEY (state_id) REFERENCES states (state_id);
CREATE TABLE places
(
  place_id INT IDENTITY NOT NULL,
  place_name VARCHAR(200) NOT NULL DEFAULT '',
  postal_code CHAR(5) NOT NULL DEFAULT '',
  latitude DECIMAL(12,4) NOT NULL DEFAULT 0,
  longitude DECIMAL(12,4) NOT NULL DEFAULT 0,
  county_id INT NOT NULL DEFAULT 0
);
ALTER TABLE places ADD CONSTRAINT pk_places PRIMARY KEY (place_id);
CREATE INDEX idx01_places ON places (place_name);
ALTER TABLE places ADD CONSTRAINT fk01_places FOREIGN KEY (county_id) REFERENCES counties (county_id);

Figure 35: Uspostalcodes database with entities created, displayed in SQL Server Management Studio

Populating a database

Now, the database needs to be populated with data. Three datasets will be used.

• A SQL script with all country states.
• A SQL script with all counties belonging to each state.
• A SQL script with all places, postal codes, and their geographic references.

Due the length limitations of this book, it’s not possible to display these scripts here. These three scripts (states:table,sql, us_counties.sql, us_places.sql) are available in the code samples package, which can be downloaded here.

The following figure shows the states table after it has been populated with the scripts.

Figure 36: States table, after being populated with data

Displaying database changes in LINQPad

As discussed in the “The Connection context menu” section of Chapter 4, a set of context menu’s commands are available for any connection displayed in the LINQPad user interface. In order to view all changes made to the uspostalcodes database, the user should right-click anywhere in the window, then select the Refresh command from the context menu. Now, the entities created in SQL Server Management Studio will appear on the screen.

Figure 37: Uspostalcodes database entities, displayed in LINQPad user interface

Retrieving rows from a table

At this point, it’s assumed that all tables are filled up with data. However, all the examples discussed in this section use the States table.

Returning a certain number of rows

The Take method returns a row set that is as large as the integer value passed as a parameter. To get the first 15 rows from the States table, select C# Expression from the Language selector, and select the .\SQLEXPRESS.sa.uspostalcodes connection from the Connection selector. After that, type and execute the following code.

Code Listing 10

States.Take(15)

The output for the previous code is displayed in Figure 38.

Figure 38: The first 15 rows from States table

Even though the States table has only three columns, note that in the previous figure a fourth column with the value of Counties is displayed in the result. Reviewing the script that creates all entities in the database, you can see that there’s a SQL statement with a foreign key definition that uses the Counties table. This foreign key references the table States using the column named State_id. So, when the Take method is executed, LINQ-to-SQL finds the reference automatically, and LINQPad creates a hyperlink to the Counties table. This hyperlink is created for each row retrieved by the method. If the user clicks on any of these hyperlinks, LINQPad will display a row set from the Counties table where the State_id column value matches in both the States and Counties tables.

Note: When using LINQ-to-SQL, LINQPad automatically finds all references for the tables that are being part of the query. This means that when the user clicks on a related table hyperlink and the rows for that table are displayed, a nested reference hyperlink could be shown if another relation is found.

Using lambda expressions

The term lambda expression comes from lambda calculus, which is a mathematical notation applied for defining functions. These expressions are created using the following syntax.

Code Listing 11

(input parameters) => statement block or expression

An example of a lambda expression is displayed in the following code snippet.

Code Listing 12

y => y * 3

In the previous sample, the lambda expression specifies a parameter named y, and the statement for this expression multiplies the value of the parameter by 3.

When a lambda expression is used within a query operator, it is evaluated by this query operator upon demand. This Boolean query evaluation is applied to all those elements in the input sequence, and only those elements that meet the filter condition are passed into the appended function. The following sample fetches the odd values from a given array and calculates and average for all of them.

Code Listing 13

int[] numbers = {1,1,2,3,5,8,13,21,34};
double average = numbers.Where(number => number % 2 == 1).Average();
Console.WriteLine(average);

In the previous example, the lambda expression within the Where method is evaluated for all numbers in the list. Each one of these numbers is used to obtain the remainder after being divided by two. If the result of the operation is equal to 1, the number evaluated will be considered for the Average method. The result obtained is displayed in the following snippet.

Code Listing 14

7.3333333

Sorting rows

As in the Average calculation sample, a lambda expression can be used by LINQ-to-SQL query operators. The following code takes the first 15 rows from the States table and sort those rows using the values stored in the State_name column.

Code Listing 15

States.Take(15).OrderBy(row => row.State_name)

In this case, the lambda expression used in the OrderBy method takes a parameter named row, which receives each of the 15 rows returned by the Take method, in a one-by-one sequence. The statement executed in the expression sends the value of the State_name column to the OrderBy method for each row. When the row sequence ends, the OrderBy method performs an alphabetical sort for all of them, based on the values received.

The output for the previous code is shown in Figure 39.

Figure 39: The result for the query with rows sorted

Using aggregate functions

LINQPad allows you to use SQL aggregate functions such as Count, Sum, or Avg (average), as a LINQ-to-SQL query methods. Each one of these methods is treated as an extension method for each table in the model. Assuming that all tables have already been populated with data, the following sample will retrieve the total row count for the Places table.

Code Listing 16

Places.Count()

The output displayed should look like the following snippet.

Code Listing 17

43563

Lambda expressions can also be used to limit the results returned by a query. For example, if you need to know the number of places belonging to the county with the id 1926, you should execute the following code.

Code Listing 18

 Places.Count(row => row.County_id == 1926)

Again, the lambda expression takes a row argument and executes a Boolean comparison statement, which will return true for every row that has a value of 1926 in the County_id column. Every time a true value is returned, the Count method increments its result by 1. If there’s no row that matches the Boolean statement, LINQPad returns nothing.

The output displayed should be the following:

Code Listing 19

Note: The Sum and Average (Avg) query methods require the use of lambda expressions.

Filtering and grouping rows

LINQPad allows you to filter and group rows with LINQ-to-SQL expressions using the following methods.

• Where: Takes a lambda expression that returns a Boolean true or false value, which will tell the method which elements must be included in the query’s result.
• GroupBy: Takes a lambda expression that returns a value to group the rows. Then, returns an IQueryable collection of IGrouping objects. That is, a collection of groups. A special property named Key is added to each one of these groups. This property holds the value used to group the rows. The group itself is a collection of data rows taken from the entity that uses the method.

An example for filtering rows

Assuming that a list of all places belonging to a county with the id 1926 is required, the following code should be executed.

Code Listing 20

Places.Where(row => row.County_id == 1926)

The Where method will iterate the Places table in a sequence, and the lambda expression will receive each row as a parameter. Then, it will return a true value for those rows where the value of County_id column is equal to 1926. A true value tells the Where method to include the row in the query’s result. A false value will cause the Where method to discard the row.

The result for the previous code is shown in Figure 40.

Figure 40: The filtered query result

An example for grouping rows

The example explained in the previous section returns a row set based on the value for the County_id column. Now, it’s going to be assumed that the user wants to retrieve all rows from the Places table, but grouped by county. This can be accomplished executing the following code.

Code Listing 21

Places.GroupBy(row => row.County_id)

The example uses the GroupBy method for the Places table. The method takes a lambda expression that returns the value for the County_id column. This value will be used to group the rows for the entire table. So, all rows that have the same value for County_id will be gathered in the same collection. The total number of collections depends on how many different values for County_id are in the table. Figure 41 shows a partial result for the query.

Figure 41: Result for the GroupBy method

Note that in Figure 41, the value for the Key property is highlighted. Since the County_id column is of type integer, the property also takes this type. So, the result returned by the query is an IGrouping<Int32, Places> collection, as displayed in the same figure.

Querying for specific columns

All queries discussed until now display all columns belonging to the table inquired. But LINQ-to-SQL allows us to choose a specific set of columns to be returned by a query. This task can be accomplished by using the Select method. The following sample returns the Place_id, Place_name, and Postal_code from the Places table.

Code Listing 22

Places.Select(row => new {row.Place_id, row.Place_name, row.Postal_code})

The Select method uses a lambda expression to query a table as a sequence, which is the Places table in this case. The row parameter of the lambda expression takes one row from the table at a time. The statement in the lambda expression is executed for each row passed to the parameter, and creates a new item for an IOrderedQueryTable<> collection. This item is populated with the contents of the Place_id, Place_name, and Postal_code columns.

A partial result of the previous query is shown in Figure 42.

Figure 42: A query with specific columns

Putting all together

All methods explained in the previous sections can be used together in order to retrieve more complex results.

The following code returns the columns Place_id, Place_name, and Postal_code for all places that belongs to the county with an id of 1926.

Code Listing 23

Places.Where(row => row.County_id == 1926).Select(row => new {row.Place_id, row.Place_name, row.Postal_code})

The previous example uses both the Where and Select methods to compose the result. In this case, the Where method will ensure that the Select method only inquires those rows with a value of 1926 for the County_id column. The Select method, as explained in the previous section, will read the table as a sequence and make an IOrderedQueryTable<> collection. All items for this collection will be populated with the values of Place_id, Place_name, and Postal_code columns.

The results displayed by the previous query are not sorted in any way, so the OrdeBy method can be used to display the results sorted by any of the columns given.

The following code will sort the result based on the value for the Place_name column.

Code Listing 24

Places.Where(row => row.County_id == 1926).OrderBy(row => row.Place_name).Select(row => new {row.Place_id, row.Place_name, row.Postal_code})

The previous sample uses the OrderBy method to sort the rows returned by the Where method. The Select method reads the rows after being sorted, creating an IOrderedQueryTable<> collection with the values of the Place_id, Place_name, and Postal_code columns.

LINQ-to-SQL with multiple statements

LINQPad also allows you to perform LINQ-to-SQL queries using multiple statements. To execute these kinds of queries, you should select C# Statements from the Language selector combo box.

Code Listing 25

var places = Places.GroupBy(row => row.County_id);
var placesSorted = from place in places orderby place.Key
                                   select place;
placesSorted.Dump();

The previous code returns a collection of rows grouped by using the value contained in the County_id column. This collection is stored in the places variable. The from statement used in the second line of code takes each group from places variable, in a one-by-one sequence. Each group is stored in the place variable, and the select statement adds the group to a collection. The collection is stored in the placesSorted variable. The orderby statement sorts the collection using the Key property value of each place group.

The PredicateBuilder class

LINQPad includes a special class called PredicateBuilder. This class is a powerful LINQ expression that simplifies queries programming when too many filter parameters are used. The class solves this issue by writing dynamic query statements based on the result of lambda expressions supplied to a set of special methods that are intended to act as logical comparison operators. The PredicateBuilder is a generic class, which means it can receive any entity reference of the database used by a connection. The use of the PredicateBuilder class results in much better performance and minimizes the amount of code to be written for filters implementation.

Using PredicateBuilder class for writing queries requires you to reference the class in LINQPad. To do this, press F4 and check the Include PredicateBuilder check box, as shown in the following figure.

Figure 43: Query Properties dialog box, including PredicateBuilder class

Next, click OK. LINQPad will now be able to use the class in the current query.

Note: The Include PredicateBuilder check box must be checked every time a new query is written.

A query that uses PredicateBuilder

The following sample assumes that the user wants to get all counties with a value between 1 and 19 in the State_id column.

Code Listing 26

var resultSet = Counties.Where(row => (row.State_id > 0 && row.State_id < 20));
resultSet.Dump();

The previous code looks like any of the previous samples discussed in this book, and it works fine for getting the results desired. But if the user wants to apply a more complex filter condition, the lambda expression within the Where method will increase in code. At this point, the query will become more difficult to maintain.

The PredicateBuilder class solves the problem stated in the previous paragraph by being in charge of dynamically building the lambda expression for the Where clause. So, the user will not need to hard-code the expression within the Where clause. Instead, an instance of the PredicateBuilder class is passed to the Where method, and this instance it creates the lambda expression needed for the method to work. Now the code for the previous query will look like the following sample.

Code Listing 27

var predicate = PredicateBuilder.False<Counties>();
predicate = predicate.Or(row => row.State_id > 0);
predicate = predicate.And(row => row.State_id < 20);
var resultSet = Counties.Where(predicate);
resultSet.Dump();

As displayed in the previous code, the first line creates an instance of the PredicateBuilder class, referencing the Counties table. Then, the query calls the Or method of the PredicateBuilder class and passes to it a lambda expression. This expression returns every row with a value greater than 0 for the State_id column. The next line uses the And method to establish the other part of the filter condition. In this case, the expression will return every row with a value less than 20 for the State_id column. Now the instance of the PredicateBuilder class is passed to the Where method instead of the lambda expression, because this instance will be in charge of building that expression when the method is executed.

Note: For proper building of a lambda expression, the Or method must be called first.

Maybe for a simple expression, like the one discussed in the samples of this section, using the PredicateBuilder class turns the code in more lines. But as the filter condition becomes more complex, the code turns simpler. The following code adds another filter condition to the query.

Code Listing 28

 var predicate = PredicateBuilder.False<Counties>();
predicate = predicate.Or(row => row.State_id > 0);
predicate = predicate.And(row => row.State_id < 20);
predicate = predicate.Or(row => row.County_name.Contains("Orange"));
var resultSet = Counties.Where(predicate);
resultSet.Dump();

As noted in the previous sample, the result for the query will return all rows that contain the phrase Orange in the County_name column, other than all rows in which the State_id column has a value greater than 0 and less than 20. The lambda expression that the predicate instance will create is the following.

Code Listing 29

row => (row.State_id > 0 && row.State_id < 20) || row.County_name.Contains("Orange")

As noted in the previous sample, the expression is a little more complex, and if the user wants to retrieve rows that match more filter criteria, this expression would be larger. So, repeatedly calling the And or the Or method is easier than creating complex expressions.

A multiple keyword search

Sometimes you want to search rows in a database based on one or more keywords. Using a lambda expression for this purpose could make the query’s code complex and hard to maintain. So, instead of hard-coding the lambda expression, a PredicateBuilder instance can be employed to perform the search dynamically, as in the following sample.

Code Listing 30

var keywords = new List<string> {"Orange","York","Fernando"};
var predicate = PredicateBuilder.False<Counties>();
foreach (string keyword in keywords)
{
        predicate = predicate.Or(row => row.County_name.Contains(keyword));
}
var resultSet = Counties.Where(predicate);
resultSet.Dump();

The previous code performs a search in the Counties table based on the keywords stored in the keywords variable, which is a list of strings. A PredicateBuilder instance is created and the Or method is called repeatedly using a foreach loop. For each item in the list, the Or method receives a lambda expression, which returns a true or a false value, whether the keyword is contained in the County_name column or not.

The predicate is used by the Where method associated to the Counties table, and returns all rows that contains any of the keywords in the list as a result.

Chapter summary

This chapter discussed how to create database queries with LINQPad using the default LINQ-to-SQL driver. All samples explained in this chapter use a database named uspostalcodes. To create this database, the Add connection hyperlink is used to call the Choose Data Context dialog box to select the LINQ-to-SQL driver. Then, the LINQ to SQL Connection dialog allows you to create the uspostalcodes database.

There is no way to create database entities with LINQ-to-SQL, so you’ll use Microsoft SQL Server Management Studio to create the database tables. Once they’re created, you’ll use a series of scripts to populate tables with data.

LINQ-to-SQL has a series of methods that retrieve rows from a given table. For example, the Take method returns a row set as large as the value of the integer parameter passed to the method as a parameter, and the OrderBy method allows you to sort the rows returned by a query.

Some methods require a lambda expression in order to work properly. The term lambda expression comes from lambda calculus, which is a mathematical notation applied for defining functions. A lambda expression is composed by a parameter, and an expression that takes the parameter and uses it to return a value.

The methods that use lambda expressions are: OrderBy (to sort rows), Where (to filter rows based on a conditional expression), Sum (to summarize the value of a given column), Average (to get an average from a series of values of a given column), and GroupBy (to group rows based on the value of a given column).

LINQ-to-SQL queries can be written as a C# expression or as several C# statements, depending on the user’s needs.

LINQPad provides a special class named PredicateBuilder. This class is intended to simplify query-writing when a complex filter condition is needed. The class solves this issue providing a set of special methods intended to act as logical comparison operators. Because PredicateBuilder is a generic class, it can receive any entity reference of the database used by a connection.

The value of the PredicateBuilder class comes from its ability to build any lambda expression dynamically, when the instance of the class is passed to any LINQ-to-SQL method. The expression is created using the values passed to the class instance using either the And or Or methods, which should be called repeatedly before using the instance.

Writing queries that use the PredicateBuilder class requires you to reference this class in LINQPad. This can be done by pressing F4 and checking the Include PredicateBuilder check box that appears in the Query Properties dialog box. You need to perform this action every time you write a new query.