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

   

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. 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.

Creating the EF Unit Test Project

10. 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.
11. 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.)
12. Copy or link the app.config from the Model EF project to the Test project.
13. 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.");
            }
        }
    }

14. Create an OrderedTest class that runs the CRUD operations in the right order: Load, Create, Edit, Delete, then TryToLoadAfterDelete.
15. 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!