Chapter 17
Introducing ADO.NET 3.0 and the Entity Framework
In This Chapter
Understanding the General Nature of the Problem and the Solution
Finding Additional Resources
Building a Sample Application Using Vanilla ADO.NET Programming
Programming with the Entity Framework
Doing It All with LINQ
“There are only two ways to live your life. One is as though nothing is a miracle. The other is as though everything is a miracle.”
—Albert Einstein
Everything in this book is based on released technology except this chapter and the final chapter based on LINQ to XSD. LINQ to XSD is closer to being released and the Entity Framework is supposed to ship with Microsoft Visual Studio SP1, sometime in 2008.
The sheer volume, the sheer magnitude of the output from 100,000+ very bright people (at Microsoft) is staggering. The ADO.NET Entity Framework encompasses LINQ, Generics, Entity Data Modeling, something new called “Entity SQL,” new classes, and a whole new way of interacting with data providers. The ADO.NET Entity Framework really needs its own book. (If the gods are willing, perhaps I will get to provide you with one. In all likelihood, there will be whole books on just Entity SQL.)
The ADO.NET Entity Framework is designed to lower the speed bump that is current database programming—getting data from flat, relational tables into bumpy objects. Of course, to be able to use the framework, you have the new speed bump of learning all of the related capabilities.
When I asked Brian Dawson, Program Manager for the ADO.NET Entity Framework at Microsoft, Brian assured me that ADO.NET 3.0 is not an all-or-nothing proposition. Brian’s response was that programmers will still be able to write database interactions as they do now with ADO.NET 2.0 or take an alternate path and a different approach and use the Entity Framework. (I suspect that they [Microsoft] think that once you get a taste of the new ADO.NET, the old way of doing things will seem less appealing.)
What you will accomplish here is a look at one way of using ADO.NET 2.0 style programming in contrast with the new way, using the ADO.NET Entity Framework. You will also see a brief example of Entity SQL and get a refresher on creating databases with good old-fashioned Structured Query Language (SQL) scripting.
Before you begin, it is crucial that you understand that although there is a lot of information and new technology here, a tremendous amount of the plumbing is handled for you by wizards and Visual Studio designers. You can get an Entity Framework example up and running by selecting one file template that runs a wizard and by writing a simple LINQ query. (Don’t let that simplicity get lost in the technology-noise.)
Understanding the General Nature of the Problem and the Solution
The general “database” problem is that based on the current relational technology everyone seems to be encouraged to write their own data access layer. There is no standard for getting data into objects, so everyone writes a bunch of plumbing code to do just that.
When almost everyone needs to do the same thing, it begs the question, “What is the general solution that everyone can use?” Ten years ago, a few companies threw object-oriented databases against the wall. They didn’t stick. So, in the ensuing years, in almost every project—at least of the dozens I have seen—a data access layer is reinvented from scratch.
The challenge is that a huge amount of data is stored in relational databases already. This state of affairs suggests that a solution needs to work existing relational technology, but the solution also needs to bridge the gap between tables, columns, and rows and complex object shapes.
Relational database technology has been around a while and so has object-oriented programming, yet this problem has persisted. I suspect Microsoft believes they are on track for a solution, but the final arbiters are you and I, which depends on the suitability of the solution in the real world.
Understanding Problems with the Relational Database Model as It Pertains to C# Programmers
In the practical world, there is a physical database model that satisfies the needs of the database engine and the file system. There is a logical model that satisfies the needs of relational technology, and then there is a conceptual model that represents the relationships that need to be expressed for the problem space. Programmers generally want to get the data into its conceptual form—customers and orders, stocks and historical prices—as painlessly as possible.
With ADO.NET 2.0 and earlier technologies, programmers had to understand a little bit about the physical model: how database files were stored and how to attach them to some context. Programmers really had to know the logical model to write queries to get the logical model into a conceptual form. Going from logical to conceptual usually entails writing complex queries that use all manner of joins, and for C# programmers, you had to understand ADO.NET and ADO+ before that.
In this (the present) style, there is no insulation between the logical model and the conceptual model. If anyone needs to change the schema, then everything from the joins up needs to change. That is, the stored procedures change, the ADO.NET plumbing changes, and, often, the classes in C# have to change. Consequently, unless you have a dedicated Database Administrator (DBA) who has available time, C# programmers also have to master SQL—and left joins, right joins, cross joins, inner joins, outer joins, and self joins.
In reality, mastering C# and the .NET Framework is a large enough task for anyone. Becoming a SQL expert, too, is likely too much to ask. Being an expert in C# and SQL at a minimum is not going to promote optimal productivity.
Understanding How the Entity Framework Is Designed to Help
A solution in the pipeline is the ADO.NET Entity Framework. The Entity Framework represents a capability that facilitates quickly and consistently assembling a conceptual data model on top of all of the SQL plumbing. The idea is that the DBA focuses on the physical and logical model and joins and SQL, and the C# programmer simply focuses on programming against the conceptual model.
In simplistic terms, the ADO.NET Entity Framework lets the C# programming focus on shaped objects like cars and parts, customers and orders, or stocks and historical prices without having to track indexes, keys, and joins clauses.
Grokking the Nature of the Solution
The Entity Framework uses a wizard to code generate wrapper classes that add a level of abstraction between the logical data model, creating a conceptual data model for you. (It’s worth noting that the logical model guides the conceptual model and the developer can influence the way the conceptual model is captured in code.)
The wrapper classes are shaped by the underlying logical model. For example, in Northwind, Customers have Orders and Orders have Order Details. The ADO.NET Entity Framework plumbing uses this information and all of the elements of relational programming, like keys and indexes, to figure out how to generate the conceptual model. In the final result, Customers will still have Orders, but you won’t have to think of these in terms of primary and foreign keys and joins. Instead, you will simply think “Customers have Orders.” The mechanics of the relational technology is elevated to the object-oriented technology for you.
Conceptual Entity Data Models (EDMs) are stored in eXtensible Markup Language (XML) as a file with an .edmx extension. You have the option of interacting with the EDM using Entity SQL (aka eSQL), a new SQL-like language invented for the Entity Framework. You also have the option of interacting with the EDM using LINQ to Entities. Entity SQL and LINQ to Entities are two different things. (You can read more about Entity SQL in the section “Querying the Entity Data Model with Entity SQL.”) Entity SQL is like SQL without joins. You use a notation like Customers.Orders instead of a join on Customers and Orders, and you are querying against the EDM instead of the database directly. There is a translation that happens from Entity SQL to regular SQL. LINQ to Entities is LINQ against the entities defined by the EDM. In the hierarchy of technologies working out to in, LINQ to Entities is the high-level technology, Entity SQL is an alternate technology branch for the Entity Framework, and beneath each of these is SQL. You have the option of interacting with your data store at any of these levels.
The basic idea is that in any project in Visual Studio 2008, you can add an ADO.NET Entity Data Model item from the Add New Item dialog box. A wizard lets you select the tables, views, and procedures to include and a conceptual wrapper for those selected items is created by Visual Studio and integrated add-ins. The conceptual model is based on the relationships in the selected database elements. Finally, you can use a Visual Studio designer and influence and add more items to the EDM.
When the wizard has finished, you will program against the EDM with LINQ to Entities in the general case and Entity SQL, perhaps, in specialized cases.
In addition, tools that integrate in Visual Studio can be run from a command line. You could also handcraft the EDM XML and wrapper classes, but all of your productivity gains would be lost in the effort. It is better to use the integrated tools when possible.
Last, but not least, it is worth reiterating that you always have the option of programming database interactions the old way. The problem with the way we, as a group, have programmed ADO.NET is that everyone—each team—basically has invented their own data access layer. This roll-your-own approach is time consuming, expensive, and rife with unmitigated challenges. But you do get to choose.
Finding Additional Resources
Sometimes a slide deck—PowerPoint slides—includes the “Resources” slide at the end of the slide deck. Even though the examples for this chapter follow, a resources section is included here because you need to download the separate Entity Framework and tools, and you might want to download some additional samples. A couple of prevailing blogs and a Wikipedia reference are included here to help you fill in any gaps that might have been missed.
Wikipedia
www.wikipedia.org is a great resource. A free, multilingual, open content encyclopedia is based on the concept that interested parties and the reading audience will ensure a balanced editorial process. You might think that Wikipedia—because it allows anyone regardless of their credentials or biases to add content—is subject to misinformation; however, it can also be a fount of great information. At a minimum, it’s a good starting point for finding out what something is. Think of Wikipedia as, at the least, pretty good Cliff Notes.
You can get a pretty good overview of the Entity Framework on Wikipedia at http://en.wikipedia.org/wiki/ADO.NET_Entity_Framework#cite_note-0. This entry includes several additional, related links.
Entity SQL Blog
Microsoft is a big place with a lot of people. By all accounts, it looks like Entity SQL was implemented independently of LINQ to Entities. Whether LINQ wasn’t ready or Entity SQL was someone’s pet project is unclear. And, it’s probably not that important for our purposes. You have two options for the Entity Framework: LINQ to Entities or Entity SQL.
A general concept behind LINQ is that you can use one query language for objects, SQL, XML, and Entities. If you choose to use Entity SQL, you are back to having to learn a new language—in addition to C#, SQL, and LINQ. Entity SQL is something different altogether. However, if you want to know more about Entity SQL, check out the Entity SQL blog at http://blogs.msdn.com/adonet/archive/2007/05/30/entitysql.aspx.
Downloading and Installing the Entity Framework
To try the Entity Framework, you need to do three things. You need to download and install the ADO.NET Entity Framework Beta 3. You need to install a Visual Studio patch, and you need to download and install the ADO.NET Entity Framework Tools CTP 2 (refer to Figure 17.1).
You can, of course, Google for the ADO.NET Entity Framework, or you can go right to http://www.codeplex.com/adonetsamples/, which is a Microsoft site. Microsoft’s downloads section also has links. Download and install the ADO.NET Entity Framework Beta 3 from http://www.microsoft.com/downloads/details.aspx?FamilyId=15DB9989-1621-444D-9B18-D1A04A21B519&displaylang=en. Then, you need to download and install a patch from http://go.microsoft.com/fwlink/?LINKID=104985. (Don’t worry: If you forget this step, the Entity Framework Tools install will remind you to install the patch.) Finally, you can download and install the ADO.NET Entity Framework Tools CTP 2 (for Beta 3) here: http://www.microsoft.com/downloads/details.aspx?FamilyId=D8AE4404-8E05-41FC-94C8-C73D9E238F82&displaylang=en.
Figure 17.1 A download page for the ADO.NET Entity Framework and framework tools.
These downloads are just a few megabytes, so the download and installation process goes very quickly.
Downloading Samples
When you download the Entity Framework Beta 3, a shortcut is placed in the installation folder. This shortcut takes you to the download page for some sample applications. You can also go right to http://www.codeplex.com/adonetsamples/ to get the samples from codeplex.
Go Live Estimate
Quoting from an MSDN blog—http://blogs.msdn.com/adonet/archive/2008/04/09/entity-framework-ado-net-data-services-to-ship-with-vs-2008-sp1-net-3-5-sp1.aspx—Elisa Flasko of Microsoft says that the ADO.NET Entity Framework will ship with Visual Studio 2008 SP1. (On May 13, 2008, a notice was posted for the Visual Studio SP1 beta. So, by the time you have this book in your hands, you should be able to get the SP1 beta, if not the RTM for SP1).
All of these resources should get you started. And, as I write this I have begun discussions for a Teach Yourself ADO.NET Entity Framework Programming book, as I suspect a few other authors have. Look for books on the Entity Framework probably in the last half of 2008. (SP1 beta was available as of June 2008.)
Building a Sample Application Using Vanilla ADO.NET Programming
A data access layer based on ADO.NET 2.0 and a good object model is a modality that works great. I have it on good authority that ADO.NET is not a lock-in or lock-out strategy. You will be able to continue to use ADO.NET 2.0 going forward or you will be able to use ADO.NET 3.0 and the Entity Framework going forward. Using one doesn’t exclude the other.
Generally, an upgrade involves an integration of new capabilities with the old. For example, ADO.NET 2.0 introduced the TransactionScope but many of the other elements such as command, reader, adapter, and DataSet remained. With ADO.NET 3.0, the transition, if you choose it, encompasses a completely different way of programming against the database. How long parallel support for database programming styles will remain is anybody’s guess, but the answer I got was for the “foreseeable future.”
In this part of the chapter, you get a chance to directly compare vanilla ADO.NET 2.0 code against ADO.NET 3.0 (Entity Framework) code, based on beta 3.
Defining a Database for Storing Stock Quotes
To demonstrate, you need a database. You could use the Northwind database or the newer AdventureWorks sample database that shipped with SQL Server 2005. Some of the samples from codeplex.com use the AdventureWorks database, so a new one was created here for you.
For the remaining samples in this chapter, create a new database using Visual Studio’s Server Explorer or SQL Server Management Studio to create a database named StockHistory. To create the tables in Listings 17.1 and 17.2 in Visual Studio, follow these steps:
1. With Visual Studio running, open Server Explorer.
2. Right-click Data Connections and select Create New SQL Server Database.
3. Enter .SQLExpress for the server name, StockHistory for the database name, and then click OK.
4. Expand the new database node, and select the Tables node (in Server Explorer).
6. In the Table designer, add the column names and data types for Listing 17.1.
7. Right-click on the CompanyID definition and select Set Primary Key. (For the PriceHistory table, make QuoteID the primary key.)
8. Close the designer, naming the first table Company.
9. Repeat the steps for Listing 17.2, and name the second table PriceHistory.
Listing 17.1 The Data to Enter in the Table Designer in Visual Studio for the Company Table
Listing 17.2 The Data to Enter in the Table Designer in Visual Studio for the PriceHistory Table
If you are still unsure how to define a table in Visual Studio, the complete script is provided in the section “Reference: The Complete Sample Database Script.” You can type the script in Listing 17.4 into a new query in the SQL Service Management Studio and create everything all at once.
Adding a Stored Procedure for Inserting Quotes
Some developers use ad hoc queries in their C# code. Some use stored procedures in SQL. Using stored procedures supports the division of labor, and the SQL Server engine validates the SQL, so the code in Listing 17.3 employs a stored procedure.
Listing 17.3 contains a stored procedure InsertQuote that checks to see if the Company record exists. If a Company record already exists, that CompanyID is returned and used; if not, a Company record is created. Finally, InsertQuote inserts the new stock quote information into the PriceHistory table. (In practice, factor out the INSERT INTO PriceHistory statement used twice into a separate, stored procedure and use EXEC to invoke it at the various locations in Listing 17.3.)
Listing 17.3 Using Stored Procedures Creates a Nice Division-of-Labor Point and the SQL Engine Verifies the SQL Code
ALTER PROCEDURE dbo.InsertQuote
(
@CompanyName nvarchar(30),
@CompanySymbol nvarchar(6),
@WhenRequested datetime,
@OpeningPrice decimal(18, 2),
@LastTradePrice decimal(18, 2),
@LastTradeTime smalldatetime,
@CurrentPrice decimal(18, 2),
@TodaysHigh decimal(18, 2)
)
AS
BEGIN
DECLARE @ID int
SET @ID = -1
SELECT @ID = CompanyID FROM Company WHERE
CompanySymbol = @CompanySymbol
PRINT @ID
PRINT @OpeningPrice
IF(@ID = -1)
BEGIN
BEGIN TRANSACTION
BEGIN TRY
DECLARE @CompanyID INT
INSERT INTO Company (CompanyName, CompanySymbol)
VALUES (@CompanyName, @CompanySymbol)
SET @CompanyID = @@IDENTITY
INSERT INTO PriceHistory
(CompanyID, WhenRequested, OpeningPrice,
LastTradePrice, LastTradeTime, CurrentPrice, TodaysHigh)
VALUES
(
@CompanyID, @WhenRequested, @OpeningPrice,
@LastTradePrice, @LastTradeTime, @CurrentPrice,
@TodaysHigh
)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR(‘Failed to insert quote %s’, 16, 1, @CompanyName)
ROLLBACK TRANSACTION
END CATCH
END
ELSE
BEGIN
SET @CompanyID = @ID
PRINT ‘ID > −1’
INSERT INTO PriceHistory
(
CompanyID, WhenRequested, OpeningPrice, LastTradePrice,
LastTradeTime, CurrentPrice, TodaysHigh
)
VALUES
(
@CompanyID, @WhenRequested, @OpeningPrice,
@LastTradePrice, @LastTradeTime, @CurrentPrice,
@TodaysHigh
)
END
END
Remember, there is no prohibition against using SQL in your C# code. Consider choosing stored procedures (sprocs, pronounced “sprox”) because it is a nice division-of-labor point and because the SQL Server engine will ensure that your SQL is, at least, syntactically correct. Finally, although the performance differences between dynamic SQL and sprocs are diminishing, you probably get at least some minor performance benefits with sprocs versus dynamic SQL.
Adding a Foreign Key
The primary keys are Company.CompanyID and PriceHistory.QuoteID. The implied relationship is that every company will have a relationship to historical prices, that is, a one-to-many relationship from Company to PriceHistory This relationship is implicit in the appearance of the PriceHistory.CompanyID column.
To express the relationship, add a foreign key to the database (see Figures 17.2 and 17.3). The steps for defining CompanyID as an explicit foreign key are as follows:
Figure 17.2 Foreign key relationships can be added from the table designer in Visual Studio by right-clicking on the table definition, selecting Relationships, and providing the table and column relationships.
Figure 17.3 Figure 17.3 shows the relationship name—use the default—and how the columns in the two tables are equated.
1. In Visual Studio, open Server Explorer.
2. Right-click on PriceHistory and select Open Table Definition.
3. Right-click on PriceHistory’s CompanyID and select Relationships.
4. In the selected relationships input, add a foreign key. (Click the Add button.)
5. Click on the Tables and Columns Specification (ellipsis button).
6. For the Primary Key table, select Company and pick the CompanyID.
7. For the Foreign key table, select PriceHistory and pick the CompanyID.
8. Click OK.
9. Click Close.
Listing 17.4 provides the SQL script for specifying the foreign key constraint.
Listing 17.4 SQL Script for Specifying the Foreign Key Constraint
ALTER TABLE dbo.PriceHistory ADD CONSTRAINT
FK_PriceHistory_Company FOREIGN KEY
(
CompanyID
) REFERENCES dbo.Company
(
CompanyID
) ON UPDATE NO ACTION
ON DELETE NO ACTION
Reference: The Complete Sample Database Script
This section is for reference only. Listing 17.5 contains the complete script for generating elements of the StockHistory database. A technique that will help you deploy and manage databases is to create the script for your database. This is a good learning aid, as well as a means of deploying a database dynamically. Scripting also makes it easier to add the database elements, such as stored procedures, to version control supporting tracking a change history.
Listing 17.5 The Complete Script for the StockHistory Database, Including the Foreign Key Constraint
/****** Object: Table [dbo].[Company] Script Date: 05/14/2008 13:48:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[Company]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[Company](
[CompanyID] [int] IDENTITY(1,1) NOT NULL,
[CompanyName] [nvarchar](30) NOT NULL,
[CompanySymbol] [nvarchar](6) NOT NULL,
CONSTRAINT [PK_Company_1] PRIMARY KEY CLUSTERED
(
[CompanyID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object: Table [dbo].[PriceHistory] Script Date: 05/14/2008 13:48:19
******/
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[PriceHistory]’) AND type in (N’U’))
BEGIN
CREATE TABLE [dbo].[PriceHistory](
[QuoteID] [int] IDENTITY(1,1) NOT NULL,
[CompanyID] [int] NOT NULL,
[WhenRequested] [datetime] NOT NULL,
[OpeningPrice] [decimal](18, 2) NULL,
[LastTradePrice] [decimal](18, 2) NULL,
[LastTradeTime] [smalldatetime] NULL,
[CurrentPrice] [decimal](18, 2) NULL,
[TodaysHigh] [decimal](18, 2) NULL,
CONSTRAINT [PK_PriceHistory] PRIMARY KEY CLUSTERED
(
[QuoteID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
/****** Object: StoredProcedure [dbo].[InsertQuote] Script Date: 05/14/2008 13:48:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N’[dbo].[InsertQuote]’) AND type in (N’P’, N’PC’))
BEGIN
EXEC dbo.sp_executesql @statement = N’CREATE PROCEDURE [dbo].[InsertQuote]
(
@CompanyName nvarchar(30),
@CompanySymbol nvarchar(6),
@WhenRequested datetime,
@OpeningPrice decimal(18, 2),
@LastTradePrice decimal(18, 2),
@LastTradeTime smalldatetime,
@CurrentPrice decimal(18, 2),
@TodaysHigh decimal(18, 2)
)
AS
BEGIN
DECLARE @ID int
SET @ID = -1
SELECT @ID = CompanyID FROM Company WHERE
CompanySymbol = @CompanySymbol
PRINT @ID
PRINT @OpeningPrice
IF(@ID = -1)
BEGIN
BEGIN TRANSACTION
BEGIN TRY
DECLARE @CompanyID INT
INSERT INTO Company (CompanyName, CompanySymbol)
VALUES (@CompanyName, @CompanySymbol)
SET @CompanyID = @@IDENTITY
INSERT INTO PriceHistory
(CompanyID, WhenRequested, OpeningPrice, LastTradePrice,
LastTradeTime, CurrentPrice, TodaysHigh)
VALUES
(
@CompanyID, @WhenRequested, @OpeningPrice,
@LastTradePrice, @LastTradeTime, @CurrentPrice,
@TodaysHigh
)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR(‘’Failed to insert quote %s’’, 16, 1, @CompanyName)
ROLLBACK TRANSACTION
END CATCH
END
ELSE
BEGIN
SET @CompanyID = @ID
PRINT ‘’ID > −1’’
INSERT INTO PriceHistory
(
CompanyID, WhenRequested, OpeningPrice, LastTradePrice,
LastTradeTime, CurrentPrice, TodaysHigh
)
VALUES
(
@CompanyID, @WhenRequested, @OpeningPrice, @LastTradePrice,
@LastTradeTime, @CurrentPrice,
@TodaysHigh
)
END
END’
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id =
OBJECT_ID(N’[dbo].[FK_PriceHistory_Company]’) AND parent_object_id =
OBJECT_ID(N’[dbo].[PriceHistory]’))
ALTER TABLE [dbo].[PriceHistory] WITH CHECK ADD CONSTRAINT [FK_PriceHistory_
→Company] FOREIGN KEY([CompanyID])
REFERENCES [dbo].[Company] ([CompanyID])
Writing Code to Obtain the Stock Quotes and Update the Database
For arguments sake, let’s say that LINQ is leading edge—if not bleeding edge. In keeping with or lagging edge comparison and contrast, Listing 17.6 contains pretty vanilla code for obtaining stock quotes from Yahoo! A description of the code follows Listing 17.6.
Listing 17.6 Obtaining Stock Quotes from Yahoo!, Parsing the Returned Data, and Updating the Database via ADO.NET 2.0
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Data.SqlClient;
using System.Data;
namespace StockQuotesWithEntityFramework
{
class Program
{
// rename to use the other Main
static void _Main(string[] args)
{
string[] stocks = { “MSFT”, “GOOG”, “DELL”, “GM”, “F”, “CSCO”, “INTC”};
WebClient client = new WebClient();
for (int i = 0; i < 25; i++)
{
for (int j = 0; j < stocks.Length; j++)
{
string result = client.DownloadString(
string.Format(“http://download.finance.yahoo.com/d/?s={0}&f=nsolph”,
stocks[j]));
UpdatePriceHistory(result);
Console.WriteLine(result);
System.Threading.Thread.Sleep(100);
}
}
Console.ReadLine();
}
static void UpdatePriceHistory(string result)
{
// get rid of goofy characters
result = result.Replace(“<b>”, ““).Replace(“</b>”, “”);
// split into separate fields; we still need to split
// last trade time and price
string[] data = result.Split(
new char[]{‘,’, ‘“‘, ‘-’}, StringSplitOptions.RemoveEmptyEntries);
if(data.Length != 7) return;
string name = data[0].Trim();
string symbol = data[1].Trim();
string open = data[2].Trim();
string lastTradeTime = data[3].Trim();
string lastTradePrice = data[4].Trim();
string price = data[5].Trim();
string high = data[6].Trim();
UpdatePriceHistory(name, symbol, open,
lastTradeTime, lastTradePrice, price, high);
}
private static void UpdatePriceHistory(string name, string symbol,
string open, string lastTradeTime,
string lastTradePrice, string price, string high)
{
UpdatePriceHistory(name, symbol, ToDecimal(open),
ToDateTime(lastTradeTime), ToDecimal(lastTradePrice),
ToDecimal(price), ToDecimal(high));
}
private static void UpdatePriceHistory(string name, string symbol,
decimal open, DateTime lastTradeTime,
decimal lastTradePrice, decimal price, decimal high)
{
string connectionString =
“Data Source=.\SQLExpress;Initial Catalog=StockHistory;” +
“Integrated Security=True;Pooling=False”;
using(SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand(“InsertQuote”, connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue(“@CompanyName”, name);
command.Parameters.AddWithValue(“@CompanySymbol”, symbol);
command.Parameters.AddWithValue(“@WhenRequested”, DateTime.Now);
SqlParameter p1 = command.Parameters.AddWithValue(“@OpeningPrice”, open);
p1.DbType = DbType.Decimal;
p1.Precision = 18;
p1.Scale = 2;
SqlParameter p2 = command.Parameters.AddWithValue(“@LastTradePrice”,
→lastTradePrice);
p2.DbType = DbType.Decimal;
p2.Precision = 18;
p2.Scale = 2;
command.Parameters.AddWithValue(“@LastTradeTime”, lastTradeTime);
SqlParameter p3 = command.Parameters.AddWithValue(“@CurrentPrice”, price);
p3.DbType = DbType.Decimal;
p3.Precision = 18;
p3.Scale = 2;
SqlParameter p4 = command.Parameters.AddWithValue(“@TodaysHigh”, high);
p4.DbType = DbType.Decimal;
p4.Precision = 18;
p4.Scale = 2;
command.ExecuteNonQuery();
}
}
private static decimal ToDecimal(string val)
{
return Convert.ToDecimal(val);
}
private static DateTime ToDateTime(string val)
{
return Convert.ToDateTime(val);
}
}
}
The demo application is a console application with no intermediate objects. The data is read from Yahoo! and sent right to the database. The Main function defines the stocks to fetch. The WebClient class is a simple class that lets you send a URL and accepts the return value as a string. In Main, an outer loop makes 25 requests, and an inner loop makes a request for each stock in the array. In the example, 175 total requests are sent to Yahoo! The formatted URL query requests the named stock by symbol (for example, MSFT for Microsoft) and the second parameter f indicates that you would like the name, symbol, opening price, last price, current price, and today’s high. (An intuitive guess or experimentation will help you figure out these format characters.) Finally, UpdatePriceHistory is called and the results are sent to the console. (The artificial latency of 100 milliseconds was added to stagger the times, but it is unlikely prices will change that fast.)
UpdatePriceHistory replaces <b> and </b> (bold tags) from the string returned from Yahoo! The second statement splits the results by comma, quote, or hyphen. (The last price includes a time and price value.) Next, each string is assigned to a named variable indicating the meaning of the data. Only rudimentary error check is used.
Two overloaded UpdatePriceHistory methods are provided. The first accepts the string variables and the second accepts these values converted to the desired type. This is a stylistic way of separating the chunks of code. The final UpdatePriceHistory method uses ADO.NET code to connect to the database, populate a SqlCommand object with the parameters and desired stored procedure, and the query is run. Two superfluous helper methods perform string conversion functions.
That’s all there is to it.
Programming with the Entity Framework
In the demo using ADO.NET 2.0, you got some stock quotes from Yahoo! and updated the database. This section starts with creating an Entity Data Model for the StockHistory database and querying the database through the conceptual data model—the Entity Framework code.
The querying capability demonstrated uses Entity SQL and LINQ to Entities. After you have created the EDM and queried it, you will get a chance to define code that uses a pure LINQ and Entity Framework approach to request the quotes and update the database.
Creating the Entity Data Model
Entity Data Models are composed of source code and XML files. The XML describes the EDM and the source code is the code-generated classes that represent the EDM. You probably could hand code these elements, but you would probably lose most, if not all, of the initial productivity gains by doing so.
To define the EDM, use the wizard.
To define an EDM in any Visual Studio 2008 project, select Project, Add New Item. From the Add New Item dialog box, select the ADO.NET Entity Data Model item. This starts the wizard. For the following steps, begin by naming the .edmx file StockHistoryModel.edmx.
1.Add the ADO.NET Entity Data Model item from the Add New Item dialog box.
2. Name it something like StockHistoryModel; this will start a wizard.
3. On the Choose Model Contents page, select Generate from Database.
4. On the Choose Your Data Connection page, select the StockHistory database.
5. Use the default name of StockHistoryEntities. Click Next.
6. Choose Your Data Objects. Select everything.
7. On the Model Namespace page, leave the default (StockHistoryModel).
8. Click Finish.
After you click finish the wizard fills out the details of the .edmx file and code generates the source code representing the EDM. After you click Finish, the EDM designer view is displayed, showing the entity elements and expressed relationships.
Figure 17.4 If you forgot to define the foreign key, the diagram shows the Company and PriceHistory tables without the implied relationship; otherwise, the relationship (visually expressed as a line between the two tables) is shown in the designer.
Adding an Association
If you forgot to define a foreign key, the mapping association is not generated by the wizard. You can define associations after the fact, too. To add an association manually (for the Company and PriceHistory tables), follow these steps:
1. Right-click on the Customer EntityType and select Add, Association.
2. Add the association where one company has many PriceHistory(s) (see Figure 17.5).
3. Click OK. The model designer should show the association now.
4. In the Mapping Details View for the association, click Add a Table or View and select PriceHistory (see Figure 17.6).
Querying the Entity Data Model with Entity SQL
Thus far, all that you have done differently is run the ADO.NET Entity Data Model wizard implicitly by adding an Entity Data Model item from the Add New Item dialog box. That’s pretty easy.
After you have the EDM, you can begin querying the data model with Entity SQL or LINQ to Entities right away.
Figure 17.5 The New Association dialog box supports adding descriptions after the wizard has run; here, a one-to-many relationship is expressed for Company and PriceHistory.
Figure 17.6 The CompanyPriceHistory association described, as depicted in Figure 17.5.
Entity SQL is a whole new language. Sometimes, things like Entity SQL are invented in conjunction with technologies like LINQ to Entities and some overlap in capability results. However, it is worth noting that Entity SQL is a big thing and there isn’t enough time or space for a whole tutorial.
In general, Entity SQL is like SQL, but it is based on natural relationships expressed by the Entity Data Model (EDM) instead of joins. Hence, the queries you write refer to the relationships as expressed in the EDM, such as Company has a PriceHistory and the dot-operator is used instead of a join. There is a lot more to Entity SQL than that. (Check the resources section earlier for links to Entity SQL information.)
The code in Listing 17.5 contains an inline Entity SQL query. One way to use Entity SQL is to create an instance of an EntityConnection initializing it with a connection string. (The Entity Data Model Wizard added a connection string in the correct format to an App.config file, so use the ConfigurationManager to request that.) Next, open the EntityConnection, create an EntityCommand, and use a DBDataReader to access the data. (Look familiar? It’s very similar to the pattern for using ADO.NET and regular SQL.)
Notice that the Entity SQL command has a SELECT, FROM, and WHERE. However, also notice that the class name StockHistoryEntities is used and the Company entity is requested from the Company property. The AS-clause and the IN operator are all pretty similar to SQL though.
The DBDataReader is used like a SQLDataReader and both actually implement the IDataReader interface from System.Data. A difference is that a SqlDataReader inherits from DBDataReader. If you know how to use a SqlDataReader, then using a DbDataReader isn’t that hard; in fact, it’s pretty similar (see Listing 17.7).
Listing 17.7 Using Entity SQL via an EntityConnection and EntityCommand Follows a Similar Pattern as Does Using SQL and ADO.NET Sql classes (like SqlConnection).
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.EntityClient;
using System.Data.Common;
using System.Data;
using System.Configuration;
using StockHistoryModel;
namespace StockQuotesWithEntityFramework
{
class Program2
{
static void Main(string[] args)
{
string connectionString =
ConfigurationManager.ConnectionStrings[“StockHistoryEntities”].ConnectionString;
EntityConnection connection = new EntityConnection(connectionString);
connection.Open(); EntityCommand command = new EntityCommand(
“SELECT company.CompanyID, company.CompanyName “ +
“FROM StockHistoryEntities.Company as company “ +
“WHERE company.CompanySymbol IN {‘MSFT’, ‘F’}”, connection);
DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
Console.WriteLine(“Company name: {0}”, reader.GetValue(1));
}
Console.ReadLine();
}
}
}
For a complete reference on Entity SQL, refer to http://msdn.microsoft.com/en-us/library/bb387118.aspx. This reference contains information about canonical functions, relationship navigation, reference, arithmetic, comparison, logical, and member operators, string concatenation, case expressions type operators, query expressions, comments, namespaces, and an Entity SQL quick reference.
Querying the Entity Data Model with LINQ to Entities
As you will see in Listing 17.8, LINQ to Entities is even easier. All you need to do to query the EDM with LINQ to Entities is create an instance of the StockHistoryEntities class and write the LINQ query.
If you check the generated code, you will see that the StockHistoryEntities class inherits from the System.Data.Objects.ObjectContext class. ObjectContext is to LINQ to Entities what DataContext is to LINQ to SQL. An ObjectContext contains the plumbing like an EntityConnection for connecting to the underlying data source. Inside of the custom ObjectContext, individual table entities are accessed through properties. These properties are instances of ObjectQuery<T>, for example ObjectQuery<Company>. An ObjectQuery<T> is to LINQ to Entities what a Table<T> is to LINQ to SQL. (Are you starting to detect a design pattern here?)
The ObjectQuery supports IEnumerable<T> and IQueryable<T>, which are essential ingredients for supporting LINQ. ObjectQuery is defined in the System.Data.Objects name-space in the System.Data.Entity.dll assembly. Listing 17.8 demonstrates just how easy it is to employ the LINQ skills learned thus far to query the EDM using LINQ.
Listing 17.8 Using LINQ to Entities to Query the EDM Directly; All of the Underlying SQL Plumbing and Logical Model Elements Are Concealed
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using StockHistoryModel;
namespace StockQuotesWithEntityFramework
{
class Program1
{
// rename to use the other main
static void Main(string[] agrs)
{
StockHistoryEntities stockHistory = new StockHistoryEntities();
var quotes = from quote in stockHistory.PriceHistory
orderby quote.Company.CompanySymbol
select new {Symbol=quote.Company.CompanySymbol,
LastPrice=quote.LastTradePrice, Time=quote.LastTradeTime};
Array.ForEach(quotes.ToArray(),
s => Console.WriteLine(s));
Console.ReadLine();
}
}
}
What remains to do is to look at how you can use LINQ to Entities to update the data via the conceptual model (the EDM) and LINQ. LINQ to Entities supports updates, too. In the final section of this chapter, updates are demonstrated as well as a rewrite of the original demo, all using LINQ.
Doing It All with LINQ
In this part, the UpdatePriceHistory piece from Listing 17.6 was rewritten using almost all LINQ-related technologies. Getting the stock quotes is the same, and basic parsing of the return string is pretty much the same. After that the code diverges. In the example LINQ to XML and functional construction is used to build an XML document from the returned data. (You will learn more about LINQ to XML in the final part, Part IV, of this book, so there is just an introduction here.)
Selecting companies uses LINQ to Entities—and there is an alternate chunk that uses the ADO.NET Entity classes like ObjectQuery—and the stock quotes are added to the database using the EDM and LINQ to Entities. The code is quite different from Listing 17.6, and the bits are broken into chunks of named methods to help you identify what each chunk is doing. To complete the examples, you can use the same EDM created at the beginning of the “Programming with the Entity Framework” section.
The first half of Listing 17.9 includes the using statements and the Main function. These didn’t need to change. This part of the code uses a WebClient to send a request to Yahoo! and reads the returned string. The changes begin in Listing 17.9’s UpdatePriceHistory method.
Listing 17.9UpdatePriceHistory, Modified to Use LINQ to XML and LINQ to Entities to Load the Database with Stock Quotes
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using System.Data.SqlClient;
using System.Data;
using System.Xml.Linq;
using StockHistoryModel;
using System.Data.Objects;
namespace StockQuotesWithEntityFramework
{
class Program3
{
static void Main(string[] args)
{
string[] stocks = { “MSFT”, “GOOG”, “DELL”, “GM”, “F”, “CSCO”, “INTC”};
WebClient client = new WebClient();
for (int i = 0; i < 5; i++)
{
for (int j = 0; j < stocks.Length; j++)
{
string result = client.DownloadString(
string.Format(“http://download.finance.yahoo.com/d/?s={0}&f=nsolph”, stocks[j]));
UpdatePriceHistory(result);
Console.WriteLine(result);
System.Threading.Thread.Sleep(100);
}
}
Console.ReadLine();
}
/// <summary>
/// Use LINQ to XML to get data
/// Use LINQ to Entities to write data to database
/// </summary>
/// <param name=”result”></param>
static void UpdatePriceHistory(string result)
{
XElement xml = CreateXMLElement(result);
Console.WriteLine(xml.ToString());
StockHistoryEntities entities = new StockHistoryEntities();
PriceHistory quote = CreatePriceHistory(xml);
string name = xml.Element(“Name”).Value;
string symbol = xml.Element(“Symbol”).Value;
#if (!USE_CLASSES)
var results = from c in entities.Company
where c.CompanySymbol == symbol
select c;
#else
ObjectParameter param = new ObjectParameter(“symbol”, symbol);
ObjectQuery<Company> results = entities.Company.Where(“it.CompanySymbol = @symbol”, param);
#endif
Company company;
if (results.Count() == 0)
{
company = new Company();
company.CompanyName = name;
company.CompanySymbol = symbol;
entities.AddToCompany(company);
}
else
{
company = results.First();
}
company.PriceHistory.Add(quote);
entities.SaveChanges(true);
}
static PriceHistory CreatePriceHistory(XElement xml)
{
Func<XElement, decimal?> toDecimal =
val =>
{
decimal? temp = (decimal?)val;
return Math.Round((decimal)val, 2, MidpointRounding.ToEven);
};
PriceHistory quote = new PriceHistory();
quote.OpeningPrice = toDecimal(xml.Element(“Open”));
quote.WhenRequested = DateTime.Now;
quote.LastTradeTime = Convert.ToDateTime(xml.Element(“LastTradeTime”).Value);
quote.LastTradePrice = toDecimal(xml.Element(“LastTradePrice”));
quote.CurrentPrice = toDecimal(xml.Element(“Price”));
quote.TodaysHigh = toDecimal(xml.Element(“High”));
return quote;
}
static XElement CreateXMLElement(string result)
{
// remove unneeded HTML tags and split into fields
result = result.Replace(“<b>”, ““).Replace(“</b>”, ““);
string[] data = result.Split(
new char[] { ‘,’, ‘“‘, ‘-’ }, StringSplitOptions.RemoveEmptyEntries);
// use functional construction to create XML document
return new XElement(“Quote”,
new XElement(“Name”, data[0].Trim()),
new XElement(“Symbol”, data[1].Trim()),
new XElement(“Open”, data[2].Trim()),
new XElement(“LastTradeTime”, data[3].Trim()),
new XElement(“LastTradePrice”, data[4].Trim()),
new XElement(“Price”, data[5].Trim()),
new XElement(“High”, data[6].Trim())
);
}
}
}
The revised code is composed of three new functions: UpdatePriceHistory, CreatePriceHistory, and CreateXMLElement. UpdatePriceHistory orchestrates calls to the other two methods, CreatePriceHistory and CreateXMLElement.
UpdatePriceHistory calls CreateXMLElement first, returning an XElement. XElement is introduced as part of LINQ to XML. CreateXMLElement accepts a string and uses functional construction to create an XML document in the form of an XElement object. Functional construction is the process of creating XML documents via method calls. You will learn a lot more about this in Part IV.
The next thing UpdatePriceHistory does is create an instance of the EDM wrapper class, StockHistoryEntities, and calls CreatePriceHistory to convert the XElement object into a PriceHistory object. CreatePriceHistory, for the most part, copies values out of the XML (XElement) object and moves them to a PriceHistory object. The generic delegate Func<T, TResult> is used to trim up some decimal numbers, but it isn’t necessary. (You could simply permit more decimal places in the database in case some of the stock quote values have more than two decimal places.)
Next, the UpdatePriceHistory method gets the Company values from the XML object (XElement). After that, two options are shown. The first option uses LINQ to Entities to see if the company already exists. The second option uses Entity SQL and ADO.NET Entity Framework classes—including ObjectParameter and ObjectQuery—to see if the company object already exists. You only need to use one of the styles; the LINQ version is a little easier.
If the Company doesn’t exist, a new one is created and added to the StockHistoryEntities collection of companies. (Basically, you are prepping to insert a Company row.) Otherwise, if the company exists, that instance is used. In both cases, the PriceHistory is added to the Company, establishing the underlying relationship and the data is saved. By calling SaveChanges(true), the change flag in the EDM is reset after the save. Without this parameter, you need to call AcceptAllChanges.
Listing 17.9 looks about as equally long as UpdatePriceHistory in Listing 17.6. However, in Listing 17.9, functions were used to segment the various pieces, and some extra techniques were demonstrated for fun. A big difference between UpdatePriceHistory versions is that Listing 17.9 offers a lot more flexibility and power. For example, you have an XML document (the XElement) object, which can be used in a wide variety of scenarios, including presentable data itself. Listing 17.9 also lets you deal with the conceptual model—Companys have a PriceHistory—and not spend any time on the underlying database plumbing. The result is that you spend your time differently; instead of focusing on stored procedures, joins, logical models, and ADO.NET, your efforts—in Listing 17.9—all focus on the Company and PriceHistory objects. The result is better focus and better productivity.
Summary
The ADO.NET Entity Framework is part of ADO.NET 3.0. It’s still in beta but it is far enough along now that shipping should be imminent—according to Microsoft sources, it will ship with Visual Studio 2008 SP1.
Although you will still have the option of programming in the previous ADO.NET 2.0 style or the ADO.NET 3.0 style, Entity Framework programming will allow you to spend more of your time working on the conceptual business problem.
In previous versions of ADO.NET, C# programmers had to focus on SQL, stored procedures, joins, keys, ADO.NET, and the business problem using several different technologies—like SQL and C#. The ADO.NET Entity Framework will insulate you from nuts-and-bolts database code and let you spend your time on more natural relationships like customers and orders or companies and stock prices. And, you can use one core technology—C#.
The ADO.NET Entity Framework did introduce Entity SQL, but if you use LINQ, you can query SQL, Entities, XML, and objects all with LINQ/C# code. Of course, if you like being a SQL guru too, that’s OK, but now everyone doesn’t have to be.
Adam Smith wrote in his eighteenth-century book, The Wealth of Nations, that improved productivity comes with specialization. This is an insightful economic trend that pervades today’s thinking—check out five-term Federal Reserve Chairman Alan Greenspan’s book, The Age or Turbulence: Adventures in a New World—and specialization will help you and your team be more productive too. Again, technology is fun and so too is knowing SQL, C#, JavaScript, ASP.NET, and a lot of other cool technologies, but the ADO.NET Entity Framework does facilitate some specialization and the result can be enhanced productivity.
If you want productivity, the ADO.NET Entity Framework and working at the conceptual level will help. And, if you just want to do something cool, well ADO.NET 3.0 is way cool, too!