IdeaBlade DevForce – Model Setup Walk-through – Sample Code

Attached here is the finished sample project, created by following the steps outlined in the IdeaBlade DevForce – Model Setup Walk-through:
Background
Step 1: The Entity Framework Project
Step 2: The DevForce Projects

Attached Code: Sample Code

Notes:
Of course I had to adjust the connection string in the ModelEF’s app.config file.
The SQL to create the tables is in the Step 1 instructions linked above.
I have not included the DLL’s and Installs in the dependencies and installations folders. These can be readily downloaded from the various vendors’ sites.

Configure the Key when using Views as the basis for Entities

When using a View as the basis for an Entity in the Entity Framework (rather than a Table), make sure that the Key fields/properties are configured properly in the Model (EDMX).

We recently experienced an anomaly where a Select All from a View-based Entity was not returning All the Entities we expected. There weren’t any filters, sorts, or joins, it was a straight select using Linq to Entities.
Apparently, when we added the View to the Model, EF picked some columns (per its own magical algorithm) and made those the Key fields/Properties. When it then loads the Entities from the recordset, any record with the same Key replaces an Entity that already has that Key. So several of the records were, because of the EF generated Key, being replaced this way.
We corrected the Key on the Entity (in both the Conceptual model and the Storage model), and the Select All returned all the right Entities, as expected.
In our case, the View only returns 5 columns, so it was simple enough to make all the fields part of the Key. For a larger query, if no compound key is apparent, I expect we would need to add a RowId to the View so that the EF can use that as a surrogate Key. We do not actually use the View Key in the app (we only use the values in those rows to populate a list box that we then parse into several fields on selection), so we can use a surrogate Key without being concerned about the actual values that are actually being used.

Purpose and Principles of the Data Layer

At its core, the Data Layer’s main purpose is:

Data Layer’s Main Purpose:

To abstract all interactions with the database so that business objects can be written to deal with business rules, not with database interaction.

For example, when promoting a standard Deal, the business logic comprises:

  1. validate that the Deal is in a promotable state
  2. promote the Deal
  3. generate the related Deal Transactions
  4. generate the related Confirmation

In addition to these items, there are interactions with the Deal Pricing, Deal Costing, Deal Transaction Pricing and a variety of other lookup tables. Records need to be saved, retrieved, updated and deleted to facilitate the persistence of the Deal’s new “Promoted” state. The Data Layer will encapsulate the code that interacts with the database, so the Deal Promoter class only needs to be concerned with the business rules of promoting a deal, and not with the mechanics of persisting those changes to the database.

ORM Principles

In order to effectively serve as an Object-Relational Mapping (ORM) tool, the Data Layer needs to implement the following principles.

Principle 1: The Data Layer should generate the code necessary to deal with different tables and should provide a common API for working with the resulting data objects.

A typical database interaction involves the following steps:

  1. Get the connection string
  2. Open the connection
  3. Create the command object
  4. Execute the command
  5. Close the connection

The only thing that changes from table to table is the names of the table and its columns. All of the database code is identical.

Principle 2: The Data Layer should be able to work with the entire Object Graph by saving and retrieving related objects as a set.

Some objects are more complex than others, for example, a Deal has Pricing, Charges and Transactions that are an integral part of it. It also has Confirmations that are related to it. When saving a Deal, the pieces and parts of the Deal should get saved too.

Principle 3: The Data Layer should handle failures within the context of a transaction and roll back the changes to a consistent, stable state.

Sometimes, when saving a complex object, an error may occur in one of the pieces. In this case, the Data Layer should gracefully handle the error and leave the object and the database in a stable, consistent state.

Principle 4: The Data Layer should intelligently map database tables to appropriate Business Objects.

In several cases, a business object will represent a concept differently than the database might. For example, the database table EMPLOYEE contains all the records for the Employees, Managers and Direct Reports business classes.

Principle 5: The Data Layer should handle concurrency properly.

When objects are saved to the database, concurrency problems arise because the data being saved is about to overwrite data that has already changed since it was last retrieved. Concurrency resolutions include: Overwrite, Merge and Discard. The Data Layer needs to support these options and allow developers to choose which resolution to employ.

Query Support

Principle 6: The Data Layer should support LINQ.

In order to provide data sources for drop downs and grids the Data Layer needs to be able to support querying, including sorting, grouping and summarizing. There are three choices to do this:

1) Oracle native SQL queries

Implementing this technique often pushes Business and UI logic all the way back into the database. It makes ORM a challenge as the query objects are not like table-based as they are not updateable and do not usually have the necessary key fields.

2) Custom querying support in the Data Layer

Implementing this technique is complex, non-standard and may have performance issues.

3) LINQ (The .Net framework’s built in query language)

Implementing LINQ provides powerful, sophisticated, query capabilities. LINQ to Entities also raises performance by taking advantage of the Entity Framework’s knowledge of the database objects.

