Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

Chapter 21
Databases

Wrox.com Code Downloads for this Chapter

The wrox.com code downloads for this chapter are found at www.wrox.com/go/beginningvisualc#2015programming on the Download Code tab. The code is in the Chapter 21 download and individually named according to the names throughout the chapter.

The previous chapter introduced LINQ (Language-Integrated Query) and showed how LINQ works with objects and XML. This chapter teaches you how to store your objects in a database and use LINQ to query the data.

Using Databases

A database is a persistent, structured storehouse for data. There are many different kinds of databases, but the most common type you will encounter for storing and querying business data is relational databases such as Microsoft SQL Server and Oracle. Relational databases use the SQL database language (SQL stands for Structured Query Language) to query and manipulate their data. Traditionally, working with such a database required knowing at least some SQL, either embedding SQL statements in your programming language or passing strings containing SQL statements to API calls or methods in a SQL-oriented database class library.

Sounds complicated, doesn't it? Well, the good news is that with Visual C# 2015 you can use a Code First approach to create objects in C#, store them in a database, and use LINQ to query the objects without having to use another language such as SQL.

Installing SQL Server Express

To run the examples shown in this chapter, you must install Microsoft SQL Server Express, the free lightweight version of Microsoft SQL Server. You will use the LocalDB option with SQL Server Express, which enables Visual Studio 2015 to create and open a database file directly without the need to connect to a separate server.

SQL Server Express with LocalDB supports the same SQL syntax as the full Microsoft SQL Server, so it is an appropriate version for beginners to learn on. Download SQL Server express from this link:

http://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx

Entity Framework

The class library in .NET that supports Code First is the newest version of the Entity Framework. The name comes from a database concept called the entity-relationship model, where an entity is the abstract concept of a data object such as a customer, which is related to other entities such as orders and products (for example, a customer places an order for products) in a relational database.

The Entity Framework maps the C# objects in your program to the entities in a relational database. This is called object-relational mapping. Object-relational mapping is code that maps your classes, objects, and properties in C# to the tables, rows, and columns that make up a relational database. Creating this mapping code by hand is tedious and time-consuming, but the Entity Framework makes it easy!

The Entity Framework is built on top of ADO.NET, the low-level data access library built into .NET. ADO.NET requires some knowledge of SQL, but luckily the Entity Framework also handles this for you and lets you concentrate on your C# code.

Also with the Entity Framework you get LINQ to Entities, the LINQ provider for the Entity Framework that makes querying the database in C# easy. Now you'll get started by creating some objects in a database.

A Code First Database

In the following Try It Out, you create some objects in a database using Code First with the Entity Framework, then query the objects you created using LINQ to Entities.

TRY IT OUT

Code First Database: BegVCSharp_21_1_CodeFirstDatabase

Follow these steps to create the example in Visual Studio 2015:

Create a new console application project called BegVCSharp_21_1_CodeFirstDatabase in the directory C:BegVCSharpChapter21.
Press OK to create the project.
To add the Entity Framework, use NuGet as you did in Chapter 19. Go to Tools NuGet Package Manager Manage NuGetPackages for Solution as shown in Figure 21.1.

Figure 21.1
Uncheck the Include Prerelease checkbox and get the Entity Framework latest stable release as shown in Figure 21.2. Click the Install button.

Figure 21.2
Click OK on the Preview dialog as shown in Figure 21.3.

Figure 21.3
Now the License Acceptance dialog for the Entity Framework appears as shown in Figure 21.4. Click the I Accept button.

Figure 21.4
Now the Entity Framework and its references are added to your project. You can see them in the References section of your project in Solution Explorer as shown in Figure 21.5.

