A database is an organized collection of data.

A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data.

SQL Server Express provides most of the features of Microsoft’s full (fee-based) SQL Server product, but has some limitations, such as a maximum database size.

A SQL Server Express database can be easily migrated to a full version of SQL Server.

The ADO.NET Entity Framework and LINQ to Entities allow you to manipulate relational data stored in a relational database, such as a SQL Server Express database.

A relational database organizes data simply in tables.

Tables are composed of rows and columns (also called fields) in which values are stored.

A column (or group of columns) of each row is the table’s primary key—a column (or group of columns) requiring a unique value that cannot be duplicated in other rows. This guarantees that a primary-key value can be used to uniquely identify a row.

A primary key composed of two or more columns is known as a composite key.

Each column represents a different data attribute.

Rows are unique (by primary key) within a table, but some column values may be duplicated between rows.

A database’s tables, their fields and the relationships between them are collectively known as a database schema.

A foreign key is a column in one table that matches the primary-key column in another table.

Foreign keys, which are specified when a database table is created, link the data in multiple tables.

Every foreign-key value must appear as another table’s primary-key value so the DBMS can ensure that the foreign-key value is valid.

Foreign keys also allow related data in multiple tables to be selected from those tables—this is known as joining the data.

There’s a one-to-many relationship between a primary key and a corresponding foreign key—a foreign key can appear many times in its own table but only once (as the primary key) in another table.

An entity-relationship (ER) diagram shows the tables in a database and their relationships.

Every row must have a value in the primary-key column, and the value of the key must be unique in the table.

With the ADO.NET Entity Framework, you interact with the database via classes that the IDE generates from the database schema when you add a new ADO.NET Entity Data Model to your project.

The IDE creates two classes for a table. The first class represents a row of the table and contains properties for each table column. Objects of this class are called row objects and store the data from individual rows of the table. The IDE uses the singular version of a table’s plural name as the row class’s name. The second class represents the table itself. An object of this class stores a collection of row objects that correspond to all of the rows in the table.

The entity data model classes have full IntelliSense support in the IDE.

Navigation properties in row classes represent the relationships between tables.

A DbContext (namespace System.Data.Entity) manages the data flow between the program and the database. When the IDE generates the entity data model’s row and table classes, it also creates a derived class of DbContext that is specific to the database being manipulated. This class contains properties that represent the database’s tables.These can be used as data sources for manipulating data in LINQ queries and in GUIs.

Changes made to the data managed by a DbContext can be saved back to the database using the DbContext’s SaveChanges method.

LINQ to Entities works through the IQueryable<T> interface, which inherits from the interface IEnumerable<T>. When a LINQ to Entities query on an IQueryable<T> object executes against the database, the results are loaded into objects of the corresponding entity data model classes for convenient access in your code.

The extension methods used with LINQ to Entities are defined as static methods of class Queryable (namespace System.Linq) and can be applied to any object that implements the IQueryable<T> interface—these include various entity data model objects and the results of LINQ to Entities queries.

The IDE provides visual programming tools and wizards that simplify accessing data in your projects. These tools establish database connections and create the objects necessary to view and manipulate the data through the GUI—a technique known as data binding.

A DataGridView (namespace System.Windows.Forms) displays data from a data source in tabularformat.

A BindingNavigator is a collection of controls that allow you to navigate through the records displayed in a GUI. The BindingNavigator controls also allow you to add records, delete records and save your changes to the database.

An entity data model describes as C# classes the data that you’ll be manipulating.

To interact with a database, you add an ADO.NET entity data model to the project (typically a class library project for reuse). This will also configure the connection to the database.

The ADO.NET entity data model’s .edmx file contains information about the entity data model.

When configuring the model, you create a connection to the database and choose the model’s contents from the database.

The entity connection string contains the information that the ADO.NET Entity Framework requires to connect to the database at runtime.

The entity data model diagram contains the selected database objects and shows the relationships between them.

To use the entity data model classes from a class library for data binding, you must first add a reference to the class library to your project. You’ll also need to add a reference to the Entity-Framework library.