Principle 7: The Data Layer should support asynchronous communication.

One of the worst aspects of application performance is the perceived lag while waiting for data to be retrieved from a database, transferred over the network and rendered in the UI. Asynchronous communication is the recommended way to prevent this lag by allowing the UI to be responsive while the data is retrieved, transferred, and even rendered, asynchronously. In Silverlight, all network communication is asynchronous, so the Data Layer must support asynchronous communication.

Oracle Support

Principle 8: The Data Layer should support Oracle specific features, such as Sequences, Packages and Oracle Data Types.

Most Oracle tables have a key field that is a numeric tied to a Sequence value. Much of the legacy code is embedded in the Database in Oracle Packages. There are also some Oracle specific Data Types (particularly LOB’s) that need to be translated to/from their .Net equivalents. The Data Layer needs to handle all three of these situations properly.

Trouble Shooting Support

Principle 9: The Data Layer should support granular logging for debugging and troubleshooting.

When debugging and troubleshooting, a detailed log of what is happening can be a very useful tool. Especially in asynchronous or Inversion of Control situations where the code cannot be easily stepped through, a log is critical to the discovery and elimination of bugs.

Performance Enhancement

Principle 10: The Data Layer should support server-side and client-side caching to improve performance.

Data Caching on the Server-side allows redundant calls for data from different clients to be served in a single database request. Data Caching on the Client-side allows redundant calls for data on the client to be served on the client without any network traffic at all.

Principle 11: The Data Layer should support validation at the client and at the server to improve performance.

Eliminating round trips by providing client-side validation will improve performance. Providing server-side validation will ensure data integrity at the server.

IdeaBlade DevForce – Model Setup Walk-through – Step 1: The Entity Framework Project

Our Problem:


We need to create an Enterprise application that is web-based, has an Excel-ish interface and pulls data from an Oracle database.

Technologies Chosen*:

  • Silverlight for the UI
  • Prism 2 for the modular framework
  • Unity for DI
  • IdeaBlade DevForce for the Business Object Layer and Async functionality
  • MS Entity Framework for the ORM
  • DevArt dotConnect for Oracle (EF Drivers)
  • Oracle 11 Database

* See here for our reasoning.

Prerequisites:


Install the DevArt dotConnect for Oracle drivers. (This includes the DevArt Entity Developer tool).

Database Structure:


For this walkthrough, I have created the following table:

CREATE TABLE SAMPLE_WIDGET 
(
    SAMPLE_WIDGET_ID int,
    DESCRIPTION varchar(100),
    CONSTRAINT SAMPLE_WIDGET_PK 
    PRIMARY KEY (SAMPLE_WIDGET_ID)
);
/
CREATE SEQUENCE SAMPLE_WIDGET_ID_SEQ;
/
CREATE TRIGGER SAMPLE_WIDGET_GETSEQ 
BEFORE INSERT
ON SAMPLE_WIDGET
FOR EACH ROW WHEN (NEW.SAMPLE_WIDGET_ID IS NULL)
BEGIN
    SELECT SAMPLE_WIDGET_ID_SEQ.nextval INTO :NEW.SAMPLE_WIDGET_ID FROM dual;
END;
/
INSERT INTO SAMPLE_WIDGET (DESCRIPTION) VALUES ('Widget 1');
INSERT INTO SAMPLE_WIDGET (DESCRIPTION) VALUES ('Widget 2');
INSERT INTO SAMPLE_WIDGET (DESCRIPTION) VALUES ('Widget 3');
INSERT INTO SAMPLE_WIDGET (DESCRIPTION) VALUES ('Widget 4');
COMMIT;
/

