Saturday, December 5, 2009

Three-tier .NET Application Utilizing Three ORM Technologies

1 Introduction

This article presents a three-tier .NET WinForms accounting application, called "LEK", that can be configured at run time to access its data (in a SQL Server database) using either LINQ to SQL, Entity Framework or NHibernate.  The download for the article contains the Visual Studio 2008 solution, including everything you need to build the application.  The source code included in the download, and the article itself, are organized so that it is as easy as possible to compare how the same functionality can be achieved with each of the three ORMs.  If you are already familiar with one of these three ORMs, and need to learn one of the other three, then I think you will be able to use this article leverage your knowledge of one to learn the other.

2 Contents at a Glance

3 A Few Screen Shots

Contents at a Glance   Detailed Contents

Here is the form that comes up when you launch LEK:

001Launch.gif

Here is the form for creating and querying for accounting transactions:

002Transactions.gif

4 Detailed Contents

5 Background

Contents at a Glance   Detailed Contents

If you have used an ORM (object-relational mapping) framework before, you know that they can eliminate a lot of code that is relatively tedious and error prone.  It is relatively easy to play around with Query Analyzer or SQL Server Management Studio to come up with some SQL to move data into or out of a database.  But whether you turn this SQL into stored procedures or paste it directly into your application source code, you end up with a lot of logic expressed as plain old strings that no compiler is going to complain about if you get wrong.  Well, I shouldn't say get wrong.  It will be right when you add it to your application.  But the schema will change, and when it does it won't be so easy to find all the places where the SQL is no longer correct. 

So, ORMs are great, but which one should you use?  To answer that you need to have a good idea about what your application needs to do and how long it will be around.  If you need something that isn't too complicated, you need it fast, and you know the database will always be SQL Server, then LINQ to SQL is probably a good choice.  If you need to create a complicated application and expect it to last a long time, then NHibernate would be a good choice.  If you really like and believe in Microsoft, and if you can use .NET 4.0 (available yet?), then Entity Framework would probably work well. 

I am not going attempt to provide much more guidance than that on selecting an ORM.  What I hope to do instead, is to provide an example application that you can use to leverage your knowledge of LINQ to SQL, Entity Framework, or NHibernate to get you started with another one of those three.  Perhaps you have used NHibernate on a large application and now find yourself on a team that needs to create something fast with LINQ to SQL.  Or maybe you started out with LINQ to SQL or Entity Framework, found that it just wasn't up to the task, and are now switching to NHibernate.  Either way, I think you can use this application to see how some basic functionality you know how to use with one of the ORMs can be achieved with another. 

If you are just getting started with one of these ORMs and don't know any of the others any better, I think this document and application can still be helpful.  The application goes beyond the typical entry level example application for an ORM in that it is architected like a large application with real large application issues, such as concurrency, transactions, composable "where" clauses, and detached entities.  If you have already made it through some simple example applications, but are struggling with how to implement functionality in a real-world application, then this application may be helpful.

Besides ORM issues, this application also addresses application architecture in considerable detail.  This document will discuss at length breaking up the functionality of an application into namespaces with clearly defined dependencies - which the application does. The model-view-presenter design is used for the user interface and the data transfer objects are used to move data between tiers.  Communication between the client and server tiers is handled via WCF (Windows Communication Foundation).

Of course, this application doesn't do everything well, and there are some things for which you should NOT look to this application for guidance.  This is a WinForms application with an uninspired UI.  You won't find anything on WPF (Windows Presentation Foundation), AJAX, ASP, or Silverlight in this application.  The data access layer (where the ORMs are used) is covered pretty well with unit tests, but not much else is.  There is a lot of ORM-related logic in the data access layer and testing it efficiently would require substituting for the real database an in-memory one that can still be operated on by the ORMs.  This has not been done.  (Can it?).  Also this example application does not properly address error handling, logging, security, deployment, instrumentation, or internationalization.

And certainly this should not be considered a complete example for any of the ORMs.  For each it only scratches the surface.

6 My Ulterior Motive (...well, one of them)

Contents at a Glance   Detailed Contents

The ideal type of application for exploring ORM, architecture, and WCF would probably be one that you can easily imagine being used in a corporate environment by hundreds of users, or a public ASP.NET application used by thousands.  On this count, I didn’t do so well.  Instead, I wrote something that I personally would find useful: a rich client accounting application.  Imagining this in use in a corporate environment with hundreds of users will be a stretch.  I do believe, however, that the architecture, database design, data access techniques, and concurrency measures taken are all proper for a large corporate environment with hundreds of users – even if the functionality itself could just as well have been provided with a much simpler design more appropriate for a single user at home. 

7 Data Model

Contents at a Glance   Detailed Contents

LEK, is a three-tier database application that helps a family manage its money.  LEK is architected as if it were a large application with hundreds of concurrent users, even though it is typically used by a single person sitting at his desk in his bedroom (that would be me).  LEK has a very simple Windows Forms UI and a server application that can communicate with the database using either LINQ to SQL, Entity Framework, or NHibernate.  The client and the server communicate via WCF. 

LEK is based on the following family accounting model:

  1. Each individual in the family owns a certain percentage of each of the assets owned by the family. 
  2. Each individual in the family is responsible for a certain percentage of each of the liabilities the family is responsible for. 
  3. Each expense incurred by the family can be allocated among the family members. 
  4. Each income received by the family can be allocated among the family members. 

Here are some examples:  "Father" and "Mother" (I will use these terms as if they were names) both work, but income earned by each belongs to both equally.  (Mother gets 50% of what Father makes and the Father gets 50% of what the Mother makes.)  Father and Mother each have their own bank accounts, of which they “own” 100%.  If they go on a vacation they take a “Joint Wallet”, in which they each have a 50% stake.  When Mother buys groceries the Father owes for half.  When Father buys diving gear for himself, he is responsible for all of it. "Son" and "Daughter" (using as if they were names) each get an allowance.  If Mother buys Son a Star Wars light saber and it isn’t Christmas, Son owes Mother 100% for it. 

I am a "database first" kind of guy.  I know this isn’t conventional wisdom.  A designer is supposed to figure out the use cases and then create an entity model that can implement the functionality described by the use cases.  To me that is just too shaky a ground to start on.  I know vaguely what kinds of operations need to be performed, but I know exactly what data I am trying to store and manage.  Perhaps  what I actually know is a logical model of the data.  When it comes time to implement the model in a real database, things could change.  Things could also change during optimization, or, non-ideally, once the database gets filled up with real production data and is being hit by queries from real users.  But at least when starting off, my database typically looks just like my conceptual model of the data. 

Here is the conceptual data model for LEK:

004ERD.gif

In the above figure, each box represents an entity.  The lines represent many-to-one associations between the entities.  For example, each Item is associated with one Transaction and one DltAllocAccnt.  A Transaction can have zero, one, or more Items.  A DltAllocAccnt can have zero, one or more Items.  A dashed line indicates that the association doesn’t have to exist. 

7.1 Net Worth Account (NWAccnt)

Contents at a Glance   Detailed Contents

A NWAccnt (Net Worth Account) is something that figures into the net worth of the family.  Examples:  Father’s checking account, Mother’s credit card (negative), the house, the mortgage (negative), Son’s piggy bank. 

7.2 Participant

Contents at a Glance   Detailed Contents

A Participant is a family member. 

7.3 Ownership

Contents at a Glance   Detailed Contents

If you made a table on paper with NWAccnts down the left and Participants across the top and each cell indicating the percentage ownership that each Participant had in each NWAccnt, then each cell would be an Ownership.  In database parlance, each Ownership record would have a foreign key to a NWAccnt record, a foreign key to a Participant record and a numerical value indicating percent ownership.  No two records would have the same pair of foreign keys. 

7.4 Delta Allocation Account (DltAllocAccnt)

Contents at a Glance   Detailed Contents

A DltAllocAccnt (Delta Allocation Account) is an expense or income account.  Examples: groceries, gasoline, entertainment, salary, interest. 

7.5 Post

Contents at a Glance   Detailed Contents

A Post is a point in time in which all money related activity is considered to be properly accounted for.  I typically collect receipts and record everything each Saturday, so there usually ends up being one post each week.

7.6 Transaction

Contents at a Glance   Detailed Contents

A Transaction is an event that changes who-owes-who, the family’s net worth, or where the money is.  A Transaction is typically brought about by a Participant, and it will eventually get associated with a Post.  Examples: Mother bought groceries, Father got pay check, Daughter allocated allowance, money moved from a savings account to a checking account. 

7.7 Item

Contents at a Glance   Detailed Contents

An Item is a portion of a Transaction that affects the net worth of one or more Participants.  Each Item is associated with a Transaction and a DltAllocAccnt.  Two Items can be associated with the same Transaction and the same DltAllocAccnt (gallon of milk and cup of cream both purchased at the store and both are considered a “dairy” expense).  The division of a Transaction into Items is arbitrary.  For example, if the Transaction is “Bought groceries at HEB” then there could be the four Items "gallon milk", "4 ounces cheese", "1 lb pork", "1 lb beef” or there could be the two Items "dairy", "meat", or there could be just a single Item "groceries".  If the DltAllocAccnts are fine-grained, this will sometimes require fine-grained Items.  For example, if the accounts in the system include “milk”, “cheese”, “pork”, and “beef” then a Transaction involving these types of things will need to be broken down into at least enough Items to allow each Item to be associated with a DltAllocAccnt.  If there is just a single “groceries” DltAllocAccnt, then breaking down the Transaction to this level of detail would be optional.  Some other examples of Items include “received pay check” (would be associated with a “salary” or “income” DltAllocAccnt) and “granted allowance” (could be associated with an “allowance” DltAllocAccnt). 

7.8 Delta Allocation (DltAlloc)

Contents at a Glance   Detailed Contents

A DltAlloc (Delta Allocation) is an allocation of the expense or income for an Item to a Participant.  Each DltAlloc is associated with an Item and a Participant.  No two DltAllocs can be associated with the same Item and Participant. 

7.9 Net Worth Account Delta (NWAccntDlt)

Contents at a Glance   Detailed Contents

A NWAccntDlt (Net Worth Account Delta) is a change in the value of a NWAccnt.  A NWAccntDlt is associated with a Transaction and a NWAccnt.  For example, if you withdraw cash from your checking account and put it in your wallet then there would be two NWAccntDlts: a negative one associated with a “checking” NWAccnt and a positive one associated with a “wallet” NWAccnt.  Both of these would be associated with the same Transaction. 

7.10 Wash Delta (WashDlt)

Contents at a Glance   Detailed Contents

If Father goes to the store and buys something for Son and Mother then they each owe Father for it.  But for the family as a whole the amount owed is “a wash” – Father is owed $5, Mother owes $3 and Son owes $2.  As you can imagine, this could get complicated very quickly as the number of Participants grows.  Rather than keeping track each possible combination of Participants owing each other, a virtual 3rd party is introduced called the “the wash”.  In the above example Mother owes “the wash” $3, Son owes “the wash” $2 and Father is owed by “the wash” $5.  Each of these would be a WashDlt.  Each WashDlt is associated with a Transaction and a Participant.  No two WashDlts have the same Participant and the same Transaction.  All the WashDlts associated with the same Transaction sum to 0. 

8 Business Rules

Contents at a Glance   Detailed Contents

The most obvious rules for the data center around the Transaction.  The sum of the NWAccntDlts in a Transaction must equal the sum of the DltAllocs in the Transaction.  The sum of the WashDlts in a Transaction must be 0.  Finally, at the time that a Transaction is created, the change of the net worth of a Participant must equal the sum of the DltAllocs in the Transaction for that Participant.  This last rule is what ties in the WashDlts.  In a Transaction with one or more NWAccntDlts each Participant is attributed a percentage of the amount in each NWAccntDlt.  For example, let’s say Mother goes to the store and buys $20 in groceries which includes a $4 can of sardines for Father.  Everyone in the family except Father HATES the smell of the sardines so that family has the policy that even though grocery expenses generally are split between Mother and Father, Father pays for sardines himself.  Mother uses the “joint wallet” to pay for groceries.  There will be one NWAccntDlt of -$20, but since Father and Mother each own 50% of "joint wallet", they have each individually seen their net worth as tracked by the NWAccnts drop by $10.  Since Father is to bear the expense of the sardines himself, however, there are also two WashDlts: a -$2 one for the Father and a +$2 one for Mother.  For both Father and Mother the change to their net worth is the sum of their portion of the NWAccntDlt changes (-$10 and -$10 for Father and Mother respectively) plus their WashDlts (-$2 and +$2 for Father and Mother respectively).  The net result is that the Father’s net worth went down by $12, and the Mother’s went down by $8. 

9 Visualization of the Data

Contents at a Glance   Detailed Contents

Here are some screen shots from LEK of a Transaction slightly more complicated than the one just described.  Note that on the forms that receive user input, amounts are shown in cents rather than in dollars. This is merely to save the user from having to enter the decimal point. 

Building on the example described in Business Rules (above), let’s say Son also goes to the store with Mother and he brings his own money.  He somehow pulls mom into the toy aisle, grabs a “light saber” and says “Mom! Mom! Can I get this light saber? I have the money for it!”.  Mother agrees.  At check out Mother gives the attendant $20 from the joint wallet and $5 from Son. 

The NWAccntDlts for this Transaction will appear like this in the UI:

005NWAccntDlts.gif

The Items for this Transaction will appear like this:

003Items.gif

The “1” and “0” in the above represent which Participant bears the expense of the Item.  “Milk” for example, has a “1” under both “Father” and “Mother”, which indicates that Father and Mother split the expense for this Item.  If the cells contained “2” and “1” for the “Father” and “Mother” columns that would indicate that Father is responsible for 2/3 of the expense and Mother is responsible for 1/3 of the expense. 

The summary of the whole Transaction will appear like this:

006Transaction.gif

10 Architecture

Contents at a Glance   Detailed Contents

LEK can be deployed as either a two-tier application or a three-tier application.  In either case there is a database tier consisting of a SQL Server database.  In the three-tier configuration the other two tiers are a client .NET application and a server .NET application communicating via WCF.  In the real world, the server application would probably be a Windows Service or would be hosted in IIS.  In this example, however, the server application is just an executable.  The two-tier configuration consists of the database and a single .NET application.  For both configurations the executables are only small shell applications that rely on numerous DLL assemblies for nearly all application logic.  In the three-tier configuration, some of the DLLs are used by the server only, some by the client only, and some are used by both.  The application for the two-tier configuration uses almost all of the DLLs. 

