Skip to main content

Data Access Practices Using Microsoft .Net: A Nerdly Comparison

Nick Muhonen
March 2010


Abstract: This article explores the variety of data access choices currently available for the Microsoft .NET Framework. To detail the discussion, a comparison of techniques using the Nerd Dinner sample application is presented for the following data access methods: connected data access with ADO.NET disconnected data access with ADO.NET, LINQ to SQL, and LINQ to Entity Framework.

Contents

Data Access in Microsoft .NET Today

An application’s ability to retrieve and modify data from a backend persistent store drives some of the most critical planning in application design. To enable this, web and N tier enterprise applications have relied mainly on relational database management systems like Microsoft SQL Server to serve as this store. In doing so, greater concurrency, performance, and management ease are achieved when interacting with persisted application data. This practice remains well established, and unless some unforeseen change occurs in this space, it will continue to be a significant way of working with data for some time in the future.

Most developers working with relational data in .NET have relied on the ADO.NET data access APIs. When introduced with the 1.0 version of the .NET Framework, ADO.NET provided a clean and elegant connected data access model, called DataReader, in which code manages data while maintaining a live connection to the data source. Along with this connected API, ADO.NET also introduced a new disconnected data access framework, called DataSets, a cached result set used for interacting with retrieved data from the database.

Since its initial release, Microsoft .NET continues to evolve with emerging trends in data access frameworks. When Language Integrated Query (LINQ) was introduced to the Microsoft .NET family programming languages, another data access framework was made available to .NET, LINQ to SQL. Responding to the growing trend of object relational mapping, LINQ to SQL provided an object relational data access suite tightly integrated syntactically with C# and Visual Basic, allowing greater ease between object oriented programming languages and SQL Server relational database systems. LINQ to Entity Framework, Microsoft .NET’s latest data access framework addition, evolves available object relational capabilities even further. By providing layers of abstraction between the conceptual model, the physical database, and the mapping between the two, LINQ to Entity Framework allows high flexibility with heterogeneous relational database platform ORM mapping.

In doing so a question arises: which of these many data access methods will work best for my application? Through a detailed comparison of the aforementioned data access methods, that is specifically what we aim to answer in this article.

A Comparison Platform for Data Access: Nerd Dinner

Before we get into the comparison, let’s have a look with the application used for this article: Nerd Dinner. This web application, created by Scott Hanselman, Scott Guthrie, and Rob Connery, serves as an application for “Code Intellectuals” (or nerds) to plan dining-related encounters. Users authenticated with the application can plan dinners on the site by providing details of the event. Visiting unauthenticated users can access a search interface based on Virtual Earth to view dinners and events near their location. This article is based on an earlier build of the NerdDinner application, which can be found on the open source codeplex site at http://nerddinner.codeplex.com/releases/view/45621.

The reasoning driving the choice for Nerd Dinner is specifically its usage of the Microsoft MVC framework and overall simplicity. In doing so, clear isolation of the data access methods are provided through MVC’s strong separation of concerns, allowing us to focus clearly on data access for the content of this article. We have used this application’s source as a template for doing the comparison, creating specific versions for each data access method we are comparing.

MVC in a Nutshell

For those unfamiliar, Microsoft’s ASP.NET MVC provides a well-organized, flexible, and highly testable framework for web applications. It does so by defining a clear separation of concerns between UI control flow, business logic, and end user rendering:

  1. All requests, based on client-sent information (requested URL path, query string, form variables, etc.) are directed to a Controller object, designed to receive this interaction with the browser client.
  2. From this point the Controller interacts with the Model to perform server-side business logic processing. The model represents logical business logic operations irrespective of what type of user interface operation is requesting the interaction.
  3. After the requests are processed, the controller then decides on a View to render the resulting output data from this interaction. These views are usually tailored to the specific requesting client, such as browser or a smart-phone, to give an optimal experience in parity with what the specific client can offer.

Models, Views, and Controllers can be interchanged with other implementations, allowing for flexibility in client applications using the framework as well as separation of concerns for automated unit testing. More information detailing Microsoft MVC can be found at http://www.asp.net/mvc/.

While the Nerd Dinner application uses all aspects of ASP.NET MVC to provide dinner scheduling functionality, this article focuses mainly on the Model. Used as a data access layer for dinner scheduling, the Model for Nerd Dinner’s interface-driven design creates a clear contract between itself and the requesting application controllers used for routing user interface interaction. This design provides great flexibility in examining the .NET Framework’s various data access methods by allowing comparison implementations for each as concrete implementation classes, which we will discuss in detail later.

Nerd Dinner Model

The MVC Model used in the Nerd Dinner application consists on a straightforward data access layer. Although ASP.NET MVC models can be used for more complex business logic or web service interaction, Nerd Dinner’s model provides mostly simple, direct database access operations. Its design can be broken down into the following elements:

  1. An interface defining the contractual operations in the model called IDinnerRepository.
  2. NerdDinner, a SQL Server Database which serves as the physical data store this application.
  3. A data access class implementing the IDinnerRepository interface, designed to access the NerdDinner SQL Server database.
IDinnerRepository interface

The IDinnerRepository interface provides the Nerd Dinner site controllers a persistent storage data access contract. With MVC Controller objects responsible for dinner planning on this site, this interface is used as their API to the data store, providing a logical model for all data access operations involving dinner planning. In this article, we will be using variants of the IDinnerRepository interface, each designed to support the underlying data access method in the most practical manner. Each version of the interface will support the following general operations:

  • FindAllDinners: Returns all dinners in the data store.
  • FindByLocation: Returns all dinners found within a specified radius of a latitude/longitude coordinate.
  • FindUpcomingDinners: Returns all dinners in the data store that have not yet occurred.
  • GetDinner: Returns the specific dinner associated with a numeric unique identifier.
  • AddDinner: Adds a dinner creation request to a buffer of data store change requests.
  • AddRsvp: Adds an RSVP from an interested user to an already created dinner.
  • DeleteDinner: Adds a dinner deletion request to a buffer of data store change requests.
NerdDinner NQL Server Database

Nerd Dinner uses the NerdDinner.mdf SQL Server database file for its backend store, stored locally in the Nerd Dinner site’s App_Data folder.  Its schema consists of a simple two-table design used for storing events:

  1. Dinners table: The actual event being stored, containing time, place, and coordination information as well as an automatically generated numeric primary key.
  2. RSVP table: A child table of Dinners, containing the name of a Person who may attend the dinner, as well as both an automatically generated numeric primary key and foreign key to the related parent Dinner table record.

Along with these two tables, the NerdDinner  schema also provides a couple of T-SQL functions to assist in location based searches:

1.      NearestDinners function: returns a table of all dinners that are within 100 miles of a particular latitude/longitude location passed as parameters.

ALTER FUNCTION[dbo].[NearestDinners]
       (
       @lat real,
       @long real
       )
RETURNS TABLE

2.      DistanceBetween Function: returns the measured distance between two latitude/longitude coordinates in miles.

ALTER FUNCTION[dbo].[DistanceBetween] (@Lat1 as real,
                @Long1 as real, @Lat2 as real, @Long2 as real)
RETURNS real

Now, on with the comparison.

Connected Data Access with ADO.NET

.NET Connected data access in ADO.NET represents the most direct and low level method available to developers for working with relational data. Existing since the initial release of the .NET framework, this API allows a high level of control over database interaction. Furthermore, all other data access methods discussed in the article rely internally on this .NET API when working with a Microsoft SQL Server data source, making it a great place to begin our comparison. 

We gain a greater understanding of connected data access by focusing on its two most intrinsic objects: the Connection object and the Command object. The Connection object provides the means to define location and manage a communication channel with a data source, i.e. where it’s located, what credentials it is being passed to communicate, timeout duration, and so on. With this communication channel established, the Command object then informs the data source what it would like to accomplish. This results in either the desired action, or if things go awry, some sort of exception indicating an error. The dance between these two objects in execution generally moves along the following steps:

  1. Create a connection object (i.e. a specific database found on specific database server).
  2. Create a command object, indicating the desired work to be performed on that database (i.e. fetch a bunch of rows, delete a few rows, etc.).
  3. Associate the connection object with the Command object.
  4. Open the connection, setting up a communication session to the data source.
  5. Execute the command, which performs the sends desired action over the Connection to the database server.
  6. Receive the results from the server.
  7. Close the connection, ending the communication session.