Creating the EF Project

  1. In Visual Studio 2008, select File | New Project | C# Class Library.

    Naming the Project
    There will be 3 model projects:

    • The EF project
    • The server-side DevForce project
    • The client-side (Silverlight) DevForce project

    We have opted to follow a [Company].[Application].[Component].[Module][Suffix] project naming convention.
    For example: Acme.BirdTrap.Model.WidgetSL would denote a Silverlight Model project for the Widget module of the BirdTrap application at Acme company.

    For this walkthrough, I will name the EF project:

    Acme.BirdTrap.Model.WidgetEF.csproj

    And, I will name the Model solution:

    Acme.BirdTrap.Model.sln

    Project Creation Dialog

  2. Delete the Class1.cs
  3. Add the references so we can use Oracle and the EF.

    But, wait. Before we add the references, we need to decide where the referenced items will live. Every solution should carry with it all the components (DLL’s) that are not part of the official Microsoft environment and that are needed to build it. These should be checked in to SCM with that solution. If not, then developers will be pointing projects to their Program Files directory and/or other random places when they add references to projects.
    To address this problem, we create two solution level folders, one for dependencies and one for installations.

    Project Folders

    The installation folder.
    Contains the Full Install package for each of the Third Party tools we use. These are checked in and promoted along with the code. This ensures that everyone is building with the same set of tools. If someone updates their PC and our solution to use a new version of a tool, the promoted code will be accompanied by a new Install package so all developers can update the version of the tool their using before trying to compile the updated code.
    The dependencies folder.
    Contains all the DLL files that are referenced by the projects in the Solution, except the ones that are part of the Microsoft .Net Framework. In so far as technologies like Silverlight are installed to the GAC as extensions to the .Net Framework, those DLL’s are referenced from the GAC and are not copied to the dependencies folder. All other referenced DLL’s ARE copied to dependencies though. This includes the Silverlight Toolkit files, the IdeaBlade files, DevArt files, etc.

    So, before we can create the references in our WidgetEF project, we need to create our installations and dependencies folders and copy the DLL files from the installed locations in Program files to the appropriate locations in the dependencies folders.

    Now the files are in the right place, we can add the references to the WidgetEF project.

    1. From the Add Reference dialog, on the .NET tab, select the System.Data.Entity 3.5 and System.Runtime.Serialization 3.0 components.
    2. On the Browse tab, navigate to the dependencies\DevArt dotConnect folder, select the DevArt.Data.Oracle.Entity.dll.
  4. Create the Entity Model. In the WidgetEF project, select to Add a New Item. Select the Data category, and choose the DevArt Entity Model item. When naming the model file, we have been naming them Model_EF.
    For this walkthrough, I will name the model file: Model_WidgetEF.edml.
    At this point, DevArt’s Entity Developer will open.

    1. Select the Project |Properties menu item. Select the Model tab.
    2. Change the EntityContainerName to Entities. The one that the tool provides is based on the model file name, which is not useful to us within the application. Naming ALL of your model Containers as Entities means that all of your modules can use Namespace.Entities.ObjectClass to access the object classes.
    3. Change the Namespace to the correct one. Again, the default is based on the model file name, so it will never be correct. I used: Acme.BirdTrap.Model.WidgetEF.
    4. Check the “View Generation” checkbox.
    5. Use the Database Explorer pane to connect to your database.
    6. Drag the SAMPLE_WIDGET table over to the Model pane.
    7. When we created the SAMPLE_WIDGET table, we used a SEQUENCE and a TRIGGER to auto-increment the primary key Id field. This makes our table behave the same way as an Identity column in SQL Server, which EF knows how to deal with. We need to tell our model, that the SampleWidgetId field is an autonumber field. DevArt has made this easy.
      In the Project Explorer, there are two main branches.
      The top branch (Acme.BirdTrap.Model.WidgetEF), is the Conceptual (or Object) model.
      The lower branch (Acme.BirdTrap.Model.WidgetEF.Store) is the Storage (or Database) model.
      (The Mapping model is not represented in this tree because it is handled in the Mapping editor for each Class/Property in the Conceptual model.)

      1. Open up the lower branch (the .Store branch).
      2. Expand the Tables/Views branch.
      3. Expand the SAMPLE_WIDGET table branch.
      4. Select the SAMPLE_WIDGET_ID column.
      5. Now, in the Properties pane, you will see the properties of the SAMPLE_WIDGET_ID column.
      6. Select the Store Generated Pattern property and change the value to Identity.
    8. Save the Model.
    9. Close the Entity Developer.

  5. Back in VS, expand the file tree below the new .edml file so you see the following files:
    Model_WidgetEF.edml
    The XML definition of the model itself. This corresponds to the .edmx file created by the MS Entity Designer. You will usually edit this file using the DevArt Entity Developer tool, but you will occasionally need to edit the XML directly in a text editor. There is plenty of technical documentation on MSDN about this file, but none of it is particularly instructive.
    Model_WidgetEF.cs
    A one-time generated file with a Partial declaration of the Entities class for you change as needed. (We change it in one of the steps below.)
    Model_WidgetEF.Designer.cs
    The generated code for your entities. Do not change this file. Period.
    Model_WidgetEF.edml.view
    The diagram layout, written in XML. This file is only relevant to the Entity Developer tool.(And I love that DevArt put this in a separate file instead of tacking it on the bottom of the EDMX file like MS did.)
    Model_WidgetEF.edps

    This XML file defines the tool settings, output path and connection string used by the Entity Developer and the code generator. You will not need to change this file until you need to build against a different database. These changes will be explained further in a later posting on building and deploying the model solution.
    Model_WidgetEF.PregeneratedViews.cs
    The pregenerated views used by the Entity Framework. If these views are pregenerated, they can be compiled into the assembly and they will not need to be generated dynamically at application startup. In my projects, this seems to have saved us anywhere from 1-10 seconds off the total spin up time.
  6. In order to fix the ORA-01790 weirdness, open the Model_WidgetEF.cs file and add this snippet:
    partial void OnContextCreated()
    {
    	DevArt.Data.Oracle.Entity.OracleEntityProviderServices.TypedNulls = true;
    }
  7. Last, but not least, in order to deploy this project to run against a different database, we will need to be able to regenerate the PregeneratedViews without running the UI tool. I.e. Regenerate from the command line. The easiest way to do that will be using a T4 template.
    There is a good article here about using a T4 template to create the views for a MS EDMX model. I started by using their CSharp.Views.tt file.

    The change necessary to make this work for our DevArt EDML model is:

    string edmxFileName = Path.GetFileNameWithoutExtension(this.Host.TemplateFile).ToLowerInvariant().Replace(".views", "") + ".edmx";

    Should be:

    string edmxFileName = Path.GetFileNameWithoutExtension(this.Host.TemplateFile).ToLowerInvariant().Replace(".pregeneratedviews", "") + ".edml";
    
  8. A weird thing happened when I added the .tt file to my project. Even though I clicked cancel, it still ran the .tt template anyway, and it created a Model_WidgetEF.PregeneratedViews1.cs file that I don’t want. To fix this, and get the .tt nested properly under the .edml file, we need to manipulate the .csproj file by hand. Close the project in VS. Open the .csproj file in the text editor of your choice. Locate this code:
    <Compile Include="Model_WidgetEF.PregeneratedViews.cs">
      <DependentUpon>Model_WidgetEF.edml</DependentUpon>
    </Compile>
    <Compile Include="Model_WidgetEF.PregeneratedViews1.cs">
      <AutoGen>True</AutoGen>
      <DesignTime>True</DesignTime>
      <DependentUpon>Model_WidgetEF.PregeneratedViews.tt</DependentUpon>
    </Compile>

    Move the contents of the second tag into the first and remove the second, so the code looks like this:

    <Compile Include="Model_WidgetEF.PregeneratedViews.cs">
      <DependentUpon>Model_WidgetEF.edml</DependentUpon>
      <AutoGen>True</AutoGen>
      <DesignTime>True</DesignTime>
    </Compile>

    Now find this code:

    <None Include="Model_WidgetEF.PregeneratedViews.tt">
      <Generator>TextTemplatingFileGenerator</Generator>
      <LastGenOutput>Model_WidgetEF.PregeneratedViews1.cs</LastGenOutput>
    </None>

    Remove the 1 from the PregeneratedViews1.cs file name and add a element, so the code looks like this:

    <None Include="Model_WidgetEF.PregeneratedViews.tt">
      <Generator>TextTemplatingFileGenerator</Generator>
      <LastGenOutput>Model_WidgetEF.PregeneratedViews.cs</LastGenOutput>
      <DependentUpon>Model_WidgetEF.edml</DependentUpon>
    </None>

    Note: The T4 engine and the Entity Developer tool will each take ownership of the .PregeneratedViews.cs file. So depending which tool ran last, the views may be nested under the .edml file, or under the .tt file.

  9. DONE. The EF model project is complete. Before going any further, let’s set up a simple unit test to make sure we have connectivity and can read data.
  10. Creating the EF Unit Test Project

  11. Add a new Test Project to the solution. We name each test project with the same name as the assembly it tests, suffixed with .Test, so in this case, it is: Acme.BirdTrap.Model.WidgetEF.Test.
  12. Add references to:
    * The model project: Acme.BirdTrap.Model.WidgetEF
    * System.Data
    * System.Data.Entity
    (You do not need to add Linq or Xml or any DevArt components.)
  13. Copy or link the app.config from the Model EF project to the Test project.
  14. Create a test class that performs the basic CRUD operations:
    using System;
    using System.Data;
    using System.Linq;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    
    namespace Acme.BirdTrap.Model.WidgetEF.Test
    {
        /// <summary>
        /// Summary description for UnitTest1
        /// </summary>
        [TestClass]
        public class ModelWidgetEfTest
        {
    
            private readonly string _testWidget = "UT" + DateTime.Now;
    
            [TestMethod]
            public void LoadWidgetsTest()
            {
                //instantiate the model
                var entities = new Entities();
    
                //get the widgets
                var widgets = from item 
                              in entities.SampleWidgets 
                              select item;
                
                //make sure we got some widgets
                Assert.IsNotNull(widgets, "LoadWidgetsTest: returned Null");
                Assert.IsTrue(widgets.Count() > 0, "LoadWidgetsTest:  Returned no widgets.");
            }
    
            [TestMethod]
            public void CreateAWidgetTest()
            {
                //instantiate the model
                var entities = new Entities();
    
                //create a sample widget
                var widget = new SampleWidget { Description = _testWidget };
                //cache the id of the new widget
                var id = widget.SampleWidgetId;
                
                //add the widget to the Entities collection
                entities.AddToSampleWidgets(widget);  
                
                //save the new widget
                var result = entities.SaveChanges();
    
                Assert.IsTrue(result > 0, "CreateAWidgetTest: Save new widget failed.");
                Assert.AreNotEqual(0, widget.SampleWidgetId, "CreateAWidgetTest: Saved Id is still zero.");
                Assert.AreNotEqual(id, widget.SampleWidgetId, "CreateAWidgetTest: Save Id did not get updated by the trigger.");
            }
    
            [TestMethod]
            public void EditAWidgetTest()
            {
                //instantiate the model
                var entities = new Entities();
                
                //get the sample widget
                var widget = (from item
                              in entities.SampleWidgets
                              where item.Description == _testWidget
                              select item).First();
                
                //make sure we got the widget
                Assert.IsNotNull(widget, "EditAWidgetTest: returned Null");
                
                //change the widget
                widget.Description = "Changed Widget";
    
                //save the changed widget
                var result = entities.SaveChanges();
    
                Assert.IsTrue(result > 0, "EditAWidgetTest: Save changed widget failed.");
                Assert.AreEqual("Changed Widget", widget.Description, "EditAWidgetTest: Saved description changed unexpectedly");
            }
    
            [TestMethod]
            public void DeleteAWidgetTest()
            {
                //instantiate the model
                var entities = new Entities();
    
                //get the sample widget
                var widget = (from item
                              in entities.SampleWidgets
                              where item.Description == "Changed Widget"
                              select item).First();
    
                //make sure we got the widget
                Assert.IsNotNull(widget, "DeleteAWidgetTest: returned Null");
    
                //delete the widget
                entities.DeleteObject(widget);
    
                //save the deletion, so the widget record is removed
                var result = entities.SaveChanges();
    
                Assert.IsTrue(result > 0, "DeleteAWidgetTest: Save deleted widget failed.");
                Assert.AreEqual(EntityState.Detached, widget.EntityState, "widget was not detached when it was saved");
            }
    
            [TestMethod]
            public void TryToLoadTheDeletedWidgetTest()
            {
                //instantiate the model
                var entities = new Entities();
    
                var widgets = from item
                              in entities.SampleWidgets
                              where item.Description == "Changed Widget"
                              select item;
    
                Assert.IsTrue(widgets.Count() == 0, "TryToLoadTheDeletedWidgetTest:  Returned a widget when it shouldn't.");
            }
        }
    }
  15. Create an OrderedTest class that runs the CRUD operations in the right order: Load, Create, Edit, Delete, then TryToLoadAfterDelete.
  16. To run the OrderedTest, open the Test View pane (menu Test | Windows | Test View). Select the Ordered Test and run it. It should be green lights all the way.