Figure 21.5
Open the main source file Program.cs and add the following code. First add the Entity Framework namespace at the top of the file below the other using clauses:
```
using System.Data.Entity; 
```
Next, add another using clause for data annotations. This enables you to give hints to the Entity Framework on how to set up the database. Finally, add the System.Console namespace as with previous examples:
```
using System.ComponentModel.DataAnnotations;
using static System.Console;
```
Next, you add a Book class with Author, Title, and Code similar to the example you used in Chapter 19. The [Key] attribute you see before the Code field is a data annotation, telling C# to use this field as the unique identifier for each object in the database.
```
namespace BegVCSharp_21_1_CodeFirstDatabase
{
    public class Book
    {
        public string Title { get; set; }
        public string Author { get; set; }
        [Key] public int Code { get; set; }
    }
```

Now add a DbContext class (Database Context) to manage create, update, and delete the table of books in the database:

    public class BookContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
    }

Next, add code in the Main() function to create a couple of Book objects, and save the book objects to the database:

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BookContext())
            {
                Book book1 = new Book { Title = "Beginning Visual C# 2015",
                                  Author = "Perkins, Reid, and Hammer" };
                db.Books.Add(book1);
                Book book2 = new Book { Title = "Beginning XML",
                                  Author = "Fawcett, Quin, and Ayers"};
                db.Books.Add(book2);
                db.SaveChanges();

Finally, add the code for a simple LINQ query to list the books in the database after creation:

                var query = from b in db.Books
                            orderby b.Title
                            select b;
                WriteLine("All books in the database:");
                foreach (var b in query)
                {
                    WriteLine($"{b.Title} by {b.Author}, code={b.Code}");
                }
                WriteLine("Press a key to exit…");
                ReadKey();
            }

The complete code for your program should now look like this:

using System.Data.Entity;
using System.Data.Annotations;
using static System.Console;
namespace BegVCSharp_21_1_CodeFirstDatabase
{
    public class Book
    {
        public string Title { get; set; }
        public string Author { get; set; }
        public int Code { get; set; }
    }
    public class BookContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
    }
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BookContext())
            {
                Book book1 = new Book { Title = "Beginning Visual C# 2015",
                                   Author = "Perkins, Reid, and Hammer" };
                db.Books.Add(book1);
                Book book2 = new Book { Title = "Beginning XML",
                                  Author = "Fawcett, Quin, and Ayers"};
                db.Books.Add(book2);
                db.SaveChanges();
                var query = from b in db.Books
                            orderby b.Title
                            select b;
                WriteLine("All books in the database:");
                foreach (var b in query)
                {
                     WriteLine($"{b.Title} by {b.Author}, code={b.Code}");
                }
                WriteLine("Press a key to exit…");
                ReadKey();
            }
        }
    }
}

Compile and execute the program (you can just press F5 for Start Debugging). You will see the information for the books database appear as shown in Figure 21-6.

Figure 21.6

Press any key to finish the program and make the console screen disappear. If you used Ctrl+F5 (Start Without Debugging), you might need to press Enter/Return twice. That finishes the program run. Now look at how it works in detail.

How It Works

As is shown in the previous chapter, this code uses extension classes from the System.Linq namespace, which is referenced by a using statement inserted automatically by Visual C# 2015 when you create the project:

using System.Linq;

Next you added the Entity Framework namespace at the top of the file below the other using clauses:

using System.Data.Entity;

Then you added the using clause for data annotations, so that you could add hints to tell the Entity Framework on how to set up the database, and the static System.Console namespace:

using System.ComponentModel.DataAnnotations;
using static System.Console;

Next, you added a Book class with Author, Title, and Code similar to the example used in Chapter 19. You used the [Key] attribute to identify the Code property as the unique identifier for each row in the database.

namespace BegVCSharp_21_1_CodeFirstDatabase
{
    public class Book
    {
        public string Title { get; set; }
        public string Author { get; set; }
        [Key] public int Code { get; set; }
    }

Next you created the BookContext class inheriting from the DbContext (Database Context) class in the Entity Framework for creating, updating, and deleting the book objects as needed in the database:

    public class BookContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
    }

The class member DbSet<Book> is a collection of all the Book entities in your database.