Dividing the application logic into numerous DLLs serves two purposes.  First, it makes it easier to deploy the application as separate client and server applications that are only as large as necessary.  Second, it makes it easy to enforce a structured architecture with well-defined dependencies between parts of the application.  There is a downside, however: It takes much longer to build an application consisting of many small DLLs than it does to build an application consisting of a few large DLLs.  A better solution is to only create as many DLLs as are needed for efficient deployment, and to enforce a structured architecture with a namespace dependency tool like NDepend (http://www.ndepend.com) or Lattix (http://www.lattix.com).  Alas – I haven’t done that yet.  So for now, we have a bunch of DLLs, each generated from code in a separate namespace. 

The LEK Visual Studio 2005 solution consists of 3 EXE projects and 19 DLL projects.  The code that makes up each project is in a distinct namespace with the same name as the name of the project.  Rather than presenting the whole thing at once, I will build up the actual architecture from the simplest possible architecture, explaining the reason behind each step’s elaboration. 

10.1 Simplest Possible

Contents at a Glance   Detailed Contents

The simplest possible architecture for the client and server components of a three-tier rich client WCF application would be to have a single executable for the client and a single executable for the server, neither of which would rely on any DLLs:

007Arch_Base.gif

10.2 Minimal Sharing

Contents at a Glance   Detailed Contents

The client and the server will need to share message formats at the very least, so rather than duplicating that specification in each project, this is put into a shared DLL called "Util":

008Arch_Util.gif

10.3 Single App Option

Contents at a Glance   Detailed Contents

Testing and debugging are usually easier when you only have to work with a single application.  This will allow you to step though code while debugging from the UI all the way to the data access code.  Pulling out all of the domain-oriented code into separate projects ("Service" and "Presenter") makes this code available to a single executable project ("WinApp") that is easier to work with during development.  This will also facilitate changing the host for the service to a custom Windows Service or to IIS. 

009Arch_WinApp.gif

10.4 Projects or Namespaces

Contents at a Glance   Detailed Contents

As mentioned earlier, for a large project it is probably best to keep the number of DLLs down and to enforce dependencies between different parts of the code by utilizing numerous namespaces and an application like NDepend (http://www.ndepend.com) or Lattix (http://www.lattix.com) to enforce dependencies between the namespaces.  I have not done this with LEK, but each project does have its own namespace with the same name as the project.  From this point forward I will refer to namespaces rather than projects when discussing how the code for the application is divided up and the dependencies between the divisions. 

10.5 WCF Configuration

Contents at a Glance   Detailed Contents

A WCF application in production is usually easiest to configure if IP address, ports, and other WCF setup information is stored in configuration files.  For development, however, it is often easier to compile this into the application.  Since the client and the server must agree on these settings, they are put into a shared namespace called ClientServer, which is only accessible from the WinFormServer and WinFormClient namespaces:

010Arch_ClientServer.gif

10.6 UI Interface

Contents at a Glance   Detailed Contents

UI code is hard to test and subject to the whims of whoever is setting the aesthetics for your application.  As much as possible, the elements that control the appearance and directly interact with the user should be separated from the rest of the code.  This will make it easier to write unit and integration tests for the rest of the code.  In LEK, every form displayed to the user has two classes: one in the Presenter namespace called a “presenter” and one in the WinUI namespace called a “view”.  The presenter sends commands to the view to show information.  The view “paints” the information on to the screen.  When the user interacts with one of the painted controls, the view sends a notification to the presenter that user wants to do something.  The presenter takes the appropriate action and then sends a command to the view to update it if necessary. 

The presenter only knows that the view it communicates with implements a specific interface.  The view only knows that the presenter it interacts with implements a specific interface.  These interfaces are defined in the UIInterface namespace.  This allows the Presenter and WinUI namespaces to be decoupled.  They depend on the UIInterface namespace but not on each other.  The presenter has access to a factory that it asks to create its view.  The presenter passes its view an interface reference to itself for the view pass events back through. 

The view factory is defined in the WinUI namespace, but implements an interface of its own (for use by the presenters) that is defined in the UIInterface namespace.  The view factory itself is created from within the WinFormClient or WinApp namespaces, which then initializes the presenters by passing them an interface reference to the view factory.  In this sense the shell executables provide an “inversion of control” functionality: they supply factories to subordinate code that is ignorant of the concrete classes that implement the functionality that the subordinate code needs. 

I have not created unit tests for the presenters, but this architecture would make it easy to do so.  The presenters would be supplied with a view factory that would create automated views that implement the required interfaces.  The automated views would expose an additional interface so that they could be programmatically directed by the test harness to simulate a user interaction. 

011Arch_UIInterface.gif

10.7 Generic Util Separate from Domain Util

Contents at a Glance   Detailed Contents

If you have worked on more than one project, then you probably have library of generic routines and types that you carry from project to project.  In LEK the Util namespace is reserved for this sort of non-domain specific code.  At this point in the elaboration of the architecture, all other shared code is reallocated to the GlobalType, UIType, and DomainUtil namespaces.  The GlobalType namespace is for simple data types (little or no logic) that must be available to nearly all namespaces – client and server.  The UIType namespace is for the simple data types that must pass between the presenters and the views (in the Presenter and WinUI namespaces), but that aren’t needed by the Service namespace.  The DomainUtil namespace is for all other types and logic that need to be accessed by the Service namespace and the Presenter namespace (need to be accessed by both the server and the client tiers). 

012Arch_GlobalType.gif

10.8 Service Interface

Contents at a Glance   Detailed Contents

The most significant divisions between different parts of a three-tier WCF WinForms application are the division between the client and server applications and the division between the server application and the database.  Data flowing across these divisions (between the tiers) moves across process boundaries, and maybe even machine boundaries.  Consequently, the communication is orders of magnitude slower, and since it is across a network it may affect other users, even users in completely different applications. 

The next step in the elaboration of the architecture concerns the division between the client and server applications.  Presenters (in the Presenter namespace) make calls via WCF to one or more objects exposed in the Service namespace.  The presenters are part of the client application – of which there may be hundreds of instances running.  Updates to the client will not typically coincide with updates to the server, so in addition to concerning yourself with network latency and utilization, you must also be careful to maintain compatibility between the server and clients.  Ideally, you will usually want to deploy a new server first that can still communicate with the old clients, but that has additional capability that can be used by new clients. 

In LEK, communication between the client and the server is restricted to calls made by the presenters to a service object defined in the Service namespace.  The Presenter namespace, however, has no dependency to the service namespace.  Instead, the presenters are provided (by WinFormClient) with WCF proxy objects that implement an interface defined in the ServiceInterface namespace.  All the types that flow across this interface are very simple types with little or no logic – data transfer objects.  The types are defined in Util (for those not specific to LEK), GlobalType (those that have to make it all the way to the views in WinUI) and DTO (the vast majority).  The DomainUtil namespace knows about the DTO, GlobalType, and Util namespaces so it can create these data transfer objects.  It does not, however, know about ServiceInterface, and ServiceInterface does not know about it (DomainUtil).  These dependency restrictions will ensure that DomainUtil is only used for logic that can be tested without having to create mocks of the server object, and that all tests of presenter objects can be tested by creating mocks of the server object.  Furthermore, isolating the interface and the data transfer objects into their own namespaces helps the developer (there could be many) focus his or her attention on these parts when necessary.  When will it be necessary?  Whenever client/server compatibility issues are being contemplated or whenever it is suspected that network latency or bandwidth are limiting the performance of the application. 

013Arch_DTO.gif

10.9 Avoiding Mocking for Unit Tests

Contents at a Glance   Detailed Contents

A well-factored application with near complete unit test coverage is easier to maintain than one with less extensive unit test coverage.  Changes can be made with greater confidence that mistakes that make it past the compiler will be caught when running the unit tests.  Integration tests that go through several layers are great at catching mistakes as well, but if they are extensive enough to cover the majority of the code they will probably pass between two or more tiers and thus take much longer to run (and when problems surface, the tests won’t point you to the source of the problem).  It only takes a few milliseconds to instantiate an object, call some functions on it, and then verify that return values and final state are as expected.  But if that object internally calls functions that cross process boundaries (through WCF or database connections) then they will take orders of magnitude longer to execute.  An application with hundreds of tests that make calls across process boundaries will take several minutes to run, and the root cause of failures will be hard to track down.  Faced with this prospect, developers will typically write fewer tests, use them less often, and spend more time waiting for tests to complete. 

A layered design is a great improvement over designs characterized by “spaghetti code”, but simple layering does not do much to make unit testing easier.  Since the UI logic depends on the business logic, which in turn depends on the data access logic, testing UI logic often means test execution flow will cross process boundaries and thus exhibit the problems discussed above.  One solution to this is mocking the lower layers, so that tests execute quickly and problems can be isolated to the layer under test and not the supporting code.  But mocking takes time and adds complexity.  Where possible, it is better to decouple code from the layers altogether.  For example, instead of having a business object on the client that validates and transforms data and then passes this data to the backend to be persisted in the database, it is better to have business object that simply validates and transforms the data according to the business rules.  This way testing the business object will not require waiting for data to cross tiers and will not require mocking.  Mocking may still be required to test the code that is responsible for instantiating the business object, getting the data transfer object from it, and forwarding this on to the backend, but this will be much less code to test. 

In the previous step of the elaboration of the architecture, the Service namespace was deemed to contain all the code for the server, which includes the code that interacts with the database.  To reinforce the notion that as much of this code as possible should not depend on the database, a new namespace, called ServerUtil, is created which will not be allowed to communicate with the database.  Your goal as a developer should be to locate as much server side code as possible in the ServerUtil namespace, where use of the database is forbidden, thus ensuring the most effective unit testing. 

Likewise, in the previous step of the elaboration of the architecture, the Presenter namespace was deemed to contain all the code for the client except for the code responsible for painting the UI and interacting with the user.  To reinforce the notion that as much of this code as possible should be independent of the back-end, a new namespace, called ClientUtil is created without any dependency on the ServiceInterface namespace.  Your goal as a developer should be to locate as much client side code as possible in the ClientUtil namespace, where use of the server object or WCF server proxies are prevented, thus ensuring the most effective unit testing. 

014Arch_TierUtils.gif

10.10 Isolating the ORM Classes From the Rest of the App

Contents at a Glance   Detailed Contents

An ORM tool will give you the ability to map classes to tables in the database.  It will also provide an API for using objects of these classes to move data into or out of the database.  A key architectural decision you will need to make is what namespaces in your application should have access to the ORM classes.  This decision will depend, in part, on an even more fundamental decision: how closely should the object model you work with in code match the relational model of your database?  If you think that a data model created exclusively for expressing the business and presentation logic of your application would be very similar to a data model created exclusively for moving data into and out of a relational database, then you should probably make your ORM classes available to all the namespaces in your application.  This is especially true if the application is relatively simple and has a short estimated life time (months).  On the other hand, if you think that a data model created exclusively for expressing the business and presentation logic of your application would be significantly different from a data model created exclusively for moving data into and out of a relational database, then you should probably be more guarded in exposing your ORM classes to the rest of your application.  This is especially true if the application is relatively complex and has long life time (years). 

Another factor to consider is how amenable the ORM tool is to generating ORM classes that don’t exactly match your database schema.  Entity Framework and NHibernate offer a lot of capability to do this.  If you are comfortable using some of the more advanced ORM features to map classes to your database that don’t exactly match up to your schema then these classes will be more suitable for use in the different layers of your application.  You should also consider how you feel about allowing your classes to carry around functions and attributes only used by the ORM layer.  NHibernate lets you map classes to the database with very few artifacts of the mapping: they are “POCO” (“Plain Old Class Objects”).  This makes them more suitable for use in different layers of your application.  Entity Framework (at least version 3.5) and LINQ to SQL, on the other hand, require mapped classes with many special functions and attributes used by the ORM.  Ideally classes used by your presentation and business rules should not expose methods and attributes that are never used by these layers. 

Due to the fact that LEK is meant to show how to use different ORM tools, and because I am attempting to model the architecture for a complex and long lived application, I have architected LEK to be very guarded in exposing the ORM classes to the rest of the application.  All the ORM classes in LEK are in the Dal namespace (actually they each have their own, but that is in the next step).  Dal is only accessible to the Service namespace (actually they each have their own one of these as well, but that is in the next step).  The code in the Service namespace transforms the ORM objects into data transfer objects when reading data from the database and transforms data transfer objects into ORM objects when writing data to the database.  Both the Service namespace and Presenter namespace make use of business objects from DomainUtil or ServerUtil or ClientUtil that implement the business logic of the application.  The classes describing these business objects make up the true data model for the application.  Typically, the business objects are initialized with the data transfer objects, and when asked to supply a snapshot of data for serialization to the database, they supply the data as data transfer objects.  The Service namespace contains the (custom, handwritten) code that translates between data transfer objects and ORM objects.  The ORM classes very closely match the actual database schema, and are structured such that the mapping to the database is as simple as possible. 

015Arch_Dal.gif

10.11 Isolating the ORM Dependant Code for Each ORM

Contents at a Glance   Detailed Contents

Rather than the Dal namespace, LEK actually has a separate namespace for each of the three ORM tools being explored.  DalEF contains the ORM classes for Entity Framework, DalNH contains the ORM classes for NHibernate, and DalLinqToSQL contains the ORM classes for LINQ to SQL.  DalEF and DalLinqToSQL each contain only the code generated by Visual Studio when generating the ORM classes from an existing database.  For both, the generated code is slightly modified (through the designer UI) from the default to change how the ORM layer deals with concurrency issues.  Although not machine-generated, the code in DalNH provides the same functionality as that in DalEF and DalLinqToSQL: the ORM classes and the specifics of how they are mapped to the database are defined. 

The Service namespace is also replaced by three ORM specific namespaces: ServiceEF, ServiceNH, and ServiceLinqToSQL.  Each of these are independent of each other and depend on their corresponding Dal* namespace.  There is some logic in these that could be factored out into a common namespace, but since LEK is meant to explore how any one of these could be used in a Visual Studio project for a real application, and not how all three could be used in a Visual Studio project for a real application, this redundancy is not factored out. 

ServiceEF, ServiceNH, and ServiceLinqToSQL each provide exactly the same functionality: They provide the API that is published via WCF.  This API is exactly described in the combination of the ServiceInterface namespace and in the supporting namespaces that provide the specification of the data types (DTO, GlobalType, and Util).  The class in the ServiceFactory namespace is responsible for instantiating the correct implementation of the interface, depending on the parameters that it is passed. 

016Arch_ORM.gif

10.12 Dependencies: The Big Picture

Contents at a Glance   Detailed Contents

Each box above represents both an assembly and a namespace of LEK.  Blue text indicates the namespace is used on the server side and a red border indicates the namespace is used on the client side.  Boxes with both blue text and a red border represent namespaces used on both the client and the server sides.  As previously mentioned, in an actual application with a team of developers and hundreds of users you would probably use fewer assemblies to improve build time, but still use a similar collection of namespaces and a tool like NDepend (http://www.ndepend.com) or Lattix (http://www.lattix.com) to manage dependencies between them. 

 

Here is the dependency matrix for the LEK namespaces, as generated by NDepend:

DependencyMatrix.GIF

11 ORM Classes and Their Mapping to Database Entities

Contents at a Glance   Detailed Contents

The primary objective of an ORM tool is to make it possible for you move data into and out of the database using objects rather than SQL.  Each of the technologies allows you to specify a set of classes that define these objects as well as a specification of how the fields of the classes map to the fields in the database.  Adding data to the database involves instantiating instances of these classes and then passing the instances into an API provided by the ORM tool.  Pulling data from the database involves passing a query into the API and getting back instances of these objects.  The query may be similar to SQL, but is in terms of the ORM classes. 

11.1 Generating a Default Set of ORM Classes in LINQ to SQL and Entity Framework

Contents at a Glance   Detailed Contents

Visual Studio makes it very easy to automatically add a complete set of ORM classes  for both Entity Framework and LINQ To SQL.   You simply bring up the "Add New Item" dialog for a project, select either "ADO.NET Entity Data Model" (for Entity Framework) or "LINQ to SQL Classes" (for LINQ to SQL), and then follow the steps in the wizard that follows.  In both cases you can browse to a database, select tables, and then let the wizard generate a default set of classes. 

11.1.1 LINQ to SQL

Contents at a Glance   Detailed Contents

Here is the dialog that was used to add the LINQ to SQL ORM classes to LEK:

AddORMClasses_LtS.GIF

And here is the DalLinqToSQL project after the operation was completed:

DalLinqToSqlProject.GIF

The DataClasses.designer.cs file contains the ORM classes and the DataClasses.dbml file contains the mapping information.

11.1.2 Entity Framework

Contents at a Glance   Detailed Contents

Here is the dialog that was used to add the Entity Framework ORM classes to LEK:

AddORMClasses_EF.GIF

And here is the DalEF project after the operation was completed:

DalEFProject.GIF

The Model.Designer.cs file contains the ORM classes and the Model.edmx file contains the mapping information.

11.2 ERDs

11.2.1 SQL Server Database

Contents at a Glance   Detailed Contents

For both LINQ to SQL and Entity Framework, using the wizard to add the classes required selecting the database that the classes would map to. Here is the SQL Server diagram of the database that was chosen in both cases:

017ERDSQL.gif

In both cases the wizard creates a set of ORM classes that map one for one to the tables in the database.  After the wizard completes the ERD will be shown for the generated classes.

11.2.2 LINQ to SQL ORM Classes

Contents at a Glance   Detailed Contents

Here is the ERD for the ORM classes generated by the wizard for LINQ to SQL:

019ERDLinqToSQL.gif

11.2.3 Entity Framework ORM Classes

Contents at a Glance   Detailed Contents

Here is the ERD for the ORM classes generated by the wizard for Entity Framework:

018ERDEF.gif

11.3 Examining an ORM Class for LINQ to SQL, Entity Framework, and NHibernate

Contents at a Glance   Detailed Contents

The primary means of creating and modifying ORM classes and associated mapping information for LINQ to SQL and Entity Framework  is through the Visual Studio IDE.  For NHibernate, you must create and maintain the ORM classes and mapping information manually.  However, the ORM classes and mapping information for NHibernate are much simpler than the corresponding code for LINQ to SQL and Entity Framework.  In this section I will show the ORM class and associated mapping information for the simplest entity in LEK: Post.  My intention is simply to show the kind of information in each of these files, and to allow you to compare the relative complexity of each.  You probably shouldn't spend to much time at this point trying to interpret the code.

11.3.1 LINQ to SQL

Contents at a Glance   Detailed Contents
11.3.1.1 The Post ORM Class for LINQ to SQL
CodeWindow 001
    [Table(Name="dbo.Post")]
    public partial class Post : INotifyPropertyChanging, INotifyPropertyChanged
    {
        
        private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
        
        private int _ID;
        
        private System.DateTime _Instant;
        
        private EntitySet<Transaction> _Transactions;
        
    #region Extensibility Method Definitions
    partial void OnLoaded();
    partial void OnValidate(System.Data.Linq.ChangeAction action);
    partial void OnCreated();
    partial void OnIDChanging(int value);
    partial void OnIDChanged();
    partial void OnInstantChanging(System.DateTime value);
    partial void OnInstantChanged();
    #endregion
        
        public Post()
        {
            this._Transactions = new EntitySet<Transaction>(new Action<Transaction>(this.attach_Transactions), new Action<Transaction>(this.detach_Transactions));
            OnCreated();
        }
        
        [Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
        public int ID
        {
            get
            {
                return this._ID;
            }
            set
            {
                if ((this._ID != value))
                {
                    this.OnIDChanging(value);
                    this.SendPropertyChanging();
                    this._ID = value;
                    this.SendPropertyChanged("ID");
                    this.OnIDChanged();
                }
            }
        }
        
        [Column(Storage="_Instant", DbType="DateTime NOT NULL")]
        public System.DateTime Instant
        {
            get
            {
                return this._Instant;
            }
            set
            {
                if ((this._Instant != value))
                {
                    this.OnInstantChanging(value);
                    this.SendPropertyChanging();
                    this._Instant = value;
                    this.SendPropertyChanged("Instant");
                    this.OnInstantChanged();
                }
            }
        }
        
        [Association(Name="Post_Transaction", Storage="_Transactions", ThisKey="ID", OtherKey="PostID")]
        public EntitySet<Transaction> Transactions
        {
            get
            {
                return this._Transactions;
            }
            set
            {
                this._Transactions.Assign(value);
            }
        }
        
        public event PropertyChangingEventHandler PropertyChanging;
        
        public event PropertyChangedEventHandler PropertyChanged;
        
        protected virtual void SendPropertyChanging()
        {
            if ((this.PropertyChanging != null))
            {
                this.PropertyChanging(this, emptyChangingEventArgs);
            }
        }
        
        protected virtual void SendPropertyChanged(String propertyName)
        {
            if ((this.PropertyChanged != null))
            {
                this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
        
        private void attach_Transactions(Transaction entity)
        {
            this.SendPropertyChanging();
            entity.Post = this;
        }
        
        private void detach_Transactions(Transaction entity)
        {
            this.SendPropertyChanging();
            entity.Post = null;
        }
    }
11.3.1.2 The Post ORM Class Mapping for LINQ to SQL
CodeWindow 002
<?xml version="1.0" encoding="utf-8"?>
<Database Name="xLekDev" Class="DataClassesDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
  <Connection Mode="AppSettings" ConnectionString="Data Source=xxMACHINENAMExx;Initial Catalog=xLekDev;Integrated Security=True" SettingsObjectName="DalLinqToSql.Properties.Settings" SettingsPropertyName="LekDevConnectionString" Provider="System.Data.SqlClient" />

  <!--text removed -->

  <Table Name="dbo.Post" Member="Posts">
    <Type Name="Post">
      <Column Name="ID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
      <Column Name="Instant" Type="System.DateTime" DbType="DateTime NOT NULL" CanBeNull="false" />
      <Association Name="Post_Transaction" Member="Transactions" ThisKey="ID" OtherKey="PostID" Type="Transaction" />
    </Type>
  </Table>

  <!--text removed -->

</Database>

11.3.2 Entity Framework

Contents at a Glance   Detailed Contents
11.3.2.1 The Post ORM Class for Entity Framework
CodeWindow 003
    /// <summary>
    /// There are no comments for LekModel.Post in the schema.
    /// </summary>
    /// <KeyProperties>
    /// ID
    /// </KeyProperties>
    [global::System.Data.Objects.DataClasses.EdmEntityTypeAttribute(NamespaceName="LekModel", Name="Post")]
    [global::System.Runtime.Serialization.DataContractAttribute(IsReference=true)]
    [global::System.Serializable()]
    public partial class Post : global::System.Data.Objects.DataClasses.EntityObject
    {
        /// <summary>
        /// Create a new Post object.
        /// </summary>
        /// <param name="id">Initial value of ID.</param>
        /// <param name="instant">Initial value of Instant.</param>
        public static Post CreatePost(int id, global::System.DateTime instant)
        {
            Post post = new Post();
            post.ID = id;
            post.Instant = instant;
            return post;
        }
        /// <summary>
        /// There are no comments for Property ID in the schema.
        /// </summary>
        [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
        [global::System.Runtime.Serialization.DataMemberAttribute()]
        public int ID
        {
            get
            {
                return this._ID;
            }
            set
            {
                this.OnIDChanging(value);
                this.ReportPropertyChanging("ID");
                this._ID = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
                this.ReportPropertyChanged("ID");
                this.OnIDChanged();
            }
        }
        private int _ID;
        partial void OnIDChanging(int value);
        partial void OnIDChanged();
        /// <summary>
        /// There are no comments for Property Instant in the schema.
        /// </summary>
        [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(IsNullable=false)]
        [global::System.Runtime.Serialization.DataMemberAttribute()]
        public global::System.DateTime Instant
        {
            get
            {
                return this._Instant;
            }
            set
            {
                this.OnInstantChanging(value);
                this.ReportPropertyChanging("Instant");
                this._Instant = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
                this.ReportPropertyChanged("Instant");
                this.OnInstantChanged();
            }
        }
        private global::System.DateTime _Instant;
        partial void OnInstantChanging(global::System.DateTime value);
        partial void OnInstantChanged();
        /// <summary>
        /// There are no comments for Transaction in the schema.
        /// </summary>
        [global::System.Data.Objects.DataClasses.EdmRelationshipNavigationPropertyAttribute("LekModel", "FK_Transaction_Post", "Transaction")]
        [global::System.Xml.Serialization.XmlIgnoreAttribute()]
        [global::System.Xml.Serialization.SoapIgnoreAttribute()]
        [global::System.Runtime.Serialization.DataMemberAttribute()]
        public global::System.Data.Objects.DataClasses.EntityCollection<Transaction> Transaction
        {
            get
            {
                return ((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.GetRelatedCollection<Transaction>("LekModel.FK_Transaction_Post", "Transaction");
            }
            set
            {
                if ((value != null))
                {
                    ((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.InitializeRelatedCollection<Transaction>("LekModel.FK_Transaction_Post", "Transaction", value);
                }
            }
        }
    }
11.3.2.2 The Post ORM Class Mapping for Entity Framework
CodeWindow 004
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="LekModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
        <EntityContainer Name="LekModelStoreContainer">
 
          <!--text removed -->

          <EntitySet Name="Post" EntityType="LekModel.Store.Post" store:Type="Tables" Schema="dbo" />
          
          <!--text removed -->
          
        </EntityContainer>
          
        <!--text removed -->
          
        <EntityType Name="Post">
          <Key>
            <PropertyRef Name="ID" />
          </Key>
          <Property Name="ID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="Instant" Type="datetime" Nullable="false" />
        </EntityType>
          
        <!--text removed -->
          
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="LekModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
        <EntityContainer Name="LekEntities">
          
          <!--text removed -->
          
          <EntitySet Name="Post" EntityType="LekModel.Post" />
          
          <!--text removed -->
          
        </EntityContainer>
          
          <!--text removed -->
          
        <EntityType Name="Post">
          <Key>
            <PropertyRef Name="ID" />
          </Key>
          <Property Name="ID" Type="Int32" Nullable="false" />
          <Property Name="Instant" Type="DateTime" Nullable="false" />
          <NavigationProperty Name="Transaction" Relationship="LekModel.FK_Transaction_Post" FromRole="Post" ToRole="Transaction" />
        </EntityType>
          
          <!--text removed -->
          
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
        <EntityContainerMapping StorageEntityContainer="LekModelStoreContainer" CdmEntityContainer="LekEntities">
          
          <!--text removed -->
          
          <EntitySetMapping Name="Post">
            <EntityTypeMapping TypeName="IsTypeOf(LekModel.Post)">
              <MappingFragment StoreEntitySet="Post">
                <ScalarProperty Name="ID" ColumnName="ID" />
                <ScalarProperty Name="Instant" ColumnName="Instant" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          
          <!--text removed -->
          
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
          
  <!--text removed -->
          
</edmx:Edmx>

11.3.3 NHibernate

Contents at a Glance   Detailed Contents
11.3.3.1 The Post ORM Class for NHibernate
CodeWindow 005
    public class Post
    {
        int _iID;
        public virtual int ID                
        {
            get{return _iID;}
            set{_iID =value;}
        }
        
        DateTime _dtInstant;
        public virtual DateTime    Instant            
        {
            get{return _dtInstant;}
            set{_dtInstant =value;}
        }
    }
11.3.3.2 The Post ORM Class Mapping for NHibernate
CodeWindow 006
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   namespace="DalNH"
                   assembly="DalNH">

  <!-- Mappings for class 'Post' -->
  <class name="Post" table="Post">

    <!-- Identity mapping -->
    <id name="ID">
      <column name="ID" />
      <generator class="native" />
    </id>

    <!-- Simple mappings -->
    <property name="Instant" />

  </class>

</hibernate-mapping>

11.3.4 Comparing the ORM Classes and Mapping for each ORM

Contents at a Glance   Detailed Contents

The mapping and ORM classes for each ORM are the same in the following respects:

  • There is an ORM class for each table.
  • For each field in the database there is a corresponding public property in the ORM class.
  • Each foreign key field in the database is mapped to an entity property in the ORM class.  The entity property in the ORM class corresponds to the record in the parent table that the foreign key points to. (this is not shown in the Post class above.)

Some of the ways they differ are:

  • In the LINQ to SQL and Entity Framework classes and mappings, the inverse of the foreign keys are also mapped.  That is, if there is a foreign key in the database from a child entity to a parent entity, then the ORM class for the parent entity will have a public property for a collection of child entities (that map to the child entities in the database).  This sort of mapping is optional in NHibernate.  For example, the NHibernate version of the Post ORM class could have a property for a collection of Transactions, but I chose not to create one. 
  • In the LINQ to SQL mappings, foreign keys in the database are also mapped to integer members or the ORM classes (this is not shown in the Post class above).

11.4 ORM Classes and Mapping Info Specifications for NHibernate

Contents at a Glance   Detailed Contents

Since the ORM classes and mappings were created manually for NHibernate, I will discuss them in detail.  Here is a snapshot of the solution project for the NHibernate ORM classes and mappings:

DalNHlProject.GIF

I chose to collect all the ORM classes into a single file: DalNH.cs, and to have a separate mapping file (the *.hbm.xml file) for each entity.

11.4.1 NHibernate ORM Classes

Contents at a Glance   Detailed Contents

The code window below shows all the contents of DalNH.cs.  The format is somewhat compressed so that common elements line up vertically:

CodeWindow 007
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using nh=NHibernate;
using s=System;

namespace DalNH
{
    public class Participant
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        byte[]          _timestamp      ;public virtual byte[]          timestamp       {get{return _timestamp      ;}set{_timestamp    =value;}}
        string          _strName        ;public virtual string          Name            {get{return _strName        ;}set{_strName      =value;}}
    }
    public class NWAccnt
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        byte[]          _timestamp      ;public virtual byte[]          timestamp       {get{return _timestamp      ;}set{_timestamp    =value;}}
        string          _strName        ;public virtual string          Name            {get{return _strName        ;}set{_strName      =value;}}
        string          _strDescrip     ;public virtual string          Descrip         {get{return _strDescrip     ;}set{_strDescrip   =value;}}
        bool            _bBudgeted      ;public virtual bool            Budgeted        {get{return _bBudgeted      ;}set{_bBudgeted    =value;}}
    }
    public class DltAllocAccnt
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        byte[]          _timestamp      ;public virtual byte[]          timestamp       {get{return _timestamp      ;}set{_timestamp    =value;}}
        string          _strName        ;public virtual string          Name            {get{return _strName        ;}set{_strName      =value;}}
        string          _strDescrip     ;public virtual string          Descrip         {get{return _strDescrip     ;}set{_strDescrip   =value;}}
        bool            _bBudgeted      ;public virtual bool            Budgeted        {get{return _bBudgeted      ;}set{_bBudgeted    =value;}}
    }
    public class Ownership
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        NWAccnt         _NWAccnt        ;public virtual NWAccnt         TheNWAccnt      {get{return _NWAccnt        ;}set{_NWAccnt      =value;}}
        Participant     _Participant    ;public virtual Participant     TheParticipant  {get{return _Participant    ;}set{_Participant  =value;}}
        int             _iShares        ;public virtual int             Shares          {get{return _iShares        ;}set{_iShares      =value;}}
    }
    #region CodeWindow 005
    public class Post
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        DateTime        _dtInstant      ;public virtual DateTime        Instant         {get{return _dtInstant      ;}set{_dtInstant    =value;}}
    }
    #endregion 
    public class Transaction
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        byte[]          _timestamp      ;public virtual byte[]          timestamp       {get{return _timestamp      ;}set{_timestamp    =value;}}
        Post            _Post           ;public virtual Post            ThePost         {get{return _Post           ;}set{_Post         =value;}}
        Participant     _Participant    ;public virtual Participant     TheParticipant  {get{return _Participant    ;}set{_Participant  =value;}}
        string          _strDescrip     ;public virtual string          Descrip         {get{return _strDescrip     ;}set{_strDescrip   =value;}}
        DateTime        _dtInstant      ;public virtual DateTime        Instant         {get{return _dtInstant      ;}set{_dtInstant    =value;}}
        
        Iesi.Collections.Generic.ISet<Item>         _Items          = new Iesi.Collections.Generic.HashedSet<Item>();
        Iesi.Collections.Generic.ISet<NWAccntDlt>   _NWAccntDlts    = new Iesi.Collections.Generic.HashedSet<NWAccntDlt>();
        Iesi.Collections.Generic.ISet<WashDlt>      _WashDlts       = new Iesi.Collections.Generic.HashedSet<WashDlt>();

        public virtual Iesi.Collections.Generic.ISet<Item>  Items
        {
            get { return _Items; }
            set { _Items = value; }
        }
        public virtual Iesi.Collections.Generic.ISet<NWAccntDlt> NWAccntDlts
        {
            get { return _NWAccntDlts; }
            set { _NWAccntDlts = value; }
        }
        public virtual Iesi.Collections.Generic.ISet<WashDlt> WashDlts
        {
            get { return _WashDlts; }
            set { _WashDlts = value; }
        }
    }
    public class Item
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        Transaction     _Transaction    ;public virtual Transaction     TheTransaction  {get{return _Transaction    ;}set{_Transaction  =value;}}
        DltAllocAccnt   _DltAllocAccnt  ;public virtual DltAllocAccnt   TheDltAllocAccnt{get{return _DltAllocAccnt  ;}set{_DltAllocAccnt=value;}}
        string          _strDescrip     ;public virtual string          Descrip         {get{return _strDescrip     ;}set{_strDescrip   =value;}}

        Iesi.Collections.Generic.ISet<DltAlloc> _DltAllocs  = new Iesi.Collections.Generic.HashedSet<DltAlloc>();
        public virtual Iesi.Collections.Generic.ISet<DltAlloc> DltAllocs
        {
            get { return _DltAllocs; }
            set { _DltAllocs = value; }
        }
    }
    public class DltAlloc
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        Item            _Item           ;public virtual Item            TheItem         {get{return _Item           ;}set{_Item         =value;}}
        Participant     _Participant    ;public virtual Participant     TheParticipant  {get{return _Participant    ;}set{_Participant  =value;}}
        int             _iAmount        ;public virtual int             Amount          {get{return _iAmount        ;}set{_iAmount      =value;}}

    }
    public class NWAccntDlt
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        NWAccnt         _NWAccnt        ;public virtual NWAccnt         TheNWAccnt      {get{return _NWAccnt        ;}set{_NWAccnt      =value;}}
        Transaction     _Transaction    ;public virtual Transaction     TheTransaction  {get{return _Transaction    ;}set{_Transaction  =value;}}
        int             _iAmount        ;public virtual int             Amount          {get{return _iAmount        ;}set{_iAmount      =value;}}

    }

    public class WashDlt
    {
        int             _iID            ;public virtual int             ID              {get{return _iID            ;}set{_iID          =value;}}
        Transaction     _Transaction    ;public virtual Transaction     TheTransaction  {get{return _Transaction    ;}set{_Transaction  =value;}}
        Participant     _Participant    ;public virtual Participant     TheParticipant  {get{return _Participant    ;}set{_Participant  =value;}}
        int             _iAmount        ;public virtual int             Amount          {get{return _iAmount        ;}set{_iAmount      =value;}}

    }

}

NHibernate allowes ORM classes to be "POCO": Plain Old CLR Objects.  They don't have to have any of the attributes or special functions that the ORM classes for LINQ to SQL and Entity Framework do.    This makes them more suitable for use in many layers of your application.  A NHibernate ORM class does have to have public setters for all the properties that map to fields in the database, and they must be virtual.  These two requirements are enough to dissuade me from using them outside of the data access layer in LEK.  (NHibernate ORM classes are, however, much more suitable for use outside of the data access layer than are LINQ to SQL or Entity Framework 3.5 ORM classes.)

As with the other ORM classes, all foreign keys are mapped to properties of an entity type.  None are mapped to simple integer properties as they are in the LINQ to SQL ORM classes.  Unlike the ORM classes for the other ORMs, only some of the possible child collections are mapped.  These include WashDlts within a Transaction,  NWAccntDlts within a Transaction, Items within a Transaction, and DltAllocs within an Item.  Recall that this is by choice: I manually created the ORM classes for NHibernate, whereas the Visual Studio IDE created the ORM classes for LINQ to SQL and Entity Framework.

The child records for a Transaction and the child records for an Item ("grandchildren" of a Transaction) are mapped so that a Transaction and all its details can be treated as one object in code when updating.  When a Transaction is saved in LEK, the Items in the Transaction and the allocations for each Item are also saved.  When a DltAllocAccnt entity is saved on the other hand,  only the DltAllocAccnt database record is touched.  The allocations (DltAlloc records, each of which does refer to a DltAllocAccnt) are not touched.

11.4.2 NHibernate ORM Class Mapping Specification

Contents at a Glance   Detailed Contents

The DalNH project contains a separate xml file for the mapping specification of each entity.  In the below code window, I have combined all of these into a single XML specification and compressed the format somewhat so that it is easier to see the correlation between the xml markup and the entity properties:

CodeWindow 008
<?xml version="1.0"?>        
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="DalNH" assembly="DalNH">      

<!--Participant-->      <class name="Participant"   table="Participant">
<!--Participant-->          <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Participant-->          <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--Participant-->          <property name="Name" />       
<!--Participant-->      </class>         

<!--NWAccnt-->          <class name="NWAccnt"       table="NWAccnt">
<!--NWAccnt-->              <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--NWAccnt-->              <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--NWAccnt-->              <property name="Name" />       
<!--NWAccnt-->              <property name="Descrip" />       
<!--NWAccnt-->              <property name="Budgeted" />       
<!--NWAccnt-->          </class>         

<!--DltAllocAccnt-->    <class name="DltAllocAccnt" table="DltAllocAccnt">       
<!--DltAllocAccnt-->        <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--DltAllocAccnt-->        <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--DltAllocAccnt-->        <property name="Name" />       
<!--DltAllocAccnt-->        <property name="Descrip" />       
<!--DltAllocAccnt-->        <property name="Budgeted" />       
<!--DltAllocAccnt-->    </class>         

<!--Ownership-->        <class name="Ownership"     table="Ownership">       
<!--Ownership-->            <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Ownership-->            <many-to-one name="TheNWAccnt"       class="NWAccnt"       column="NWAccntID"       not-null="true"/>     
<!--Ownership-->            <many-to-one name="TheParticipant"   class="Participant"   column="ParticipantID"   not-null="true"/>     
<!--Ownership-->            <property name="Shares" />       
<!--Ownership-->        </class>         

<!--Post-->             <class name="Post"          table="Post">       
<!--Post-->                 <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Post-->                 <property name="Instant" />       
<!--Post-->             </class>         

<!--Transaction-->      <class name="Transaction"   table="`Transaction`">       
<!--Transaction-->          <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Transaction-->          <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--Transaction-->          <many-to-one name="TheParticipant"   class="Participant"   column="ParticipantID"/>      
<!--Transaction-->          <many-to-one name="ThePost"          class="Post"          column="PostID"/>      
<!--Transaction-->          <property name="Descrip" />       
<!--Transaction-->          <property name="Instant" />       
<!--Transaction-->          <set name="Items"       inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="Item"/>       </set>
<!--Transaction-->          <set name="NWAccntDlts" inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="NWAccntDlt"/> </set>
<!--Transaction-->          <set name="WashDlts"    inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="WashDlt"/>    </set>
<!--Transaction-->      </class>         

<!--Item-->             <class name="Item"          table="Item">       
<!--Item-->                 <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Item-->                 <many-to-one name="TheTransaction"   class="Transaction"   column="TransactionID"   not-null="true" cascade="save-update"/>    
<!--Item-->                 <many-to-one name="TheDltAllocAccnt" class="DltAllocAccnt" column="DltAllocAccntID" not-null="true"/>     
<!--Item-->                 <property name="Descrip" />       
<!--Item-->                 <set name="DltAllocs"   inverse="true" lazy="true" cascade="save-update"> <key column="ItemID"/>        <one-to-many class="DltAlloc"/>   </set>
<!--Item-->             </class>         

<!--DltAlloc-->         <class name="DltAlloc"      table="DltAlloc">       
<!--DltAlloc-->             <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--DltAlloc-->             <many-to-one name="TheItem"          class="Item"          column="ItemID"          not-null="true" cascade="save-update"/>    
<!--DltAlloc-->             <many-to-one name="TheParticipant"   class="Participant"   column="ParticipantID"   not-null="true"/>     
<!--DltAlloc-->             <property name="Amount" />       
<!--DltAlloc-->         </class>         

<!--NWAccntDlt-->       <class name="NWAccntDlt"    table="NWAccntDlt">       
<!--NWAccntDlt-->           <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--NWAccntDlt-->           <many-to-one name="TheNWAccnt"       class="NWAccnt"       column="NWAccntID"       not-null="true"/>     
<!--NWAccntDlt-->           <many-to-one name="TheTransaction"   class="Transaction"   column="TransactionID"   not-null="true" cascade="save-update"/>    
<!--NWAccntDlt-->           <property name="Amount" />       
<!--NWAccntDlt-->       </class>         

<!--WashDlt-->          <class name="WashDlt"       table="WashDlt">       
<!--WashDlt-->              <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--WashDlt-->              <many-to-one name="TheTransaction"   class="Transaction"   column="TransactionID"   not-null="true" cascade="save-update"/>    
<!--WashDlt-->              <many-to-one name="TheParticipant"   class="Participant"   column="ParticipantID"   not-null="true"/>     
<!--WashDlt-->              <property name="Amount" />       
<!--WashDlt-->          </class>         

</hibernate-mapping>

Although it must be created and maintained manually, it is much less code than is required for the mappings in LINQ to SQL and Entity Framework.

11.4.2.1 Arranged and Organized

If the lines of the specification are rearranged somewhat (for demonstration only, this is invalid xml) it becomes easier to grasp what is being specified and how it is being specified:

CodeWindow 009
<?xml version="1.0"?>        
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="DalNH" assembly="DalNH">      
 
<!--## Classes ##-->
<!--Participant-->      <class name="Participant"   table="Participant">
<!--NWAccnt-->          <class name="NWAccnt"       table="NWAccnt">
<!--DltAllocAccnt-->    <class name="DltAllocAccnt" table="DltAllocAccnt">       
<!--Ownership-->        <class name="Ownership"     table="Ownership">       
<!--Post-->             <class name="Post"          table="Post">       
<!--Transaction-->      <class name="Transaction"   table="`Transaction`">       
<!--Item-->             <class name="Item"          table="Item">       
<!--DltAlloc-->         <class name="DltAlloc"      table="DltAlloc">       
<!--NWAccntDlt-->       <class name="NWAccntDlt"    table="NWAccntDlt">       
<!--WashDlt-->          <class name="WashDlt"       table="WashDlt">       
 
<!--## Primary Keys ##-->
<!--Participant-->          <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--NWAccnt-->              <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--DltAllocAccnt-->        <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Ownership-->            <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Post-->                 <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Transaction-->          <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--Item-->                 <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--DltAlloc-->             <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--NWAccntDlt-->           <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
<!--WashDlt-->              <id name="ID"> <column name="ID" /> <generator class="native" /> </id> 
 
<!--## Row Versions ##-->
<!--Participant-->          <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--NWAccnt-->              <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--DltAllocAccnt-->        <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--Transaction-->          <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
 
<!--## FKs That Can be Null ##-->
<!--Transaction-->          <many-to-one name="TheParticipant"   class="Participant"   column="ParticipantID"/>      
<!--Transaction-->          <many-to-one name="ThePost"          class="Post"          column="PostID"/>      
 
<!--## FKs to Parents that Don't Track Entity as a Child ##-->
<!--Ownership-->            <many-to-one name="TheNWAccnt"       class="NWAccnt"       column="NWAccntID"       not-null="true"/>     
<!--NWAccntDlt-->           <many-to-one name="TheNWAccnt"       class="NWAccnt"       column="NWAccntID"       not-null="true"/>     
<!--DltAlloc-->             <many-to-one name="TheParticipant"   class="Participant"   column="ParticipantID"   not-null="true"/>     
<!--Ownership-->            <many-to-one name="TheParticipant"   class="Participant"   column="ParticipantID"   not-null="true"/>     
<!--WashDlt-->              <many-to-one name="TheParticipant"   class="Participant"   column="ParticipantID"   not-null="true"/>     
<!--Item-->                 <many-to-one name="TheDltAllocAccnt" class="DltAllocAccnt" column="DltAllocAccntID" not-null="true"/>     
 
<!--## FKs to Parents that Track Entity as a Child ##-->
<!--Item-->                 <many-to-one name="TheTransaction"   class="Transaction"   column="TransactionID"   not-null="true" cascade="save-update"/>    
<!--NWAccntDlt-->           <many-to-one name="TheTransaction"   class="Transaction"   column="TransactionID"   not-null="true" cascade="save-update"/>    
<!--WashDlt-->              <many-to-one name="TheTransaction"   class="Transaction"   column="TransactionID"   not-null="true" cascade="save-update"/>  
<!--DltAlloc-->             <many-to-one name="TheItem"          class="Item"          column="ItemID"          not-null="true" cascade="save-update"/>    
  
<!--## Children ##-->
<!--Transaction-->          <set name="Items"       inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="Item"/>       </set>
<!--Transaction-->          <set name="NWAccntDlts" inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="NWAccntDlt"/> </set>
<!--Transaction-->          <set name="WashDlts"    inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="WashDlt"/>    </set>
<!--Item-->                 <set name="DltAllocs"   inverse="true" lazy="true" cascade="save-update"> <key column="ItemID"/>        <one-to-many class="DltAlloc"/>   </set>
 
<!--## Simple Fields ##-->
<!--Participant-->          <property name="Name" />       
<!--NWAccnt-->              <property name="Name" />       
<!--NWAccnt-->              <property name="Descrip" />       
<!--NWAccnt-->              <property name="Budgeted" />       
<!--DltAllocAccnt-->        <property name="Name" />       
<!--DltAllocAccnt-->        <property name="Descrip" />       
<!--DltAllocAccnt-->        <property name="Budgeted" />       
<!--Ownership-->            <property name="Shares" />       
<!--Post-->                 <property name="Instant" />       
<!--Transaction-->          <property name="Descrip" />       
<!--Transaction-->          <property name="Instant" />       
<!--Item-->                 <property name="Descrip" />       
<!--DltAlloc-->             <property name="Amount" />       
<!--NWAccntDlt-->           <property name="Amount" />       
<!--WashDlt-->              <property name="Amount" />       
 
 
<!--## End Tag for Class ##-->
<!--Participant-->      </class>         
<!--NWAccnt-->          </class>         
<!--DltAllocAccnt-->    </class>         
<!--Ownership-->        </class>         
<!--Post-->             </class>         
<!--Transaction-->      </class>         
<!--Item-->             </class>         
<!--DltAlloc-->         </class>         
<!--NWAccntDlt-->       </class>         
<!--WashDlt-->          </class>         
 
</hibernate-mapping>

Each section in the code window above is described below:

11.4.2.2 Classes

Class elements are used to map ORM classes to a database tables.

11.4.2.3 Primary Keys

Id elements are used to map ORM class properties to primary keys in the database.  The contained generator element in each id element indicates to NHibernate that values for these fields are automatically generated by the database (they are each an identity column in SQL Server)

11.4.2.4 Row Versions

Version elements are used to map ORM class properties to SQL Server timestamp fields in the database. (A timestamp field for a record in SQL Server is automatically updated whenever any of the fields of the record are updated.  Timestamps are used for optimistic concurrency.)

11.4.2.5 FKs That Can be Null

Many-to-one elements without a not-null attribute are used to map ORM class properties (that are themselves entities) to nullable foreign keys in the database.

11.4.2.6 FKs to Parents that Don't Track Entity as a Child

Many-to-one elements with a not-null attribute are used to map ORM class properties (that are themselves entities) to non-nullable foreign keys in the database.

11.4.2.7 FKs to Parents that Track Entity as a Child

Many-to-one elements with a cascade='save-update' attribute are used to map ORM class properties (that are themselves entities) to foreign keys in the database when the entity corresponding to the foreign key tracks the entity containing the property as a child object.

11.4.2.8 Children

Set elements are used to map ORM class properties (that are a collection of child entities) to a collection of child records in the database.

11.4.2.9 Simple Fields

Property elements are used to map ORM class properties to fields in the database that are not keys, timestamps, or child records.

12 How the ORM Classes Fit in the Architecture

Contents at a Glance   Detailed Contents

Each ORM has a namespace for the ORM classes and a namespace for the data access code that uses the ORM classes.  Here is the dependency diagram that was shown earlier: 

016Arch_ORM.gif

The ORM classes for LINQ to SQL are in the DalLinqToSQL namespace, the ORM classes for Entity Framework are in DalEF namespace, and the ORM classes for NHibernate are in the DalNH namespace.  I will refer these namespaces as the "Dal*" namespaces.

The LINQ to SQL ORM classes are only used in the ServiceLinqToSQL namespace, the Entity Framework ORM classes are only used in the ServiceEF namespace, and the NHibernate ORM classes are only used in the ServiceNH namespace.  I will refer to these namespaces as the Service* namespaces.

12.1 The Service Classes

Contents at a Glance   Detailed Contents

Each Service* namespace contains a Service class which defines a singleton object that is capable of providing all the data access services required by the client.  At run time a single Service object (for one of the ORMs) will be instantiated.  It will have access to the API for the corresponding ORM.  To persist data to the database, clients send data transfer objects to the service singleton, which converts them to ORM class objects and saves them to the database using the ORM's API.  Reading data from the database is essentially the reverse: upon receiving a request for data, the service singleton uses the ORM's API to generate ORM class objects containing database data, it then converts these to data transfer objects and sends them back to the client.

12.2 The IService Interface

Contents at a Glance   Detailed Contents

The Service class for each ORM implements an interface called IService.  The client only knows that the object it uses for data access implements the IService interface. (I am using the term "client" rather loosely here to actually refer to classes from the Presenter namespace (see the diagram above).)   The client itself is created from the hosting application run by the user (and defined in either the WinFormClient or WinApp namespaces).   If LEK is running in a three-tier configuration, then the WCF server (WinFormServer namespace, in the middle tier) instantiates one of the Service classes via a factory, and exposes it via WCF.  Each hosting application (WinFormClient namespace) will instantiate a WCF proxy to the server-side singleton and pass the proxy to its contained client (Presenter namespace).  The proxy also implements IService.  If LEK is running in a two-tier configuration, then the hosting application (WinApp namepace) instantiates one of the Service classes directly and passes it to its contained client.  In either case, the client only knows that it has an object that implements IService.  It does not know or care if the object is a proxy to a singleton on the server or a singleton in its own process.  It also does not know or care if the object uses LINQ to SQL, Entity Framework, or NHibernate.

CodeWindow 010
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using s=System;
using si=ServiceInterface;
using ser=System.Runtime.Serialization;
using sm=System.ServiceModel;
using gt=GlobalType;

namespace ServiceInterface
{
    [sm.ServiceContract]
    public interface IService
    {
        //for unit tests only
        [sm.OperationContract]int GetDltAllocAccntCount(    int? iExpected);
        [sm.OperationContract]int GetNWAccntCount(          int? iExpected);
        [sm.OperationContract]int GetParticipantCount(      int? iExpected);
        [sm.OperationContract]int GetOwnershipCount(        int? iExpected);
        [sm.OperationContract]int GetPostCount(             int? iExpected);
        [sm.OperationContract]int GetTransactionCount(      int? iExpected);
        [sm.OperationContract]int GetItemCount(             int? iExpected);
        [sm.OperationContract]int GetDltAllocCount(         int? iExpected);
        [sm.OperationContract]int GetNWAccntDltCount(       int? iExpected);
        [sm.OperationContract]int GetWashDltCount(          int? iExpected);

        [sm.OperationContract]void Test();
        [sm.OperationContract]void ClearDatabase();
        [sm.OperationContract]void InitDatabase(string str);

        [sm.OperationContract]DTO.Participant[] GetParticipants();

        [sm.OperationContract]gt.Participant[]      Get2WayParticipants();
        [sm.OperationContract]gt.NWAccnt[]          Get2WayNWAccnts();
        [sm.OperationContract]gt.DltAllocAccnt[]    Get2WayDltAllocAccnts();
    
        [sm.OperationContract]DTO.Cache GetCache();

        [sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void UpdateParticipants(  gt.Participant[]    arr);
        [sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void UpdateNWAccnts(      gt.NWAccnt[]        arr);
        [sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void UpdateDltAllocAccnts(gt.DltAllocAccnt[]  arr);
        
        [sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void UpdateOwnerships(DTO.Ownership[] arr);
        
        #region CodeWindow 044
        [sm.OperationContract]DTO.Transaction[] GetTransactions(DTO.TransPred aTransPred, int? iParticipantID);
        #endregion
        [sm.OperationContract]DTO.Transaction   GetTransaction(int iTransactionID);
        
        [sm.OperationContract]void VerifyAllTransactions();
    
        [sm.OperationContract]DTO.ItemEx[] GetItemsInTransactions(DTO.TransPred aTransPred);
        
        [sm.OperationContract]DTO.Summary GetSummaryOfTransactions(DTO.TransPred aTransPred, s.DateTime dtBudgetStartDate, int iMonthlyBudgetAmount);
        
        [sm.OperationContract]DTO.NWAccntDltEx[]    GetNWAccntDltsOfTransactions(int iNWAccntID, DTO.TransPred aTransPred);
        [sm.OperationContract]DTO.DltAlloc[]        GetDltAllocsOfTransactions(int iDltAllocAccntID, DTO.TransPred aTransPred);

        [sm.OperationContract]void Post();

        [sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void  DeleteTransaction(int iTransactionID, gt.RowVersion oTransactionRowVersion);
        #region CodeWindow 056
        [sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]int   SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance);
        #endregion
        [sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void  ResetItemsInTransactions(DTO.ItemUpdate[] arrItemUpdates);
    }
}

13 Using the ORM Frameworks for Simple Operations

Contents at a Glance   Detailed Contents

The Participant, NWAccnt, and DltAllocAccnt entities are manipulated in LEK very similarly.  For each of these there is a simple form with a datagrid that shows all of the entities in the database.  For each, the datagrid allows any of the Items to be edited, and it allows new Items to be added.  Changes are saved in batches: after the user edits a few entities and/or adds  a few entities the user submits all the changes at once to the database.  The UI utilizes two-way binding of the rows in the grid to entity objects, which makes the UI code extremely simple.  The classes for the entity objects that are bound to the grid are distinct from the ORM classes.  The former are defined in the GlobalType namespace, which doesn't depend on any of the ORM frameworks.  The latter are defined within the dal* namespaces, each of which is closely associated with an ORM framework.  In the LEK source code, the actual classes for the three mentioned GlobalType entities are derived from VerySimpleTwoWayDTO, which is also in the GlobalType namespace. Simple usage of the frameworks will be described in this section by focusing on the Participant GlobalType and ORM classes.  For the GlobalType Participant class however, this discussion  will use a version without a base class.  This should make it a bit easier to focus on the concepts of this section, without having to wade through the inheritance structure of the Participant GlobalType.

13.1 Row Version (GlobalType Namespace)

Contents at a Glance   Detailed Contents

The Participant GlobalType depends on RowVersion, also in the GlobalType namespace.  RowVersion is an ORM independent type used in LEK for timestamp fields in the database.  Timestamp fields in SQL Server are actually version stamps.  For a table with a timestamp field, each time any field in a record changes, SQL Server will automatically update the timestamp field for the record.  LEK uses the timestamp fields for optimistic concurrency.

CodeWindow 011
[ser.DataContract]
public class RowVersion
{
    [ser.DataMember]byte[] _data;
    public RowVersion(byte[] data)
    {
        _data = data;
    }
    public byte[] GetCopyOfData()
    {
        if(_data==null)
            return null;
        return (byte[])_data.Clone();
    }
    public static bool ValueEquals(RowVersion l, RowVersion r)
    {
        if(l == r)                  return true;
        if(l == null)               return false;
        if(r == null)               return false;
        
        if(l._data == r._data)  return true;
        if(l._data == null)     return false;
        if(r._data == null)     return false;
        if(l._data.Length != r._data.Length) return false;
        int C = l._data.Length;
        for(int i=0; i<C; i++)
        {
            if(l._data[i] != r._data[i])
                return false;
        }

        return true;
    }
}

13.2 Paticipant (GlobalType Namespace)

Contents at a Glance   Detailed Contents

In addition to having a member for each field in the database, the Participant GlobalType also has members that make it possible to track the edit state of the entity.

CodeWindow 012
    [ser.DataContract]
    public class Participant
    {
        [ser.DataMember]bool _bNew;
        [ser.DataMember]int? _iID;
        [ser.DataMember]RowVersion _RowVersion;
        [ser.DataMember]string _strName;
        [ser.DataMember]protected bool _bModified;
        public int? ID { get { return _iID; } }
        public string Name 
        { 
            get 
            { 
                return _strName; 
            } 
            set 
            { 
                _strName = value;
                if(_iID.HasValue)
                    _bModified = true;
            } 
        }
        public bool Modified
        {
            get { return _bModified; }
        }
        public bool New
        {
            get { return _bNew; }
        }
        public RowVersion GetRowVersion(){return _RowVersion;}
        public override string ToString()
        {
            return Name;
        }

        public Participant(int iID, RowVersion oRowVersion, string strName)
        {
            _iID = iID;
            _RowVersion = oRowVersion;
            _strName = strName;
            _bNew = false;
        }
        public Participant(string strName   )
        {
            _strName = strName;
            _bNew = true;
        }
        public Participant()
        :this("?")
        {
        }
    }

13.3 The Service Class

Contents at a Glance   Detailed Contents

In a running instance of LEK, the Service object is responsible for receiving requests from clients and generating the responses.  There is a Service class defined for each ORM Framework, but only one is instantiated at run time.  The Service classes are in the Service* namespaces.

13.4 Simple Data Read Operations

Contents at a Glance   Detailed Contents

Each Service class has a public function called Get2WayParticpants() which is responsible for returning a collection of all the Participants as an array of GlobalType.Participant objects, sorted by the name of the Participant.

13.4.1 Data Access Code

Contents at a Glance   Detailed Contents
13.4.1.1 LINQ to SQL

The version of the Get2WayParticipants function in the service class for LINQ to SQL is shown below:

CodeWindow 013
public gt.Participant[] Get2WayParticipants()
{
    using (var ctx = new dal.DataClassesDataContext(CONN))
    {
        var strm =
            from e in ctx.Participants
            orderby e.Name
            select new gt.Participant(e.ID, new gt.RowVersion(e.timestamp.ToArray()), e.Name);

        return strm.ToArray();
    }
}

First a context object for LINQ to SQL is created.  Next a LINQ query is specified that retrieves ORM entity objects from the database and translates them into GlobalType.Participant objects.  Finally, with a call to strm.ToArray() the query gets executed and the results are returned to the caller. 

13.4.1.2 Entity Framework

The Entity Framework version of the function differs from the LINQ to SQL version in that the creation of the GlobalType.Participant objects happens outside of the framework rather than within it.  The version of Entity Framework that comes with .NET 3.5 SP1 is not capable of projecting results into objects by calling a constructor for those objects with parameters.  So, instead, the results are projected into an anonymous type, and these are translated into GlobalType.Participants via a LINQ to Objects expression:

CodeWindow 014
public gt.Participant[] Get2WayParticipants()
{
    using (var ctx = new dal.LekEntities(CONN))
    {
        var strm =
            from e in ctx.Participant
            orderby e.Name
            select new {e.ID, e.timestamp, e.Name};

        //Entity Framework can't handle constructors with parameters
        //so the gt.Participants will  be created outside of EF
        var lst = strm.ToList();
        IEnumerable<gt.Participant> strmParticipants = lst.Select(
            e=>new gt.Participant(e.ID, new gt.RowVersion(e.timestamp), e.Name)
        );
        
        return strmParticipants.ToArray();
    }
}
13.4.1.3 NHibernate

The NHibernate version of the function uses a completely different paradigm for reading the entities out of the database.  Instead of creating a LINQ query, an NHibernate Criteria object is created, to which is added the filtering, projecting, and ordering information.  Setting up the Criteria is made a bit easier by using the NHibernate Lambda Extensions (available at http://code.google.com/p/nhlambdaextensions/).  Without this library, creating the Criteria object would require supplying ORM entity names and fields as strings.  

As with the Entity Framework version, the NHibernate version of the function translates the results into a collection of GlobalType.Participant objects via a LINQ to Objects expression. 

CodeWindow 015
public gt.Participant[] Get2WayParticipants()
{
    using(var ctx = dal.SessionFactory.OpenSession(CONN))
    {
        nh.ICriteria aCriterea  = ctx.CreateCriteria(typeof(dal.Participant));
        aCriterea.AddOrder<dal.Participant>(e=>e.Name, nh.Criterion.Order.Asc);
        IList<dal.Participant> lst = aCriterea.List<dal.Participant>();

        IEnumerable<gt.Participant> strmParticipants= lst.Select(
            e=>new gt.Participant(e.ID, new gt.RowVersion(e.timestamp), e.Name)
        );
        
        return strmParticipants.ToArray();

    }
}

13.4.2 Generated SQL

Contents at a Glance   Detailed Contents

Microsoft's SQL Server Profiler is very handy for checking the SQL that the ORM is generating.  If you are writing a LINQ statements or creating NHibernate Criteria objects that are different from those you have created before, you will probably want to check that the SQL being generated is what you expect. 

13.4.2.1 LINQ to SQL

The SQL generated for the LINQ to SQL version of Get2WayParticipants() is straight forward:

CodeWindow 016
SELECT 
    [t0].[ID] AS [iID], 
    [t0].[timestamp], 
    [t0].[Name] AS [strName]
FROM 
    [dbo].[Participant] AS [t0]
ORDER BY 
    [t0].[Name]
13.4.2.2 Entity Framework

The SQL generated for the Entity Framework version of Get2WayParticipants() is a bit more complicated, but produces the same results:

CodeWindow 017
SELECT 
    [Project1].[C1] AS [C1], 
    [Project1].[ID] AS [ID], 
    [Project1].[timestamp] AS [timestamp], 
    [Project1].[Name] AS [Name]
FROM ( 
    SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[timestamp] AS [timestamp], 
        1 AS [C1]
    FROM [dbo].[Participant] AS [Extent1]
)  AS [Project1]
ORDER BY 
    [Project1].[Name] ASC
13.4.2.3 NHibernate

The SQL generated for the NHibernate version of Get2WayParticipants() is nearly identical to that created for LINQ to SQL:

CodeWindow 018
SELECT 
    this_.ID as ID9_0_, 
    this_.timestamp as timestamp9_0_, 
    this_.Name as Name9_0_ 
FROM 
    Participant this_ 
ORDER BY 
    this_.Name asc

13.5 Simple Data Write Operations

Contents at a Glance   Detailed Contents

Each Service class has a public function called UpdateParticipants() which is responsible for updating the database from a passed in collection of GlobalType.Participant objects.  The passed in collection can contain unmodified Participants, modified Participants, and new Participants.  The function will ignore the unchanged Participants, update the modified Participants, and add the new Participants.  If the RowVersion field of a modified GlobalType.Participant object contains a different value than the corresponding timestamp field in the database, then LEK will assume that the record changed since the snapshot was taken (in the call to Get2WayParticipants()), and will throw a specially designed exception.

As mentioned earlier, the Particpant, NWAccnt, and DltAllocAccnt entities are very similar.  The GlobalType versions of these are actually all derived from a common base class called VerySimpleTwoWayDTO.  This document, however, presents GlobalType.Participant as a single, non-derived class.  Similarly in the source code for LEK UpdateParticpants(), UpdateNWAccnts() and UpdateDltAllocAccnts() are all shell functions, that pass on their arguments, along with several delegates, to a private functions named UpdateVerySimpleDTO(), which does all the work.  This document, however, will present UpdateParticipants() as representative of all three functions, and will present it as doing nearly all the work without the aid of a generic implementation function.

13.5.1 Data Access Code

13.5.1.1 Supporting Code Not Specific to Any ORM
Contents at a Glance   Detailed Contents

The specially designed exception that is thrown for concurrency errors contains the details of the concurrency problem in a contained object of a custom type ConcurrencyFault, defined in the DTO namespace:

CodeWindow 019
[ser.DataContract]
public class ConcurrencyFault
{
    [ser.DataMember] public string Description{get; private set;}
    public ConcurrencyFault(string strDescription)
    {
        Description = strDescription;
    }
}

The actual type for the exception is a constructed type based on the .NET framework supplied generic type System.ServiceModel.FaultException and on the above mentioned ConcurrencyFault type.  System.ServiceModel.FaultException is specially designed to work well with SOAP and WCF.

A factory method is defined in the ServerUtil namespace for creating the concurrency exception as a System.ServiceModel.FaultException<DTO.ConcurrencyFault> object:

CodeWindow 020
public static s.ServiceModel.FaultException<DTO.ConcurrencyFault> CreateConcurrencyFaultException(string strDescrip)
{
    DTO.ConcurrencyFault fault = new DTO.ConcurrencyFault(strDescrip);
    return new s.ServiceModel.FaultException<DTO.ConcurrencyFault>(
        fault, 
        "ConcurrencyFault: " + fault.Description);
}
13.5.1.2 LINQ to SQL Code for a Simple Data Write Operation
Contents at a Glance   Detailed Contents

The LINQ to SQL version of UpdateParticipants() also depends on a helper function to translate from the ORM independent timestamp type (GlobalType.RowVersion) to the type used by the LINQ to SQL Framework to represent timestamps (System.Data.Linq.Binary):

CodeWindow 021
public static s.Data.Linq.Binary CreateORMRowVersion(gt.RowVersion aRowVersion)
{
    if(aRowVersion==null)
        return null;
    return new System.Data.Linq.Binary(aRowVersion.GetCopyOfData());
}

So, having covered everything that UpdateParticipants() depends on, here, at long last, is the LINQ to SQL version of UpdateParticipants():

CodeWindow 022
        public void UpdateParticipants(gt.Participant[] arr)
        {
            using (var ctx = new dal.DataClassesDataContext(CONN))
            {
                foreach (gt.Participant o in arr)
                {
                    dal.Participant e = new dal.Participant();
                    if (o.New)
                    {
                        e.Name = o.Name;
                        ctx.Participants.InsertOnSubmit(e);
                    }
                    else if (o.Modified)
                    {
                        e.ID = o.ID.Value; 
                        e.timestamp=CreateORMRowVersion(o.GetRowVersion());
                        e.Name = o.Name;
                        ctx.Participants.Attach(e, /*asModified=*/true);
                    }
                }
                try
                {
                    ctx.SubmitChanges();
                }
                catch(s.Data.Linq.ChangeConflictException)
                {
                    throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Participant).Name);
                }
            }
        }

The function loops through all the passed in GlobalType.Participants, adding the new ones, updating the database with the modified ones, and ignoring the rest.   If the LINQ to SQL framework notices that the timestamp field of a record being updated is different from the timestamp field of the object being used to update the record then the framework will throw a System.Data.Linq.ChangeConflictException.  This exception will be caught, translated into a (non-ORM specific) System.ServiceModel.FaultException<DTO.ConcurrencyFault> and re-thrown.

13.5.1.3 Entity Framework Code for a Simple Data Write Operation
Contents at a Glance   Detailed Contents

The Entity Framework version of UpdateParticipants() is structurally very similar, but making this so requires several helper functions when using the version of Entity Framework that comes with .NET Framework 3.5 SP1.  This version of Entity Framework is easy to work with if you are willing to query for an ORM object, update it, and then submit the changes.  But if you are unwilling to query for data that you already have (because you don't want to hit the database more than is really necessary) then you must jump through quite a few hoops.

With LINQ to SQL manually setting the primary key of an ORM entity was as simple as assigning the key as an integer to the ORM entity field that maps to the primary key in the database (example: e.ID = iID.Value).  With Entity Framework keys are stored in ORM objects as System.Data.EntityKeys - which can accommodate non-integer compound keys.  This extra flexibility has a cost though: it is much more difficult to create one of these types of keys than it is to assign a simple integer.  Here is the helper function for creating a System.Data.EntityKey when it is just going to be an integer:

CodeWindow 023
public static s.Data.EntityKey CreateEntityKey(int iID, string strQualifiedEntitySetName)
{
    IEnumerable<KeyValuePair<string, object>> entityKeyValues =
        new KeyValuePair<string, object>[] {
            new KeyValuePair<string, object>("ID", iID) };

    return  new s.Data.EntityKey(strQualifiedEntitySetName, entityKeyValues);
}

As with LINQ to SQL, the Entity Framework version of UpdateParticipant() also depends on a helper function to translate from the ORM independent timestamp type (GlobalType.RowVersion) to the type used by Entity Framework to represent timestamps (Byte[]):

CodeWindow 024
public static Byte[] CreateORMRowVersion(gt.RowVersion aRowVersion)
{
    if(aRowVersion==null)
        return null;
    return aRowVersion.GetCopyOfData();
}

Updating an Entity Framework ORM that you have just queried is easy: you just update the fields and then call SaveChanges() on the context.  As with LINQ to SQL, this will only work if you queried for the object with the same context that you are using to save the object (probably meaning that you just queried for it).  If, instead you will be updating the database with an ORM entity that you created yourself (or with one that you queried earlier using another context) then you must "attach" it to the context.  With LINQ to SQL, attaching an ORM object that contains updated data required a call to the context's Attach() function with the AsModified parameter set to true. A Entity Framework context (in .NET 3.5 SP1) also has an "Attach" function, but it does not have an AsModified parameter to set.  Instead you must loop through the meta data for the ORM object specifically indicating which fields have modified data.  If your ORM object has dozens of fields, only a few of which will have updated data, then this is a good thing, as you will want to do this anyway so that the resulting SQL won't attempt to update fields that didn't change.  But for the simpler situation where you just want to update nearly all the fields, there isn't an easy default.

The Entity Framework version of  UpdateParticipants() relies on the SetAllPropsAsModified() helper function to indicate to Entity Framework that nearly all the fields in a recently attached entity should be used to update the corresponding fields in the associated database record:

CodeWindow 025
public static void SetAllPropsAsModified(dal.LekEntities ctx, object entity)
{
    s.Data.Objects.ObjectStateEntry aObjectStateEntry = ctx.ObjectStateManager.GetObjectStateEntry(entity);
    s.Collections.ObjectModel.ReadOnlyCollection<s.Data.Common.FieldMetadata> collFieldMetadatas
     = aObjectStateEntry.CurrentValues.DataRecordInfo.FieldMetadata;
    foreach(var propertyName in collFieldMetadatas.Select(o => o.FieldType.Name))
    {
        if(propertyName == "ID")
            continue;
        if(propertyName == "timestamp")
            continue;
        aObjectStateEntry.SetModifiedProperty(propertyName);
    }
}

By relying on the above helper functions, the Entity Framework version of  UpdateParticipants() can assume a form very similar to that of LINQ to SQL version:

CodeWindow 026
        public void UpdateParticipants(gt.Participant[] arr)
        {
            string strQualifiedEntitySetName = typeof(dal.LekEntities).Name + "." + typeof(dal.Participant).Name;
            using (var ctx = new dal.LekEntities(CONN))
            {
                foreach (gt.Participant o in arr)
                {
                    dal.Participant e = new dal.Participant();
                    if (o.New)
                    {
                        e.Name = o.Name;
                        ctx.AddToParticipant(e);
                    }
                    else if (o.Modified)
                    {
                        e.ID = o.ID.Value; 
                        e.timestamp = CreateORMRowVersion(o.GetRowVersion()); 
                        e.EntityKey = CreateEntityKey(e.ID, strQualifiedEntitySetName);
                        e.Name = o.Name;;
                        ctx.Attach(e); 
                        SetAllPropsAsModified(ctx, e);
                    }
                }
                try
                {
                    ctx.SaveChanges();
                }
                catch(s.Data.OptimisticConcurrencyException)
                {
                    throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Participant).Name);
                }
            }
        }

As with the LINQ to SQL version, the function loops through all the passed in GlobalType.Participants, adding the new ones, updating the database with the modified ones, and ignoring the rest.   If Entity Framework notices that the timestamp field of a record being updated is different from the timestamp field of the object being used to update the record then the Entity Framework will throw a System.Data.OptimisticConcurrencyException.  This exception will be caught, translated into a (non-ORM specific) s.ServiceModel.FaultException<DTO.ConcurrencyFault> and re-thrown.

13.5.1.4 NHibernate Code for a Simple Data Write Operation
Contents at a Glance   Detailed Contents

The NHibernate version of the UpdateParticipants() function is similar to the LINQ to SQL and Entity Framework versions.  

The helper function for creating the ORM specific type for the timestamp is the same as it is in Entity Framework:

CodeWindow 027
public static Byte[] CreateORMRowVersion(gt.RowVersion aRowVersion)
{
    if(aRowVersion==null)
        return null;
    return aRowVersion.GetCopyOfData();
}

As with LINQ to SQL, integers that are primary keys can be set directly and you are not required to loop through the fields of the object to indicate that the fields contain updated data.

CodeWindow 028
        public void UpdateParticipants(gt.Participant[] arr)
        {
            using(var ctx = dal.SessionFactory.OpenSession(CONN))
            {
                foreach (gt.Participant o in arr)
                {
                    dal.Participant e = new dal.Participant();
                    if (o.New)
                    {
                        e.Name = o.Name;
                        ctx.Save(e);
                    }
                    else if (o.Modified)
                    {
                        e.ID = o.ID.Value; 
                        e.RowVersion = CreateORMRowVersion(o.GetRowVersion());
                        e.Name = o.Name;
                        ctx.Update(e);
                    }
                }
                try
                {
                    ctx.Flush();
                }
                catch(nh.StaleObjectStateException)
                {
                    throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Participant).Name);
                }
            }
        }

As with the other versions, the function loops through all the passed in GlobalType.Participants, adding the new ones, updating the database with the modified ones, and ignoring the rest.   If NHibernate notices that the timestamp field of a record being updated is different from the timestamp field of the object being used to update the record then NHibernate will throw a NHibernate.StaleObjectStateException.  This exception will be caught, translated into a (non-ORM specific) s.ServiceModel.FaultException<DTO.ConcurrencyFault> and re-thrown.

13.5.2 Generated SQL for a Simple Data Write Operation

Contents at a Glance   Detailed Contents

The code windows below show the SQL generated by each ORM when UpdateParticipants() is passed one updated Participant and one new Participant.  I obtained the SQL from a SQL Server Profiler trace.  I reformatted the code a bit and added the comments.

13.5.2.1 LINQ to SQL
CodeWindow 029
-- INSERT TSQL
    INSERT INTO [dbo].[Participant]([Name])
    VALUES (@p0)

    SELECT [t0].[ID], [t0].[timestamp]
    FROM [dbo].[Participant] AS [t0]
    WHERE [t0].[ID] = (SCOPE_IDENTITY())

-- INSERT PARAMS
--  varchar(5) @p0='Misty'


-- UPDATE TSQL
    UPDATE [dbo].[Participant]
    SET [Name] = @p2
    WHERE ([ID] = @p0) AND ([timestamp] = @p1)

    SELECT [t1].[timestamp]
    FROM [dbo].[Participant] AS [t1]
    WHERE ((@@ROWCOUNT) > 0) AND ([t1].[ID] = @p3)

-- UPDATE PARAMS
--  int        @p0=2
--  timestamp  @p1=0x000000000001334F
--  varchar(5) @p2='Debra'
--  int        @p3=2
13.5.2.2 Entity Framework
CodeWindow 030
-- UPDATE TSQL
    update [dbo].[Participant]
    set [Name] = @0
    where (([ID] = @1) and ([timestamp] = @2))
    
    select [timestamp]
    from [dbo].[Participant]
    where @@ROWCOUNT > 0 and [ID] = @1

-- UPDATE PARAMS
--  varchar(5) @0='Debra',
--  int        @1=2,
--  binary(8)  @2=0x0000000000013371


-- INSERT TSQL
    insert [dbo].[Participant]([Name])
    values (@0)

    select [ID], [timestamp]
    from [dbo].[Participant]
    where @@ROWCOUNT > 0 and [ID] = scope_identity()

-- INSERT PARAMS
--  varchar(5) @0='Misty'
13.5.2.3 NHibernate
CodeWindow 031
-- INSERT TSQL, PART 1
    INSERT INTO Participant (Name) VALUES (@p0); 

    select SCOPE_IDENTITY()

-- INSERT PARAMS, PART 1
--  nvarchar(5) @p0=N'Misty'


-- INSERT TSQL, PART 2
    SELECT participan_.timestamp as timestamp9_ 
    FROM Participant participan_ 
    WHERE participan_.ID=@p0

-- INSERT PARAMS, PART 2
--  int @p0=5


-- UPDATE TSQL, PART 1
    UPDATE Participant 
    SET Name = @p0 
    WHERE ID = @p1 AND timestamp = @p2

-- UPDATE PARAMS, PART 1
--  nvarchar(5)  @p0=N'Debra',
--  int          @p1=2,
--  varbinary(8) @p2=0x0000000000013383


-- UPDATE TSQL, PART 2
    SELECT participan_.timestamp as timestamp9_ 
    FROM Participant participan_ 
    WHERE participan_.ID=@p0

-- UPDATE PARAMS, PART 2
--  int @p0=2

 

14 Using the ORM Frameworks for a Complex Query

Contents at a Glance   Detailed Contents

Consider what would be required to create a collection of Transaction entities that complies with some criteria like date range or post status.  You should be able to specify the criteria for the Transactions using the ORM specific syntax, submit this to the ORM, and get back a collection of Transaction entities complete with all their descendent entities.  The child descendents entities for a Transaction include NWAccntDlts, WashDlts, and Items.  The grandchild descendents for a Transaction are the DltAllocs for each of the Transaction's (child) Items.  The default set of ORMs created for LINQ to SQL and Entity Framework, and the custom ORMs already described for NHibernate include members for all of these descendent entities.   However, issuing a typical query on each of these ORMs to return a collection of Transaction entities will not automatically return a set of entities with all these descendant initialized.  By default the ORMs provide "lazy loading" - associated entities are not loaded until you specifically ask for them.  So, after issuing a simple query for Transactions we could walk the list of Transaction entities and access each of the descendant entities in order to load them from the database.  However, this could result in a lot of queries being issued against the database.   If Transactions have an average of two NWAccntDlts, four WashDlts, four Items, and four DltAllocs for each Item and if just 10 Transactions are returned that would be a total of 261 separate queries being issued against the database (1 query for the list of Transactions, 20 queries for the NWAccntDlts, 40 queries for the WashDlts, 40 queries for the Items, and 160 queries for the DltAllocs.)  Clearly we want to avoid that. 

I believe that, for each ORM, it is possible to specify the ORM class mappings such that the framework will "eager" load specified descendent entities.  It is easy to imagine how that would work for a simple parent-child relationship:  The ORM framework would join the tables involved for a result set of the child entities with the parent fields tacked on to each child.  To convert this result set into entities the framework would loop through the result set creating a child entity for each result and a parent entity for each result with a unique set of parent fields.  You can probably imagine that the SQL for a more complex set of relationships like those we have for Transactions with several descendant entities would be quite a bit more complex.  Furthermore there would be much more redundant data returned, as each descendent would probably need to carry parent entity fields along with it. 

Another strategy that could be employed by the ORM would be to first obtain the list of top level objects using a simple query, and then, for each top level object issue a query for each type of descendant.  In the example scenario described above this would result in 41 queries in all - much better, but still a lot of hits to the database. 

A better strategy yet would be if the ORM would make a single query for each entity type and then weave the results together into entities.  In the example scenario described this would result in just 4 queries, and that would be independent of the number of Transactions returned (still only 4 queries even if 100 Transactions met the criteria instead of only 10). 

I do not know if any of the three ORMs can be configured to utilize this last strategy just described.  If they can this would be an advanced usage of the ORM, and one that I am not yet knowledgeable enough to describe.  But I can implement this strategy myself fairly easily, and thus allow the ORMs to keep their rudimentary, and accessible, configuration in place.

So to recap, the goal is to generate a list of Transaction entities that complies with some criteria and that have all their descendent data.   To do this five separate queries will be executed:

  • A query to return the Transactions that match the criteria.
  • A query to return the NWAccntDlts for the matched Transactions.
  • A query to return the WashDlts for the matched Transactions.
  • A query to return the Items for the matched Transactions.
  • A query to return the DltAllocs for the Items for the matched Transactions.

Each query will sort the results by the Transaction date and ID.  A routine will be executed that takes these 5 result streams and steps through them in a coordinated manner,  creating the collection of Transaction entities that include all the descendant data.

The biggest challenges for this endeavor, and the one that will have the most significance to you in your own work, is how to minimize the amount of redundant code in spite of having may different possible Transaction criteria.  The criteria possibilities will include Transactions that:

  • Occurred before a specified date.
  • Occurred on or after a specified date.
  • Occurred within a specified date range.
  • Have a specified ID.
  • Have an ID within a specified range.
  • Have a specified Post ID.
  • Have a Post ID within a specified range.
  • Have a Post ID that is less than or equal to a specified value.
  • Have a Post ID that is greater than or equal to a specified value or that is not posted (NULL Post ID)
  • Are posted (have a non-null Post ID).
  • Are not posted (have a null Post ID).

14.1 Segregation of Criteria Setup Code

Contents at a Glance   Detailed Contents

We would like to avoid the situation in which the code to setup the criteria is repeated for each of the 5 queries that will need to be executed.  The code to setup the criteria will need to be segregated from the rest of the logic.  Each ORM will require a different approach, and some approaches will be better than others.

Consider the following two entity classes, not related to LEK, or any ORM framework:

CodeWindow 032
        public class State
        {
            public string Name{get;set;}
            public int Area{get;set;}
            public int Perimiter{get;set;}
        }
        public class City
        {
            public string Name{get;set;}
            public int Population{get;set;}
            public State TheState{get;set;}
        }

14.1.1 Segregatable Criteria

Contents at a Glance   Detailed Contents
14.1.1.1 In LINQ

In LINQ to SQL and Entity Framework filtering is typically performed with LINQ statements:  

CodeWindow 033
        public static List<State> GetStatesSimple(IEnumerable<State> states)
        {
            IEnumerable<State> qry = from o in states where o.Area > 4 where o.Perimiter > 30  select o;
            return qry.ToList();
        }
        
        public static List<State> GetStatesSegregatable(IEnumerable<State> states)
        {
            IEnumerable<State> qry = from o in states select o;
            qry = from o in qry where o.Area > 4 select o;
            qry = from o in qry where o.Perimiter > 30 select o;
            return qry.ToList();
        }

The first function above shows a typical LINQ query and the second one shows one that has been broken down so that the criteria part can be segregated out.  In both cases the query isn't actually executed until ToList() is called.  The logic executed in the call to ToList() is the same in both cases.  Only the means of expressing that logic is different in the two functions.

14.1.1.2 In NHibernate

In NHibernate segregateable filtering is performed using an NHibernate Criteria object:

CodeWindow 034
        public static IList<State> GetStatesSegretable(nh.ISession ctx)
        {
            nh.ICriteria aCriteria = ctx.CreateCriteria<State>();
            aCriteria.Add<State>(o=>o.Area > 4);
            aCriteria.Add<State>(o=>o.Perimiter > 30);
            return aCriteria.List<State>();
        }

Note that a Criteria object has an entity type intrinsically associated with it, but this type isn't known to the compiler.  If you created an ICriteria based on City, the compiler wouldn't prevent you from from calling Add<State>(..).  In this respect LINQ is safer than NHibernate.

14.1.2 Traversing an Association

Contents at a Glance   Detailed Contents
14.1.2.1 In LINQ

In LINQ, if you are trying to filter a object based on attributes of a parent object it can be as simple as the following:

CodeWindow 035
        public static List<City> GetCitiesSegregateable(IEnumerable<City> cities)
        {
            IEnumerable<City> qry = from o in cities select o;
            qry = from o in qry where o.TheState.Area > 4 select o;
            qry = from o in qry where o.TheState.Perimiter > 30 select o;
            return qry.ToList();
        }

Note that the actual ORM provider (LINQ to SQL or Entity Framework) may require you to specify a join, but at least the LINQ syntax has the potential for being very clear.

14.1.2.2 In NHibernate

In NHibernate, filtering a object based on attributes of a parent is a bit trickier, partially due to limitations of the API syntax.  Each of the three functions below will compile, but only the third will execute:

CodeWindow 036
        public static IList<City> X1_GetCitiesSegregateable(nh.ISession ctx)
        {
            nh.ICriteria aCriteria = ctx.CreateCriteria<City>();
            //won't work, Criteria is intrinsically of Cities
            aCriteria.Add<State>(o=>o.Area > 4);
            aCriteria.Add<State>(o=>o.Perimiter > 30);
            return aCriteria.List<City>();
        }
    
        public static IList<City> X2_GetCitiesSegregateable(nh.ISession ctx)
        {
            nh.ICriteria aCriteria = ctx.CreateCriteria<City>();
            //won't work, must prepare for traversing relationship
            aCriteria.Add<City>(o=>o.TheState.Area > 4);
            aCriteria.Add<City>(o=>o.TheState.Perimiter > 30);
            return aCriteria.List<City>();
        }

        public static IList<City> GetCitiesSegregateable(nh.ISession ctx)
        {
            nh.ICriteria aCriteria = ctx.CreateCriteria<City>();
            State oStateAlias = null;
            aCriteria.CreateAlias<City>(o=>o.TheState, ()=>oStateAlias);
            aCriteria.Add(()=>oStateAlias.Area > 4);
            aCriteria.Add(()=>oStateAlias.Perimiter > 30);
            return aCriteria.List<City>();
        }

The first version of the function illustrates a trap that is easy to fall into due to the fact that the compiler doesn't know about the type behind aCriteria.  The second version will fail because it doesn't (but it must) explicitly indicate that a foreign key based relationship will be traversed.   Incidentally a similar situation can occur in LINQ to SQL.  The third version executes correctly, however it introduces yet another trap that is easy to fall into.  The oStateAlias variable in the aCriteria.CreateAlias... line must have the same name (but not necessarily be the same variable) as the oStateAlias variable in the aCriteria.Add... line.  The compiler doesn't know this, so it is easy to make a mistake when the aCriteria.Add... lines are segregated out into their own function.

14.1.3 A Function To Set Criteria

Contents at a Glance   Detailed Contents
14.1.3.1 In LINQ

Segregating (factoring) out the criteria logic into its own function in LINQ requires that the type to be acted on in the criteria setting function be applicable to all the cases in which the function is required.  Any function requiring the use of the criteria setting function will need to transform the type that it is working with into a type acceptable by the criteria setting function. Consider the following:

CodeWindow 037
        public interface IStateReferer
        {
            State TheState{get;}
        }
        public class CityState : IStateReferer
        {
            public City TheCity{get;set;}
            public State TheState{get;set;}
        }
        public class StateWrapper : IStateReferer
        {
            public State TheState{get;set;}
        }
    
        

        static IEnumerable<T> GetWithStateFilter<T>(IEnumerable<T> qry) where T : IStateReferer
        {
            qry = from o in qry where o.TheState.Area > 4 select o;
            qry = from o in qry where o.TheState.Perimiter > 30 select o;
            return qry;
        }

In the above code window, IStateReferer is the interface that expresses what will be required in a criteria setting function based on State.  CityState implements the interface and is convenient when querying for Cities that are in a State that complies with some criteria.  StateWrapper implements the interface and is convenient when querying for States that comply with some criteria.  GetWithStateFilter  is the criteria setting function based on the IStateReferer interface. 

14.1.3.2 In NHibernate

Segregating (factoring) out the criteria logic into its own function in NHibernate is very simple:

CodeWindow 038
        public static void AddStateFilterBasedOn_oStateAlias(nh.ICriteria aCriteria)
        {
            State oStateAlias = null;//must match alias setup in caller
            aCriteria.Add(()=>oStateAlias.Area > 4);
            aCriteria.Add(()=>oStateAlias.Perimiter > 30);
        }

In spite of its simplicity however, it is very easy to make a mistake.  If we pass an ICriteria object to this function that does not have a State alias setup the function will compile but won't execute.  Also if we pass an ICriteria object to this function that has an alias of the correct type setup, but for which setup was achieved with a different local variable name, then the function will compile but won't execute.

14.1.4 Calling Criteria Setting Function

Contents at a Glance   Detailed Contents
14.1.4.1 In LINQ

Calling the criteria setting function for LINQ requires that the type of the enumeration be "projected" (transformed) into a type that implements the IStateReferer interface, and that the transformed type be transformed back when returning the actual results:

CodeWindow 039
        public static List<City> GetCities(IEnumerable<City> cities)
        {
            IEnumerable<CityState> qry = from o in cities select new CityState{TheCity=o, TheState=o.TheState};
            qry = GetWithStateFilter<CityState>(qry);
            return qry.Select(o=>o.TheCity).ToList();
        }
14.1.4.2 In NHibernate

Calling the criteria setting function for NHibernate requires that an alias be setup using a local variable with the same type and the same name as a corresponding local variable in the criteria setting function:

CodeWindow 040
        public static IList<City> GetCities(nh.ISession ctx)
        {
            nh.ICriteria aCriteria = ctx.CreateCriteria<City>();
            State oStateAlias = null;//must match alias setup in AddStateFilterBasedOn_oStateAlias
            aCriteria.CreateAlias<City>(o=>o.TheState, ()=>oStateAlias);
            AddStateFilterBasedOn_oStateAlias(aCriteria);
            return aCriteria.List<City>();
        }

14.1.5 Calling Criteria Setting Function When NOT Traversing an Association

Contents at a Glance   Detailed Contents
14.1.5.1 In LINQ

Calling the criteria setting function for LINQ when not traversing a child to parent association is basically the same as calling the criteria setting function when a child to parent association is being traversed.  The only difference is that we are transforming to and from a StateWrapper instead of a CityState.

CodeWindow 041
        public static List<State> GetStates(IEnumerable<State> states)
        {
            IEnumerable<StateWrapper> qry = from o in states select new StateWrapper{TheState = o};
            qry = GetWithStateFilter<StateWrapper>(qry);
            return qry.Select(o=>o.TheState).ToList();
        }
14.1.5.2 In NHibernate

Calling the criteria setting function using NHibernate when not traversing a child to parent association is a bit tricky.  Both of the functions below will compile, but the first generates an error.  The second works correctly:

CodeWindow 042
        public static IList<State> X_GetStates(nh.ISession ctx)
        {
            nh.ICriteria aCriteria = ctx.CreateCriteria<State>();
            State oStateAlias = null;//must match alias setup in AddStateFilterBasedOn_oStateAlias
            aCriteria.CreateAlias<State>(o=>o, ()=>oStateAlias);//won't work
            AddStateFilterBasedOn_oStateAlias(aCriteria);
            return aCriteria.List<State>();
        }
        public static IList<State> GetStates(nh.ISession ctx)
        {
            State oStateAlias = null;//must match alias setup in AddStateFilterBasedOn_oStateAlias
            nh.Criterion.DetachedCriteria aDetCriteria = nhl.DetachedCriteria<State>.Create(()=>oStateAlias);
            nh.ICriteria aCriteria = aDetCriteria.GetExecutableCriteria(ctx);
            AddStateFilterBasedOn_oStateAlias(aCriteria);
            return aCriteria.List<State>();
        }

14.2 Transaction Predicates

Contents at a Glance   Detailed Contents

In LEK, Transaction criteria are specified in TransPred objects:

CodeWindow 043
[ser.DataContract]
[ser.KnownType(typeof(OptionalDateRangeTransPred))]
[ser.KnownType(typeof(BeforeTransPred           ))]
[ser.KnownType(typeof(OnOrAfterTransPred        ))]
[ser.KnownType(typeof(DateRangeTransPred        ))]
[ser.KnownType(typeof(IDTransPred               ))]
[ser.KnownType(typeof(IDRangeTransPred          ))]
[ser.KnownType(typeof(PostIDTransPred           ))]
[ser.KnownType(typeof(PostIDRangeTransPred      ))]
[ser.KnownType(typeof(MaxPostIDTransPred        ))]
[ser.KnownType(typeof(MinPostIDTransPred        ))]
[ser.KnownType(typeof(PostedTransPred           ))]
[ser.KnownType(typeof(UnPostedTransPred         ))]
public abstract class TransPred
{
}

#region TransPred derived classes
[ser.DataContract]
public class OptionalDateRangeTransPred : TransPred
{
    [ser.DataMember]public s.DateTime? OnOrAfter{get; private set;} 
    [ser.DataMember]public s.DateTime? Before   {get; private set;}
    public OptionalDateRangeTransPred(
        s.DateTime? dtOnOrAfter , 
        s.DateTime? dtBefore    )
    {
        OnOrAfter   = dtOnOrAfter   ;
        Before      = dtBefore      ;
    }
}
[ser.DataContract]
public class BeforeTransPred : TransPred
{
    [ser.DataMember]public s.DateTime Before{get; private set;}
    public BeforeTransPred(s.DateTime dtBefore)
    {
        Before = dtBefore;
    }
}
[ser.DataContract]
public class OnOrAfterTransPred : TransPred
{
    [ser.DataMember]public s.DateTime OnOrAfter{get; private set;}
    public OnOrAfterTransPred(s.DateTime dtOnOrAfter)
    {
        OnOrAfter = dtOnOrAfter;
    }
}
[ser.DataContract]
public class DateRangeTransPred : TransPred
{
    [ser.DataMember]public s.DateTime OnOrAfter {get; private set;} 
    [ser.DataMember]public s.DateTime Before    {get; private set;}
    public DateRangeTransPred(
        s.DateTime dtOnOrAfter  , 
        s.DateTime dtBefore     )
    {
        OnOrAfter   = dtOnOrAfter   ;
        Before      = dtBefore      ;
    }
}
[ser.DataContract]
public class IDTransPred : TransPred
{
    [ser.DataMember]public int ID{get; private set;}    
    public IDTransPred(int iID)
    {
        ID = iID;
    }
}
[ser.DataContract]
public class IDRangeTransPred : TransPred
{
    [ser.DataMember]public int SmallestID   {get; private set;} 
    [ser.DataMember]public int LargestID    {get; private set;}
    public IDRangeTransPred(
        int iSmallestID , 
        int iLargestID  )
    {
        SmallestID  = iSmallestID   ;
        LargestID   = iLargestID    ;
    }
}
[ser.DataContract]
public class PostIDTransPred : TransPred
{
    [ser.DataMember]public int ID{get; private set;}    
    public PostIDTransPred(int iID)
    {
        ID = iID;
    }
}
[ser.DataContract]
public class PostIDRangeTransPred : TransPred
{
    [ser.DataMember]public int SmallestID   {get; private set;} 
    [ser.DataMember]public int LargestID    {get; private set;}
    public PostIDRangeTransPred(
        int iSmallestID , 
        int iLargestID  )
    {
        SmallestID  = iSmallestID   ;
        LargestID   = iLargestID    ;
    }
}
[ser.DataContract]
public class MaxPostIDTransPred : TransPred
{
    [ser.DataMember]public int MaxID{get; private set;} 
    public MaxPostIDTransPred(int iMaxID)
    {
        MaxID = iMaxID;
    }
}
[ser.DataContract]
public class MinPostIDTransPred : TransPred
{
    [ser.DataMember]public int MinID{get; private set;} 
    public MinPostIDTransPred(int iMinID)
    {
        MinID = iMinID;
    }
}
[ser.DataContract]
public class PostedTransPred : TransPred
{
    
}
[ser.DataContract]
public class UnPostedTransPred : TransPred
{
    
}

#endregion

A TransPred object defines the criteria for returning a set of Transactions.  The abstract base class has a set of  System.Runtime.Serialization.KnownType attributes that specify all the concrete types that objects of this type can be.  This makes it possible for WCF to deserilize messages carrying objects of these types, even though the interface only specifies the base class.

Here is the signature of the actual function that will return the list of Transaction entities that complies with the specified criteria:

CodeWindow 044
[sm.OperationContract]DTO.Transaction[] GetTransactions(DTO.TransPred aTransPred, int? iParticipantID);

14.3 Supporting Code for Segregated Criteria Setup

Contents at a Glance   Detailed Contents

Recall that GetTransactions() will be implemented by querying separately for WashDlts, NWAccntDlts, Items, DltAllocs and Transactions ("the five queries").  The query for the Transactions will ensure that the Transactions match the criteria, and the query for each of the four descendant entities will ensure that the ancestor Transaction object for each child object will match the criteria.  Each of the five streams for the the five queries will be sorted by Transaction date first and Transaction id second.  Finally a splicing algorithm will be used to iterate through these steams in a coordinated manner, creating full initialized Transaction objects by combing Transaction entity objects with their child entity objects.

14.3.1 ..In LINQ to SQL and Entity Framework

The criteria setting function or functions for LINQ to SQL and Entity Framework will ideally be able to work with a type which can be created within the LINQ statement for each query.  This type must provide access to an underlying Transaction object so that the criteria setting function can impose its restrictions on it.  For both LINQ to SQL and Entity Framework the type is the interface ITransComposit.  Five concrete types (each made to work well with one of the five queries) implement the interface:

CodeWindow 045
interface ITransComposit
{
    dal.Transaction eTransaction {get;}
}
class WashDltJoinedToTrans : ITransComposit
{
    public dal.WashDlt eWashDlt {get; set;}
    public dal.Transaction eTransaction {get; set;}
}
class NWAccntDltJoinedToTrans : ITransComposit
{
    public dal.NWAccntDlt eNWAccntDlt {get; set;}
    public dal.Transaction eTransaction {get; set;}
}
class ItemJoinedToTrans : ITransComposit
{
    public dal.Item eItem {get; set;}
    public dal.Transaction eTransaction {get; set;}
}
class DltAllocJoinedToItemJoinedToTrans : ITransComposit
{
    public dal.DltAlloc eDltAlloc {get; set;}
    public dal.Item eItem {get; set;}
    public dal.Transaction eTransaction {get; set;}
}
class TransactionWrapper : ITransComposit
{
    public dal.Transaction eTransaction {get; set;}
}

14.3.2 ..In NHibernate

No supporting code is required in NHibernate to enable the five queries to use a common criteria setting function.

14.4 Criteria Setting Function

Contents at a Glance   Detailed Contents

14.4.1 ..In LINQ to SQL

Here is the criteria setting function for LINQ to SQL:

CodeWindow 046
IQueryable<T> AppendWhereClauses<T>(IQueryable<T> query, DTO.TransPred aTransPred) where T : ITransComposit
{
    aTransPred = ConvertIfConvPred(aTransPred);
    
    if(     aTransPred == null  )               {;}

    else if(aTransPred is DTO.BeforeTransPred   )   query = from composite in query where  composite.eTransaction.Instant < ((DTO.BeforeTransPred       )aTransPred).Before     select composite;
    else if(aTransPred is DTO.OnOrAfterTransPred)   query = from composite in query where  composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred    )aTransPred).OnOrAfter  select composite;
    else if(aTransPred is DTO.DateRangeTransPred){  query = from composite in query where  composite.eTransaction.Instant < ((DTO.DateRangeTransPred    )aTransPred).Before     select composite;
                                                    query = from composite in query where  composite.eTransaction.Instant >=((DTO.DateRangeTransPred    )aTransPred).OnOrAfter  select composite;}
    else if(aTransPred is DTO.IDTransPred       )   query = from composite in query where  composite.eTransaction.ID      ==((DTO.IDTransPred           )aTransPred).ID         select composite;
    else if(aTransPred is DTO.IDRangeTransPred  ){  query = from composite in query where  composite.eTransaction.ID      <=((DTO.IDRangeTransPred      )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.ID      >=((DTO.IDRangeTransPred      )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.PostIDTransPred   )   query = from composite in query where  composite.eTransaction.PostID  ==((DTO.PostIDTransPred       )aTransPred).ID         select composite;
    else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where  composite.eTransaction.PostID  <=((DTO.PostIDRangeTransPred  )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.PostID  >=((DTO.PostIDRangeTransPred  )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.MaxPostIDTransPred)   query = from composite in query where  composite.eTransaction.PostID  <=((DTO.MaxPostIDTransPred    )aTransPred).MaxID      select composite;
    else if(aTransPred is DTO.MinPostIDTransPred)   query = from composite in query where(!composite.eTransaction.PostID.HasValue 
                                                                                        || composite.eTransaction.PostID  >=((DTO.MinPostIDTransPred    )aTransPred).MinID)     select composite;
    else if(aTransPred is DTO.PostedTransPred   )   query = from composite in query where  composite.eTransaction.PostID.HasValue                                               select composite;
    else if(aTransPred is DTO.UnPostedTransPred )   query = from composite in query where !composite.eTransaction.PostID.HasValue                                               select composite;

    else throw new Exception();

    return query;
}

Notice that the signature and structure is very similar to the criteria setting function shown previously for the example State and City entities.

14.4.2 ..In Entity Framework

This very similar function for Entity Framework compiles:

CodeWindow 047
IQueryable<T>                                   AppendWhereClauses<T>(  IQueryable<T>                                   query, DTO.TransPred aTransPred) where T : ITransComposit
{
    aTransPred = ConvertIfConvPred(aTransPred);
    
    if(     aTransPred == null  )               {;}

    else if(aTransPred is DTO.BeforeTransPred   )   query = from composite in query where  composite.eTransaction.Instant < ((DTO.BeforeTransPred       )aTransPred).Before     select composite;
    else if(aTransPred is DTO.OnOrAfterTransPred)   query = from composite in query where  composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred    )aTransPred).OnOrAfter  select composite;
    else if(aTransPred is DTO.DateRangeTransPred){  query = from composite in query where  composite.eTransaction.Instant < ((DTO.DateRangeTransPred    )aTransPred).Before     select composite;
                                                    query = from composite in query where  composite.eTransaction.Instant >=((DTO.DateRangeTransPred    )aTransPred).OnOrAfter  select composite;}
    else if(aTransPred is DTO.IDTransPred       )   query = from composite in query where  composite.eTransaction.ID      ==((DTO.IDTransPred           )aTransPred).ID         select composite;
    else if(aTransPred is DTO.IDRangeTransPred  ){  query = from composite in query where  composite.eTransaction.ID      <=((DTO.IDRangeTransPred      )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.ID      >=((DTO.IDRangeTransPred      )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.PostIDTransPred   )   query = from composite in query where  composite.eTransaction.Post.ID  ==((DTO.PostIDTransPred      )aTransPred).ID         select composite;
    else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where  composite.eTransaction.Post.ID  <=((DTO.PostIDRangeTransPred )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.Post.ID  >=((DTO.PostIDRangeTransPred )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.MaxPostIDTransPred)   query = from composite in query where  composite.eTransaction.Post.ID  <=((DTO.MaxPostIDTransPred   )aTransPred).MaxID      select composite;
    else if(aTransPred is DTO.MinPostIDTransPred)   query = from composite in query where (composite.eTransaction.Post == null 
                                                                                        || composite.eTransaction.Post.ID  >=((DTO.MinPostIDTransPred   )aTransPred).MinID)     select composite;
    else if(aTransPred is DTO.PostedTransPred   )   query = from composite in query where  composite.eTransaction.Post    != null                                               select composite;
    else if(aTransPred is DTO.UnPostedTransPred )   query = from composite in query where  composite.eTransaction.Post    == null                                               select composite;

    else throw new Exception();

    return query;
}

But unfortunately execution produces the following error:

"Unable to cast the type 'ServiceEF.WashDltJoinedToTrans' to type 'ServiceEF.ITransComposit'. LINQ to Entities only supports casting Entity Data Model primitive types."

To bad.

We are forced to fall back on a separate non-generic implementation for each of the five queries:

CodeWindow 048
IQueryable<WashDltJoinedToTrans>                AppendWhereClauses(     IQueryable<WashDltJoinedToTrans>                query, DTO.TransPred aTransPred)// where T : ITransComposit
{
    aTransPred = ConvertIfConvPred(aTransPred);
    
    if(     aTransPred == null  )               {;}

    else if(aTransPred is DTO.BeforeTransPred   )   query = from composite in query where  composite.eTransaction.Instant < ((DTO.BeforeTransPred       )aTransPred).Before     select composite;
    else if(aTransPred is DTO.OnOrAfterTransPred)   query = from composite in query where  composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred    )aTransPred).OnOrAfter  select composite;
    else if(aTransPred is DTO.DateRangeTransPred){  query = from composite in query where  composite.eTransaction.Instant < ((DTO.DateRangeTransPred    )aTransPred).Before     select composite;
                                                    query = from composite in query where  composite.eTransaction.Instant >=((DTO.DateRangeTransPred    )aTransPred).OnOrAfter  select composite;}
    else if(aTransPred is DTO.IDTransPred       )   query = from composite in query where  composite.eTransaction.ID      ==((DTO.IDTransPred           )aTransPred).ID         select composite;
    else if(aTransPred is DTO.IDRangeTransPred  ){  query = from composite in query where  composite.eTransaction.ID      <=((DTO.IDRangeTransPred      )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.ID      >=((DTO.IDRangeTransPred      )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.PostIDTransPred   )   query = from composite in query where  composite.eTransaction.Post.ID ==((DTO.PostIDTransPred       )aTransPred).ID         select composite;
    else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred  )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred  )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.MaxPostIDTransPred)   query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred    )aTransPred).MaxID      select composite;
    else if(aTransPred is DTO.MinPostIDTransPred)   query = from composite in query where (composite.eTransaction.Post    == null 
                                                                                         ||composite.eTransaction.Post.ID  >=((DTO.MinPostIDTransPred   )aTransPred).MinID)     select composite;
    else if(aTransPred is DTO.PostedTransPred   )   query = from composite in query where  composite.eTransaction.Post    != null                                               select composite;
    else if(aTransPred is DTO.UnPostedTransPred )   query = from composite in query where  composite.eTransaction.Post    == null                                               select composite;

    else throw new Exception();

    return query;
}
IQueryable<NWAccntDltJoinedToTrans>             AppendWhereClauses(     IQueryable<NWAccntDltJoinedToTrans>             query, DTO.TransPred aTransPred)// where T : ITransComposit
{
    aTransPred = ConvertIfConvPred(aTransPred);
    
    if(     aTransPred == null  )               {;}

    else if(aTransPred is DTO.BeforeTransPred   )   query = from composite in query where  composite.eTransaction.Instant < ((DTO.BeforeTransPred       )aTransPred).Before     select composite;
    else if(aTransPred is DTO.OnOrAfterTransPred)   query = from composite in query where  composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred    )aTransPred).OnOrAfter  select composite;
    else if(aTransPred is DTO.DateRangeTransPred){  query = from composite in query where  composite.eTransaction.Instant < ((DTO.DateRangeTransPred    )aTransPred).Before     select composite;
                                                    query = from composite in query where  composite.eTransaction.Instant >=((DTO.DateRangeTransPred    )aTransPred).OnOrAfter  select composite;}
    else if(aTransPred is DTO.IDTransPred       )   query = from composite in query where  composite.eTransaction.ID      ==((DTO.IDTransPred           )aTransPred).ID         select composite;
    else if(aTransPred is DTO.IDRangeTransPred  ){  query = from composite in query where  composite.eTransaction.ID      <=((DTO.IDRangeTransPred      )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.ID      >=((DTO.IDRangeTransPred      )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.PostIDTransPred   )   query = from composite in query where  composite.eTransaction.Post.ID ==((DTO.PostIDTransPred       )aTransPred).ID         select composite;
    else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred  )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred  )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.MaxPostIDTransPred)   query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred    )aTransPred).MaxID      select composite;
    else if(aTransPred is DTO.MinPostIDTransPred)   query = from composite in query where (composite.eTransaction.Post    == null 
                                                                                         ||composite.eTransaction.Post.ID  >=((DTO.MinPostIDTransPred   )aTransPred).MinID)     select composite;
    else if(aTransPred is DTO.PostedTransPred   )   query = from composite in query where  composite.eTransaction.Post    != null                                               select composite;
    else if(aTransPred is DTO.UnPostedTransPred )   query = from composite in query where  composite.eTransaction.Post    == null                                               select composite;

    else throw new Exception();

    return query;
}
IQueryable<ItemJoinedToTrans>                   AppendWhereClauses(     IQueryable<ItemJoinedToTrans>                   query, DTO.TransPred aTransPred)// where T : ITransComposit
{
    aTransPred = ConvertIfConvPred(aTransPred);
    
    if(     aTransPred == null  )               {;}

    else if(aTransPred is DTO.BeforeTransPred   )   query = from composite in query where  composite.eTransaction.Instant < ((DTO.BeforeTransPred       )aTransPred).Before     select composite;
    else if(aTransPred is DTO.OnOrAfterTransPred)   query = from composite in query where  composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred    )aTransPred).OnOrAfter  select composite;
    else if(aTransPred is DTO.DateRangeTransPred){  query = from composite in query where  composite.eTransaction.Instant < ((DTO.DateRangeTransPred    )aTransPred).Before     select composite;
                                                    query = from composite in query where  composite.eTransaction.Instant >=((DTO.DateRangeTransPred    )aTransPred).OnOrAfter  select composite;}
    else if(aTransPred is DTO.IDTransPred       )   query = from composite in query where  composite.eTransaction.ID      ==((DTO.IDTransPred           )aTransPred).ID         select composite;
    else if(aTransPred is DTO.IDRangeTransPred  ){  query = from composite in query where  composite.eTransaction.ID      <=((DTO.IDRangeTransPred      )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.ID      >=((DTO.IDRangeTransPred      )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.PostIDTransPred   )   query = from composite in query where  composite.eTransaction.Post.ID ==((DTO.PostIDTransPred       )aTransPred).ID         select composite;
    else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred  )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred  )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.MaxPostIDTransPred)   query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred    )aTransPred).MaxID      select composite;
    else if(aTransPred is DTO.MinPostIDTransPred)   query = from composite in query where (composite.eTransaction.Post    == null 
                                                                                         ||composite.eTransaction.Post.ID  >=((DTO.MinPostIDTransPred   )aTransPred).MinID)     select composite;
    else if(aTransPred is DTO.PostedTransPred   )   query = from composite in query where  composite.eTransaction.Post    != null                                               select composite;
    else if(aTransPred is DTO.UnPostedTransPred )   query = from composite in query where  composite.eTransaction.Post    == null                                               select composite;

    else throw new Exception();

    return query;
}
IQueryable<DltAllocJoinedToItemJoinedToTrans>   AppendWhereClauses(     IQueryable<DltAllocJoinedToItemJoinedToTrans>   query, DTO.TransPred aTransPred)// where T : ITransComposit
{
    aTransPred = ConvertIfConvPred(aTransPred);
    
    if(     aTransPred == null  )               {;}

    else if(aTransPred is DTO.BeforeTransPred   )   query = from composite in query where  composite.eTransaction.Instant < ((DTO.BeforeTransPred       )aTransPred).Before     select composite;
    else if(aTransPred is DTO.OnOrAfterTransPred)   query = from composite in query where  composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred    )aTransPred).OnOrAfter  select composite;
    else if(aTransPred is DTO.DateRangeTransPred){  query = from composite in query where  composite.eTransaction.Instant < ((DTO.DateRangeTransPred    )aTransPred).Before     select composite;
                                                    query = from composite in query where  composite.eTransaction.Instant >=((DTO.DateRangeTransPred    )aTransPred).OnOrAfter  select composite;}
    else if(aTransPred is DTO.IDTransPred       )   query = from composite in query where  composite.eTransaction.ID      ==((DTO.IDTransPred           )aTransPred).ID         select composite;
    else if(aTransPred is DTO.IDRangeTransPred  ){  query = from composite in query where  composite.eTransaction.ID      <=((DTO.IDRangeTransPred      )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.ID      >=((DTO.IDRangeTransPred      )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.PostIDTransPred   )   query = from composite in query where  composite.eTransaction.Post.ID ==((DTO.PostIDTransPred       )aTransPred).ID         select composite;
    else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred  )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred  )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.MaxPostIDTransPred)   query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred    )aTransPred).MaxID      select composite;
    else if(aTransPred is DTO.MinPostIDTransPred)   query = from composite in query where (composite.eTransaction.Post    == null 
                                                                                         ||composite.eTransaction.Post.ID  >=((DTO.MinPostIDTransPred   )aTransPred).MinID)     select composite;
    else if(aTransPred is DTO.PostedTransPred   )   query = from composite in query where  composite.eTransaction.Post    != null                                               select composite;
    else if(aTransPred is DTO.UnPostedTransPred )   query = from composite in query where  composite.eTransaction.Post    == null                                               select composite;

    else throw new Exception();

    return query;
}
IQueryable<TransactionWrapper>                  AppendWhereClauses(     IQueryable<TransactionWrapper>                  query, DTO.TransPred aTransPred)// where T : ITransComposit
{
    aTransPred = ConvertIfConvPred(aTransPred);
    
    if(     aTransPred == null  )               {;}

    else if(aTransPred is DTO.BeforeTransPred   )   query = from composite in query where  composite.eTransaction.Instant < ((DTO.BeforeTransPred       )aTransPred).Before     select composite;
    else if(aTransPred is DTO.OnOrAfterTransPred)   query = from composite in query where  composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred    )aTransPred).OnOrAfter  select composite;
    else if(aTransPred is DTO.DateRangeTransPred){  query = from composite in query where  composite.eTransaction.Instant < ((DTO.DateRangeTransPred    )aTransPred).Before     select composite;
                                                    query = from composite in query where  composite.eTransaction.Instant >=((DTO.DateRangeTransPred    )aTransPred).OnOrAfter  select composite;}
    else if(aTransPred is DTO.IDTransPred       )   query = from composite in query where  composite.eTransaction.ID      ==((DTO.IDTransPred           )aTransPred).ID         select composite;
    else if(aTransPred is DTO.IDRangeTransPred  ){  query = from composite in query where  composite.eTransaction.ID      <=((DTO.IDRangeTransPred      )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.ID      >=((DTO.IDRangeTransPred      )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.PostIDTransPred   )   query = from composite in query where  composite.eTransaction.Post.ID ==((DTO.PostIDTransPred       )aTransPred).ID         select composite;
    else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred  )aTransPred).LargestID  select composite;
                                                    query = from composite in query where  composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred  )aTransPred).SmallestID select composite;}
    else if(aTransPred is DTO.MaxPostIDTransPred)   query = from composite in query where  composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred    )aTransPred).MaxID      select composite;
    else if(aTransPred is DTO.MinPostIDTransPred)   query = from composite in query where (composite.eTransaction.Post    == null 
                                                                                         ||composite.eTransaction.Post.ID  >=((DTO.MinPostIDTransPred   )aTransPred).MinID)     select composite;
    else if(aTransPred is DTO.PostedTransPred   )   query = from composite in query where  composite.eTransaction.Post    != null                                               select composite;
    else if(aTransPred is DTO.UnPostedTransPred )   query = from composite in query where  composite.eTransaction.Post    == null                                               select composite;

    else throw new Exception();

    return query;
}