Conclusion

In this part of the walk through, we have created the Entity Framework model project for an Oracle database using the DevArt dotConnect drivers and its accompanying Entity Developer tool.

In the next part of the walk through, we will set up the IdeaBlade DevForce models that sit on top of the EF model.

Until then: Get outside and enjoy the sunshine. Which is to say, “Take the dog for a walk!” Woof!

IdeaBlade DevForce – Model Setup Walk-through – Background

I know the world is about to change in April when VS2010 goes live, however, we are still using VS2008 and will be for at least a few more weeks.  In the hopes that this walk-through provides some insight to someone, even if it is just me, here goes.

Background

We are  creating a Silverlight application for several reasons:

  1. Web development is not nearly complex enough, so we sought out a unique challenge involving the newest, least documented, voted most-likely-to-be-completely-re-done-in-the-next-version technology (excepting WWF, I mean, WF, which will probably hold that distinction for years to come).
  2. Our users do everything in Excel, meaning that much of the data entry UI for this app needs to be as Excel-ish as possible, including the dreaded Multi-Add (adding multiple rows between saves) and Multi-Edit (editing multiple rows between saves).  [FYI: It turns out that none(?) of the OTC Grids are actually designed for this.  You can make it work with Telerik if you want to try hard.  See postings like this one in Telerik’s forums for more info.  They have come a long way since that posting.]
  3. Management is opposed to fat client or virtualization, so the app has to be web-based.
  4. After much trial-and-error, and trial-and-failure, and trial-and-compromise, we deployed an ASP/Ajax solution that was less than satisfactory and not-at-all Excel-ish, so we had to find a “better way”.
  5. We have a code base that comprises 8 years of work in VB 6, classic ASP, ASP.Net 1, ASP.Net 2, ASP.Net 3.5, VB.Net (all sorts), Infragistics (an old version), Telerik (several versions), copious quantities of javascript, several generations of CSS and DHTML, including many of the really exotic first generation tricks, Ajaxified ASP pages, ASP/Ajax pages and an Oracle back-end, so we knew that ASP was not going to give us what we needed.
  6. None of us had ANY desire to explore Flex.
  7. We are a Microsoft shop, so building a Java app was not really an option.
  8. Silverlight 3 was coming and promised enough features for us to begin writing an Enterprise app.  (It turns out that we jumped the gun on this, but we were not alone and SL4 promises to remedy many of the “it’s-not-ready-for-the-enterprise” issues.)