Next you added code to use the BookContext to create two Book objects and save them to the database:

            using (var db = new BookContext())
            {
                Book book1 = new Book { Title = "Beginning Visual C# 2015",
                                   Author = "Perkins, Reid, and Hammer" };
                db.Books.Add(book1);
                Book book2 = new Book { Title = "Beginning XML",
                                  Author = "Fawcett, Quin, and Ayers"};
                db.Books.Add(book2);
                db.SaveChanges();

The using(var db = new BookContext()) clause lets you create a new BookContext instance for use in all the following code between the curly braces. Besides being a convenient shorthand, the using() clause ensures that the database connection and other underlying plumbing objects associated with the connection are closed properly when your program is finished, even if there is an exception or other unexpected event.

The Book creation and assignment statements such as

                Book book = new Book { Title = "Beginning Visual C# 2015",
                                   Author = "Perkins, Reid, and Hammer" };

are fairly straightforward creation of Book objects; no database magic has occurred yet as these are simple objects in memory. You'll note that you did not assign any value for the Code property; at this point the unassigned Code property simply contains a default value.

Next you saved the changes to BookContext db to the database:

                db.SaveChanges();

Now some magic has happened; because you used the [Key] attribute to identify Code as a key, a unique value was assigned to the Code field when each object was saved to the database. You don't have to use this value or even care what it is, because it is taken care of for you by the Entity Framework.

Figure 21.7

Finally, you execute the code for a simple LINQ query to list the books in the database after creation:

                var query = from b in db.Books
                            orderby b.Title
                            select b;
                WriteLine("All books in the database:");
                foreach (var b in query)
                {
                     WriteLine($"{b.Title} by {b.Author}, code={b.Code}");
                }
                WriteLine("Press a key to exit…");
                ReadKey();
            }

This LINQ query is very similar to the one you used in the previous chapter, but instead of querying objects in memory using the LINQ to Objects provider, you are querying the database with the LINQ to Entities provider. LINQ infers the correct provider based on the types referenced in the query; you don't have to make any changes in your logic.

Finally you just use the standard ReadKey() to pause the program before exiting so you can see the output.

That was easy, right? You created some objects, saved them to a database, and queried the database using LINQ.

But Where Is My Database?

But wait, you say. Where is the database you created? You never specified a file name or a folder location—it was all magic! You can see the database in Visual Studio 2015 through the Server Explorer. Go to Tools Connect to Database. The Entity Framework will create a database in the first local SQL Server instance it finds on your computer.

If you never had any databases on your computer previously, Visual C# 2015 creates a local SQL Server instance for you called (localdb)MSSQLLocalDB. To connect to this database type (localdb)MSSQLLocalDB into the Server Name field as shown in Figure 21.8.

Screenshot of Add Connection dialog displaying selected data source, server name, and options to log on to the server and connect to a database. — **Figure 21.8**

The database containing your data will be called BegVCSharp_21_1_CodeFirstDatabase.BookContext assuming you typed in the example name exactly as shown in the chapter. It will show up in the Select or enter a database name field after taking a moment to connect.

Now you can press OK and the database will appear in the Server Explorer Data Connections window in Visual C# 2015 as shown in Figure 21.9.

Screenshot of the Server Explorer Data Connections window displaying the cascaded Books node with items Code, Title, and Author. — **Figure 21.9**

From here you can explore the database directly. For example you can right-click on the Books table and choose Show Table Data to see the data you entered as shown in Figure 21.10.

Screenshot of the Server Explorer Data Connections window displaying the cascaded Books node with the Show Table Data option in the right-click menu. On the right is a table listing titles and authors of each code. — **Figure 21.10**

Navigating Database Relationships

One of the most powerful aspects of the Entity Framework is its capability to automatically create LINQ objects to help you navigate relationships between related tables in the database.

In the following Try It Out, you add two new classes related to the Book class to make a simple bookstore inventory report. The new classes are called Store (to represent each bookstore) and Stock, to represent the inventory of books on hand (in the store on the shelf) and on order from the publisher. A diagram of these new classes and relationships is shown in Figure 21.11.