14.4.3 ..In NHibernate

Here is the criteria setting function for NHibernate:

CodeWindow 049
void AddTransactionCriteria(nh.ICriteria aCriteria, DTO.TransPred aTransPred, bool bIncludeSort)
{
    aTransPred = ConvertIfConvPred(aTransPred);
    
    //must match name of local variable in calling function
    dal.Transaction oTransactionAlias = null;

    if(     aTransPred == null  )               {;}
    
    else if(aTransPred is DTO.BeforeTransPred   )   aCriteria.Add(()=>oTransactionAlias.Instant     <  ((DTO.BeforeTransPred     )aTransPred).Before);
    else if(aTransPred is DTO.OnOrAfterTransPred)   aCriteria.Add(()=>oTransactionAlias.Instant     >= ((DTO.OnOrAfterTransPred  )aTransPred).OnOrAfter);
    else if(aTransPred is DTO.DateRangeTransPred){  aCriteria.Add(()=>oTransactionAlias.Instant     <  ((DTO.DateRangeTransPred  )aTransPred).Before);
                                                    aCriteria.Add(()=>oTransactionAlias.Instant     >= ((DTO.DateRangeTransPred  )aTransPred).OnOrAfter);}
    else if(aTransPred is DTO.IDTransPred       )   aCriteria.Add(()=>oTransactionAlias.ID          == ((DTO.IDTransPred         )aTransPred).ID);
    else if(aTransPred is DTO.IDRangeTransPred){    aCriteria.Add(()=>oTransactionAlias.ID          <= ((DTO.IDRangeTransPred    )aTransPred).LargestID);
                                                    aCriteria.Add(()=>oTransactionAlias.ID          >= ((DTO.IDRangeTransPred    )aTransPred).SmallestID);}
    else if(aTransPred is DTO.PostIDTransPred)      aCriteria.Add(()=>oTransactionAlias.ThePost.ID  == ((DTO.PostIDTransPred     )aTransPred).ID);
    else if(aTransPred is DTO.PostIDRangeTransPred){aCriteria.Add(()=>oTransactionAlias.ThePost.ID  <= ((DTO.PostIDRangeTransPred)aTransPred).LargestID);
                                                    aCriteria.Add(()=>oTransactionAlias.ThePost.ID  >= ((DTO.PostIDRangeTransPred)aTransPred).SmallestID);}
    else if(aTransPred is DTO.MaxPostIDTransPred)   aCriteria.Add(()=>oTransactionAlias.ThePost.ID  <= ((DTO.MaxPostIDTransPred  )aTransPred).MaxID);
    else if(aTransPred is DTO.MinPostIDTransPred)
    {
        nh.Criterion.AbstractCriterion orExpressions = nh.Criterion.Restrictions.Or(
            SqlExpression.IsNull(()=>oTransactionAlias.ThePost),
            SqlExpression.CriterionFor(()=>oTransactionAlias.ThePost.ID >= ((DTO.MinPostIDTransPred)aTransPred).MinID));
        aCriteria.Add(orExpressions);
    }

    else if(aTransPred is DTO.PostedTransPred   )   aCriteria.Add(SqlExpression.IsNotNull(  ()=>oTransactionAlias.ThePost));
    else if(aTransPred is DTO.UnPostedTransPred )   aCriteria.Add(SqlExpression.IsNull(     ()=>oTransactionAlias.ThePost));

    else throw new Exception();

    if(bIncludeSort)
    {
        aCriteria.AddOrder(()=>oTransactionAlias.Instant, nh.Criterion.Order.Desc);
        aCriteria.AddOrder(()=>oTransactionAlias.ID     , nh.Criterion.Order.Desc);
    }

}