We are using DevForce and the Entity Framework because:

  1. Having worked with Hibernate, NHibernate, and SubSonic (which I preferred over NHibernate), I was convinced that a Data Layer / ORM would make our application specific code much easier to write and would provide more and better infrastructure/plumbing than we ever could.
  2. Having been told:  “No Open Source”  and given 3 weeks to pull SubSonic out of a working project and replace it with a roll-my-own ORM, I knew that many features we needed (Concurrency and caching, to name just two) were going to be a HUGE effort to write myself, and that if this was a Make or Buy decision, that Buy was clearly the better choice.  (Check out Davy Brion’s Build Your Own Data Access Layer Series for a deeper examination of the Make option.)
  3. Having been exposed to DevForce Classic a few years ago, I knew their product provided much of what we needed, and they were on the cusp of releasing a Silverlight version of their WPF framework.  As an added bonus, their documentation, and Ward Bell’s blog, are highly readable and provide excellent project guidance and design philosophy.
  4. DevForce sits on top of Entity Framework, which does not support Oracle, but as this was about to kill the deal, we found that DevArt’s Oracle drivers were finally supporting EF properly.

We are using Prism and Unity because:

  1. If you’re gonna do this thing, you might as well go all in.
  2. Having worked with CAB for a Windows App, I understood the potential of a component based application framework — or at least imagined I understood it, can anyone really understand anything PnP publishes?
  3. After dabbling in the Java world for a few months, I had developed an appreciation for Spring, Dependency Injection, configuration over coding and convention over configuration.
  4. I am unrepentant about preferring Agile development practices, and many of the “best practices” prescribed by PnP and implemented in Prism, represent many Agile coding principles in action.  Both VersionOne and RallyDev have Agile 101 documentation on-line.   Mike Cohn has written several excellent books introducing teams to Agile practices.