Diagram of the relationships of class boxes Store, Stock, and Book as connected through items Inventory and Item, respectively. — **Figure 21.11**

Each store has a name, address, and an Inventory collection consisting of one or more stock objects, one for each different book (title) carried by the store. The relationship between Store and Stock is one-to-many. Each stock record is related to exactly one book. The relationship between Stock and Book is one-to-one. You need the stock record because one store may have three copies of a particular book, but another store will have six copies of the same book.

You'll see how with Code First, all you have to do is create the C# objects and collections, and the Entity Framework will create the database structure for you and let you easily navigate the relationships between your database objects and then query the related objects in the database.

TRY IT OUT

Navigating Database Relationships: BegVCSharp_21_2_DatabaseRelations

Follow these steps to create the example in Visual Studio 2015:

Create a new console application project called BegVCSharp_21_2_DatabaseRelations in the directory C:BegVCSharpChapter21.
Press OK to create the project.
Add the Entity Framework using NuGet as you did in the previous example. Go to Tools NuGet Package Manager Manage NuGetPackages for Solution.
In the NuGet Package Manager, choose the Entity Framework, uncheck the Include Prerelease checkbox and get the Entity Framework latest stable release. Click the Install button. It does not have to download because you already downloaded it in the previous step. Click OK on the Preview Changes and the I Accept button for the License Acceptance dialog.

Open the main source file Program.cs. As in the previous example, add the using statements for the System.Console, System.Data.Entity, and DataAnnotations namespaces, as well as the code to create the Book class:

using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
using static System.Console;
namespace BegVCSharp_21_2_DatabaseRelations
{
    public class Book
    {
        public string Title { get; set; }
        public string Author { get; set; }
        [Key]
        public int Code { get; set; }
    }

Now declare the Store and Stock classes as shown below. Make sure to declare Inventory and Item as virtual. You'll see why in the How It Works section.

    public class Store
    {
        [Key]
        public int StoreId { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public virtual List<Stock> Inventory { get; set; }
    }
    public class Stock
    {
        [Key]
        public int StockId { get; set; }
        public int OnHand { get; set; }
        public int OnOrder { get; set; }
        public virtual Book Item{ get; set; }
    }

Next add Stores and Stocks to the DbContext class:

    public class BookContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
        public DbSet<Store> Stores { get; set; }
        public DbSet<Stock> Stocks { get; set; }
    }

Now add code to the Main() method to use the BookContext and create the two instances of the Book class as in the previous example:

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BookContext())
            {
                Book book1 = new Book
                {
                    Title = "Beginning Visual C# 2015",
                    Author = "Perkins, Reid, and Hammer"
                };
                db.Books.Add(book1);
                Book book2 = new Book
                {
                    Title = "Beginning XML",
                    Author = "Fawcett, Quin, and Ayers"
                };
                db.Books.Add(book2);
}

Now add an instance for the first store and its inventory, still inside the using(var db = new BookContext()) clause:

                var store1 = new Store
                {
                    Name = "Main St Books",
                    Address = "123 Main St",
                    Inventory = new List<Stock>()
                };
                db.Stores.Add(store1);
                Stock store1book1 = new Stock
                { Item = book1, OnHand = 4, OnOrder = 6 };
                store1.Inventory.Add(store1book1);
                Stock store1book2 = new Stock
                { Item = book2, OnHand = 1, OnOrder = 9 };
                store1.Inventory.Add(store1book2);

Now add an instance for the second store and its inventory:

                var store2 = new Store
                {
                    Name = "Campus Books",
                    Address = "321 College Ave",
                    Inventory = new List<Stock>()
                };
                db.Stores.Add(store2);
                Stock store2book1 = new Stock
                { Item = book1, OnHand = 7, OnOrder = 23 };
                store2.Inventory.Add(store2book1);
                Stock store2book2 = new Stock
                { Item = book2, OnHand = 2, OnOrder = 8 };
                store2.Inventory.Add(store2book2);