Notice that the signature and structure are very similar to the NHibernate criteria setting function shown previously for the example State and City entities.

14.5 Five Queries for Building a Collection of Transactions

Contents at a Glance   Detailed Contents

14.5.1 ..In LINQ to SQL

Here are the five queries for LINQ to SQL:

CodeWindow 050
IList<dal.WashDlt>      GetWashDltDAOsForTransactions(      dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
    var query = 
            from eWashDlt       in ctx.WashDlts
            join eTransaction   in ctx.Transactions on eWashDlt.TransactionID   equals eTransaction.ID
            select new WashDltJoinedToTrans{eWashDlt=eWashDlt, eTransaction=eTransaction};
    
    query = AppendWhereClauses(query, aTransPred);
    
    return (
        from eComposite in query 
        orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eWashDlt.ParticipantID ascending
        select eComposite.eWashDlt).ToList();
}
IList<dal.NWAccntDlt>   GetNWAccntDltDAOsForTransactions(   dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
    var query = 
            from eNWAccntDlt    in ctx.NWAccntDlts
            join eTransaction   in ctx.Transactions on eNWAccntDlt.TransactionID    equals eTransaction.ID
            select new NWAccntDltJoinedToTrans{eNWAccntDlt=eNWAccntDlt, eTransaction=eTransaction};
    
    query = AppendWhereClauses(query, aTransPred);

    
    return (
        from eComposite in query 
        orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eNWAccntDlt.NWAccntID ascending
        select eComposite.eNWAccntDlt).ToList();
}
IList<dal.Item>         GetItemDAOsForTransactions(         dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
    var query = 
            from eItem          in ctx.Items
            join eTransaction   in ctx.Transactions on eItem.TransactionID  equals eTransaction.ID
            select new ItemJoinedToTrans{eItem=eItem, eTransaction=eTransaction};
    
    query = AppendWhereClauses(query, aTransPred);

    return (
        from eComposite in query 
        orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eItem.ID ascending 
        select eComposite.eItem).ToList();
}
IList<dal.DltAlloc>     GetDltAllocDAOsForTransactions(     dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
    var query = 
            from eDltAlloc      in ctx.DltAllocs
            join eItem          in ctx.Items        on eDltAlloc.ItemID     equals eItem.ID
            join eTransaction   in ctx.Transactions on eItem.TransactionID  equals eTransaction.ID
            select new DltAllocJoinedToItemJoinedToTrans{eDltAlloc=eDltAlloc, eItem=eItem, eTransaction=eTransaction};
    
    query = AppendWhereClauses(query, aTransPred);

    return (
        from eComposite in query 
        orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eItem.ID ascending, eComposite.eDltAlloc.ParticipantID ascending
        select eComposite.eDltAlloc).ToList();
    
}
IList<dal.Transaction>  GetTransactionDAOsForTransactions(  dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
    var query = from dal.Transaction eTransaction in ctx.Transactions select new TransactionWrapper{eTransaction=eTransaction};
    
    query = AppendWhereClauses(query, aTransPred);

    return (from eWrapper in query orderby eWrapper.eTransaction.Instant descending, eWrapper.eTransaction.ID descending select eWrapper.eTransaction).ToList();
}