So we ended up with the following technology stack:

  1. Prism 2 for its Modular framework
  2. Unity for Dependency Injection
  3. Microsoft Silverlight 3 for the UI
  4. Telerik for Silverlight for several of its UI components (alas, we still have to do far too much ourselves in this area though)
  5. IdeaBlade DevForce for Silverlight for its Silverlight friendly Business Entity model and asynchronous client-server communication layer
  6. DevArt dotConnect for Oracle drivers for its support of the MS Entity Framework and it’s excellent Entity Developer tool
  7. Microsoft Entity Framework for the server-side Entity Model and database connectivity

Moving Code from the Developer’s Integration Environment to the QA Environment

When we’ve completed coding (and, if we’re feeling especially adventurous, testing said code), we promote our locally developed code to the Dev server. This serves as our integration environment. CruiseControl.Net watches our SCM for any changes promoted to the DEV branch, then it:

  1. Updates the configuration files to point to the Dev server instead of the developer’s local workstation;
  2. Builds our solution in Debug mode (for more verbose logging during the unit test runs);
  3. Runs the unit tests;
  4. Builds the solution again in Release mode;
  5. Runs the unit tests again to ensure they work in Release mode too; and
  6. Finally, publishes the necessary files to IIS.

Today, I need to figure out how much has to change to get this project onto our QA server.

The Problem

Pointing the QA environment to the QA database.

The biggest difference between the process described above and the process to move to QA is going to be changing the back end database that the solution points to. I.e. The Developer’s local workstation and the Dev server both point to the Dev database. (It’s an Oracle back end, so we do not have a separate local and server database — even if we wanted to, but I’m not sure we would.)

ORACLISM

An aside about Oracle schema ownership. As a SQL Server developer, there are several Oraclisms that confound me. I’m not sure that this is one of them, but here’s my take on it: In SQL Server, most databases are created by dbo and most tables are created as dbo. Security is then applied to users and groups to grant access to various tables. If you need to create a Dev database and a QA database for the Production database named AccountingSystemDB, you might create a new database, again as dbo, called AccountingSystemDEV and another called AccountingSystemQA. In Oracle, this notion of separate databases is superseded by a notion of separate schemas WITHIN a single database. There is no obvious corollary to dbo. The Oracle system user serves a very different purpose, perhaps relating more to the notion of sa in SQL Server. Instead, an Oracle DBA will create a Schema Owner (i.e. a user?) and then create the Schema as that owner, granting rights to that Schema to users.

For example, the Oracle Server has a single Database instance, within which, you will see three schemas named ACC_SYS_DV, ACC_SYS_QA and ACC_SYS_P. Within each schema, you will find that schema’s version of the tables. I.e. ACC_SYS_DV.ACCOUNT, ACC_SYS_QA.ACCOUNT and ACC_SYS_P.ACCOUNT. Also, a lot of database apps, use a single Application User Id to access the database, relying on the Application to implement security. (Or at least, ours does.) For each of the three environments, this Application User logs in to one of the three Schemas.

