Advantage Developer Zone


Advantage with the Entity Framework

Thursday, July 9, 2009

The Microsoft Entity Framework, included in Visual Studio 2008 SP1, is now supported by the Advantage .NET Data Provider. The entity framework creates an additional layer of abstraction between the data and the application. This enables developers to “model” the data for their application without changing the actual database.

Creating an Entity

Entities are created by adding an ADO.NET Entity Data Model (EDM) to your project. This will start the Entity Data Model Wizard which allows you to create an empty model or generate one from an existing database. To generate the model from a database you use an ADO.NET Data Provider which provides information about the database to the wizard.

The model wizard can generate objects for tables, views and stored procedures defined in the database. It also configures all of the relationships between the various database objects. The model pictured below was generated from an Advantage Data Dictionary which is based on the School database included in the Microsoft Entity Framework Quickstart.

Accessing the Data

Once the model has been created the data can be accessed by using an ObjectQuery. First you must add references to the System.Data.Objects and System.Data.Objects.DataClasses library to your project. The entity model is accessed like any other object by your application. Therefore you must create an Entity object to be used. The model above is named SchoolEntities.

With the entity object initialized all of the tables can now be accessed using an ObjectQuery. The ObjectQuery is a typed query against a model for the given object context. Once the ObjectQuery has been created it can be used as the DataSource for any data aware control including ListBoxes, ComboBoxes, TextBoxes, GridViewControls and many others.

The example code below demonstrates initializing the object context, creating a query object for the Person, sorted by last name, and a query object for the Course table. Finally the courseQuery is bound to a ComboBox control.

   1: // Create the context
   2: Private SchoolEntities schoolContext;
   4: // Initialize the Object context
   5: schoolContext = new SchoolEntities();
   7: // Create the queries 
   8: ObjectQuery<Person> personQuery = schoolContext.Person.OrderBy("it.LastName");
   9: ObjectQuery<Course> courseQuery = schoolContext.Course;
  11: // Bind the couresQuery to a ComboBox control
  12: cboCourses.DisplayMember = "Title";
  13: cboCourses.DataSource = courseQuery;

The courseQuery will be executed when the form loads and the control is created. It will then populate the list with the courses.

Handling Master / Detail Relationships

Master / Detail relationships between objects are very easy since the model keeps track of the relationship through the navigation property. These are generated automatically for any referential integrity rules defined in the database. They can also be created after the model is generated.

When a relationship exists between two objects (tables) in the model the detail records can become part of the main object. For example; the department object has a navigational item to the course object, each course is run by a specific department.

This one-to-many relationship can be used by an ObjectQuery. The Include method of the entity uses the navigational link to provide a mechanism for retrieving the matching records. By including the course link in our ObjectQuery the courses associated with each department become part of the department object and are accessible.

The code below demonstrates setting up an ObjectQuery for the person object that includes the course information for each person. When we select a specific person from the list it will include all of the course objects that are related to it. This list of courses can then be bound to a data aware control.

   1: // Define a query that returns all People and the related courses
   2: ObjectQuery<Person> studentQuery = schoolContext.Person.Include("Course");
   4: // Bind the combobox control to the query, which is executed during data binding
   5: studentList.DisplayMember = "LastName";
   6: studentList.DataSource = studentQuery;
   8: // ***************************************************
   9: // code for the studentList SelectedIndexChanged Event
  10: // ***************************************************
  12: // Create a new person object based on the selected student
  13: Person person = (Person)studentList.SelectedItem;
  15: courseGrid.DataSource = person.Course;
  16: courseGrid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);

The following screenshot shows the application of the example code.

Saving Changes and Closing Connections

The entity framework follows the disconnected data model introduced in ADO.NET. The data is cached locally at each client and all changes are written back to the database through INSERT, UPDATE and DELETE statements. To save all changes made to a particular entity, schoolContext in our example, you simply call the SaveChanges() method.

// Save all changes to the school data

The schoolContext uses an ADO.NET connection to work with the data stored in the database. The connection is opened when the data is accessed. We used an ObjectQuery in our example. The connection is closed when the object is destroyed.
Although the garbage collector will eventually clean up connectionconnections after the object is destroyed, it is good practice to call the Dispose() event to ensure that the connection has been closed.

// Dispose the object context to ensure ADO.NET connections are closed

The entity framework can be a powerful tool which allows developers to model data specifically for their application. It also allows developers to easily bind the data to controls and manage relationships between tables. The framework can provide an additional layer of abstraction allowing developers more freedom in how the data is presented without spending a lot of time on storage and access.

The Advantage .NET Data Provider supports the entity framework giving you a powerful new option for using Advantage with your Visual Studio based applications.