Notice that the structure of each is similar to the GetStates() and GetCities() example functions presented earlier for LINQ.  One difference however is that with the LINQ to SQL provider, the 4 child entity LINQ statements must specify the joins necessary to get to the Transaction Entity..

14.5.2 ..In Entity Framework

Here are the five queries for Entity Framework:

CodeWindow 051
IList<dal.WashDlt>      GetWashDltDAOsForTransactions(      dal.LekEntities ctx, DTO.TransPred aTransPred)
{
    var query = 
            from eWashDlt       in ctx.WashDlt
            select new WashDltJoinedToTrans{eWashDlt=eWashDlt, eTransaction=eWashDlt.Transaction};
    
    query = AppendWhereClauses(query, aTransPred);
    
    return (
        from eComposite in query 
        orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eWashDlt.Participant.ID ascending 
        select eComposite.eWashDlt).ToList();
}
IList<dal.NWAccntDlt>   GetNWAccntDltDAOsForTransactions(   dal.LekEntities ctx, DTO.TransPred aTransPred)
{
    var query = 
            from eNWAccntDlt    in ctx.NWAccntDlt
            select new NWAccntDltJoinedToTrans{eNWAccntDlt=eNWAccntDlt, eTransaction=eNWAccntDlt.Transaction};
    
    query = AppendWhereClauses(query, aTransPred);
    
    return (
        from eComposite in query 
        orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eNWAccntDlt.NWAccnt.ID ascending
        select eComposite.eNWAccntDlt).ToList();
}
IList<dal.Item>         GetItemDAOsForTransactions(         dal.LekEntities ctx, DTO.TransPred aTransPred)
{
    var query = 
            from eItem          in ctx.Item
            select new ItemJoinedToTrans{eItem=eItem, eTransaction=eItem.Transaction};
    
    query = AppendWhereClauses(query, aTransPred);

    return (
        from eComposite in query 
        orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eItem.ID ascending 
        select eComposite.eItem).ToList();
}
IList<dal.DltAlloc>     GetDltAllocDAOsForTransactions(     dal.LekEntities ctx, DTO.TransPred aTransPred)
{
    var query = 
            from eDltAlloc      in ctx.DltAlloc
            select new DltAllocJoinedToItemJoinedToTrans{eDltAlloc=eDltAlloc, eItem=eDltAlloc.Item, eTransaction=eDltAlloc.Item.Transaction};
    
    query = AppendWhereClauses(query, aTransPred);

    return (
        from eComposite in query 
        orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eItem.ID ascending, eComposite.eDltAlloc.Participant.ID ascending
        select eComposite.eDltAlloc).ToList();
    
}
IList<dal.Transaction>  GetTransactionDAOsForTransactions(  dal.LekEntities ctx, DTO.TransPred aTransPred)
{
    var query = from dal.Transaction eTransaction in ctx.Transaction select new TransactionWrapper{eTransaction=eTransaction};
    
    query = AppendWhereClauses(query, aTransPred);

    return (from eWrapper in query orderby eWrapper.eTransaction.Instant descending, eWrapper.eTransaction.ID descending select eWrapper.eTransaction).ToList();
}