Next save the database changes as in the previous example:
```
                db.SaveChanges();
```

Now create a LINQ query on all the stores, and print out the results:

                var query = from store in db.Stores
                            orderby store.Name
                            select store;

Finally add code to print out the results of the query and pause the output:

                WriteLine("Bookstore Inventory Report:");
                foreach (var store in query)
                {
                     WriteLine($"{store.Name} located at {store.Address}");
                    foreach (Stock stock in store.Inventory)
                    {
                        WriteLine($"- Title: {stock.Item.Title}");
                        WriteLine($"-- Copies in Store: {stock.OnHand}");
                        WriteLine($"-- Copies on Order: {stock.OnOrder}");
                    }
                }
                WriteLine("Press a key to exit…");
                ReadKey();
            }
        }
    }
}

Compile and execute the program (you can just press F5 for Start Debugging). You will see the information for the bookstore inventory appear as shown in Figure 21.12.

Figure 21.12

How It Works

The basics of the Entity Framework, DbContext, and data annotations were covered in the previous example, so here you'll concentrate on what is different.

The Store and Stock classes are similar to the original Book class but you added some new virtual properties for Inventory and Item as shown here:

    public class Store
    {
        [Key]
        public int StoreId { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public virtual List<Stock> Inventory { get; set; }
    }
    public class Stock
    {
        [Key]
        public int StockId { get; set; }
        public int OnHand { get; set; }
        public int OnOrder { get; set; }
        public virtual Book Item{ get; set; }
    }

The Inventory property looks and behaves like a normal in-memory List<Stock> collection. However because it is declared as virtual, the Entity Framework can override its behavior when storing to and retrieving from the database.

The Entity Framework takes care of the database details such as adding a foreign key column to the Stocks table in the database to implement the Inventory relationship between a Store and its Stock records. Similarly the Entity Framework adds another foreign key column to the Stock table in the database to implement the Item relationship between Stock and Book. If you're curious you can see this in Server Explorer database design view of the BegVCSharp_21_2_DatabaseRelations.BookContext database as shown in Figure 21.13.

Figure 21.13

In the past you would have had to decide how to map the collection in your program to foreign keys and columns in the database and keep that code up-to-date as your design changes. However, with the Entity Framework you do not need to know these details; with Code First you simply work with C# classes and collections and let the framework take care of the plumbing for you.

Next you added the DbSet classes for Store and Stock to the BookContext.

    public class BookContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
        public DbSet<Store> Stores { get; set; }
        public DbSet<Stock> Stocks { get; set; }
    }