I am concerned that Entity Framework knows the Database and Schema in the connectionstring in the app.config for the ModelEF project:

 ...
 <edmKeys>
  <edmKey
   name="DataSource"
   connection="metadata=res://ModelEF/ModelEF.csdl| res://ModelEF/ModelEF.ssdl| res://ModelEF/ModelEF.msl; provider=Devart.Data.Oracle; provider connection string=" User Id=APPDV; Password=*****; Server=devTns.world; Home=oraodac11g_home1;Persist Security Info=True""
   containerName="ModelEF.Entities">
   ...
  </edmKey>
  ...
 </edmKeys>
</ideablade.configuration>

Above we see the Oracle server instance devTns.world, and the Dev database schema owner APPDV. There is also a Home= entry pointing to the developer’s OraHome that needs to be removed.

EF then maps the Schema owner into the EDML (EDMX) file:

...<EntitySet
  Name="ACCOUNTs"
  EntityType="ModelEF.Store.ACCOUNT"
  store:Type="Tables"
  Schema="APPDV" <=NOTICE DV SCHEMA OWNER IS HERE
  Table="ACCOUNT" />...

If this was a SQL Server solution, the schema owner would be the same (whether it is dbo or not) in the AccountingSystemDEV and AccountingSystemQA databases. In Oracle, this is not the case, so I am curious if I will need to update the Schema tag in the EDML (EDMX) . If so, the promotion to QA will force a rebuild, not just a reconfigure.

The Ideablade configuration also knows the Database and Schema in the connection attribute of the edmKey tag in the Model project’s app.config and in the web host project’s web.config:

<configuration>
 <connectionStrings>
  <add name="Model.EFConnectionString"
   connectionString="metadata=res://*/ModelEF.csdl| res://*/ModelEF.ssdl| res://*/ModelEF.msl; provider=Devart.Data.Oracle; provider connection string=&quot; User Id=APPDV; Password=*****; Server=devTns.world; Home=oraodac11g_home1; Persist Security Info=True&quot;"
   providerName="System.Data.EntityClient" />
 </connectionStrings>
</configuration>

[It turns out, I do need to change the tags, change the connection strings, and regenerate the pregenerated views.]

A SOLUTION

Since we are using CruiseControl.Net (CCNet) for our automated build, and since we are using Nant for some of the less “build-ish” and more “configure-ish” tasks, I was able to add a Nant task in the CCNet project configuration that does a string replace in all the EDML (EDMX) and .config files, and then uses the T4 Template technique to recreate the Pregenerated Views.

In order to use the T4 Template attached to the ADO .Net blog, I had to make two changes because we are using Devart Entity Developer. First, the .tt file needs to be renamed from ModelEF.Views.tt to ModelEF.PregeneratedViews.tt. Second, the following line in the .tt file has to be changed:

string edmxFileName = Path.GetFileNameWithoutExtension(this.Host.TemplateFile).ToLowerInvariant().Replace(".views", "") + ".edmx";

Should be replaced with:

string edmxFileName = Path.GetFileNameWithoutExtension(this.Host.TemplateFile).ToLowerInvariant().Replace(".pregeneratedviews", "") + ".edml";

Here is the CCnet task in the CruiseControl.Net ccnet.config file to execute the Nant task:

<project name="PromoteToQA">
 ...
 <tasks>
  <nant>
   <buildFile>ChangesBeforeBuild-QA.build</buildFile>
   <executable>E:\Program Files\nant-0.86-beta1\bin\nant.exe</executable>
  </nant>
 </tasks>
 ...
</project>

And here is the Nant task itself:

<project name="ChangesBeforeBuild-QA" default="run">

 <target name="run">
  <call target="change Database" />
 </target> 

 <target name="change Database">
  <foreach item="File" property="filename">
   <in>
    <items>
     <include name="**/*.edml" />
     <include name="**/app.config" />
     <include name="**/web.config" />
     <include name="**/*EF.Designer.cs" />
     <include name="**/*EF.edps" />
    </items>
   </in>
   <do>
    <attrib file="${filename}" normal="true" />
    <loadfile file="${filename}"
     property="file.textContent">
     <filterchain>
      <replacestring from="APPDV" to="APPQA" ignorecase="true"/>
      <replacestring from="*****" to="*****" />
      <replacestring from="devTns.world" to="qaTns.world" ignorecase="true"/>
      <replacestring from="Home=oraodac11g_home1;" to="" />
     </filterchain>
    </loadfile>
    <echo message="Writing Database Change to: ${filename}" />
    <echo file="${filename}">${file.textContent}</echo>
   </do>
  </foreach>
 </target>
 
 <target name="Recreate Precompiled Views Files">
  <!-- unlock the view files -->
  <foreach item="File" property="filename">
   <in>
    <items>
     <include name="**/*.PregeneratedViews.cs" />
    </items>
   </in>
   <do>
    <attrib file="${filename}" normal="true" />
   </do>
  </foreach>
  <!-- transform the *.PregeneratedViews.tt files to recreate the *.PregeneratedViews.cs files -->
  <foreach item="File" property="filename">
   <in>
    <items>
     <include name="**/*.PregeneratedViews.tt" />
    </items>
   </in>
   <do>
    <echo message="Transforming: ${filename}" />
    <exec program="dependencies\TextTransform\TextTransform.exe">
     <arg value="${filename}"/>
    </exec>
   </do>
  </foreach>
 </target>
 