Notice that the structure of each is similar to the GetStates() and GetCities() example functions presented earlier for LINQ.  Unlike with the LINQ to SQL version, join clauses are not required.  They can be specified, but doing so causes less efficient SQL to be generated with the version of Entity Framework that comes with .NET 3.5 SP1.

14.5.3 ..In NHibernate

Here are the five queries for NHibernate:

CodeWindow 052
IList<dal.WashDlt>      GetWashDltDAOsForTransactions(      nh.ISession ctx, DTO.TransPred aTransPred)
{
    dal.Transaction oTransactionAlias   =null; //must match name of local variable in AddTransactionCriteria
    nh.ICriteria aCriteria = ctx.CreateCriteria(typeof(dal.WashDlt));
    aCriteria.CreateAlias<dal.WashDlt>(o=>o.TheTransaction, ()=>oTransactionAlias);
    AddTransactionCriteria(aCriteria, aTransPred, /*bIncludeSort=*/true);
    aCriteria.AddOrder<dal.WashDlt>(o=>o.TheParticipant.ID, nh.Criterion.Order.Asc);
    return aCriteria.List<dal.WashDlt>();
}
IList<dal.NWAccntDlt>   GetNWAccntDltDAOsForTransactions(   nh.ISession ctx, DTO.TransPred aTransPred)
{
    dal.Transaction oTransactionAlias   =null; //must match name of local variable in AddTransactionCriteria
    nh.ICriteria aCriteria = ctx.CreateCriteria(typeof(dal.NWAccntDlt));
    aCriteria.CreateAlias<dal.NWAccntDlt>(o=>o.TheTransaction, ()=>oTransactionAlias);
    AddTransactionCriteria(aCriteria, aTransPred, /*bIncludeSort=*/true);
    aCriteria.AddOrder<dal.NWAccntDlt>(o=>o.TheNWAccnt.ID, nh.Criterion.Order.Asc);
    return aCriteria.List<dal.NWAccntDlt>();
}
IList<dal.Item>         GetItemDAOsForTransactions( nh.ISession ctx, DTO.TransPred aTransPred)
{
    dal.Transaction oTransactionAlias   =null; //must match name of local variable in AddTransactionCriteria
    nh.ICriteria aCriteria = ctx.CreateCriteria(typeof(dal.Item));
    aCriteria.CreateAlias<dal.Item>(o=>o.TheTransaction, ()=>oTransactionAlias);
    AddTransactionCriteria(aCriteria, aTransPred, /*bIncludeSort=*/true);
    aCriteria.AddOrder<dal.Item>(o=>o.ID, nh.Criterion.Order.Asc);
    return aCriteria.List<dal.Item>();
}
IList<dal.DltAlloc>     GetDltAllocDAOsForTransactions(     nh.ISession ctx, DTO.TransPred aTransPred)
{
    dal.Transaction oTransactionAlias   =null;//must match name of local variable in AddTransactionCriteria
    nh.ICriteria aCriteria = ctx.CreateCriteria(typeof(dal.DltAlloc));
    
    //This alias is required to be setup, even if it isn't used explicitly
    dal.Item        oItemAlias          =null;
    aCriteria   = aCriteria .CreateAlias<dal.DltAlloc  >( o=>o.TheItem                ,()=>oItemAlias         );
    
    //either of these will work
    aCriteria   = aCriteria .CreateAlias<dal.DltAlloc  >( o=>o.TheItem.TheTransaction ,()=>oTransactionAlias  );//don't use the alias
//      aCriteria   = aCriteria .CreateAlias(                ()=>oItemAlias.TheTransaction,()=>oTransactionAlias  );//DO use the alias
    
    AddTransactionCriteria(aCriteria, aTransPred, /*bIncludeSort=*/true);
    aCriteria.AddOrder<dal.DltAlloc>(o=>o.TheItem.ID        , nh.Criterion.Order.Asc);
    aCriteria.AddOrder<dal.DltAlloc>(o=>o.TheParticipant.ID , nh.Criterion.Order.Asc);
    return aCriteria.List<dal.DltAlloc>();
}
IList<dal.Transaction>  GetTransactionDAOsForTransactions(  nh.ISession ctx, DTO.TransPred aTransPred)
{
    dal.Transaction oTransactionAlias   =null;//must match name of local variable in AddTransactionCriteria
    nh.Criterion.DetachedCriteria aDetCriteria = nhl.DetachedCriteria<dal.Transaction>.Create(()=>oTransactionAlias);
    nh.ICriteria aCriteria = aDetCriteria.GetExecutableCriteria(ctx);
    AddTransactionCriteria(aCriteria, aTransPred, /*bIncludeSort=*/true);
    return aCriteria.List<dal.Transaction>();
}

Notice that the structure of each of the queries returning child objects is similar to the GetCities() query presented earlier for NHibernate.  Notice that the structure of GetTransactionDAOsForTransactions() is very similar to the GetStates() query presented earlier for NHibernate.

14.6 SQL Generated for One of the Queries for Building a Collection of Transactions

Contents at a Glance   Detailed Contents

Out of the five queries, GetDltAllocDAOsForTransaactions() is most complex, since it must traverse the most child to parent associations.  This section will show the SQL generated by each of the ORMs for this query.

14.6.1 ..In LINQ to SQL

The SQL generated by LINQ to SQL couldn't be much simpler:

CodeWindow 053
SELECT 
    [t0].[ID], 
    [t0].[ItemID], 
    [t0].[ParticipantID], 
    [t0].[Amount]
FROM 
    [dbo].[DltAlloc] AS [t0]
    INNER JOIN [dbo].[Item] AS [t1] ON [t0].[ItemID] = [t1].[ID]
    INNER JOIN [dbo].[Transaction] AS [t2] ON [t1].[TransactionID] = [t2].[ID]
WHERE 
    (NOT ([t2].[PostID] IS NOT NULL)) OR ([t2].[PostID] >= @p0)
ORDER BY 
    [t2].[Instant] DESC, 
    [t2].[ID] DESC, 
    [t1].[ID], 
    [t0].[ParticipantID]

14.6.2 ..In Entity Framework

The SQL generated by Entity Framework is more complex:

CodeWindow 054
SELECT 
    [Project1].[C1] AS [C1], 
    [Project1].[ID] AS [ID], 
    [Project1].[Amount] AS [Amount], 
    [Project1].[ItemID] AS [ItemID], 
    [Project1].[ParticipantID] AS [ParticipantID]
FROM (  SELECT 
        [Extent1].[ID] AS [ID], 
        [Extent1].[ItemID] AS [ItemID], 
        [Extent1].[ParticipantID] AS [ParticipantID], 
        [Extent1].[Amount] AS [Amount], 
        [Extent2].[ID] AS [ID1], 
        [Extent3].[ID] AS [ID2], 
        [Extent3].[Instant] AS [Instant], 
        1 AS [C1]
    FROM    
        [dbo].[DltAlloc] AS [Extent1]
        LEFT OUTER JOIN [dbo].[Item] AS [Extent2] ON [Extent1].[ItemID] = [Extent2].[ID]
        LEFT OUTER JOIN [dbo].[Transaction] AS [Extent3] ON [Extent2].[TransactionID] = [Extent3].[ID]
        LEFT OUTER JOIN [dbo].[Post] AS [Extent4] ON [Extent3].[PostID] = [Extent4].[ID]
    WHERE 
        ([Extent4].[ID] IS NULL) OR ([Extent3].[PostID] >= @p__linq__9)
)  AS [Project1]
ORDER BY 
    [Project1].[Instant] DESC, 
    [Project1].[ID2] DESC, 
    [Project1].[ID1] ASC, 
    [Project1].[ParticipantID] ASC

I am not sure why the Entity Framework uses left outer joins when inner joins will due.  This query is significantly slower than the LINQ to SQL version on my machine when executed against a database with a few thousand records in it (yes, I really do use this system).  If the LINQ statement is written to contain joins like the LINQ to SQL version is then the generated SQL is even more complex and even slower.  I suspect that the complexity of the generated SQL has something to do with an attempt to create SQL that will work on a variety of database servers.  Or maybe the complex query is better at handling certain error conditions.  I really can't say.   I hope that the version of Entity Framework due out with .NET 4.0 will generate simpler and faster SQL for this sort of query.

14.6.3 ..In NHibernate

The SQL generated by NHibernate is nearly as simple as that generated by LINQ to SQL:

CodeWindow 055
SELECT 
    this_.ID as ID1_2_, 
    this_.ItemID as ItemID1_2_, 
    this_.ParticipantID as Particip3_1_2_, 
    this_.Amount as Amount1_2_, 
    oitemalias1_.ID as ID7_0_, 
    oitemalias1_.TransactionID as Transact2_7_0_, 
    oitemalias1_.DltAllocAccntID as DltAlloc3_7_0_, 
    oitemalias1_.Descrip as Descrip7_0_, 
    otransacti2_.ID as ID8_1_, 
    otransacti2_.timestamp as timestamp8_1_, 
    otransacti2_.ParticipantID as Particip3_8_1_, 
    otransacti2_.PostID as PostID8_1_, 
    otransacti2_.Descrip as Descrip8_1_, 
    otransacti2_.Instant as Instant8_1_ 
FROM 
    DltAlloc this_ 
    inner join Item oitemalias1_ on this_.ItemID=oitemalias1_.ID 
    inner join [Transaction] otransacti2_ on oitemalias1_.TransactionID=otransacti2_.ID 
WHERE 
    (otransacti2_.PostID is null or otransacti2_.PostID >= @p0) 
ORDER BY 
    otransacti2_.Instant desc, 
    otransacti2_.ID desc, 
    this_.ItemID asc, 
    this_.ParticipantID asc

However, it does unnecessarily include fields for Item and Transaction entities, which were not requested in the NHibernate Criteria query.

15 Using the ORM Frameworks for a Complex Data Write Operation

Contents at a Glance   Detailed Contents

To save a new Transaction or to modify an existing Transaction, a LEK client calls the SaveTransaction() function declared on the ServiceInterface.IService interface:

CodeWindow 056
[sm.OperationContract]
[sm.FaultContract(typeof(DTO.ConcurrencyFault))]
int   SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance);

This function is implemented by each of the three Service classes (one for each ORM).  For each of the ORM's the function deals with three distinct entity types:

  • DTO.Transaction: for transferring data between tiers
  • DominUtil.Transaction: for enforcing the business rules of a Transaction
  • Dal*.Transaction (ORM class): for packaging up Transaction data to be sent to the database, or Transaction data read from the database.

DomainUtil.Transaction is in a namespace that is accessible to both client and server, and is used to validate Transaction data on both the client and the server.  The client creates a DomainUtil.Transaction object from the data collected by the UI.  The client will then transform this into a DTO.Transaction before sending it to the server.  DTO.Transaction objects are optimized for data transmission.  They contain no logic, and only the data essential to describe a Transaction to the server (account IDs are included but not account names.) Once the server receives the DTO.Transaction, it will transform it back into a DomainUtil.Transaction, possibly re-validate the data, and then transform it into a Dal*.Transaction (an ORM entity object) to save it to the database.

Saving a new Transaction to the database, or an existing Transaction with modified child records, requires more than just a single ORM entity object.  NWAccntDlts, WashDlts, Items, and DltAllocs will need to be saved as well.  If the Transaction is new this is a simple matter: all the child entities will need to be added.  If the Transaction is an edit to a Transaction already in the database then saving has the potential to be much more complex.  In that case the child entities could be new, edited, or unchanged.  Also, in that case, the Transaction entity would need to carry an indication of child entities that were part of it but no longer are - deleted entities.

The Transaction ORM class for each ORM includes members for the child entity objects. These child objects, in coordination with an ORM context, keep track of whether they are new, modified, unchanged, or deleted.  Saving an ORM Transaction object complete with child objects in any of these change states is easy if all the additions, edits and deletions occurred during the lifetime of an existing ORM context, and if that ORM context was used to retrieve the data prior to making the changes.  In that case you just tell the ORM context to save the entity and it handles all of the details.  Context lifetimes however are supposed to be short, and in a n-tier application entity objects typically pick up their changes on the client were they may be for any amount of time (waiting on the user).  Also, when we don't particularly like the public interface exposed by the ORM classes we may not want the client to even have the ORM entity objects. 

