Monday 30 April 2012

Configurable OData Service - Part 5 – End To End Example

This is the final post in the series on the configurable OData service. In this post I will cover an end to end example of how the Domain Model Service and be configured and used by an OData client. For the sake of simplicity I am going to expose a simple database with only one table, the table is called Employees. The script below shows the layout of the table.

CREATE TABLE [dbo].[Employees](
    [EmployeeID] [uniqueidentifier] NOT NULL,
    [LastName] [nvarchar](20) NOT NULL,
    [FirstName] [nvarchar](10) NOT NULL,
    [Title] [nvarchar](30) NULL,
    [BirthDate] [datetime] NULL,
    [HireDate] [datetime] NULL,
    [Address] [nvarchar](60) NULL,
    [City] [nvarchar](15) NULL,
    [Region] [nvarchar](15) NULL,
    [PostalCode] [nvarchar](10) NULL,
    [Country] [nvarchar](15) NULL,
    [HomePhone] [nvarchar](24) NULL,
    [Extension] [nvarchar](4) NULL
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO

The Domain Model Service is deployed to IIS via its WIX based installer which takes care of configuring the service to execute under IIS.

DomainModelIIS

There are two services exposed from the Domain Model Service

  • DefinitionService.svc
    • Implements the functionality to define the shape and structure of the exposed Domain Model Service
  • ManagementService.svc
    • Implements functionality to start and stop instances of the Domain Model Service

Before jumping into the client console application I will just mention the configuration settings that are required to configure the service.

<?xml version="1.0" encoding="utf-8"?>
<domainModel 
  tablePrefixFilter="" 
  connectionStringName="DomainModelMetadata" 
  targetStoreConnectionStringName="TargetDatabase" 
  domainModelServiceBaseAddress="http://127.000.000.001:8080"/>

The previous configuration fragment contains the following elements:
  • tablePrefixFilter
    • A prefix that is applied by the service when enumerating tables from the database
  • connectionStringName
    • The connection string element name for the store that contains the Domain Model metadata
  • targetStoreConnectionStringName
    • The connection string element name for the target database that will be exposes as an OData service
  • domainModelServiceBaseAddress
    • The base address that a Domain Model Service will be exposed relative too. Notice that the service base address is http:// not https:// more on this later
Listing the Target Schema

After creating the proxy to the service. The first step is to retrieve the table metadata from the Domain Model Service, this is used as reference information to define a domain model.
DefinitionServiceClient client = new DefinitionServiceClient();


client.ClientCredentials.UserName.UserName = "xxxxxx";
client.ClientCredentials.UserName.Password = "xxxxxx";
client.ClientCredentials.ServiceCertificate.Authentication.CertificateValidationMode = System.ServiceModel.Security.X509CertificateValidationMode.None;


client.Open();


var tableMetadata = client.GetTableMetadata();


foreach (var item in tableMetadata)
{
    Console.WriteLine(item.Name);


    foreach (var column in item.Columns)
    {
        Console.WriteLine("\t" + column.Name);
    }
}

For the example database this produces the following output:

schemalist

We now have the list of the tables and columns from the target database. This information is intended for consumption in a designer that will allow for a Domain Model Service to be defined by an end user.

Defining and Activating Domain Model Service

Now that we can query the table metadata the next step is to create a Domain Model Definition. This is achieved by creating a proxy to the definition service and calling the CreateDomainModelDefinition
DefinitionServiceClient client = new DefinitionServiceClient();


client.Open();



...


domainModelDefinition = client.CreateDomainModelDefinition(domainModelDefinition);

Then the Domain Model needs to be activated. This is achieved by calling the ActivateDomainModel method of the management service proxy.

ManagementServiceClient managmentServiceClient = new ManagementServiceClient();
managmentServiceClient.ActivateDomainModel(domainModelDefinition.Identifier);

The queried metadata is mapped to the entities that are to be exposed by instances of a Domain Model service. In this example I will expose two versioned “views” of the Employees entity. As an example of how the exposed Domain Model Service entity can diverge from the underlying database I will expose the Employees entity as an entity called People.

Version 1
DomainModelEntity domainModelEntity = new DomainModelEntity();
domainModelEntity.TableName = "Employees";
domainModelEntity.TypeName = "People";
domainModelEntity.Properties = new List<DomainModelProperty>();
domainModelEntity.Properties.Add(new DomainModelProperty()
    {
        ColumnName = "EmployeeID",
        PropertyName = "Identifier",
        IsIdentity = true
    });


domainModelEntity.Properties.Add(new DomainModelProperty()
{
    ColumnName = "FirstName",
    PropertyName = "Givename"
});


domainModelEntity.Properties.Add(new DomainModelProperty()
{
    ColumnName = "LastName",
    PropertyName = "Surname"
});


DomainModelDefinition domainModelDefinition = new DomainModelDefinition()
    {
        Entities = new List<DomainModelEntity>(),
        Comments = "Version 1"
    };


domainModelDefinition.Entities = new List<DomainModelEntity>();
domainModelDefinition.Entities.Add(domainModelEntity);

For version one  of the Domain Model the Employee table is exposed as an entity called People. The three required fields are exposed as aliases of the underlying columns. The Employee is mapped to a field called Identifier. After the definition is created we can query the service at the address “http://localhost:8081/V1/DomainModelService” which will describe the atom for the “Peoples” entity set.

version1

The metadata can be queried from this OData service at “http://localhost:8081/V1/DomainModelService/$metdata”, which shows only the three fields that where mapped.

version1metadata

Using the ODataExplorer tool we can perform some query and updates to the data exposed by the service.

OdataV1

Unfortunately the ODataExplorer does not allow you to insert entities, so for the example I pre seeded the table with a number of rows. And yes i am that unoriginal to come up with the names of people.

OdataV1Data

The data can be modified using the ODataExplorer

OdataV1DataChange

We can see the changes reflected in the database

RawDatachanged

Just to prove that this is a fully functioning OData service you can see that the data can also be queried.

OdataV1DataQuery

Version 2
For Version 2 of the Domain Model we can add a more of the underlying fields to the entities. For the sake of brevity I will add only one the BirthDate.

DomainModelEntity domainModelEntity = new DomainModelEntity();
domainModelEntity.TableName = "Employees";
domainModelEntity.TypeName = "People";
domainModelEntity.Properties = new List<DomainModelProperty>();
domainModelEntity.Properties.Add(new DomainModelProperty()
    {
        ColumnName = "EmployeeID",
        PropertyName = "Identifier",
        IsIdentity = true
    });


domainModelEntity.Properties.Add(new DomainModelProperty()
{
    ColumnName = "FirstName",
    PropertyName = "Firstname"
});


domainModelEntity.Properties.Add(new DomainModelProperty()
{
    ColumnName = "LastName",
    PropertyName = "Lastname"
});


domainModelEntity.Properties.Add(new DomainModelProperty()
{
    ColumnName = "BirthDate",
    PropertyName = "DateOfBirth"
});


DomainModelDefinition domainModelDefinition = new DomainModelDefinition()
    {
        Entities = new List<DomainModelEntity>(),
        Comments = "Version 2"
    };


domainModelDefinition.Entities = new List<DomainModelEntity>();
domainModelDefinition.Entities.Add(domainModelEntity);


Again the metadata can be queried from the instance of the service that exposes this version of the Domain Model. For the second version the Url is “http://localhost:8081/V2/DomainModelService/$metdata”.

version2metadata

Again the ODataExplorer tool can be used to query data.

OdataV2Data

We can also update the entities to change the value of the underlying data in the database.

OdataV2DataChange

RawDatachangedV2

Multiple versions of Domain Models can be exposed using this service, they can contain multiple entities.

Further Improvements

This version of the Domain Model service satisfies the initial requirements but there are a number of areas in which improvements and enhancements can be made. The following is a brief discussion of these enhancements:
  • Entity Identity
    • At the moment the implementation makes the assumption that all entities exposed from the Domain Model have an identifier of type of System.Guid. This is fine for the intended target schema that this component will expose, but it is not a truly flexible approach. A better approach would be to allow for the data type  of the identity field to be specified when creating the Domain Model. The DynamicDataContext class would then use a different mechanism to generate and manipulate the  identity field.

  • Security
    • The DefinitionService and the ManagementService exposed by the Domain Model Service are secured using message level security. The Domain Model Service instances that are instantiated and exposed are not secure. They are exposed as http endpoints. With some additional configuration they can be exposed as https and use any of the following methods of security.

  • Relationships
    • The current implementation supports only exposing entities that do not have relationships to other entities defined in the domain model. It is possible to enhance the service to support relationships between entity types by extending the IDataServiceMetadataProvider and IDataServiceUpdateProvider derived classes.

Hopefully this series of posts has given a good overview of the implementation of a configurable OData service and given a good example of how a Custom Data provider can be implemented to source data.

4 comments:

  1. Congratulations on the article. Maybe share the source code?

    ReplyDelete
    Replies
    1. Thanks

      Unforunately I cant share the source code as this is a component that is part of a commercial product for the company I work for, legally I cannot. If that was not the case I would be more than happy to release the source code.

      The articles try to give as much of an overview as possible for someone to try this on their own.

      Delete
  2. Derek, as the article is now a few years old, if developing today, how would you differ implementation using WebAPI vs. WCF? And would you not use OData and simply utilize plain REST? Any thoughts?

    ReplyDelete
  3. Thomas,At the time this was implemented the company that it was designed and built for where invested in WCF. That was the reason for the choice of webapi vs wcf. If I where to implement this again unencumbered by existing investment/commitment I would use webapi as it supports the later V4 ODATA specification. WCF Data Services supports V3 of the specification and MS is not adding further enhancements.

    As for ODATA vs REST at the time of development, if memory serves me right, this component was to be integrated into an existing three tier web application based on ASP.NET and MVC with EF on top of SQL Server. There was a requirement to maintain the existing domain/object model semantics in this new configurable odata service. One of the requirements was to maintain the ability to query and aggregate data hence the choice of ODATA. Also this component was to be the integration layer with third parties so they could do some simple adhoc reporting. Choosing a standards based implementation instead of a roll your own was felt to be a better approach as there where a number of customers who used odata compatible reporting tools.

    If the aggregation and querying capability where not required then yes plain REST could be used but that would be a completely different implementation.

    ReplyDelete