</project>

And that’s it. Before CruiseControl.net starts running the actual tasks, it runs this Nant task to prepare the source files for the QA environment.

Why does he keep saying EDML instead of EDMX?

In the Microsoft Entity Framework (EF), the GUI for the Entity Model is called the Entity Designer.  When you create an Entity Model, the model itself is stored in an XML file named model.EDMX.

The EDMX file is: 

a) an XML representation of the Entities and Relationships in your Database (the Storage Model, or SSDL), your application (the Conceptual Model, or CSDL), and the mapping from one to the other (the  Mapping Specification, or MSL); and

b) a repository for the metadata that defines the Entity Designer Diagram layout.

And this works out just fine for SQL Server.  Microsoft provides a nice little driver that is EF compatible, they provide a nice little GUI to create your Model with, and SQL Server tables — if named “normally”, i.e. Customer, Account, etc. — map beautifully to your object model.

This is NOT the case for Oracle.  Oracle still holds its nose whenever anyone says “Microsoft”, so they haven’t, and won’t, provide a driver for EF.  Heaven forbid they should make it easy to use Visual Studio and the .Net framework to work with their data.  Nope — Java, Eclipse, and the Oracle Application Framework are their preferred toolset.

So what is a .Net developer to do?  How can one create an EF model from an Oracle database?

ANSWER:  DevArt

DevArt has gone to the trouble of creating ADO.Net drivers for a variety of databases, including SQL Server and, da-da-da-da, Oracle.

I have been using DevArt’s dotConnect for Oracle drivers for EF purposes for about a year now.  In the early days, they were quite quirky.  I posted to their forums regularly.  Every single problem, suggestion, requirement or wish list item that I posted has been implemented, usually in their next release.  (They post quiet releases every 4 to 6 weeks.  Yay for Agile Development.)  Their latest version (5.35.79 as of this writing) works flawlessly for my purposes.

Anyway, included with their dotConnect drivers, is the DevArt Entity Developer.  This tool ROCKS!  Over the past year, it has matured to the point that I have completely abandoned using the MS Entity Designer for anything.  Instead, I create  and maintain my Entity Model’s using the DevArt Entity Developer. 

Anyway, in order to not step on anyone’s toes, DevArt saves the Entity Model as an EDML file, not an EDMX file.  (Yes, this is the “exact same” Entity Model XML that MS saves.)  They also store a bunch of metadata and project selections in a set of generated files behind the EDML file.  And, as of December, you can select to have the Entity Developer automatically generate the precompiled views file too.  (MS doesn’t do that and provides a 30-step process to accomplish it — A problem that has spawned many web posts.)

One of my favorite features of the DevArt Entity Developer over the MS one, has to do with an Oraclism that just bugs me. 

ORACLISM
Oracle DBA’s, (by tradition, by culture, or by requirement, I’m not sure which), often name database objects in all caps with underscore delimiters.  For example, a table named CustomerAccountType in SQL Server, will probably be named CUST_ACCT_TYP in Oracle.  (There’s an unreasonably low limit to how long the name can be in Oracle, so the DBAs often have to truncate or compress names to make them fit.) 

So, if you have a table defined thus:

CREATE TABLE  CUST_ACCT_TYP (
CUST_ACCT_TYP_ID NUMBER(10) NOT NULL,
TYPE_DESCRIPTION VARCHAR(20) NOT NULL)

Now, you drag and drop that table into the MS Entity Designer*, you will get an object defined thus:

Class:     CUST_ACCT_TYP
Property:  CUST_ACCT_TYP_ID, Double
Property:  TYPE_DESCRIPTION, String

Not a very “.Net-ish” way of defining a class, is it?  Also notice the NUMBER(10), an integral number type, is mapped to a Double.

DevArt’s Entity Developer, however, does a grand job of converting all those names, so you end up with an object defined thus:

Class:     CustAcctTyp
Property:  CustAcctTypId, Int64
Property:  TypeDescription, String

Notice how it even got the data type right for the Id. 

Now, if only I could UPDATE my model (instead of recreating the affected parts of it) when I change the Tables and Views in Oracle… :)

* By the way, you still need the DevArt drivers to do  this.

Follow

Get every new post delivered to your Inbox.