The following C# code represents this pattern:

// Create the database connection
using(var connection = new SqlConnection("Data Source = ..."))
{
    // Create the database operation
    var command = new SqlCommand("select * from Foo",connection);
    // Open the connection
    connection.Open();
    // Execute a command
    var dr = command.ExecuteReader();

    // Do a little work here...
    while (dr.Read())
    {
        var currentID = (int)dr["ID"];
        var currentName = (string)dr["Name"];
        ... // Do something with the data
    }
}

Although we are only going to discuss its merits in context of the NerdDinner application, the flexibility of connected data access goes well beyond the scope of this article. More details on this API’s specifics can be found at MSDN library online: http://msdn.microsoft.com/en-us/library/ff361664(VS.100).aspx

Defining the IDinnerRepository Contract

The interface definition for IDinnerRepository used for connected data access allows interaction with the underlying database as required by the Nerd Dinner site. It is comprised of a contract allowing interaction with the data source, as well as two strongly-typed data objects representing Dinner and RSVP data. We begin building our model by first hand-coding its interface definition and data classes:

//Contract for the NerdDinner DinnerRepository model
public interface IDinnerRepository
{
    IEnumerable<Dinner> FindAllDinners();
    IEnumerable<Dinner> FindByLocation(float latitude, float longitude);
    IEnumerable<Dinner> FindUpcomingDinners();
    Dinner GetDinner(int id);
    void AddDinner(Dinner dinner);
    void UpdateDinner(Dinner dinner);
    void DeleteDinner(int id);
    void AddDinnerRsvp(int dinnerID, RSVP rsvp);
}
//Data object representing dinner data
public partial class Dinner
{
    public Dinner() { RSVPs = newList<RSVP>(); }
    public int DinnerID { get; set; }
    public string Title { get; set; }
    public DateTime EventDate { get; set; }
    public string Description { get; set; }
    public string HostedBy { get; set; }
    public string ContactPhone { get; set; }
    public string Address { get; set; }
    public string Country { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
    public List<RSVP> RSVPs { get; set; }
}
//Data object representing
public class RSVP
{
    public int RsvpID { get; set; }
    public int DinnerID { get; set; }
    public string AttendeeName { get; set; }
}

IDinnerRepository is fairly straightforward, representing the NerdDinner site’s data access layer with the defined Dinner and RSVP objects as representations of data. The Dinner and RSVP data class definitions also remain simple, representing data objects for the RSVP and Dinner tables.

Note: When working with MVC, it’s a good idea to provide an interface for the model instead of just a class implementation. MVC’s excellent ability to separate concerns allows one to create mock of the model to unit test controller functionality without actually connecting to a live database during the test.

Creating the Implementation for IDinnerRepository

We continue fleshing out our model by providing an implementation of the IDinnerRepository interface with the basics of the DinnerRepository class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace NerdDinner.Models
{
    public class DinnerRepository : IDinnerRepository
    {
        private string connectionString;
        public DinnerRepository()
        {
            const string connectionStringKey = "NerdDinnerConnectionString";
            this.connectionString = ConfigurationManager
                .ConnectionStrings[connectionStringKey].ConnectionString;
        }
        ...
    }
}

This plumbing code provides a constructor that retrieves a connection string from the configuration file and stores it in a local field called, cleverly enough, connectionString. This will be used later to specify our database location and authentication credentials to the connection objects in our code. With this in place, we have enough to move onto DinnerRepository’s functional implementation of the IDinnerRepository interface.

Data Retrieval Implementation

We introduce DinnerRepository’s data retrieval functionality by providing implementations of IDinnerRepository’s FindAllDinners, FindByLocation, FindUpcomingDinners, and GetDinner methods. FindByLocation’s implementation provides a good representation of four retrieve methods internals. Let’s take a look:

public IEnumerable<Dinner> FindByLocation(float latitude, float longitude)
{
   
    using (var connection = new SqlConnection(this.connectionString))
    {
        var commandText =
        @"
            select d.DinnerID, d.Title, d.EventDate, d.[Description], d.HostedBy,
                d.ContactPhone, d.[Address], d.Country, d.Latitude, d.Longitude 
            from Dinners d
            inner join dbo.NearestDinners(@Latitude,@Longitude) nd on
                d.DinnerID = nd.DinnerID
            where @CurrentDate &lt;= d.EventDate
            order by d.DinnerID
            select r.RsvpID, r.DinnerID, r.AttendeeName from RSVP r
            inner join Dinners d on
              d.DinnerID = r.DinnerID
            inner join dbo.NearestDinners(@Latitude,@Longitude) nd on
                d.DinnerID = nd.DinnerID
            where @CurrentDate &lt;= d.EventDate
            order by r.DinnerID, r.RsvpID
        ";
        var command = new SqlCommand(commandText, connection);
        var parameters = new[]{
                new SqlParameter{ParameterName = "Latitude", DbType = DbType.Double,
                     Value = latitude},
                new SqlParameter{ParameterName = "Longitude", DbType = DbType.Double,
                     Value = longitude},
                new SqlParameter{ParameterName = "CurrentDate", DbType = DbType.Date,
                     Value = DateTime.Now}};
        command.Parameters.AddRange(parameters);   
        connection.Open();
        return GetDinnersFromCommand(command);
    }
}

On review of the method, we can break down its execution into the following steps:

  1. Create a connection object using the connectionString member field. Doing so in a using statement ensures proper connection termination whether or not an error occurs.
  2. Create a command object using the previously created connection. We will also include an ad hoc T-SQL statement to retrieve two result sets via a filter based on location and time created:
    1. a. One with data used to create Dinner objects.
    2. b. One with data used to create its related RSVP objects.
    We perform this in one call to reduce communication traffic between the calling application code and target database server.
  3. Open the connection, letting the database know we are about to ask it to do some work.
  4. Call GetDinnersFromCommand helper method with the command object, which does tells the database the work needed to be done, and returns Dinner objects based on the query results.
  5. Exit the using block, which effectively closes the connection.

Interestingly enough, the other retrieve methods, FindAllDinners, FindUpcomingDinners, and GetDinner, follow an almost identical setup, varying only on the specifics in the SQL query and parameters passed to the query. This makes code more maintainable, as well as allowing use of the GetDinnersFromCommand helper method to execute the bulk of the work.

To finish our exploration of data retrieval, let’s look into this helper method:

private List<Dinner> GetDinnersFromCommand(SqlCommand command)
{
    var returnDinners = new List<Dinner>();
    using (var reader = command.ExecuteReader())
    {
        //Project first result set into a collection of Dinner Objects
        while (reader.Read())
        {
            var dinner = newDinner()
            {
                DinnerID = (int)reader["DinnerID"],
                Title = (string)reader["Title"],
                Description = (string)reader["Description"],
                Address = (string)reader["Address"],
                ContactPhone = (string)reader["ContactPhone"],
                Country = (string)reader["Country"],
                HostedBy = (string)reader["HostedBy"],
                EventDate = (DateTime)reader["EventDate"],
                Latitude = (double)reader["Latitude"],
                Longitude = (double)reader["Longitude"]
            };
            returnDinners.Add(dinner);
        }
        //Project second result set into Rsvp objects. Associate them with
        //their parent dinner object.
        reader.NextResult();
        int? dinnerID = null;
        Dinner parentDinner = null;
        while (reader.Read())
        {
            var rsvp = newRSVP()
            {
                RsvpID = (int)reader["RsvpID"],
                DinnerID = (int)reader["DinnerID"],
                AttendeeName = (string)reader["AttendeeName"]
            };
            if (dinnerID != rsvp.DinnerID)
            {
                dinnerID = rsvp.DinnerID;
                parentDinner = returnDinners
                  .Where(dinner => dinner.DinnerID == dinnerID)
                  .First();
            }
            parentDinner.RSVPs.Add(rsvp);
        }
    }
    return returnDinners;
}

Execution of GetDinnersFromCommand can be broken down into the following steps:

  1. Execute the command object passed as parameter,which should be designed to retrieve a data reader with two result sets:
    1. a. The first with Dinners table data.
    2. b. The second with child data from the RSVP table.
  2. Iterate through the first result set containing Dinners table data, project its contents to Dinner objects, and add the results to a list. We will use this list later to add results.
  3. Move the DataReader to the next result set, now containing the RSVP table data. This object will be used to retrieve the data row by row as it being fetched from the database.
  4. Iterate through the second result set containing RSVP table data and project its contents to RSVP objects. After the objects are created, we then use a LINQ query from the Dinners object list to find its parent object. Once doing so, we add the child object to the parent’s RSVPs collection.
  5. Exit the using block, effectively cleaning up the DataReader object.
  6. Return the serialized Dinner objects, populated with their RSVP children.

After implementing the other three data retrieval methods in similar fashion, we are ready to move on to data modification.

Data Modification Implementation

Data modification code is introduced of through implementations IDinnerRepository’s AddDinner, UpdateDinner, DeleteDinner methods in the DinnerRepository class. Like the methods used for data retrieval, we are going to stick to an established pattern to allow for greater understanding and maintainability. Let’s take a look on how we would implement UpdateDinner:

public void UpdateDinner(Dinner dinner)
{
    using (var connection = new SqlConnection(this.connectionString))
    {
        var commandText = @"
            update Dinners
            set Title = @Title,
                Description = @Description,
                Address = @Address,
                Country = @Country,
                ContactPhone = @ContactPhone,
                Latitude = @Latitude,
                Longitude = @Longitude,
                EventDate = @EventDate,
                HostedBy = @HostedBy
            where DinnerID = @DinnerID
        ";
        var command = new SqlCommand(commandText, connection);
        command.Parameters.Add(
            new SqlParameter {
                   ParameterName = "DinnerID",
                   DbType = DbType.Int32,
                   Value = dinner.DinnerID });
        var dinnerObjectParameters = GetDinnerObjectParameters(dinner);
        command.Parameters.AddRange(dinnerObjectParameters);
        connection.Open();
        command.ExecuteNonQuery();
    }
}

On review of the method, we can break down its execution into the following steps:

  1. Create a connection object using the connectionString member field. Like in our data retrieval methods, doing so in a using statement ensures proper connection termination whether or not an error occurs.
  2. Create a command object to modify data in the database. For the update method, we are choosing to update the Dinner table with a T-SQL update statement based on parameters passed in from the command object.
  3. Add a SqlParameter to the command object, based on the passed Dinner object’s DinnerID property. This identifies the record being updated in the T-SQL statement.
  4. Project to the current state of the dinner object to an array of SqlParameter objects using the GetDinnerObjectParameters helper method. Then, add them to the Parameters collection of the command object
  5. Open the connection, letting the database know we are about to ask it to do some work.
  6. Execute the command, performing the update on the record.
  7. Exit the using statement, effectively closing the connection to SQL Server database.

The GetDinnerObjectParameters helper method simply creates an array of parameters based on the dinner object. We use this in both UpdateDinner and AddDinner:

private SqlParameter[] GetDinnerObjectParameters(Dinner dinner)
{
    return new[]{
        new SqlParameter{ParameterName="Title", DbType=DbType.String,
            Value=dinner.Title},
        new SqlParameter{ParameterName="Description", DbType=DbType.String,
            Value=dinner.Description},
        new SqlParameter{ParameterName="EventDate", DbType=DbType.DateTime,
            Value=dinner.EventDate},
        new SqlParameter{ParameterName="Address", DbType=DbType.String,
            Value=dinner.Address},
        new SqlParameter{ParameterName="ContactPhone", DbType=DbType.String,
            Value=dinner.ContactPhone},
        new SqlParameter{ParameterName="Country", DbType=DbType.String,
            Value=dinner.Country},
        new SqlParameter{ParameterName="Latitude", DbType=DbType.Double,
            Value=dinner.Latitude},
        new SqlParameter{ParameterName="Longitude", DbType=DbType.Double,
            Value=dinner.Longitude},
        new SqlParameter{ParameterName="HostedBy", DbType=DbType.String,
            Value=dinner.HostedBy}};
}

Some differences exist between the modification methods, such as AddDinner and AddRsvp working with the SCOPE_IDENTITY() value after the call.  For the most part however, the implementation of the other three methods follow a similar pattern.

Pros and Cons of Connected Data Access

One nice aspect of ADO.NET Connected Data Access is the amount of control developers are allotted in their code. For instance, if during production of the NerdDinner site we find some of the SQL statements are not performing well, we can explicitly tune the SQL statements to achieve better results. Furthermore, we know exactly where the boundaries are between for the scope of the connection, and the command calls over that connection. If performance is critical, connected data access gives us the most low-level tools in .NET to accomplish this task most effectively.

However, this power and control comes with several drawbacks. For one, the data access code must explicitly manage every database resource, providing effective clean up when finished. Forgetting to do so in a web application like Nerd Dinner can cause resource management problems, effectively leaving it up to the garbage collector to free up connections. This can lead to performance issues as your hanging database connections max out the SQL Server’s available session resources. Furthermore, handcrafted T-SQL statements are prone to errors that will not be resolved at compile time- i.e. misnaming a table, field, or stored procedure call. Be careful before deciding on Connected Data Access as your sole option. As we will see in the upcoming examples, there may be a better tool for the job.

Disconnected Data Access with ADO.NET and Typed DataSets

Along with .NET connected API, Microsoft also included a .NET “disconnected” model. Building on the objects provided with the connected model, this API provides the DataSet object- allowing for interaction with data outside of an active database connection. Along with this, Visual Studio provides integrated tools to create strongly typed data objects. It’s these objects, called Typed DataSets, we’ll focus primarily on this comparison.

In Disconnected Data Access, two additional object types, the DataAdapter and DataSet, are thrown into the existing mix with the Connection and Command Objects. The DataAdapter object serves as coordinator with a database, containing Command objects used for data retrieval and modification. The DataSet object provides a neutral in-memory relational database, complete with Tables (DataTable objects and DataRow objects) and key-based Relationships (DataRelation objects). A data adapter coordinates the dataset with a tabular data source in the relational database: either a specified table, or custom SQL queries for retrieving consistently sourced data. The DataSet also contains built in change tracking, allowing any changes that have occurred in it since it was filled to be pushed by a DataAdapter to the database.

Typed DataSets take this model a step further, by providing developers with tools for creating strongly- typed DataSets specific to the data source. Development is accomplished with a modeling environment integrated into Visual Studio, complete with modeling tools to define the Typed DataSet schema. Creating a Typed DataSet against a relational data source is described as follows:

  1. Within a Visual Studio project, add a Typed DataSet item to the project. This creates a Visual design surface representing the Typed DataSets data model.
  2. Using the Data Connections tool in Server Explorer, drag and drop desired tables from the Source database onto the design surface. Doing so generates strongly-typed classes based on the disconnected object model.
  3. If necessary, refine the graphical model in the design surface to fine tune additional functionality required by requesting applications.

Like with Connected Data Access, there is much more involved with .NET disconnected Data Access and Typed DataSets than is discussed in this article. For more information visit the following URL: http://msdn.microsoft.com/en-us/library/ff361664(VS.100).aspx.

Defining the IDinnerRepository Contract

Defining the overall model of the Nerd Dinner’s data access with Typed DataSets requires a two-step process:

  1. Create a Typed DataSet with Visual Studio tools from the tables found in the NerdDinner database.
  2. Define an IDinnerRepository interface based on types created in the Typed DataSet.
Creating the NerdDinnerDataSet Typed DataSet

Typed DataSets, as stated above, are created using modeling tools built into Visual Studio. We start this process by adding a Typed DataSet project item to our Visual Studio project. We will name the Typed DataSet used in this implementation “NerdDinnerDataSet”:

This action both generates code for the NerdDinnerDataSet, and provides a design surface in our Visual Studio project. NerdDinnerDataSet inherits functionality from the DataSet, containing all the DataSet’s change tracking capabilities as well as strongly typed properties related to its internally housed data.

With the design surface created, we can now generate DataTables and Relationships for NerdDinnerDataSet based on the Nerd Dinner data model.  Right clicking on the project’s NerdDinner.mdb (found in the solution explorer’s App_Data folder) and selecting “Open” automatically displays a Data Connections tool window. From here, simply dragging and dropping the displayed Dinners and RSVP Tables onto the design surface generates two strongly-typed DataTables for Dinner and RSVP data in NerdDinnerDataSet, as well as a typed data relation between the two. Also, for style and consistency, we rename the Dinners DataTable name to its singular, “Dinner”, in the Properties windows.

Examining the generated code, we can see that along with the DataTables, two strongly typed DataRows have been defined. (Note: Some code content has been omitted for readability):

public partial class NerdDinnerDataSet: global::System.Data.DataSet
{
    public partial class DinnersDataTable :
        global::System.Data.TypedTableBase<DinnerRow>{...}
    public partial class RSVPDataTable :
        global::System.Data.TypedTableBase<RSVPRow>{...}
    public partial class DinnerRow : global::System.Data.DataRow
    {
        public int DinnerID { get{...}set{...} }
        public string Title { get{...}set{...} }
        public System.DateTime EventDate { get{...}set{...} }
        public string Description { get{...}set{...} }
        public string HostedBy { get{...}set{...} }
        public string ContactPhone { get{...}set{...} }
        public string Address { get{...}set{...} }
        public string Country { get{...}set{...} }
        public double Latitude { get{...}set{...} }
        public double Longitude { get{...}set{...} }
        public RSVPRow[] GetRSVPRows() {...}
    }
    public partial class RSVPRow : global::System.Data.DataRow
    {
        public int RsvpID { get{...}set{...} }
        public int DinnerID { get{...}set{...} }
        public string AttendeeName { get{...}set{...} }
        public DinnersRow DinnersRow { get{...}set{...} }
    }
}

These classes, DinnerRow and RSVPRow, provide the IDinnerRepository’s model objects to be used with the NerdDinner MVC site. There is a hitch, however. In the properties generated for the fields of DinnerRow and RSVPRow, data containing null values throws an exception at runtime when retrieved. This creates undesirable behavior in the Nerd Dinner MVC application, causing some Views to fail when binding to these types of objects. A more acceptable approach would either return null if it the respective property is a reference type or, if it is a value type, return a default value. Luckily, the design surface allows this correction by providing modifiable settings related to the specified Data field. We correct this error by modifying these data fields in their respective property windows:

  1. If the generated property is a reference type,such as the DinnerRow’s string “Title” field, we:
    1. Change the AllowDBNull property from “false” to "true".
    2. Change the NullValue property configuration from “Throw Exception” to “Null”.
  2. If the generated property is a value type, such as the DinnerRow’s DateTime “EventDate” field, we change the DefaultValue property configuration from “” to a specified value, such as a given Date.

Performing these slight adjustments provides us with enough to complete the definition of IDinnerRepository.

IDinnerRepository Definition

The IDinnerRepository definition for Disconnected Data Access, as in the connected model, uses strongly-typed data objects to represent Dinner and RSVP data. This time around, we will use the DinnerRow and RSVPRow types generated in the previous steps:

public interface IDinnerRepository
{
    //Data Access Methods
    IEnumerable FindAllDinners();
    IEnumerable FindByLocation(floatlatitude, float longitude);
    IEnumerable FindUpcomingDinners();
    NerdDinnerDataSet.DinnerRow GetDinner(int id);
    void AddDinner(NerdDinnerDataSet.DinnerRow dinner);
    void DeleteDinner(NerdDinnerDataSet.DinnerRow dinner);
    void AddDinnerRsvp(
        NerdDinnerDataSet.DinnerRow dinner, NerdDinnerDataSet.RSVPRow rsvp);
    void DeleteRsvp(NerdDinnerDataSet.RSVPRow rsvp);
    // Persistence Method
    void Save();
    //Object factory methods
    NerdDinnerDataSet.DinnerRow CreateDinnerObject();
    NerdDinnerDataSet.RSVPRow CreateRsvpObject();
}

From the interface definition above, we can see that like the Connected Data Access implementation, IDinnerRepository supports retrieval and update methods of FindAllDinners, FindByLocation, and FindUpcomingDinners. However, unlike the connected model, the modification methods AddDinner, DeleteDinner, AddDinnerRsvp, and DeleteRsvp do not immediately update the database. This is due to Typed Data Set’s built-in support for change tacking and caching. Data objects retrieved via FindAllDinners, FindByLocation, and FindUpcomingDinner also use these tracking features, negating the need for Connected Data Access’ UpdateDinner method. Since all update changes are tracked internally in the underlying repository object, introducing a Save method provides all necessary means to update the NerdDinner database.

The generated DinnerRow and RSVPRow classes do not have default constructors, and only be created via factory methods within the TypedDataSet’s DataTable plumbing. Since the ability to create these objects is needed by the NerdDinner MVC Application, we define two factory methods in DinnerRepository: CreateDinnerObject and CreateRsvpObject.

With the IDinnerRepository interface complete, we move on to its implementation.

Creating the Implementation for IDinnerRepository

Creating the DinnerRepository class for Typed DataSets follows a similar process to Connected Data Access: Create a class containing some plumbing, then implement functionality for IDinnerRepository. Before we start down that path, we need to extend the functionality of the TableAdapters generated in NerdDinnerDataSet.

Configuring NerdDinnerDataSet’s data access functionality

One of the nice things about using Typed DataSets with Visual Studio is the automatic creation of TableAdapters associated with Typed DataTables. These objects, built upon DataAdapters, coordinate interaction between the strongly typed DataTables and the Source database.  We can see these TableAdapters represented on the design surface generated for NerdDinnerDataSet:

When we initially created the Typed DataSet for NerdDinner, the DinnerTableAdapter and RSVPTableAdapter were generated for the DinnerTable and RSVPTable respectively, allowing Retrieval and Update actions between our Typed DataSet and its source database. Along with this, connection management code has been also added, allowing configuration of the Connection Object via the ConnectionStrings section of NerdDinner’s web.config file.

Implementing DataRow Refresh Functionality

One of the features NerdDinner MVC requires is retrieving of the latest version of Typed DataRow data once it has been either inserted or updated by its TableAdapter. We can ensure this via the DataTable’s Configure option, under its advanced options section. Selecting the “Refresh the data table” option for the TableAdapter‘s provided dialog enables this refresh functionality to occur:

We ensure this is fulfilled by checking the text of the Insert command, found in the TableAdapter’s properties window. As we can see, the generated Insert Command for the DinnersTableAdapter now contains the following T-SQL insert and select statement, ensuring that DinnerDataRow object is refreshed after it is inserted:

INSERT INTO [dbo].[Dinners]
     ([Title], [EventDate], [Description], [HostedBy], [ContactPhone],
       [Address], [Country], [Latitude], [Longitude])
      VALUES(@Title, @EventDate, @Description, @HostedBy, @ContactPhone,
      @Address, @Country, @Latitude, @Longitude);
SELECT DinnerID, Title, EventDate,Description, HostedBy, ContactPhone,
      Address, Country, Latitude, Longitude
      FROM Dinners WHERE(DinnerID =SCOPE_IDENTITY())
Extending Typed TableAdapters with Custom Methods

Since our TableAdapters now contain functionality for Fill and Update, we want to extend our  TableAdapters with custom query methods needed by the DinnerRepository class. We can accomplish this by extending the TableAdapters with the following methods:

DinnerTableAdapter:

  1. FillByDinnerID: Adds a single Dinner object based on its primary key.
  2. FillByUpcomingDinners: Adds all Dinner objects that have not occurred yet.
  3. FillByLocation: Adds all Dinner objects that have not occurred yet within 100 miles of a specified latitude/longitude coordinate.

RSVPTableAdapter:

  1. FillByDinnerID: Adds all RSVP objects based on its parent dinner’s primary key.
  2. FillByUpcomingDinners: Adds all RSVP objects for dinners that have not yet occurred.
  3. FillByLocation: Adds all RSVP objects for dinners that have not yet occurred within 100 miles of a latitude/longitude coordinate.

Let’s look into creating the FillByUpcomingDinner method in the DinnerTableAdapter.

On the NerdDinnerDataSet design surface, after selecting the “Add Query…” context menu option on Dinner table, we create Select based query- inserting our own custom parameterized SQL statement related to the desired functionality:

We also specify the name to this DinnerTableAdapter method: FillByLocation. The design surface for NerdDinnerDataSet now shows that a FillByLocation method has been added to the DinnerTableAdapter:

With this method created, we use its associated properties window associated to customize its parameter types and order:

After repeating this process for all required methods needed for the Dinner and RSVP Table Adapters, we end up with a design surface that looks like the following:

These changes visible in the designer are reflected in the generated TableAdapter codebase. (Note: some generated code has been omitted for readability):

namespace NerdDinner.Models.NerdDinnerDataSetTableAdapters
{
    public partial class DinnerTableAdapter : global::System.ComponentModel.Component
    {
        public virtual int Fill(NerdDinnerDataSet.DinnerDataTable dataTable){...}
        public virtual int FillByDinnerID(
            NerdDinnerDataSet.DinnerDataTable dataTable,
            int DinnerID){...}
        public virtual int FillByLocation(
            NerdDinnerDataSet.DinnerDataTable dataTable,
            double Latitude,
            double Longitude,
            System.DateTime CurrentDate){...}
        public virtual int FillByUpcomingDinners(
            NerdDinnerDataSet.DinnerDataTable dataTable,
            System.DateTime CurrentDate){...}
    }
    public partial class RSVPTableAdapter : global::System.ComponentModel.Component
    {
        public virtual int Fill(NerdDinnerDataSet.RSVPDataTable dataTable){...}
        public virtual int FillByDinnerID(
            NerdDinnerDataSet.RSVPDataTable dataTable,
            intDinnerID){...}
        public virtual int FillByLocation(
            NerdDinnerDataSet.RSVPDataTable dataTable,
            double Latitude,
            double Longitude,
            System.DateTime CurrentDate){...}
        public virtual int FillByUpcomingDinners(
            NerdDinnerDataSet.RSVPDataTable dataTable,
            System.DateTime CurrentDate){...}
    }
    public partial class TableAdapterManager : global::System.ComponentModel.Component
    {
        public DinnerTableAdapter DinnerTableAdapter { get{...}set{...} }
        public RSVPTableAdapter RSVPTableAdapter { get{...}set{...} }
        public global::System.Data.IDbConnection Connection { get{...}set{...} }
        public virtual int UpdateAll(NerdDinnerDataSet dataSet) {...}
    }
}

We are now ready for the actual implementation of the DinnerRepository.

Creating the DinnerRepository Class

With the NerdDinnerDataSet configuration complete, the implementation of the DinnerRepository class is actually fairly straightforward. Like with Connected Data Access, we start by implementing some basic plumbing:

public class DinnerRepository : IDinnerRepository
{
    NerdDinnerDataSet nerdDinnerDataSet = new NerdDinnerDataSet();
    …
}

This time around, we only require a field containing an instance of the NerdDinnerDataSet, which we call, cleverly enough, nerdDinnerDataSet. And that’s it. Since connection management is implicitly handled in our TableAdapters, there is no need to add plumbing for this in our class. Next, we implement data retrieval functionality.

Data Retrieval Implementation

A good way to understand data retrieval used in TypedDataSets is by examining the implementation of the FindByLocation method:

public IEnumerable<NerdDinnerDataSet.DinnerRow> FindByLocation(
    float latitude, float longitude)
{
    var now = DateTime.Now;
    var dinnerTableAdapter = newDinnerTableAdapter();
    var rsvpTableAdapter = newRSVPTableAdapter();
    dinnerTableAdapter.FillByLocation(
        nerdDinnerDataSet.Dinner, latitude, longitude, now);
    rsvpTableAdapter.FillByLocation(
        nerdDinnerDataSet.RSVP, latitude, longitude, now);
    return this.nerdDinnerDataSet.Dinner;
}

Since most of the work providing data access functionality went into designing the FillByLocation methods in the DinnerTableAdapter and RSVPTableAdapter, the actual implementation for this method is fairly simple:

  1. Create DinnerTableAdapter and RSVPTableAdapter instances.
  2. Call the DinnerTableAdapter FillByLocation method, which populates the Dinner table in the nerdDinnerDataSet data field based on parameters passed.
  3. Call the RSVPTableAdapter FillByLocation method, which populates the RSVP table in the nerdDinnerDataSet data field based on parameters passed.

4.       Return the nerdDinnerDataSet’s Dinner table, a collection supporting the IEnumerable interface of DinnerRow objects.

Notice the lack of code to manage the connection object and fill associate RSVP objects with their Dinner parent. This is because the generated Typed DataSet and TableAdapter objects already contain internal plumbing to handle these operations.  FindRecentDinners, FindAllDinners, and GetDinner contain similarly sparse code, varying only by methods called in the instantiated TableAdapters.

With data retrieval implemented, let’s finish off our disconnected data access exploration with the data update methods in DinnerRepository.

Data Update Implementation

Like with data retrieval, data update methods for this DinnerRepository implementation are simple. Let’s look at the methods for AddDinner and DeleteDinner:

public void AddDinner(NerdDinnerDataSet.DinnerRow dinner)
{
    this.nerdDinnerDataSet.Dinner.AddDinnerRow(dinner);
}
public void DeleteDinner(NerdDinnerDataSet.DinnerRow dinner)
{
    dinner.Delete();
}

Since the Save method now coordinates updates with the NerdDinner database, AddDinner and DeleteDinner simply provide state management in the NerdDinnerTypedDataSet. The same is true for AddDinnerRsvp and DeleteRsvp. Which leaves us with the Save method:

public void Save()
{
    var manager = newTableAdapterManager
    {
        DinnerTableAdapter = newDinnerTableAdapter(),
        RSVPTableAdapter = newRSVPTableAdapter(),
    };
    manager.UpdateAll(this.nerdDinnerDataSet);
    this.nerdDinnerDataSet.AcceptChanges();
}

Again, like previously discussed methods for this version of DinnerRepository, most of the work was taken care when configuring NerdDinnerDataSet’s TableAdapters. With this complete, the Save needs only three simple steps to push changes to the database:

  1. Create an instance of TableAdapterManager, providing it with instances of DinnerTableAdapter and RSVPTableAdapter objects. Like the TableAdapter objects, the Visual Studio NerdDinnerDataSet designer generates this class for us automatically.
  2. Call the UpdateAll method the TableAdapterManager instance, effective pushing all changes to the database, as well as refreshing the data in any effected objects.
  3. Inform the NerdDinnerDataSet field to reset its change tracking, since we have effectively updated the database with the tracked changes.

We have now completed IDinnerRepository’s implementation using Disconnected Data Access and Typed DataSets.

Pros and Cons of Disconnected Data Access

Typed DataSets provide many advantages over Connected Data Access for code maintenance. For the most part, all of the code generation for strongly typed data objects is automatically managed with Visual Studio’s design tools. This provides both a visual representation of typed data object model, as well as removing the tedious work of creating strongly-typed data objects. Furthermore, the ability in disconnected data access for automatic change tracking allows for more choices in data access implementations. In Nerd Dinner, we use this to our advantage and extend our Repository Model with the Save method, using all the previous Update methods to cache changes, calling the Save method only when it is desired to update all the cached changes.

However, ADO.NET Disconnected Data Access does have its disadvantages. For one, since TableAdapter queries can only relate specific DataTable data, we are prevented use of optimized multiple result set queries, such as  the ones used in the connected model’s data retrieval methods. In our implementation for instance, this limitation requires duplicate FillBy methods for both the DinnerTableAdapter and RSVPTableAdapter. Using these in practice is less efficient, requiring separate calls to the database for each DataTable even though the data is related. Furthermore, Typed Data Sets require large amounts of caching, even if it is undesired, such as running an aggregate date function on the returned result. This can cause application performance issues which may not be necessary.

Now on to basic Object Relational Mapping with LINQ to SQL.

Basic Object Relation Mapping with LINQ to SQL

LINQ to SQL, an Object Relational Mapping framework used with Microsoft’s LINQ technologies, allows for an even higher layer of extraction than provided with Typed DataSets.  For the most part, its ability to model an existing database follows very similar steps to Typed Data Sets: working with a UI design surface, dragging entities and stored procedures from Data Connection, and automatically generating code for custom data objects.  LINQ to SQL also introduces the DataContext object which, like TableAdapters and DataAdapters, provides a means of retrieving or communicating data changes to the database. However, by leveraging custom LINQ expression syntax, the amount of custom SQL necessary in the previously described disconnected and connected models has been eliminated and replaced with strongly-typed code checked at compile time.

Like our previous two comparisons, we start by defining the IDinnerRepository interface for the Nerd Dinner site.

Defining the IDinnerRepository Contract

As with Typed DataSets, we begin our work with LINQ to SQL by first providing the interface definition IDinnerRepository. Creating this interface is broken into two steps:

1.       Using Visual Studio’s support for LINQ to SQL, define a DataContext class and Data objects used in our interface definition.

2.       Define an IDinnerRepository interface based on the generated strongly-typed data objects.

Creating the NerdDinnerDataContext object

Creating a DataContext object specific to the NerdDinner schema, like typed data sets, is managed through Visual Studio integration. We begin by adding a new LINQ to SQL item to our project, named “NerdDinner”:

Completing this creates a DataContext implementation named NerdDinnerDataContext, complete with design surface for defining its data model:

Next, we define the data objects used in the IDinnerRepository definition for Dinner and RSVP data. Like with Typed Datasets, this is accomplished by dragging and dropping Dinners and RSVP tables from the NerdDinner.mdf Data Connections onto the design surface:

Examining the generated code, we can see data object implementations for Dinner and RSVP, as well as our NerdDinnerDataContext with strongly-typed interaction points for table. (Note: Some code has been omitted for readability):

[DatabaseAttribute(Name = "NerdDinner")]
public partial class NerdDinnerDataContext : System.Data.Linq.DataContext
{
}
[TableAttribute(Name = "dbo.RSVP")]
public partial class RSVP : INotifyPropertyChanging, INotifyPropertyChanged
{
    public RSVP(){…}
    [ColumnAttribute(
        Storage = "_RsvpID",
        AutoSync = AutoSync.OnInsert,
        DbType = "Int NOT NULL IDENTITY",
        IsPrimaryKey = true,
        IsDbGenerated = true)]
    public int RsvpID{get{...}set{...}}
    [ColumnAttribute(Storage = "_DinnerID", DbType = "Int NOT NULL")]
    public int DinnerID{get{...}set{...}}
    [ColumnAttribute(Storage = "_AttendeeName", DbType = "NVarChar(30) NOT NULL", CanBeNull = false)]
    public string AttendeeName{get{...} set{...}}
    [AssociationAttribute(
        Name = "Dinner_RSVP",
        Storage = "_Dinner",
        ThisKey = "DinnerID",
        OtherKey = "DinnerID",
        IsForeignKey = true)]
    public Dinner Dinner{get{...}set{...}}
}

[TableAttribute(Name = "dbo.Dinners")]
public partial class Dinner : INotifyPropertyChanging, INotifyPropertyChanged
{
    public Dinner(){…}
        [ColumnAttribute(
            Storage = "_DinnerID",
            AutoSync = AutoSync.OnInsert,
            DbType = "Int NOT NULL IDENTITY",
            IsPrimaryKey = true,
            IsDbGenerated = true)]
    public int DinnerID{get{...}set{...}}
   