The EntityFramework library is required in each app that will use an entity data model.

Each app that will use an entity data model also requires the connection string that tells the Entity Framework how to connect to the database. The connection string is stored in the App.Config file of the project in which the entity data model is defined and must be copied into the App.Config file of any project that uses the entity data model.

You must write code to enable the autogenerated GUI to interact with the entity data model.

To use the entity data model classes for data binding, first add them as a data source via Visual Studio’s Data Sources window. The entity data model classes are used to create objects representing the tables in the database, so use an Object data source.

The default GUI for a table is a DataGridView with column names representing all the properties of the data source object. The IDE also creates a BindingNavigator that contains Buttons for moving between entries, adding entries, deleting entries and saving changes to the database. The IDE also generates a BindingSource, which handles the transfer of data between the data source and the data-bound controls on the Form.

Nonvisual components such as the BindingSource and the nonvisual aspects of the BindingNavigator appear in the component tray—the gray region below the Form in Design view.

The IDE names the BindingNavigator and BindingSource based on the data source’s name.

To edit the columns displayed in a DataGridView, right click it, then select Edit Columns… to display the Edit Columns dialog.

To complete the data binding, you must create an object of the entity data model’s DbContext derived class and use it to obtain data.

The OrderBy extension method receives a Func delegate and orders rows in ascending order by the value returned by the delegate.

The ThenBy extension method receives a Func delegate and enables you to order results by an additional column.

The Load extension method (defined in class DBExtensions from the namespace System.Data.Entity) loads data into memory. This data is tracked by the DbContext in local memory so that any changes made to the data can eventually be saved into the database.

The Local property of a DbContext property is an ObservableCollection<T> that represents the data in memory.

When a BindingSource’s DataSource property is assigned an ObservableCollection<T> (namespace System.Collections.ObjectModel), the GUI that’s bound to the BindingSource is notified of any changes to the data so the GUI can be updated accordingly. In addition, changes made by the user to the data in the GUI will be tracked so the DbContext can eventually save those changes to the database.

Calling EndEdit on the BindingSource forces it to save any pending changes into the DbContext model in memory.

Calling SaveChanges on the DbContext object stores any changes into the database.

The data bindings created by the IDE automatically update a DataGridView each time its Bind-ingSource’s DataSource changes.

The MoveFirst method of the BindingSource sets the Position property to 0 to move to the first row of the data source.

Invoking OrderBy on an ObservableCollection<T> returns an IOrderedEnumerable<T>.

A Func delegate that’s used with the Where extension method must return a bool value indicating whether the delegate’s argument matches the specified criteria.

To join data from multiple tables you use the properties that the entity data model contains based on foreign-key relationships between the database’s tables. These properties enable you to easily access related rows in other tables.

Most queries return result with data arranged in relational-style rows and columns. With LINQ to Entities you can create queries that return hierarchical results in which each item in the result contains a collection of other items.

All properties of an anonymous type are public and read-only.

You refer to objects of anonymous types via implicitly typed local variables.

An anonymous type’s autogenerated Equals method compares the properties of the anonymous object that calls the method and the anonymous object that it receives as an argument.

In a master/detail view, one part of the GUI (the master) allows you to select an entry, and another part (the details) displays detailed information about that entry.

The IDE allows you to specify the type of control(s) that it will create when you drag-and-drop an object from the Data Sources window onto a Form. To do so, click a data source object’s node in the Data Sources window—it should change to a drop-down list from which you can select the controls to use on the GUI. The Details option indicates that the IDE should generate Label– TextBox pairs that represent each column of the data source object.

Dragging a navigation property from a data source object in the Data Sources window creates a BindingSource and a GUI for the items in another table that are associated with the data source object. The items in this second BindingSource will automatically update based on the currently selected master object.

You cannot bind a LINQ to Entities query’s results directly to a BindingSource ’s DataSource.

When you convert a query result to a List, only changes to existing records in the DbContext are tracked by the DbContext—any records that you add or remove while viewing the filtered data in a List would be lost.