In LEK the client deals with entity objects made specifically for the client - without any dependency on an ORM (the classes are in the DomainUtil namespace).  Furthermore when it sends data to the server it sends it as objects specifically made for transferring data (the classes are in the DTO namespace).  For the server to properly deal with entity data like that it must simulate on the server what happened on the client.  One way to do this would be for the server to retrieve a fresh copy of all the Transaction data (as ORM entities) from the database and then "replay" what happened on the client.  Of course, to do this, the server would have to receive data transfer objects that included state tracking information (what's new, what's changed, what's deleted).  Also, timestamps or row versions would need to be employed so that the server could tell if the database data changed since the snapshot was formed and sent to the client. Having all this information at hand the server would walk the object graph of both the ORM entities fresh from the database and the entities received from the client.  It would walk both these object graphs in a coordinated manner, updating the ORM entities based on the entities received from the client.  It would then submit the modified Transaction ORM entity (with child entities) to the ORM context just used to retrieve them, which would properly handle all additions, updates, and deletions.

The main reason this approach was not taken in LEK was because I don't want the server to have to query for each of the objects to "replay" the changes on.  This would take time and server resources, and if I can get something equally functional that is more efficient (I think I did), that would be preferable.

So, if we already have all the data we need from the client (including what is new and what is modified), why do we need to query it from the database?   Well, the short answer is that you don't, or at least you shouldn't.  In each ORM updating the database from an object that is created, rather than recently queried requires that the created object be "attached" to the context.  This is straight forward in NHibernate, a bit difficult in LINQ to SQL (I think each child object must be attached individually), but nearly impossible in the version of Entity Framework that comes with .NET 3.5 SP1. The problems with doing that in Entity Framework have to do with what are considered to be the valid state transitions for entities.  Changing the state of a child entity from "unchanged" (the state it gets when attached) to "added" is not allowed. (Supposedly Entity Framework in .NET 4.0 won't have this problem.)

The problems some of the ORMs have with attaching are mostly with attaching a mixture of new and modified child entities of a modified parent entity. Adding an object graph in which everything is new is not a problem in any of the ORMs.  Attaching a object graph in which the root is modified and all of the child objects are new is easy in NHibernate and LINQ to SQL, but I couldn't get it to work in Entity Framework.  (Accepting suggestions!)

15.1 The Strategy

Contents at a Glance   Detailed Contents

For a new Transaction LEK simply creates the ORM entity objects complete with all child entity collections, and then hands this over to the ORM to insert.  

The strategy that I decided to go with in LEK for updating modified Transactions is to:

  1. Check the row version for the Transaction to ensure it hasn't changed since the snapshot was sent to the client.
  2. Delete all the child entities using an (efficient) stored procedure.
  3. Create and attach a Transaction entity in LINQ to SQL and NHibernate.  Query for and then update a Transaction entity in Entity Framework
  4. Add all the child entities as new objects.
  5. Hand the whole resulting object graph to the ORM for saving into the database.

If most Transactions that are modified only have a handful of Items, DltAllocs, NWAccntDlts, and WashDlts then this strategy will work fine. If a Transaction has dozens of child entities, and the update of the Transaction is simply to change one of them, then this is going to cause a lot of extra processing.  The pay off in terms of code simplicity is substantial however.   I think this is a reasonable approach if most Transactions don't have to be edited after they are added.  If that is not the case, then it would probably be worthwhile to make a new procedure specifically for updates.  This procedure would first walk the object graph of the data transfer entity  from the bottom (children) to the top (parent) deleting deleted objects, and then from the top down adding new objects and updating modified objects. At each of these deletes, additions, and updates an ORM entity object would be created from the data transfer object and then passed to the ORM context as a delete, addition, or update.  Updates and deletes would first require attaching the object to the ORM context.

15.2 About the Presentation of the Code in This Section

Contents at a Glance   Detailed Contents

If you read the previous section then you should have a pretty good idea about how LEK handles saving new and updated Transactions.  Hopefully, that means I can present the code for saving and updating Transactions from the bottom up rather than the top down.  I think probably the top down approach is more typical: the top level function is described first and then a second pass is made describing the functions that it calls and then a third pass is made describing the low-level functions that are called by the functions that the top level function calls.   That approach has always bothered me because I hate seeing something used before I know what that something is.    The sections that follow will describe the server-side code in LEK for saving and updating a Transaction entity and its child entities. The lowest level functions will be described first and then the functions that call those functions, and then the functions that call those and so on until we get to the  SaveTransaction() function itself.  I haven't shown ALL the dependant code.  You will need to download the source to see the ORM classes (Dal* namespace), data transfer classes (DTO namespace), and business object classes (DomainUtil namespace). 

15.3 Function Dependency Tree for SaveTransaction

Contents at a Glance   Detailed Contents

Here is a dependency tree of all the functions that will be described: (Note that the "bottom" - as in the code that everything else relies on - is actually at the top.)

SaveTransFunctions.GIF

15.4 Stored Procedures

Contents at a Glance   Detailed Contents

In addition to typical ORM operations, LEK uses two stored procedures when it updates a Transaction.  Both are related to the way that concurrency is addressed.  Rather than using a timestamp or row version for each entity that makes up a Transaction, LEK only uses timestamp on the Transaction entity.   Anytime any change is made to a Transaction, even if the Transaction entity itself is unchanged and only a single descendant entity is modified (like a DltAlloc), LEK updates the Transaction entity in the database.   A timestamp field is setup in the database in the Transaction table, so the database will then update this timestamp field.  The Transaction timestamp field will thus be a stand-in for the version of the Transaction as a whole. 

15.4.1 GetTransactionTimestamp

Contents at a Glance   Detailed Contents

LEK updates a Transaction without first retrieving the Transaction object to be updated.  It does however need to at least retrieve the timestamp field for the Transaction being updated.  This is accomplished by calling the GetTransactionTimestamp stored procedure:

CodeWindow 057
CREATE PROCEDURE [dbo].[GetTransactionTimestamp] 
    @ID int,                 
    @Timestamp timestamp OUTPUT 

AS 
SET @Timestamp = (
    SELECT timestamp FROM dbo.[Transaction] 
    WHERE         ID = @ID        
)

15.4.2 DeleteTransactionChildRecords

Contents at a Glance   Detailed Contents

Probably the typical way child objects are deleted using an ORM is to query the ORM for the full object graph of the object being deleted, delete the child objects from the graph, and then submit it back to the ORM context.  The ORM will then delete each database entity for which the corresponding ORM entity was deleted from the graph, so long as an defined concurrency related fields don't indicate that the ORM had a stale version of the object.  In LEK however, since the timestamp field in the Transaction entity will be used to track the version of the Transaction as a whole, there is no need to perform a concurrency check on each of the child entities.  We can instead just delete them all in one shot - so long as we don't mind recreating the ones that didn't have to be deleted in the first place.

The DeleteTransactionChildRecords stored procedure does the job:

CodeWindow 058
CREATE PROCEDURE [dbo].[DeleteTransactionChildRecords]
    @TransactionID int 
AS
BEGIN
--  SET NOCOUNT ON;

    DELETE DltAlloc FROM DltAlloc INNER JOIN Item ON DltAlloc.ItemID = Item.ID  WHERE Item.TransactionID        = @TransactionID
    DELETE          FROM WashDlt                                                WHERE WashDlt.TransactionID     = @TransactionID
    DELETE          FROM NWAccntDlt                                             WHERE NWAccntDlt.TransactionID  = @TransactionID
    DELETE          FROM Item                                                   WHERE Item.TransactionID        = @TransactionID
END

15.5 Supporting Functions Unique to Each ORM

In a sense, this section is about code to make up for deficiencies in an ORM.   The top level functions, presented later, are very similar for each ORM.  The reason that they can be is because most the differences are accounted for in the functions described in this section.

15.5.1 LINQ to SQL

Contents at a Glance   Detailed Contents

LINQ to SQL is Microsoft's first pass at a production ready ORM.  They didn't delve to deeply into any hard issues or attempt to achieve something that would work on every database.   However, for doing simple things, it is very easy to use.  When it comes to updating the database, the other ORMs don't do anything in a manner simpler than the way LINQ to SQL does it, so there is no code to present for LINQ to SQL in this section. 

15.5.2 Entity Framework

Contents at a Glance   Detailed Contents

Entity Framework is more capable than LINQ to SQL, but for simple things, it is harder to use.  The version of Entity Framework due out with .NET 4 will supposedly make Entity Framework as easy to use as LINQ to SQL, while maintaining all of its capabilities.

15.5.2.1 CreateEntityKey
Contents at a Glance   Detailed Contents

In Entity Framework a key can be much more complicated than a simple integer.  It can be a collection of primitives of different types.  The CreateEntityKey() function is used to create the only kind of key used in LEK: a non-composite key based on a single integer:

CodeWindow 059
public static s.Data.EntityKey CreateEntityKey(int iID, string strQualifiedEntitySetName)
{
    IEnumerable<KeyValuePair<string, object>> entityKeyValues =
        new KeyValuePair<string, object>[] {
            new KeyValuePair<string, object>("ID", iID) };

    return  new s.Data.EntityKey(strQualifiedEntitySetName, entityKeyValues);
}
15.5.2.2 GetFK
Contents at a Glance   Detailed Contents

Entity Framework doesn't have simple public members for foreign keys in entities.  As far as Entity Framework is concerned, integer based foreign keys are "relational database think" and not true "object-think".   Thankfully the folks at Microsoft have reconsidered this, and simple foreign keys will be available in the .NET 4.0 version of Entity Framework.  Until then we must do something like the following to get an integer based foreign key out of an Entity Framework entity:

CodeWindow 060
public static int? GetFK<T>(/*this */s.Data.Objects.DataClasses.EntityReference<T> refr) where T : class, s.Data.Objects.DataClasses.IEntityWithRelationships 
{
    if(refr == null)
        throw new s.Exception("ServiceEF:21");
    if(refr.EntityKey == null)
    {
        if(refr.Value!=null)
            throw new s.Exception("ServiceEF:27");
        return null;
    }

//  can be null if not yet loaded even if FK is not null
//  if(refr.Value == null)
//      throw new s.Exception("ServiceEF:31");

    if(refr.EntityKey.EntityKeyValues ==null)
        throw new s.Exception("ServiceEF:33");
    if(refr.EntityKey.EntityKeyValues.Length !=1)
        throw new s.Exception("ServiceEF:35");
    return (int)refr.EntityKey.EntityKeyValues.First().Value;
}
15.5.2.3 EnsureFK
Contents at a Glance   Detailed Contents

If getting an integer-based foreign key was difficult then you know it is going to be hard to set one.  I can't wait for the .NET 4.0 version.  Until then, this seems to work:

CodeWindow 061
public static void EnsureFK<T>(/*this */s.Data.Objects.DataClasses.EntityReference<T> refr, s.Data.Objects.ObjectContext ctx, int? iID) where T: class, s.Data.Objects.DataClasses.IEntityWithRelationships
{
    int? iCurrentID = Helper.GetFK(refr);
    if(iCurrentID == iID)
        return;
    if(iCurrentID != null)
        refr.Value = null;
    else
    {
        if(refr.Value != null)
            throw new s.Exception();
    }
    if(iID != null)
        refr.EntityKey = new System.Data.EntityKey(ctx.GetType().Name + "." + typeof(T).Name, /*typeof(T).Name +*/ "ID", iID.Value);
}
15.5.2.4 SetAllPropsAsModified
Contents at a Glance   Detailed Contents

In LINQ to SQL, the function used to attach freshly created ORM entity to the context has a parameter to indicate whether or not the object should be considered modified.  NHibernate provides the function Update() to do the same thing.  With Entity Framework we must indicate each field that has updated data.  This is great if the entity object has 30 fields and only one is going to be updated.  For the simple case where we want them all updated however, the Enity Framework way is more complicated than it has to be.  SetAllPropsAsModified() provides the simple typical operation where we just want Entity Framework to consider nearly all the fields to contain modified data:

CodeWindow 062
public static void SetAllPropsAsModified(dal.LekEntities ctx, object entity)
{
    s.Data.Objects.ObjectStateEntry aObjectStateEntry = ctx.ObjectStateManager.GetObjectStateEntry(entity);
    s.Collections.ObjectModel.ReadOnlyCollection<s.Data.Common.FieldMetadata> collFieldMetadatas
     = aObjectStateEntry.CurrentValues.DataRecordInfo.FieldMetadata;
    foreach(var propertyName in collFieldMetadatas.Select(o => o.FieldType.Name))
    {
        if(propertyName == "ID")
            continue;
        if(propertyName == "timestamp")
            continue;
        aObjectStateEntry.SetModifiedProperty(propertyName);
    }
}

The strategy that employed this function for SaveTransaction() did not work so it wasn't actually used. See the Entity Framework version of the LLSaveTransaction function below, where the call to this function is commented out.

15.5.3 NHibernate

NHibernate is more powerful than Entity Framework and almost as easy to use as LINQ to SQL. There are two supporting functions that are unique to NHibernate: EnsureFKWithInteger() and EnsureFKWithEntity().

15.5.3.1 EnsureFKWithInteger
Contents at a Glance   Detailed Contents

Like Entity Framework, NHibernate doesn't typically use ORM classes in which the foreign keys are directly exposed.  Setting a foreign key however is much easier than it is in Entity Framework.   Here is the function that does it:

CodeWindow 063
public static void EnsureFKWithInteger<T>(s.Action<T> delSet, s.Action<T,int> delSetID,  s.Action<T,byte[]> delSetRowVersion, int? iID) where T: new()
{
//Example of what this function does:
//  if(!iID.HasValue)
//      return;
//  oDal.TheParticipant = new DalNH.Participant();
//  oDal.TheParticipant.ID = iID.Value;
//  oDal.TheParticipant.RowVersion      = new byte[]{0};//if ID is set, then this must be as well, even though it won't be used

    if(!iID.HasValue)
        return;
    T parent = new T();
    delSet(parent);
    delSetID(parent, iID.Value);
    delSetRowVersion(parent, new byte[]{0});
}
15.5.3.2 EnsureFKWithEntity
Contents at a Glance   Detailed Contents

In both Entity Framework and LINQ to SQL setting up a new entity that is mapped as a child to another entity only required that the new child entity be added to the child collection in the parent entity.  It was not also necessary to setup the foreign key in the new child to the parent.  With NHibernate you do.  The function couldn't be simpler though.  I only made it a function to make it easier to contrast against EnsureFKWithInteger().  Here is EnsureFKWithEntity(): 

CodeWindow 064
public static void EnsureFKWithEntity<T>(s.Action<T> delSet, T parent)
{
//Example of what this function does:
//  oDal.TheParticipant = parent;

    delSet(parent);
}

15.6 SaveTransaction and the Supporting Functions With an Implementation in Each ORM

Contents at a Glance   Detailed Contents

All of the functions shown in this section have a distinct implementation for at least one of the ORMs.  The implementations of each function are shown in consecutive code windows with line numbers.  The line numbers are not the actual line numbers from the source code, but rather, are line numbers scoped to each code window (they start over in each code window).  The lines of each function are vertically spaced such that lines that do the same thing in multiple versions have the same line number in each code window.  This should make it possible for you to compare the versions of a function on a line-by-line basis.  If the code windows for a function are shown with scroll bars you may want to scroll each code window such that they show the same first line.

15.6.1 CreateORMRowVersion

Contents at a Glance   Detailed Contents

LINQ to SQL uses System.Data.Linq.Binary types to represent SQL Server timestamp fields.  Entity Framework and NHibernate use Byte[] (Byte arrays).  LEK uses a custom created type GlobalType.RowVersion to carry timestamp field data in the client and across the client-server interface.  The CreateORMRowVersion() function creates the ORM specific type from the GlobalType.RowVersion type.

15.6.1.1 LINQ to SQL
CodeWindow 065
   1:  public static s.Data.Linq.Binary CreateORMRowVersion(gt.RowVersion aRowVersion)
   2:  {
   3:      if(aRowVersion==null)
   4:          return null;
   5:      return new System.Data.Linq.Binary(aRowVersion.GetCopyOfData());
   6:  }
15.6.1.2 Entity Framework and NHibernate
CodeWindow 066
   1:  public static Byte[] CreateORMRowVersion(gt.RowVersion aRowVersion)
   2:  {
   3:      if(aRowVersion==null)
   4:          return null;
   5:      return aRowVersion.GetCopyOfData();
   6:  }

15.6.2 SPs: GetTransactionTimestamp and DeleteTransactionChildRecords

Contents at a Glance   Detailed Contents

GetTransactionTimestamp() and DeleteTransactionChildRecords() are both simple wrappers for like stored procedures with the same name. (See the Stored Procedure section above for a discussion of the need for these two functions.

15.6.2.1 LINQ to SQL

The wizard that produces the default set of ORM classes for LINQ to SQL can also create wrappers for stored procedures.  The two functions below are actually wrappers of the wrappers:

CodeWindow 067
   1:  gt.RowVersion GetTransactionTimestamp(int iTransactionID, dal.DataClassesDataContext ctx)
   2:  {
   3:      s.Data.Linq.Binary timestamp = null;
   4:      
   5:      
   6:      
   7:      
   8:   
   9:      
  10:   
  11:   
  12:      
  13:      
  14:      
  15:      
  16:      
  17:      int iRC = ctx.GetTransactionTimestamp(iTransactionID, ref timestamp);
  18:      if(iRC != 0)
  19:          throw new Exception();
  20:   
  21:      
  22:      return new gt.RowVersion(timestamp.ToArray());
  23:  }
  24:  void DeleteTransactionChildRecords(int iTransactionID, dal.DataClassesDataContext ctx)
  25:  {
  26:   
  27:      
  28:      
  29:   
  30:   
  31:      
  32:      
  33:      
  34:      ctx.DeleteTransactionChildRecords(iTransactionID);
  35:  }
15.6.2.2 Entity Framework

Wrapper functions for the stored procedures were created manually for Entity Framework:

CodeWindow 068A
   1:  gt.RowVersion GetTransactionTimestamp(int iTransactionID, dal.LekEntities ctx)
   2:  {
   3:      byte[] bytes = null;
   4:      s.Data.EntityClient.EntityConnection aEntityConnection = (s.Data.EntityClient.EntityConnection)ctx.Connection;
   5:      s.Data.Common.DbConnection conn = aEntityConnection.StoreConnection;
   6:      s.Data.IDbCommand cmd = conn.CreateCommand();
   7:   
   8:      cmd.CommandText = "GetTransactionTimestamp";
   9:      cmd.CommandType = System.Data.CommandType.StoredProcedure;
  10:      
  11:      cmd.Parameters.Add(new s.Data.SqlClient.SqlParameter("@ID", iTransactionID));
  12:      
  13:      s.Data.SqlClient.SqlParameter output = new System.Data.SqlClient.SqlParameter("@Timestamp", s.Data.SqlDbType.Timestamp);
  14:      output.Direction = System.Data.ParameterDirection.Output;
  15:      cmd.Parameters.Add(output);
  16:      
  17:      int iRC = cmd.ExecuteNonQuery();
  18:      if(iRC!=-1)
  19:          throw new s.Exception();
  20:      
  21:      bytes= (byte[])output.Value;
  22:      return new gt.RowVersion(bytes);
  23:  }
  24:  void DeleteTransactionChildRecords(int iTransactionID, dal.LekEntities ctx)
  25:  {
  26:      s.Data.EntityClient.EntityConnection aEntityConnection = (s.Data.EntityClient.EntityConnection)ctx.Connection;
  27:      s.Data.Common.DbConnection conn = aEntityConnection.StoreConnection;
  28:      s.Data.IDbCommand cmd = conn.CreateCommand();
  29:   
  30:      cmd.CommandText = "DeleteTransactionChildRecords";
  31:      cmd.CommandType = System.Data.CommandType.StoredProcedure;
  32:      cmd.Parameters.Add(new s.Data.SqlClient.SqlParameter("@TransactionID", iTransactionID));
  33:   
  34:      cmd.ExecuteNonQuery();
  35:  }

Note that obtaining the connection object to create the command object required a bit of "digging" (lines 4, 5, 27, 28)

15.6.2.3 NHibernate

Wrapper functions for the stored procedures were also created manually for NHibernate, and is nearly identical to the one for Entity Framework:

CodeWindow 068B
   1:  gt.RowVersion GetTransactionTimestamp(int iTransactionID, nh.ISession ctx)
   2:  {
   3:      byte[] bytes= null;
   4:      
   5:      
   6:      s.Data.IDbCommand cmd = ctx.Connection.CreateCommand();
   7:      ctx.Transaction.Enlist(cmd);
   8:      cmd.CommandText = "GetTransactionTimestamp";
   9:      cmd.CommandType = System.Data.CommandType.StoredProcedure;
  10:      
  11:      cmd.Parameters.Add(new s.Data.SqlClient.SqlParameter("@ID", iTransactionID));
  12:      
  13:      s.Data.SqlClient.SqlParameter output = new System.Data.SqlClient.SqlParameter("@Timestamp", s.Data.SqlDbType.Timestamp);
  14:      output.Direction = System.Data.ParameterDirection.Output;
  15:      cmd.Parameters.Add(output);
  16:      
  17:      int iRC = cmd.ExecuteNonQuery();
  18:      if(iRC!=-1)
  19:          throw new s.Exception();
  20:      
  21:      bytes= (byte[])output.Value;
  22:      return new gt.RowVersion(bytes);
  23:  }
  24:  void DeleteTransactionChildRecords(int iTransactionID, nh.ISession ctx)
  25:  {
  26:      
  27:      
  28:      s.Data.IDbCommand cmd = ctx.Connection.CreateCommand();
  29:      ctx.Transaction.Enlist(cmd);
  30:      cmd.CommandText = "DeleteTransactionChildRecords";
  31:      cmd.CommandType = System.Data.CommandType.StoredProcedure;
  32:      cmd.Parameters.Add(new s.Data.SqlClient.SqlParameter("@TransactionID", iTransactionID));
  33:      
  34:      cmd.ExecuteNonQuery();
  35:  }

Note that creation of the command object in line 6 is easier than in was in Entity Framework.  Also note that, unlike with Entity Framework, it is possible to explicitly enlist the command into the transaction (line 7 and 29).

15.6.3 CreateTransactionReadyToAttach

Contents at a Glance   Detailed Contents

To submit changes to a database entity with an ORM entity that you didn't just query using an on hand ORM context you must "attach" it to the ORM context.  If you are creating the ORM entity from scratch (didn't query for it) then you must ensure the fields are properly initialized before you attach it.  The CreateTransactionReadyToAttach()  function does this.  Note that the version of the function for LINQ to SQL and NHibernate are identical except for the ORM type.  The version for Entity Framework requires the use of the previously described CreateEntityKey() helper function to properly setup the primary key (line 5).

15.6.3.1 LINQ to SQL
CodeWindow 069
   1:  dal.Transaction CreateTransactionReadyToAttach(int iTransactionID, gt.RowVersion aRowVersion)
   2:  {
   3:      dal.Transaction oDal = new DalLinqToSql.Transaction();
   4:      oDal.ID = iTransactionID;
   5:      
   6:      oDal.timestamp = Helper.CreateORMRowVersion(aRowVersion);
   7:      return oDal;
   8:  }
15.6.3.2 Entity Framework
CodeWindow 070
   1:  dal.Transaction CreateTransactionReadyToAttach(int iTransactionID, gt.RowVersion aRowVersion)
   2:  {
   3:      dal.Transaction oDal = new DalEF.Transaction();
   4:      oDal.ID = iTransactionID;
   5:      oDal.EntityKey = Helper.CreateEntityKey(iTransactionID,typeof(dal.LekEntities).Name + "." + typeof(dal.Transaction).Name);
   6:      oDal.timestamp = Helper.CreateORMRowVersion(aRowVersion);
   7:      return oDal;
   8:  }
15.6.3.3 NHibernate
CodeWindow 071
   1:  dal.Transaction CreateTransactionReadyToAttach(int iTransactionID, gt.RowVersion aRowVersion)
   2:  {
   3:      dal.Transaction oDal = new DalNH.Transaction();
   4:      oDal.ID = iTransactionID;
   5:      
   6:      oDal.timestamp = Helper.CreateORMRowVersion(aRowVersion);
   7:      return oDal;
   8:  }

15.6.4 TransactionRowVersionMatches

Contents at a Glance   Detailed Contents

The TransactionRowVersionMatches() function retrieves the data for the timestamp field in the database and compares it to a supplied value previously retrieved from the database.  The code is nearly identical for each ORM:

15.6.4.1 LINQ to SQL
CodeWindow 072
   1:  bool TransactionRowVersionMatches(int iTransactionID, dal.DataClassesDataContext ctx, gt.RowVersion aRowVersion)
   2:  {
   3:      gt.RowVersion oCurrentRowVersion = GetTransactionTimestamp(iTransactionID, ctx);
   4:      return gt.RowVersion.ValueEquals(oCurrentRowVersion, aRowVersion);
   5:  }
15.6.4.2 Entity Framework
CodeWindow 073
   1:  bool TransactionRowVersionMatches(int iTransactionID, dal.LekEntities ctx, gt.RowVersion aRowVersion)
   2:  {
   3:      gt.RowVersion oCurrentRowVersion = GetTransactionTimestamp(iTransactionID, ctx);
   4:      return gt.RowVersion.ValueEquals(oCurrentRowVersion, aRowVersion);
   5:  }
15.6.4.3 NHibernate
CodeWindow 074
   1:  bool TransactionRowVersionMatches(int iTransactionID, nh.ISession ctx, gt.RowVersion aRowVersion)
   2:  {
   3:      gt.RowVersion oCurrentRowVersion = GetTransactionTimestamp(iTransactionID, ctx);
   4:      return gt.RowVersion.ValueEquals(oCurrentRowVersion, aRowVersion);
   5:  }

15.6.5 SetDALObjectFromDomainObject

Contents at a Glance   Detailed Contents

SetDALObjectFromDomainObject() sets all the fields of an ORM Transaction entity (including child entity collections) from a business object Transaction entity (DomainUtil namespace).

15.6.5.1 LINQ to SQL
CodeWindow 075
   1:  void SetDALObjectFromDomainObject(du.ITransaction oDmn, dal.Transaction oDal, dal.DataClassesDataContext ctx)
   2:  {
   3:      oDal.Descrip = oDmn.Descrip;
   4:      oDal.Instant = oDmn.Instant;
   5:      oDal.ParticipantID = oDmn.ParticipantID;
   6:      foreach (DTO.Item aItem in oDmn.Items)
   7:      {
   8:          dal.Item aDALItem = new dal.Item();
   9:          aDALItem.Descrip = aItem.Descrip;
  10:          aDALItem.DltAllocAccntID = aItem.DltAllocAccntID;
  11:   
  12:          for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
  13:          {
  14:              int iParticipantAmount = aItem.ParticipantAmounts[iParticipant];
  15:              if (iParticipantAmount == 0)
  16:                  continue;
  17:              dal.DltAlloc aDALDltAlloc = new dal.DltAlloc();
  18:              aDALDltAlloc.Amount = iParticipantAmount;
  19:              int iParticipantID = oDmn.GetParticipantID(iParticipant);
  20:              aDALDltAlloc.ParticipantID = iParticipantID;
  21:              
  22:              aDALItem.DltAllocs.Add(aDALDltAlloc);
  23:          }
  24:          oDal.Items.Add(aDALItem);
  25:      }
  26:      foreach (DTO.NWAccntDlt aNWAccntDlt in oDmn.NWAccntDlts)
  27:      {
  28:          dal.NWAccntDlt aDALNWAccntDlt = new dal.NWAccntDlt();
  29:          aDALNWAccntDlt.Amount = aNWAccntDlt.Amount;
  30:          aDALNWAccntDlt.NWAccntID = aNWAccntDlt.NWAccntID;
  31:         
  32:          oDal.NWAccntDlts.Add(aDALNWAccntDlt);
  33:      }
  34:      for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
  35:      {
  36:          int iParticipantWashAmount = oDmn.GetWashAmount(iParticipant);
  37:          if (iParticipantWashAmount == 0)
  38:              continue;
  39:          dal.WashDlt aDALWashDlt = new dal.WashDlt();
  40:          aDALWashDlt.Amount = iParticipantWashAmount;
  41:          int iParticipantID = oDmn.GetParticipantID(iParticipant);
  42:          aDALWashDlt.ParticipantID = iParticipantID;
  43:         
  44:          oDal.WashDlts.Add(aDALWashDlt);
  45:      }
  46:  }

Note that in lines 5, 10, 20, 30, and 42 a foreign key is setup simply by setting the mapped foreign key integer field.

15.6.5.2 Entity Framework
CodeWindow 076
   1:  void SetDALObjectFromDomainObject(du.ITransaction oDmn, dal.Transaction oDal, dal.LekEntities ctx)
   2:  {
   3:      oDal.Descrip = oDmn.Descrip;
   4:      oDal.Instant = oDmn.Instant;
   5:      Helper.EnsureFK(oDal.ParticipantReference, ctx, oDmn.ParticipantID);
   6:      foreach (DTO.Item aItem in oDmn.Items)
   7:      {
   8:          dal.Item aDALItem = new dal.Item();
   9:          aDALItem.Descrip = aItem.Descrip;
  10:          Helper.EnsureFK(aDALItem.DltAllocAccntReference, ctx, aItem.DltAllocAccntID);
  11:   
  12:          for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
  13:          {
  14:              int iParticipantAmount = aItem.ParticipantAmounts[iParticipant];
  15:              if (iParticipantAmount == 0)
  16:                  continue;
  17:              dal.DltAlloc aDALDltAlloc = new dal.DltAlloc();
  18:              aDALDltAlloc.Amount = iParticipantAmount;
  19:              int iParticipantID = oDmn.GetParticipantID(iParticipant);
  20:              Helper.EnsureFK(aDALDltAlloc.ParticipantReference, ctx, iParticipantID);
  21:              
  22:              aDALItem.DltAlloc.Add(aDALDltAlloc);
  23:          }
  24:          oDal.Item.Add(aDALItem);
  25:      }
  26:      foreach (DTO.NWAccntDlt aNWAccntDlt in oDmn.NWAccntDlts)
  27:      {
  28:          dal.NWAccntDlt aDALNWAccntDlt = new dal.NWAccntDlt();
  29:          aDALNWAccntDlt.Amount = aNWAccntDlt.Amount;
  30:          Helper.EnsureFK(aDALNWAccntDlt.NWAccntReference, ctx, aNWAccntDlt.NWAccntID);
  31:          
  32:          oDal.NWAccntDlt.Add(aDALNWAccntDlt);
  33:      }
  34:      for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
  35:      {
  36:          int iParticipantWashAmount = oDmn.GetWashAmount(iParticipant);
  37:          if (iParticipantWashAmount == 0)
  38:              continue;
  39:          dal.WashDlt aDALWashDlt = new dal.WashDlt();
  40:          aDALWashDlt.Amount = iParticipantWashAmount;
  41:          int iParticipantID = oDmn.GetParticipantID(iParticipant);
  42:          Helper.EnsureFK(aDALWashDlt.ParticipantReference, ctx, iParticipantID);
  43:          
  44:          oDal.WashDlt.Add(aDALWashDlt);
  45:      }
  46:  }

Note that in lines 5, 10, 20, 30, and 42 setting up a foreign key requires use of the custom EnsureFK() function, discussed earlier.

15.6.5.3 NHibernate
CodeWindow 077
   1:  void SetDALObjectFromDomainObject(du.ITransaction oDmn, dal.Transaction oDal, nh.ISession ctx)
   2:  {
   3:      oDal.Descrip = oDmn.Descrip;
   4:      oDal.Instant = oDmn.Instant;
   5:      Helper.EnsureFKWithInteger<DalNH.Participant>(o=>oDal.TheParticipant=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, oDmn.ParticipantID);
   6:      foreach (DTO.Item aItem in oDmn.Items)
   7:      {
   8:          dal.Item aDALItem = new dal.Item();
   9:          aDALItem.Descrip = aItem.Descrip;
  10:          Helper.EnsureFKWithInteger<DalNH.DltAllocAccnt>(o=>aDALItem.TheDltAllocAccnt=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, aItem.DltAllocAccntID);
  11:          Helper.EnsureFKWithEntity<DalNH.Transaction  >(o=>aDALItem.TheTransaction  =o, oDal);
  12:          for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
  13:          {
  14:              int iParticipantAmount = aItem.ParticipantAmounts[iParticipant];
  15:              if (iParticipantAmount == 0)
  16:                  continue;
  17:              dal.DltAlloc aDALDltAlloc = new dal.DltAlloc();
  18:              aDALDltAlloc.Amount = iParticipantAmount;
  19:              int iParticipantID = oDmn.GetParticipantID(iParticipant);
  20:              Helper.EnsureFKWithInteger<DalNH.Participant    >(o=>aDALDltAlloc.TheParticipant=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, iParticipantID);
  21:              Helper.EnsureFKWithEntity<DalNH.Item        >(o=>aDALDltAlloc.TheItem       =o, aDALItem);
  22:              aDALItem.DltAllocs.Add(aDALDltAlloc);
  23:          }
  24:          oDal.Items.Add(aDALItem);
  25:      }
  26:      foreach (DTO.NWAccntDlt aNWAccntDlt in oDmn.NWAccntDlts)
  27:      {
  28:          dal.NWAccntDlt aDALNWAccntDlt = new dal.NWAccntDlt();
  29:          aDALNWAccntDlt.Amount = aNWAccntDlt.Amount;
  30:          Helper.EnsureFKWithInteger<DalNH.NWAccnt>(o=>aDALNWAccntDlt.TheNWAccnt=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, aNWAccntDlt.NWAccntID);
  31:          Helper.EnsureFKWithEntity<DalNH.Transaction  >(o=>aDALNWAccntDlt.TheTransaction  =o, oDal);
  32:          oDal.NWAccntDlts.Add(aDALNWAccntDlt);
  33:      }
  34:      for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
  35:      {
  36:          int iParticipantWashAmount = oDmn.GetWashAmount(iParticipant);
  37:          if (iParticipantWashAmount == 0)
  38:              continue;
  39:          dal.WashDlt aDALWashDlt = new dal.WashDlt();
  40:          aDALWashDlt.Amount = iParticipantWashAmount;
  41:          int iParticipantID = oDmn.GetParticipantID(iParticipant);
  42:          Helper.EnsureFKWithInteger<DalNH.Participant>(o=>aDALWashDlt.TheParticipant=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, iParticipantID);
  43:          Helper.EnsureFKWithEntity<DalNH.Transaction  >(o=>aDALWashDlt.TheTransaction  =o, oDal);
  44:          oDal.WashDlts.Add(aDALWashDlt);
  45:      }
  46:  }

Note that in lines 5, 10, 20, 30, and 42 setting up a foreign key requires use of the custom EnsureFKWithInteger() function, discussed earlier.  Also note that, unlike with the other two ORMs, foreign keys in child entities to parent entities that track the child entity as a child must also be setup.  This occurs in lines 11, 21, 31, and 43 using a call to EnsureFKWithEntity(), which does nothing more than set the entity member to the parent entity.

15.6.6 LLSaveTransaction

Contents at a Glance   Detailed Contents

LLSaveTransaction() ("Low Level Save Transaction") first performs an optional check that the supplied business object Transaction entity is valid. It then braches into one block if the Transaction is a modification of an existing Transaction and another block if the Transaction is new. 

For a modified Transaction the block (lines 9-40) will first check that the record in the database hasn't been updated since the snapshot was taken for the client.  If this check fails then an exception is thrown.  Next all of the child records in the database are deleted (line 13).  Finally an ORM Transaction entity is created or queried and then setup with data from the passed in business object Transaction entity (lines 14-40).  Even if the only changes are in child entity objects, the Transaction entity is still setup as containing modified data.  This will ensure that the timestamp field in the Transaction record can be used as the version for the Transaction as a whole. 

For a new Transaction there is no database data to check for concurrency and no child entities to delete.  The Transaction entity is simply created and setup and then the ORM context is informed about the new entity (lines 43-45).

The most significant difference between the versions is how a modified Transaction is setup and how it is "attached" to the context.  In the LINQ to SQL version the fields of the ORM entity are setup after attaching it to the context.  In the NHibernate version the fields are setup prior to attaching it.  I could not get either order to work in the Entity Framework version of the function, and fell back to querying for the object to set it up correctly.

15.6.6.1 LINQ to SQL (The Function)
CodeWindow 078
   1:  dal.Transaction LLSaveTransaction(du.ITransaction oDmn, bool bAssumeInBalance, dal.DataClassesDataContext ctx)
   2:  {
   3:      if (!bAssumeInBalance)
   4:      {
   5:          if (0 != oDmn.GetAmountNWDltsExceedAllocDltsBy())
   6:              throw new ArgumentException("transaction not in balance");
   7:      }
   8:      dal.Transaction oDAL = null;
   9:      if (oDmn.ID.HasValue)
  10:      {
  11:          if(!TransactionRowVersionMatches(oDmn.ID.Value, ctx, oDmn.TheRowVersion))
  12:              throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
  13:          DeleteTransactionChildRecords(oDmn.ID.Value, ctx);
  14:      
  15:          #region spacer
  16:          
  17:          
  18:          
  19:          
  20:          
  21:          
  22:          
  23:          
  24:          
  25:          
  26:          #endregion
  27:          
  28:      //ATTACH, THEN SET FIELDS
  29:   
  30:          
  31:   
  32:          oDAL = CreateTransactionReadyToAttach(oDmn.ID.Value, oDmn.TheRowVersion);
  33:          ctx.Transactions.Attach(oDAL, /*bAsModified=*/true);
  34:          SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
  35:          
  36:      
  37:      
  38:   
  39:      
  40:      }
  41:      else
  42:      {
  43:          oDAL = new dal.Transaction();
  44:          SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
  45:          ctx.Transactions.InsertOnSubmit(oDAL);
  46:      }
  47:   
  48:      return oDAL;
  49:  }
15.6.6.2 Entity Framework (The Function)
CodeWindow 079
   1:  dal.Transaction LLSaveTransaction(du.ITransaction oDmn, bool bAssumeInBalance, dal.LekEntities ctx)
   2:  {
   3:      if (!bAssumeInBalance)
   4:      {
   5:          if (0 != oDmn.GetAmountNWDltsExceedAllocDltsBy())
   6:              throw new ArgumentException("transaction not in balance");
   7:      }
   8:      dal.Transaction oDAL=null;
   9:      if (oDmn.ID.HasValue)
  10:      {
  11:          if(!TransactionRowVersionMatches(oDmn.ID.Value, ctx, oDmn.TheRowVersion))
  12:              throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
  13:          DeleteTransactionChildRecords(oDmn.ID.Value, ctx);
  14:   
  15:      //SET FIELDS, THEN ATTACH
  16:      //doesn't work: Call to Attach will cause framework to try to attach 
  17:      //all the child items as modified entities, 
  18:      //even though they are actually new entities
  19:      //Since all the (new, unsaved) child entities have an ID of 0,
  20:      //the framework will think that we are attempting to attach 
  21:      //multiple items all with an ID of "0" (which it won't allow).
  22:      //rather than adding the children as new entities.
  23:      //  oDAL = CreateTransactionReadyToAttach(oDmn.ID.Value, oDmn.TheRowVersion);
  24:      //  SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
  25:      //  ctx.Attach(oDAL);
  26:      //  Helper.SetAllPropsAsModified(ctx, oDAL);
  27:   
  28:      //ATTACH, THEN SET FIELDS
  29:      //doesn't work: when we finally get to save changes, it will use ParticipantID in a 
  30:      //concurrency check, which will fail since at the moment the object was attached
  31:      //the ParticipantID was set to null.  How do I disable the concurrency check on ParticipantID?
  32:      //  oDAL = CreateTransactionReadyToAttach(oDmn.ID.Value, oDmn.TheRowVersion);
  33:      //  ctx.Attach(oDAL);
  34:      //  SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
  35:      //  Helper.SetAllPropsAsModified(ctx, oDAL);
  36:   
  37:      //GIVE UP, QUERY FOR IT TO ATTACH IT.
  38:          oDAL = ctx.Transaction.Where(o=>o.ID == oDmn.ID.Value).ToList().FirstOrDefault();//The "ToList" part is required against SQLServer 2000, but not SQLSever 2005.
  39:          SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
  40:      }
  41:      else
  42:      {
  43:          oDAL = new dal.Transaction();
  44:          SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
  45:          ctx.AddToTransaction(oDAL);
  46:      }
  47:   
  48:      return oDAL;
  49:  }
15.6.6.3 NHibernate (The Function)
CodeWindow 080
   1:  dal.Transaction LLSaveTransaction(du.ITransaction oDmn, bool bAssumeInBalance, nh.ISession ctx)
   2:  {
   3:      if (!bAssumeInBalance)
   4:      {
   5:          if (0 != oDmn.GetAmountNWDltsExceedAllocDltsBy())
   6:              throw new ArgumentException("transaction not in balance");
   7:      }
   8:      dal.Transaction oDAL = null;
   9:      if (oDmn.ID.HasValue)
  10:      {
  11:          if(!TransactionRowVersionMatches(oDmn.ID.Value, ctx, oDmn.TheRowVersion))
  12:              throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
  13:          DeleteTransactionChildRecords(oDmn.ID.Value, ctx);
  14:      
  15:          //SET FIELDS THEN ATTACH
  16:          
  17:          
  18:          
  19:          
  20:          
  21:          
  22:          
  23:          oDAL = CreateTransactionReadyToAttach(oDmn.ID.Value, oDmn.TheRowVersion);
  24:          SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
  25:          ctx.Update(oDAL);
  26:   
  27:          #region spacer
  28:          
  29:   
  30:   
  31:   
  32:   
  33:   
  34:   
  35:   
  36:   
  37:   
  38:          
  39:          #endregion
  40:      }
  41:      else
  42:      {
  43:          oDAL = new dal.Transaction();
  44:          SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
  45:          ctx.Save(oDAL);
  46:      }
  47:   
  48:      return oDAL;
  49:  }
15.6.6.4 LINQ to SQL (The Description)

To set up the context to save a modified Transaction, the LINQ to SQL version of the function first creates the ORM entity (line 32), then attaches it to the context (line 33), and finally sets all the fields in the ORM entity (line 34).  (Notice that the order is different than for NHibernate.)  Creation of the entity and field set up occur via helper functions already discussed.  Attaching the entity to the context occur via a call to the wizard-generated "Attach" function.

To set up the context to save a new Transaction the LINQ to SQL version of the function first creates a new ORM entity (line 43), then sets all the fields in the ORM entity (line 44), and finally notifies the context about the new entity (line 45).  Field setup occurs via a helper function already discussed.  Notifying the context about the new object occur via a call to the wizard-generated InsertOnSubmit() function. Except for the name of the function used to notify the context about the new object, the block is the same as in the the Entity Framework and NHibernate versions.

15.6.6.5 Entity Framework (The Description)

To set up the context to save a modified Transaction, the Entity Framework version of the function first queries for the object (line 38), and then sets all the fields in the returned object (line 39) via a helper function already discussed.  Simple, but not efficient.  I would much prefer to not query for the object, and instead create one "home made" and attach it.  For various reasons (see comments on lines 16-22 and 29-31) I could not get this to work. (Ideas?) This will probably easier in the version of Entity Framework that will ship with .NET 4.0.

To set up the context to save a new Transaction the Entity Framework version of the function first creates a new ORM entity (line 43), then sets all the fields in the ORM entity (line 44), and finally notifies the context about the new entity (line 45).  Field setup occurs via a helper function already discussed.  Notifying the context about the new object occur via a call to the context's (wizard-generated) AddToTransaction() function.  Except for the name of the function used to notify the context about the new object the block is the same as in the the LINQ to SQL and NHibernate versions.

15.6.6.6 NHibernate (The Description)

To set up the context to save a modified Transaction, the NHibernate version of the function first creates the ORM entity (line 23), then sets all the fields in the ORM entity (line 24), and finally attaches it to the context (line 25).   (Notice that the order is different than for LINQ to SQL.)  Creation of the entity and field set up occur via helper functions already discussed.  Attaching the entity to the context occur via a call to the context's "Update" function.

To set up the context to save a new Transaction the NHibernate version of the function first creates a new ORM entity (line 43), then sets all the fields in the ORM entity (line 44), and finally notifies the context about the new entity (line 45).  Field setup occurs via a helper function already discussed.  Notifying the context about the new object occur via a call to the context's Save() function.  Except for the name of the function used to notify the context about the new object the block is the same as in the the LINQ to SQL and Entity Framework versions.

15.6.7 Save Transaction

Contents at a Glance   Detailed Contents

Finally we come to the top level function for saving a new or updated Transaction.  SaveTransaction() wraps the lower level (LLSaveTransaction()) in a try-catch block so that any ORM specific concurrency exception can be replaced with the custom exception made to work with with WCF.  The try-catch block is in turn wrapped in a using statement that establishes a transaction (in the regular sense of the word) that will be rolled back if anything goes wrong.  The transaction scope block is in turn wrapped in a using statement that creates the ORM context.    The three versions are nearly identical except for the fact that the Entity Framework version explicitly opens a connection to the database (lines 11-13).

15.6.7.1 LINQ to SQL
CodeWindow 081
   1:  public int SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance)
   2:  {
   3:      du.ITransaction oDmn = du.TransactionFactory.CreateDomainTransation(aTransaction);
   4:      int iID;
   5:      using (var ctx = new dal.DataClassesDataContext(CONN))
   6:      using (s.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
   7:      {
   8:   
   9:          try
  10:          {
  11:   
  12:   
  13:              
  14:              
  15:              dal.Transaction oDal= LLSaveTransaction(oDmn, bAssumeInBalance, ctx);
  16:              ctx.SubmitChanges();
  17:              iID = oDal.ID;
  18:              tran.Complete();
  19:          }
  20:          catch(s.Data.Linq.ChangeConflictException)
  21:          {
  22:              //I think this will never happen because concurrency exception will
  23:              //always occur in the call to "TransactionRowVersionMatches".
  24:              //But since that is just a questionable optimization that we might remove
  25:              //I will leave this
  26:              throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
  27:          }
  28:   
  29:   
  30:      
  31:      
  32:      
  33:      }
  34:      return iID;
  35:  }
15.6.7.2 Entity Framework
CodeWindow 082
   1:  public int SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance)
   2:  {
   3:      du.ITransaction oDmn = du.TransactionFactory.CreateDomainTransation(aTransaction);
   4:      int iID;
   5:      using (var ctx = new dal.LekEntities(CONN))
   6:      using (s.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
   7:      {
   8:          s.Data.Common.DbConnection conn =null;
   9:          try
  10:          {
  11:              s.Data.EntityClient.EntityConnection aEntityConnection = (s.Data.EntityClient.EntityConnection)ctx.Connection;
  12:              conn = aEntityConnection.StoreConnection;
  13:              conn.Open();
  14:   
  15:              dal.Transaction oDal= LLSaveTransaction(oDmn, bAssumeInBalance, ctx);
  16:              ctx.SaveChanges();
  17:              iID = oDal.ID;
  18:              tran.Complete();
  19:          }
  20:          catch(s.Data.OptimisticConcurrencyException)
  21:          {
  22:              //I think this will never happen because concurrency exception will
  23:              //always occur in the call to "TransactionRowVersionMatches".
  24:              //But since that is just a questionable optimization that we might remove
  25:              //I will leave this
  26:              throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
  27:          }
  28:          finally
  29:          {
  30:              if(conn!=null && conn.State == System.Data.ConnectionState.Open)
  31:                  conn.Close();
  32:          }
  33:      }
  34:      return iID;
  35:  }
15.6.7.3 NHibernate
CodeWindow 083
   1:  public int SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance)
   2:  {
   3:      du.ITransaction oDmn = du.TransactionFactory.CreateDomainTransation(aTransaction);
   4:      int iID;
   5:      using (var ctx = dal.SessionFactory.OpenSession(CONN))
   6:      using (s.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
   7:      {
   8:   
   9:          try
  10:          {
  11:              
  12:              
  13:              
  14:              
  15:              dal.Transaction oDal= LLSaveTransaction(oDmn, bAssumeInBalance, ctx);
  16:              ctx.Flush();
  17:              iID = oDal.ID;
  18:              tran.Complete();
  19:          }
  20:          catch(nh.StaleObjectStateException)
  21:          {
  22:              //I think this will never happen because concurrency exception will
  23:              //always occur in the call to "TransactionRowVersionMatches".
  24:              //But since that is just a questionable optimization that we might remove
  25:              //I will leave this
  26:              throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
  27:          }
  28:   
  29:   
  30:      
  31:      
  32:      
  33:      }
  34:      return iID;
  35:  }
15.6.7.4 Contrasting the Three Versions of SaveTransasction

The creation of the context (line 5) occurs via construction of a DataClassesDataContext for LINQ to SQL, via construction of a (wizard-generated) LekEntitites for Entity Framework, and via a call to OpenSession() for NHibernate.

In contrast to the versions for LINQ to SQL and NHibernate, the Entity Framework version of SaveTransaction() explicitly opens the database connection (line 13).  This would not be necessary of there were no stored procedure calls.  The first database operation performed by LLSaveTransactions() is a call to the GetTransactionTimestamp() stored procedure.  For LINQ to SQL execution of the stored procedure is via a wizard-generated wrapper function, that, like other ORM related tasks, shields us from having to worry about opening database connections.  For NHibernate we do have to write most of the code to call the stored procedure, but the database connection (from which the stored procedure command is obtained) is already open at the point when the stored procedure is called (I am not sure when NHibernate opens it).   For Entity Framework execution of the stored procedure also occurs via code that explicitly sets up the stored procedure call.  However, unlike with NHibernate the connection obtainable from the context will not necessarily be open.  I suspect that NHibernate opens the connection when the session is created, and Entity Framework only opens it when an ORM action occurs that requires it.  Since no ORM action is executed prior to the stored procedure call, the connection will not be open unless our code explicitly opens it.

The actual write to the database (line 16) is triggered by the SubmitChanges() function of the LINQ to SQL context, the SaveChanges() function of the Entity Framework context, and the Flush() function on the NHibernate context.

If  a concurrency problem occurs (line 15), LINQ to SQL throws a System.Data.Linq.ChangeConflictExcepion, Entity Framework throws System.Data.OptimisticConcurrencyException, and NHibernate throws a NHibernate.StaleObjectStateException.   Each of the three versions has a catch for the appropriate exception type (line 20), and then translates this into a WCF friendly ConcurrencyFaultException (line 26)

16 A Few Other Helpful Views and the Functions that Feed Them

Contents at a Glance   Detailed Contents

Of the 33 functions that make up the IService interface, I have only discussed four: Get2WayParticipants(),  UpdateParticipants(), GetTransactions(), and SaveTransactions().  Those four however are the core functions of the application and the logic in them is representative of most of the logic in the other functions.  In this section I will discuss four other functions that are less significant, but that do play an important role in LEK.  I will not present the source code for any of these, but merely describe the LEK functionality that they make possible.

16.1 Net Worth Account Deltas for an Account

Contents at a Glance   Detailed Contents

When I check my actual checking account from my bank's web site the balance almost never matches what LEK says it should be.  Inevitably I lose a receipt or forget about an automatic draft.  So balancing accounts against on "official" statement is essential.  In LEK this activity is facilitated by the "Net Worth Account Deltas for an Account" view. This view shows all the changes to a particular NWAccnt that occurred over a period of time or between post events.  I typically pull up (over the web) my latest statement from my bank , convert it into a bitmap, display it and the view for the same period side-by-side, and then mark off transactions from the bitmap that have an entry in the view.  Those that didn't get a mark are the ones that I lost the receipt for or whatever, so I enter those into LEK.  The GetNWAccntDltsOfTransations() function serves up the data consumed by the "Net Worth Account Deltas for an Account" view.

NetWorthAccountDeltasForAnAccount.GIF

16.2 Items for an Account

Contents at a Glance   Detailed Contents

To review the history of a particular type of expense or income stream, like electric bills, LEK provides the "Items for an Account" view.  This view is very similar to the "Net Worth Account Deltas for an Account" view in that you specify a time period or bounding post events and then get back a list of changes to an account.  Each row in the view actually corresponds to an Item (that was allocated to the selected DltAllocAccnt).  Each row has a cell for each possible Participant, and those with non-zero values correspond to the actual DltAllocs.  The GetDltAllocsOfTransactions() function serves up the data consumed by the "Items for an Account" view.

ItemsForAnAccount.gif

16.3 Summary

Contents at a Glance   Detailed Contents

LEK provides the "Summary" view to show the total amount of all the changes made to each account over a period of time.  Some examples of information obtainable from this view are: the amount spent on groceries over the last year for a Participant,  the total amount deposited into a savings account over the last year for a Participant, the change in a Participant's Net Worth over the last year, the change to the amount that a Participant owes "the pot:" (wash) over the last year.  Such values can be shown for any time period (not just year).  If the first few Transactions in the system establishes the current values in all NWAccnts, and the time period is set to start prior to the first Transaction, then the Summary view will show the actual value of all NWAccnts for each Participant.  The "Summary" view shows all amounts for each Participant and for the family as a whole.  The GetSummaryOfTransactions() function serves up the data consumed by the "Summary" view.

Summary.GIF

16.4 Items

Contents at a Glance   Detailed Contents

Allocating expenses among Participants and accounts is often quite subjective.  Often, while entering a Transaction for something my wife bought that I don't really see the need for I will allocate expenses in a way that I know she won't agree with.  Later, while reviewing the entered Transactions in preparation for a post, I come to my senses and modify the allocation so that domestic tranquility will be maintained.  These two activities, reviewing entered Transactions and modifying allocations can be done from the Transactions and Transaction views, but can be done much more efficiently from the "Items" view.  This view show all the Items for a particular period of time or post events, without regard to the Transactions they are contained in.  The Items can be sorted by the percentage of allocation between Participants and by the accounts.  This makes it very easy to see at a glance who is paying for what across a collection of Transactions.  The view also has a means of making changes directly on the view, without having to open individual Transactions.  The GetItemsInTransactions() function serves up the data consumed by the "Items" view.  The ResetItemsInTransactions() function saves changes made in the "Items" view. 

Items.GIF

17 Downloading, Setting up the Database, Building, and Running

Contents at a Glance   Detailed Contents

All of the source files are available as a zip file here.

The zip file contains a everything needed to build the application using Visual Studio 2008.  Besides the files in the zip, you will need Microsoft Visual Studio 2008 with .NET Framework 3.5 SP1.  To run the application you will also need a SQL Server 2005 database.

Here are all the steps to download, build and run the application:

  1. Download the zip file.
  2. Extract all the files and directories from the zip file into a directory on your hard drive.  I will refer to this directory as "INSTALL".
  3. Open INSTALL\Lek.sln in Visual Studio 2008.
  4. Make sure SQL Server 2005 is running.
  5. Open SQL Server Management Studio and create a database called Lek (the main database) and a database called LekDev (the testing database).
  6. In SQL Server Management Studio run the DDL SQL in Solution Items\SchemaAdd.sql for each database.
  7. In Visual Studio open ServiceFactory\LEKSettings.config and change the value for the ProdDBConnectionString setting to the connection string for the Lek database that you just created and change the value for the DevDBConnectionString setting to the connection string for the LekDev database that you just created. You may need to only replace "ADIT80" with your own machine name in both connection strings..
  8. Build the solution.
  9. Select from the menu "Test | Run | All Tests in Solution", and make sure all 94 tests pass.
  10. Run the "WinApp" project, or, if you want to run the app as a true three-tier app run the "WinFormServer" project.  You will be shown the "Select ORM and Database" dialog.
  11. In the "Select ORM and Database" dialog check the "reset data" check box and then click one of the buttons in the "Testing Database" column. 
  12. You will be shown the LEK dialog.

Lek.GIF

18 Using The Code

Contents at a Glance   Detailed Contents

LEK doesn't try to be smart. It won't try to guess what you want to do next or try to guess what you want.  None of the modeless dialogs communicate with any other modeless dialog.  So, for example, when you save a new Transaction it will NOT automatically appear in the "Transactions" form.   You will need to click "Reload" to see it.  And, of course, if will only appear then if your query in the "Transactions" form is set such that the new Transaction matches the criteria of the query.  By default the criteria is "not posted" and new Transactions are always un-posted, so by default it will show up when you click "Reload".  The modal dialogs include those for specifying criteria, the "Net Worth Account Deltas for Transaction" dialog (for setting up a Transaction), and the "Transaction Items" dialog (also for setting up a Transaction).  Information that you enter into these will reflect immediately in the parent dialog from which they were launched once you close the modal (child) dialog.

19 History

Contents at a Glance   Detailed Contents
  • 2009 DEC 20: Initial Posting
  • 2009 DEC 22:
    • This Page: Added code window labels.
    • This Page: Changed location of zip to standard location.
    • This Page: Fixed line wrap bug in Code Window 002.
    • This Page: Fixed small formatting mistake in section 13.2.
    • This Page: Added "alt" tag to all images.
    • Download: Improved error handling for case where SQL Server is not available.
  • 2009 DEC 25:
    • This Page: Made code windows wider in version on periodnet.blogspot.com
    • This Page: A few corrections to grammar and spelling.
    • This Page: Minor adjustments to wording in a few places.
    • Download: Removed a few extra files.
  • 2010 JAN 30:
    • Made DTO.Ownership serializable over WCF by adding the [ser.DataContract] attribute.
    • Added App.Config to WinFormClient with a commented out name-value pair for address of service on internet.
    • Made WinFormClient override address of service with any value found in WinFormClient.exe.config.
    • Gave ServiceLinqToSql.Service a default constructor that will read the connection string from a web config file (for IIS) or from WinFormServer.exe.config.
Conceived and Created by:
Bryan Weikel
Posted by Bryan Weikel at 2:49 PM
Labels: , , , , , , , , , , , ,

7 comments:

Anonymous said...

After looking into a handful of the blog articles on your web site,
I truly like your technique of writing a blog. I book-marked
it to my bookmark webpage list and will be checking back soon.

Take a look at my web site as well and let me know how you feel.



My web blog; windows registry cleaner

March 11, 2013 at 1:08 AM
Anonymous said...

I loved аѕ much аs уou will геceive carriеd
оut гіght here. Τhе ѕketch
іѕ attrаctiѵe, youг authored subjeсt mattеr ѕtylish.
nonеtheless, yοu command get got аn еdgineѕs оѵеr that уou wish be deliveгіng the fοllοωing.
unwеll unquestionably come fuгtheг foгmeгly аgain аs
eхactly thе samе neагly а lot often insіde саsе you ѕhiеld this hikе.


Herе is my pаge :: gizli oyunlar

April 2, 2013 at 8:17 PM
Anonymous said...

I pay a quick visit day-to-day a few sites and blogs to
read articles, except this webpage provides feature based
posts.

Feel free to visit my webpage - online seo training course

June 15, 2013 at 2:05 PM
Anonymous said...

Undeniably believe that which you stated. Your favorite justification appeared to be on the web the easiest
thing to be aware of. I say to you, I certainly get annoyed
while people think about worries that they plainly don't know about. You managed to hit the nail upon the top and defined out the whole thing without having side-effects , people could take a signal. Will likely be back to get more. Thanks

My weblog: 3d dolphin tattoos

June 16, 2013 at 2:59 AM
Anonymous said...

Hey! This is my first visit to your blog! We are a group of volunteers and starting a new project in
a community in the same niche. Your blog provided
us useful information to work on. You have done a outstanding
job!

My blog post: learn about seo

June 16, 2013 at 6:31 PM
Anonymous said...

hello!,I like your writing very a lot! share we be in contact more approximately your post on AOL?
I require an expert in this area to solve my problem.
May be that is you! Looking forward to peer you.



my webpage :: 3d dolphin tattoos

June 16, 2013 at 9:59 PM
Anonymous said...

For individuals who want to race online, there is
certainly plenty for those to enjoy. As you head over to your
local hotel to make use of the device to involve help, you get there is something really
wrong using the place. Males love motors that twinkle this
will let you good sound getting energized, and people who
get attention. All in the details who go
into wedge adjustments and tire pressure are offered through the pits.
s not probably the most realistic among Xbox 360 racing games nevertheless, you may easily grab and drive anything from cars
and motorcycles to boats, airplanes and helicopters.


My web page unblocked games

June 17, 2013 at 2:38 PM

Post a Comment

Subscribe to: Post Comments (Atom)

About Me

My photo
I live in the suburbs with my wife and two children. I am a pretty good husband and dad, except I don’t mow the lawn enough, and often don’t get around to putting up Christmas lights. My children are loud, I am kind of quiet, and my wife is hot.