    [ColumnAttribute(Storage = "_Title", DbType = "NVarChar(50) NOT NULL", CanBeNull = false)]
    public string Title{get{...}set{...}}
    [ColumnAttribute(Storage = "_EventDate", DbType = "DateTime NOT NULL")]
    public DateTime EventDate{get{...}set{...}}
    [ColumnAttribute(Storage = "_Description", DbType = "NVarChar(256) NOT NULL", CanBeNull = false)]
    public string Description{get{...}set{...}}
    [ColumnAttribute(Storage = "_HostedBy", DbType = "NVarChar(20) NOT NULL", CanBeNull = false)]
    public string HostedBy{get{...}set{...}}
    [ColumnAttribute(Storage = "_ContactPhone", DbType = "NVarChar(20) NOT NULL", CanBeNull = false)]
    public string ContactPhone{get{...}set{...}}
       
    [ColumnAttribute(Storage = "_Address", DbType = "NVarChar(50) NOT NULL", CanBeNull = false)]
    public string Address{get{...}set{...}}
    [ColumnAttribute(Storage = "_Country", DbType = "NVarChar(30) NOT NULL", CanBeNull = false)]
    public string Country{get{...}set{...}}
    [ColumnAttribute(Storage = "_Latitude", DbType = "Float NOT NULL")]
    public double Latitude{get{...}set{...}}
    [ColumnAttribute(Storage = "_Longitude", DbType = "Float NOT NULL")]
    public double Longitude{get{...}set{...}}
    [AssociationAttribute(Name = "Dinner_RSVP", Storage = "_RSVPs", ThisKey = "DinnerID", OtherKey = "DinnerID")]
    public EntitySet<RSVP> RSVPs{get{...} set{...}}
}

Like the DinnerRow and RSVPRow of the NerdDinnerDataSet object model, properties associated with source table columns as well as relationship mappings are created for the Dinner and RSVP data objects. However, unlike the TypedDataSet, Dinner and RSVP data objects do not rely on an underlying base class type to handle functionality. Instead all mapping between properties and their respective SQL columns are managed through attribute decorations used by NerdDinnerDataContext at runtime. Furthermore, null values are handled appropriately on properties, eliminating the need to do subsequent configuration on the design surface. With this complete, we have everything that we need to define the IDinnerRepository interface.

IDinnerRepository Definition

IDinnerRepository definition for LINQ to SQL, like with Typed DataSets, contains data access methods for retrieving data, methods for tracking added and deleted objects, and Save method for persisting changes:

public interface IDinnerRepository
{
    IQueryable<Dinner> FindAllDinners();
    IQueryable<Dinner> FindByLocation(float latitude, float longitude);
    IQueryable<Dinner> FindUpcomingDinners();
    Dinner GetDinner(int id);
    void Add(Dinner dinner);
    void Delete(Dinner dinner);
    void Save();
}

However, since LINQ to SQL manages tracking of RSVP objects associated with Dinner objects automatically, there is no need for the disconnected model’s AddDinnerRsvp and DeleteRsvp methods. Furthermore, since the objects created include default constructors, the factory methods CreateDinnerObject and CreateRsvpObject can be omitted as well.

Also, notice how IQueryable replaces IEnumerable as the return type for FindAllDinners, FindByLocation, and FindUpcomingDinner. Unlike IEnumerable, IQueryable results  treat the source LINQ query as an expression tree rather than a compiled enumerator. In doing so, an external LINQ query can use one of these IQueryable methods and when executed, translate the both the containing expression and the method’s expression contents into a single TSQL request. This allows the operations of the LINQ query to be performed completely on the SQL Server database, reducing the amount of work and data retrieved by the Nerd Dinner application. When we later explore the FindByLocation implementation, we will see how this implementation of Nerd Dinner takes advantage of this concept.

We now have everything in place necessary to implement DinnerRepository.

Creating the Implementation for IDinnerRepository

Before we begin our NerdDinner Repository implementation, we need provide access to the NearestDinners database function found in NerdDinner.mdf, allowing us to implement FindByLocation as a LINQ query. Accomplishing this is fairly simple. In our Visual studio design surface for NerdDinnerDataContext, we simply drag and drop the NearestDinners function from our DataConnections tool window into the functions and stored procedures portion section:

Doing so adds a new method called NearestDinners to our NerdDinnerDataContext class, ready to be called from our custom LINQ queries:

[FunctionAttribute(Name="dbo.NearestDinners", IsComposable=true)]
public IQueryable<NearestDinnersResult> NearestDinners(
    [ParameterAttribute(DbType="Real")] Nullable<float> lat,
    [ParameterAttribute(Name="long", DbType="Real")] Nullable<float> @long)
{
}

Since we are managing our queries explicitly with LINQ expressions, there is no need to create data retrieval methods. This saves us a lot of work previously required for NerdDinnerTypedDataSet’s TableAdapters. 

We are now ready to move on to the implementation of the DinnerRepository class.

Creating the DinnerRepository Class

Like with the previous comparisons, we start our DinnerRepository class with some basic plumbing: a NerdDinnerDataContext field named db.

public class DinnerRepository : IDinnerRepository
{
     NerdDinnerDataContext db = new NerdDinnerDataContext();
}

Like the NerdDinnerDataSet, the implementation for NerdDinnerDataContext automatically resolves configuration file connection string loading, removing the need to manage this manually in the class.

Data Retrieval Implementation

Since the NerdDinnerDataContext allows custom LINQ queries against its Dinner and RSVP properties for the syntax, we create IDinnerRepository’s FindAllDinners, FindByLocation, FindUpcomingDinners, and GetDinner using custom LINQ:

public IQueryable<Dinner> FindAllDinners()
{
    return db.Dinners;
}
public IQueryable<Dinner> FindByLocation(float latitude, float longitude)
{
    var dinners = from dinner in FindUpcomingDinners()
                  join i in db.NearestDinners(latitude, longitude)
                  on dinner.DinnerID equals i.DinnerID
                  select dinner;
    return dinners;
}
public IQueryable<Dinner> FindUpcomingDinners()
{
    return from dinner in FindAllDinners()
                 where dinner.EventDate > DateTime.Now.AddDays(-1)
                 orderby dinner.EventDate
           select dinner;
}
public Dinner GetDinner(int id)
{
    return db.Dinners.SingleOrDefault(d => d.DinnerID == id);
}

As stated earlier, since the methods FindAllDinners, FindByLocation, FindUpcomingDinners, and NearestDinners all result in IQueryable interfaces, invoking their methods actually calls dynamically-generated T-SQL based on the structure of their LINQ queries. Notice invoking the FindUpcomingDinners method logically combines the query contents of FindAllDinners, FindByLocation, and the FindUpcomingDinners method itself into one complete TSQL statement:

exec sp_executesql N'SELECT [t0].[DinnerID], [t0].[Title],
      [t0].[EventDate], [t0].[Description],
      [t0].[HostedBy], [t0].[ContactPhone],
      [t0].[Address], [t0].[Country],
      [t0].[Latitude], [t0].[Longitude]
      FROM [dbo].[Dinners] AS [t0]
      INNER JOIN [dbo].[NearestDinners](@p0, @p1) AS [t1] ON
          [t0].[DinnerID] = [t1].[DinnerID]
      WHERE [t0].[EventDate] > @p2
      ORDER BY [t0].[EventDate]',
      N'@p0 real,@p1 real,@p2 datetime',
      @p0=45.859378814697266,
      @p1=-122.84087371826172,
      @p2='2010-03-28 10:55:23.7970000'
Data Modification Implementation

Like with TypedDataSets, all change tracking for NerdDinner data is automatically handled in the NerdDinnerDataContext, as well as its data objects for Dinner and RSVP. This leaves us with a fairly simple implementation for the Add and Delete methods:

public void Add(Dinner dinner)
{
    db.Dinners.InsertOnSubmit(dinner);
}
public void Delete(Dinner dinner)
{
    db.RSVPs.DeleteAllOnSubmit(dinner.RSVPs);
    db.Dinners.DeleteOnSubmit(dinner);
}

Finishing up with the Save methods implementation, we simply call SubmitChanges on the underlying NerdDinnerDataContext--even less work than with the TypedDataSet implementation. Doing so pushes all of the collected changes to the NerdDinner database:

public void Save()
{
    db.SubmitChanges();
}

Pros and Cons of LINQ to SQL

Compared to the connected and disconnected model, using LINQ to SQL for basic data access operations reduces the amount of work necessary to implement a data access layer. Using the design surface in Visual Studio needs far less work than with TypedDataSets, and eliminates a lot of the cruft necessary for Typed DataRows to work properly. Furthermore since LINQ expressions are the direct means of defining our queries, type safety and expression syntax are resolved at compile time, eliminating many issues that occur with writing hand-crafted SQL when using ADO.NET.

However LINQ to SQL does have some issues. Generating DataContexts relies on the underlying data model to drive the design of the object model. A conceptual model that uses object-oriented concepts such as inheritance and more flexible relationship cardinality (i.e. many-to-many relationships) would require custom coding to be available.

Now on to our final Nerdly comparision using LINQ to Entities.

Object Relational Mapping with LINQ to Entities and the Entity Framework

LINQ to Entities, Microsoft’s latest incarnation of tools for Object Relational Mapping, improves LINQ to SQL’s modeling flexibility. The Entity Framework, which LINQ to Entities is part of, introduces a sophisticated mapping model to relate relational data to the actual data objects used in code.

With this mapping in place, applications can write code using a specialized object for the Entity Framework called the ObjectContext, similar to the LINQ to SQL DataContext.  In addition to rich LINQ support through the ObjectContext, a custom query language can also be used to allow interaction with a conceptual model, Entity SQL. The Entity Framework translates Entity SQL to the database-specific SQL based on how the conceptual model maps to the underlying storage schema definition, executes the query and returns the results mapped as conceptual model objects.  When using LINQ, the resulting application code is very similar to LINQ to SQL; i.e. with a strongly-typed ObjectContext and custom application queries using the LINQ syntax.

Like the previous three explorations into data access, we start our NerdDinner comparison with LINQ to Entities by defining the IDinnerRepository interface.

Defining the IDinnerRepository Contract

Like LINQ to SQL and Typed Datasets, Entity Framework also offers a visual design surface in Visual Studio.  Also like these previously compared data access methods, creation of the IDinnerRepository contract is broken into two steps.

1.       Using Visual Studio’s support for Entity Framework, define Entities used in our interface definition.  An ObjectContext class is generated automatically and,  like the DataContext, provides all the necessary means to communicate with the underlying database.

2.       Define an IDinnerRepository interface based on generated strongly-typed data objects.

Populating the Entity Framework design surface, like LINQ to SQL and Typed datasets, results in a strongly-typed object based on the underlying NerdDinner database, as well as a visualization of the model.  We initiate this process by adding an ADO.NET Entity Data Model item to our NerdDinner project, which we name “NerdDinner”:

The Entity Data model (EDM) is the core modeling language of the Entity Framework (EF). Choosing this item launches a wizard, which automatically analyzes the NerdDinner database after selecting it as a source. After selecting specific database objects, completing the wizard with its default settings generates a default storage model based on Nerd Dinner, a default conceptual model, and a mapping definition between the two models:

Along with this, code for an ObjectContext named NerdDinnerEntities is generated, as well as strongly-typed Dinner and RSVP classes.

publicpartial class NerdDinnerEntities : ObjectContext
{
    public ObjectSet<Dinner> Dinners { get{...}set{...}}
    public ObjectSet<RSVP> RSVPs { get{...}set{...}}
    publicvoid AddToDinners(Dinner dinner){...}
    publicvoid AddToRSVPs(RSVP rSVP){...}
}
[EdmEntityTypeAttribute(NamespaceName = "NerdDinnerModel", Name = "Dinner")]
public partial class Dinner : EntityObject
{
    public Int32 DinnerID { get{...}set{...}}
    public String Title { get{...}set{...}}
    public DateTime EventDate { get{...}set{...}}
    public String Description { get{...}set{...}}
    public String HostedBy { get{...}set{...}}
    public String ContactPhone { get{...}set{...}}
    public String Address { get{...}set{...}}
    public String Country { get{...}set{...}}
    public Double Latitude { get{...}set{...}}
    public Double Longitude { get{...}set{...}}
    [EdmRelationshipNavigationPropertyAttribute("NerdDinnerModel",
      "FK_RSVP_Dinners", "RSVP")]
    public EntityCollection<RSVP> RSVPs { get{...}set{...}}
}
[EdmEntityTypeAttribute(NamespaceName = "NerdDinnerModel", Name = "RSVP")]
publicpartial class RSVP : EntityObject
{
    public Int32 RsvpID { get{...}set{...}}
    public Int32 DinnerID { get{...}set{...}}
    public String AttendeeName { get{...}set{...}}
    [EdmRelationshipNavigationPropertyAttribute("NerdDinnerModel",
      "FK_RSVP_Dinners", "Dinners")]
    public Dinner Dinner { get{...}set{...}}
}

A notable feature found in EF is support for mapping custom-written “Plain Old CLR Objects” (POCOs) to the conceptual model. Using this allows developers flexibility in designing their own data classes, bypassing the entity classes created by the Visual Studio wizard. However, for the sake of simplicity, we will skip using this feature and rely on the use of the default generated Dinner and RSVP classes instead.

We now have enough code in place to define our IDinnerRepository interface. Since LINQ to Entities follows a similar internal object model like LINQ to SQL, we can use the same interface definition for both:

public interface IDinnerRepository
{
    IQueryable<Dinner> FindAllDinners();
    IQueryable<Dinner> FindByLocation(float latitude, float longitude);
    IQueryable<Dinner> FindUpcomingDinners();
    Dinner GetDinner(int id);
    void Add(Dinner dinner);
    void Delete(Dinner dinner);
    void Save();
}

Now let’s finish by implementing the DinnerRepository class.

Creating the Implementation for IDinnerRepository

Like our previous examples, we start our DinnerRepository implementation with some basic plumbing:

public class DinnerRepository : NerdDinner.Models.IDinnerRepository
{
    NerdDinnerEntities db = new NerdDinnerEntities();
}

As with LINQ to SQL and TypedDataSets, the Entity Framework’s DinnerRepository plumbing requires only defining and instantiating a single NerdDinnerEntities field named db.  With this in place, we move onto exposing NearestDinners functionality necessary for our FindByLocation method.

Exposing NearestDinners Functionality with Entity Framework

The FindByLocation method of the DinnerRepository class uses the NearestDinners table value function from Nerd Dinner database. As seen in our earlier work, integrating this type of database function with LINQ to SQL is a straightforward drag and drop process. Unfortunately, this cannot be done with Entity Framework due to its current lack of support for table value functions. However, there is workaround.

Although NearestDinners cannot be accessed via EF, NerdDinner’s DistanceBetween function is available, since it is a scalar value function. Since NearestDinner’s T-SQL logic is little more than a query using the DistanceBetween function, we can replicate NearestDinner as a custom C# LINQ expression, eliminating the need to call it directly. Furthermore, This DistanceBetween function was automatically mapped in the storage model when we initially created the NerdDinnerEntities ObjectContext, giving us some means of accessing its functionality.

Our first step is exposing the DistanceBetween function mapped in our edmx file in manner that can be consumed in a LINQ Query. We do so by creating the following method, decorated with an EdmFunction attribute to provide mapping information:

[EdmFunction("NerdDinnerModel.Store", "DistanceBetween")]
public static double DistanceBetween(float lat1, float long1,
    float lat2, float long2)
{
    throw new NotImplementedException("Only call through LINQ expression");
}

Notice how we leave the method implementation empty. Since this method will ultimately be combined in an IQueryable based LINQ query, its internals are never actually executed. When the parent expression used in the is executed the Nerd Dinner application, only the mapping information and function signature are used to provide the correct final translation to TSQL executed in the Nerd Dinner database.  The rest is ignored.

With this in place, we simply add our own version of NearestDinners using a custom LINQ expression, ready to be consumed by the FindByLocation method:

public IQueryable<Dinner> NearestDinners(float latitude, float longitude)
{
    return from d in db.Dinners
           where DistanceBetween(latitude, longitude,
                 (float)d.Latitude, (float)d.Longitude) < 100
           select d;
}

We are now ready to implement DinnerRepository’s data retrieval methods.

Data Retrieval Implementation

Since LINQ to Entities leverages LINQ for performing data retrieval, NerdDinnerRepository’s methods for FindAllDinners, FindByLocation, FindUpcomingDinners, and GetDinner query based implementations are very similar to its LINQ to SQL counterparts:

public IQueryable<Dinner> FindAllDinners()
{
    return db.Dinners;
}
public IQueryable<Dinner> FindUpcomingDinners()
{
    return from dinner in FindAllDinners()
           where dinner.EventDate >= DateTime.Now
           orderby dinner.EventDate
           select dinner;
}
public IQueryable<Dinner> FindByLocation(float latitude, float longitude)
{
    var dinners = from dinner in FindUpcomingDinners()
                  join i in NearestDinners(latitude, longitude)
                  on dinner.DinnerID equals i.DinnerID
                  select dinner;
    return dinners;
}
public Dinner GetDinner(int id)
{
    return db.Dinners.SingleOrDefault(d => d.DinnerID == id);
}

And, like LINQ to SQL, these queries are IQueryable based, automatically translating to and executing T-SQL against the underlying NerdDinner database. Here’s a SQL Trace capture of actual T-SQL executed during the FindByLocation call, complete with the previously described workaround for the NearestDinners function:

exec sp_executesql N'SELECT
[Extent1].[DinnerID] AS [DinnerID],
[Extent1].[Title] AS [Title],
[Extent1].[EventDate] AS [EventDate],
[Extent1].[Description] AS [Description],
[Extent1].[HostedBy] AS [HostedBy],
[Extent1].[ContactPhone] AS [ContactPhone],
[Extent1].[Address] AS [Address],
[Extent1].[Country] AS [Country],
[Extent1].[Latitude] AS [Latitude],
[Extent1].[Longitude] AS [Longitude]
FROM [dbo].[Dinners] AS [Extent1]
WHERE ([Extent1].[EventDate] >= (SysDateTime()))
      AND (([dbo].[DistanceBetween](
                  @p__linq__0,
                  @p__linq__1,
                  [Extent1].[Latitude],
                  [Extent1].[Longitude]))
          < cast(100 as float(53)))',
N'@p__linq__0 float,@p__linq__1 float',
@p__linq__0=45.859531402587891,
@p__linq__1=-122.84081268310547
Data Modification Implementation

Again, like LINQ to SQL and Typed DataSets, all change tracking for DinnerRepostory is automatically handled in the NerdDinnerEntities field and the Dinner and RSVP objects. Again, this leaves us with very simple implementations for the Add and Delete methods:

public void Add(Dinner dinner)
{
    db.Dinners.AddObject(dinner);
}
public void Delete(Dinner dinner)
{
    foreach(RSVP rsvp in dinner.RSVPs.ToList()) db.RSVPs.DeleteObject(rsvp);
    db.Dinners.DeleteObject(dinner);
}

Finishing up with the Save methods implementation, we simply call SaveChanges on the underlying NerdDinnerDataContext. Like with LINQ to SQL DataContext’s SubmitChanges, calling SaveChanges pushes all of the tracked inserts updates and deletes for Dinner and RSVP objects to the NerdDinner database:

public void Save()
{
    db.SaveChanges();
}

Pros and Cons of Entity Framework

Like LINQ to SQL, EF provides a set of tools for creating a strongly-typed data objects, fully supporting flexible LINQ queries (via LINQ to Entities). Expanding this with its mapping technologies allows for greater flexibility when defining the conceptual data model. Unlike LINQ to SQL, Entity Framework’s separation of conceptual and storage models allows for inheritance and greater flexibility in defining the cardinality of entity relationships. Although not so evident in Nerd Dinner’s simple two table database, these features really show their weight when working with large enterprise models.

Another great feature found only in the Entity Framework is built in support for the OData protocol. OData allows clients to interact with data objects over the web via HTTP requests contain XML messages based on atom.  Client application frameworks available for .NET, Silverlight, PHP, AJAX, Java and Excel (via PowerPivot) with intrinsic OData support can then use this as a fully functional data access protocol, complete with CRUD operations and complex query support such as filtering and ordering. Using WCF Data Services library, a model designed in Entity Framework can easily exposed as an OData-based WCF service in a few lines of code, ready to be consumed by an OData ready client application:

public class NerdDinner : DataService<NerdDinnerEntities>
{
    public static void InitializeService(DataServiceConfiguration config)
    {
        config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
        config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
    }
}
}