Then you used those DbSet classes to create instances of two books, two stores, and two stock records for each book under each store:

    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new BookContext())
            {
                Book book1 = new Book
                {
                    Title = "Beginning Visual C# 2015",
                    Author = "Perkins, Reid, and Hammer"
                };
                db.Books.Add(book1);
                Book book2 = new Book
                {
                    Title = "Beginning XML",
                    Author = "Fawcett, Quin, and Ayers"
                };
                db.Books.Add(book2);
                  var store1 = new Store
                {
                    Name = "Main St Books",
                    Address = "123 Main St",
                    Inventory = new List<Stock>()
                };
                db.Stores.Add(store1);
                Stock store1book1 = new Stock
                { Item = book1, OnHand = 4, OnOrder = 6 };
                store1.Inventory.Add(store1book1);
                Stock store1book2 = new Stock
                { Item = book2, OnHand = 1, OnOrder = 9 };
                store1.Inventory.Add(store1book2);
                  var store2 = new Store
                  {
                    Name = "Campus Books",
                    Address = "321 College Ave",
                    Inventory = new List<Stock>()
                  };
                  db.Stores.Add(store2);
                  Stock store2book1 = new Stock
                  { Item = book1, OnHand = 7, OnOrder = 23 };
                  store2.Inventory.Add(store2book1);
                  Stock store2book2 = new Stock
                  { Item = book2, OnHand = 2, OnOrder = 8 };
                  store2.Inventory.Add(store2book2);

After creating the objects, you saved the changes to the database:

                db.SaveChanges();

Then you made a simple LINQ query to list all the stores' information:

                var query = from store in db.Stores
                            orderby store.Name
                            select store;

The code to print out the results of the query is very straightforward because it simply deals with objects and collections, no database-specific code:

                WriteLine("Bookstore Inventory Report:");
                foreach (var store in query)
                {
                    WriteLine($"{store.Name} located at {store.Address}");
                    foreach (Stock stock in store.Inventory)
                    {
                        WriteLine($"- Title: {stock.Item.Title}");
                        WriteLine($"-- Copies in Store: {stock.OnHand}");
                        WriteLine($"-- Copies on Order: {stock.OnOrder}");
                    }
                }

To print the inventory under each store, you simply use a foreach loop like with any collection.

Handling Migrations

Inevitably as you develop your code, you are going to change your mind. You will come up with a better name for one of your properties, or you will realize you need a new class or relationship. If you change the code in a class connected to a database, via the Entity Framework, you will encounter the Invalid Operation Exception shown in Figure 21.14 when you first run the changed program.

Screenshot of an Invalid Operation Exception prompt. — **Figure 21.14**

Keeping the database up to date with your changed classes is complicated, but again the Entity Framework steps in with a facility to make it relatively easy. As the error message suggests, you need to add the Code First Migrations package to your program.

To do this, go to Tools NuGet Package Manager Package Manager Console. This brings up a command window as shown in Figure 21.15.

To enable automatic migration of your database to your updated class structure, enter this command in the Package Manager Console at the PM> prompt:

Enable-Migrations –EnableAutomaticMigrations

Screenshot of the Package Manager Console window displaying codes for nuget.org packet source. — **Figure 21.15**

This adds a Migrations class to your project, shown in Figure 21.16.

Screenshot of the Solution Explorer window displaying a tree view of files under BegVCSSharp_21_2_DatabaseRelations. Folder Migrations with configuration file is highlighted. — **Figure 21.16**

The Entity Framework will compare the timestamp of the database to your program and advise you when the database is out of sync with your classes. To update the database, simply enter this command in the Package Manager Console at the PM> prompt:

Update-Database

Creating and Querying XML from an Existing Database

For the last example you will combine all you have learned about LINQ, databases, and XML.

XML is often used to communicate data between client and server machines or between “tiers” in a multitier application. It is quite common to query for some data in a database and then produce an XML document or fragment from that data to pass to another tier.

In the following Try It Out, you create a query to find some data in the previous example database, use LINQ to Entities to query the data, and then use LINQ to XML classes to convert the data to XML. This is an example of Database First as opposed to Code First programming where you take an existing database and generate C# objects from it.

TRY IT OUT

Generating XML from Databases: BegVCSharp_21_3_XMLfromDatabase

Follow these steps to create the example in Visual Studio 2015:

Create a new console application called BegVCSharp_21_3_XMLfromDatabase in the directory C:BegVCSharpChapter21.
As described in the previous example, add the Entity Framework to the project.
Add a connection to the database used by the previous example by selecting Project Add New Item. Choose ADO.NET Entity Data Model in the Add New Item dialog and change the name from Model1 to BookContext as shown in Figure 21.17.

Figure 21.17
In the Entity Data Model Wizard, choose the connection to BegVCSharp_21_2_DatabaseRelations.BookContext database you created in the previous example as shown in Figure 21.18.

Figure 21.18
Open the main source file Program.cs.

Add a reference to the System.Xml.Linq namespace to the beginning of Program.cs, as shown:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml.Linq;
using System.Text;
using static System.Console;

Add the following code to the Main() method in Program.cs:

    static void Main(string[] args)
    {
       using (var db = new BookContext())
       {
           var query = from store in db.Stores
                       orderby store.Name
                       select store;
           foreach (var s in query)
           {
                 XElement storeElement = new XElement("store",
                     new XAttribute("name", s.Name),
                     new XAttribute("address", s.Address),
                     from stock in s.Stocks
                     select new XElement("stock",
                           new XAttribute("StockID", stock.StockId),
                           new XAttribute("onHand",
                            stock.OnHand),
                           new XAttribute("onOrder",
                            stock.OnOrder),
                    new XElement("book",
                    new XAttribute("title",
                        stock.Book.Title),
                    new XAttribute("author",
                        stock.Book.Author)
                    )// end book
                  ) // end stock
                ); // end store
             WriteLine(storeElement);
          }
       Write("Program finished, press Enter/Return to continue:");
       ReadLine();
      }
   }

Compile and execute the program (you can just press F5 for Start Debugging). You will see the output shown in Figure 21.19.

Figure 21.19

Simply press Enter/Return to exit the program and make the console screen disappear. If you used Ctrl+F5 (Start Without Debugging), you might need to press Enter/Return twice.

How It Works

In Program.cs you added the reference to the System.Xml.Linq namespace in order to call the LINQ to XML constructor classes in addition to the Entity Framework classes.

When you added the Database First code by choosing ADO.NET Entity Data Model in the Add New Item dialog, Visual Studio generated a separate BookContext.cs class and added it to your project using the information from the existing BegVCSharp_21_2_DatabaseRelations.BookContext database created in the previous example.

In the main program, you created an instance of the BooksContext database context class and the same LINQ to Entities query used in previous examples:

       using (var db = new BookContext())
       {
           var query = from store in db.Stores
                       orderby store.Name
                       select store;

When you processed the results of the query in a foreach loop, you used the LINQ to XML classes to transform the query results into XML using a nested set of LINQ to XML elements and attributes:

                foreach (var s in query)
                {
                    XElement storeElement = new XElement("store",
                        new XAttribute("name", s.Name),
                        new XAttribute("address", s.Address),
                        from stock in s.Stocks
                        select new XElement("stock",
                              new XAttribute("StockID", stock.StockId),
                              new XAttribute("onHand",
                               stock.OnHand),
                              new XAttribute("onOrder",
                               stock.OnOrder),
                       new XElement("book",
                       new XAttribute("title",
                           stock.Book.Title),
                       new XAttribute("author",
                           stock.Book.Author)
                       )// end book
                     ) // end stock
                   ); // end store
                   WriteLine(storeElement);
                }

Congratulations! You have combined your data access knowledge from Chapters 19, 20, and 21 into a single program using the full power of LINQ and the Entity Framework!

What You Learned in this Chapter

Topic	Key Concepts
Using Databases	A database is a persistent, structured storehouse for data. While there are many different kinds of databases, the most common type used for business data are relational databases.
Entity Framework	The Entity Framework is a set of .NET classes for object-relational mapping between C# objects and relational databases.
How to Create Data with Code First	By using the Code First classes in the Entity Framework you can create databases directly from C# classes and collections using object-relational mapping.
How to use LINQ with Databases	LINQ to Entities enables powerful queries on databases using the same Entity Framework classes to create the data.
How to Navigate Database Relationships	The Entity Framework enables creation and navigation of related entities in your database through the use of virtual properties and collections in your C# code.
How to create and query XML from Databases	You can construct XML from databases by combining LINQ to Entities, LINQ to Objects, and LINQ to XML in a single query.

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

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

Table of Contents for Chapter 21: Databases

Create new playlist

Sign In

Sign Up

Wrox.com Code Downloads for this Chapter

Using Databases

Installing SQL Server Express

Entity Framework

A Code First Database

But Where Is My Database?

Navigating Database Relationships

Handling Migrations

Creating and Querying XML from an Existing Database

What You Learned in this Chapter

Table of Contents for
Chapter 21: Databases