With an Entity Framework model exposed in this manner, it can now be queried directly via an HTTP Get request:

What we’ve just done using the same model that drives Entity Framework is to expose a complete REST-based programmatic interface to NerdDinner.com with only a few lines of code. That’s the work of WCF Data Services exposing the NerdDinner.com data model via OData. For more information on this protocol, visit the following link: http://www.odata.org/.

Entity Framework however does have some drawbacks. As seen in this comparison, support for some LINQ to SQL features, such as mapping to table-valued functions, is currently lacking. This requires developers to work around these omissions with custom code, including sometimes difficult-to-debug custom EDMX configurations.

Conclusion

Microsoft’s .NET platform provides developers with many choices for data access. Using the Nerd Dinner web site to compare these options gives us a greater understanding of each technology. We have seen on the lowest level, using .NET Connected Data Access provides tight control over managing a database query, as demonstrated by our custom multiple-result set queries. On the higher level, we have seen Entity Framework and LINQ to SQL build upon type-safe concepts introduced with ADO.NET Disconnected Typed Datasets, demonstrated in the type-safe LINQ based data access to the underlying NerdDinner database.

It is also good to mention that there are many more supported features to these data access methods not covered in this article. Connected data access, for instance, now has built in support for SQL Server 2008’s table value parameter, allowing for greater flexibility for data modification queries on multiple items. And although we briefly mentioned OData and POCO support in the Entity Framework, much more detail on these concepts can be found online regarding these topics. . A good place to start is the MSDN Data Developer Center at http://msdn.com/data/.

Exploring the methods in this Nerd Dinner comparison provides clarity on of how these data access methods relate and can be used. Feel free to venture more and Bon Appétit!

Microsoft is conducting an online survey to understand your opinion of the MSDN Web site. If you choose to participate, the online survey will be presented to you when you leave the MSDN Web site.

Would you like to participate?