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 to leverage your knowledge of one to learn the other.
2 Contents at a Glance
- Introduction
- Contents at a Glance
- A Few Screenshots
- Detailed Contents
- Background
- My Ulterior Motive (...well, one of them)
- Data Model
- Business Rules
- Visualization of the Data
- Architecture
- ORM Classes and Their Mapping to Database Entities
- How the ORM Classes Fit in the Architecture
- Using the ORM Frameworks for Simple Operations
- Using the ORM Frameworks for a Complex Query
- Using the ORM Frameworks for a Complex Data Write Operation
- A Few Other Helpful Views and the Functions that Feed Them
- Downloading, Setting up the Database, Building, and Running
- Using the Code
- History
3 A Few Screenshots
Contents at a Glance Detailed Contents
Here is the form that comes up when you launch LEK:
Here is the form for creating and querying for accounting transactions:
4 Detailed Contents
- Introduction
- Contents at a Glance
- A Few Screenshots
- Detailed Contents
- Background
- My Ulterior Motive (...well, one of them)
- Data Model
- Business Rules
- Visualization of the Data
- Architecture
- ORM Classes and Their Mapping to Database Entities
- How the ORM Classes Fit in the Architecture
- Using the ORM Frameworks for Simple Operations
- Using the ORM Frameworks for a Complex Query
- Using the ORM Frameworks for a Complex Data Write Operation
- A Few Other Helpful Views and the Functions that Feed Them
- Downloading, Setting up the Database, Building, and Running
- Using the Code
- History
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:
- Each individual in the family owns a certain percentage of each of the assets owned by the family.
- Each individual in the family is responsible for a certain percentage of each of the liabilities the family is responsible for.
- Each expense incurred by the family can be allocated among the family members.
- 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:
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 of each possible combination of Participants owing each other, a virtual third 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 screenshots 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:
The Items for this Transaction will appear like this:
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:
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:
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":
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.
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 are 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:
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 the 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 to 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.
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 a 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).
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.
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 a 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.
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 a 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.
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.
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:
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 to 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:
And here is the DalLinqToSQL project after the operation was completed:
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:
And here is the DalEF project after the operation was completed:
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 requires selecting the database that the classes would map to. Here is the SQL Server diagram of the database that was chosen in both cases:
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:
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:
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 too 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
Code window 001:
Please see Code Window 001 in the version of this article on periodnet.blogspot.com.
11.3.1.2 The Post ORM Class Mapping for LINQ to SQL
Code window 002:
="1.0"="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" />
<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>
</Database>
11.3.2 Entity Framework
Contents at a Glance Detailed Contents
11.3.2.1 The Post ORM Class for Entity Framework
Code window 003:
Please see the Code Window 003 in the version of this article on periodnet.blogspot.com.
11.3.2.2 The Post ORM Class Mapping for Entity Framework
Code window 004:
Please see Code Window 004 in the version of this article on periodnet.blogspot.com.
11.3.3 NHibernate
Contents at a Glance Detailed Contents
11.3.3.1 The Post ORM Class for NHibernate
Code window 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
Code window 006:
="1.0"
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="DalNH"
assembly="DalNH">
<class name="Post" table="Post">
<id name="ID">
<column name="ID" />
<generator class="native" />
</id>
<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 of 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:
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:
Code window 007:
Please see Code Window 007 in the version of this article on periodnet.blogspot.com.
NHibernate allows 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. An 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:
Code window 008:
Please see Code Window 008 in the version of this article on periodnet.blogspot.com.
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:
Code window 009:
Please see Code Window 009 in the version of this article on periodnet.blogspot.com.
Each section in the code window above is described below:
11.4.2.2 Classes
Class elements are used to map ORM classes to 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:
The ORM classes for LINQ to SQL are in the DalLinqToSQL namespace, the ORM classes for Entity Framework are in the DalEF namespace, and the ORM classes for NHibernate are in the DalNH namespace. I will refer to 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 namespace). 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 namespace) 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.
Code window 010:
Please see Code Window 010 in the version of this article on periodnet.blogspot.com.
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.
Code window 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.
Code window 012:
Please see Code Window 012 in the version of this article on periodnet.blogspot.com.
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:
Code window 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 object 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:
Code window 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};
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.
Code window 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 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 straightforward:
Code window 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:
Code window 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:
Code window 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 function 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:
Code window 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:
Code window 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):
Code window 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():
Code Window 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, 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:
Code window 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[]):
Code window 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. An 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:
Code window 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 the LINQ to SQL version:
Code window 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 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:
Code window 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.
Code window 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 an updated Participant and a 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
Code window 029:
INSERT INTO [dbo].[Participant]([Name])
VALUES (@p0)
SELECT [t0].[ID], [t0].[timestamp]
FROM [dbo].[Participant] AS [t0]
WHERE [t0].[ID] = (SCOPE_IDENTITY())
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)
13.5.2.2 Entity Framework
Code window 030:
update [dbo].[Participant]
set [Name] = @0
where (([ID] = @1) and ([timestamp] = @2))
select [timestamp]
from [dbo].[Participant]
where @@ROWCOUNT > 0 and [ID] = @1
insert [dbo].[Participant]([Name])
values (@0)
select [ID], [timestamp]
from [dbo].[Participant]
where @@ROWCOUNT > 0 and [ID] = scope_identity()
13.5.2.3 NHibernate
Code window 031:
INSERT INTO Participant (Name) VALUES (@p0);
select SCOPE_IDENTITY()
SELECT participan_.timestamp as timestamp9_
FROM Participant participan_
WHERE participan_.ID=@p0
UPDATE Participant
SET Name = @p0
WHERE ID = @p1 AND timestamp = @p2
SELECT participan_.timestamp as timestamp9_
FROM Participant participan_
WHERE participan_.ID=@p0
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 descendent 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 descendants 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:
Code window 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:
Code window 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:
Code window 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 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 an object based on attributes of a parent object, it can be as simple as the following:
Code window 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 an 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:
Code window 036:
public static IList<City> X1_GetCitiesSegregateable(nh.ISession ctx)
{
nh.ICriteria aCriteria = ctx.CreateCriteria<City>();
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>();
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:
Code window 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 Citys 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:
Code window 038:
public static void AddStateFilterBasedOn_oStateAlias(nh.ICriteria aCriteria)
{
State oStateAlias = null;
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 the 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:
Code window 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 the corresponding local variable in the criteria setting function:
Code window 040:
public static IList<City> GetCities(nh.ISession ctx)
{
nh.ICriteria aCriteria = ctx.CreateCriteria<City>();
State oStateAlias = null;
aCriteria.CreateAlias<City>(o=>o.TheState, ()=>oStateAlias);
AddStateFilterBasedOn_oStateAlias(aCriteria);
return aCriteria.List<City>();
}
14.1.5 Calling the 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.
Code window 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:
Code window 042:
public static IList<State> X_GetStates(nh.ISession ctx)
{
nh.ICriteria aCriteria = ctx.CreateCriteria<State>();
State oStateAlias = null;
aCriteria.CreateAlias<State>(o=>o, ()=>oStateAlias);
AddStateFilterBasedOn_oStateAlias(aCriteria);
return aCriteria.List<State>();
}
public static IList<State> GetStates(nh.ISession ctx)
{
State oStateAlias = null;
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:
Code window 043:
Please see Code Window 043 in the version of this article on periodnet.blogspot.com.
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:
Code window 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 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 combining 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:
Code window 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:
Code window 046:
Please see Code window 046 in the version of this article on periodnet.blogspot.com.
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:
Code window 047:
Please see Code Window 047 in the version of this article on periodnet.blogspot.com.
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."
ToO bad. We are forced to fall back on a separate non-generic implementation for each of the five queries:
Code window 048:
Please see Code Window 048 in the version of this article on periodnet.blogspot.com.
14.4.3 ..In NHibernate
Here is the criteria setting function for NHibernate:
Code window 049:
Please see Code Window 049 in the version of this article on periodnet.blogspot.com.
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:
Code window 050:
Please see Code Window 050 in the version of this article on periodnet.blogspot.com.
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:
Code window 051:
Please see Code Window 051 in the version of this article on periodnet.blogspot.com.
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:
Code window 052:
Please see Code Window 052 in the version of this article on periodnet.blogspot.com.
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:
Code window 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:
Code window 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 do. 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:
Code window 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, an LEK client calls the SaveTransaction() function declared on the ServiceInterface.IService interface:
Code window 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 ORMs, the function deals with three distinct entity types:
DTO.Transaction: for transferring data between tiersDominUtil.Transaction: for enforcing the business rules of a TransactionDal*.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 an n-tier application, entity objects typically pick up their changes on the client where 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 straightforward 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 an 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:
- Check the row version for the
Transaction to ensure it hasn't changed since the snapshot was sent to the client. - Delete all the child entities using an (efficient) Stored Procedure.
- Create and attach a
Transaction entity in LINQ to SQL and NHibernate. Query for and then update a Transaction entity in Entity Framework. - Add all the child entities as new objects.
- 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.)
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 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:
Code window 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 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:
Code window 058:
CREATE PROCEDURE [dbo].[DeleteTransactionChildRecords]
@TransactionID int
AS
BEGIN
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 of 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 too 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:
Code window 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:
Code window 060:
public static int? GetFK<T>(
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;
}
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:
Code window 061:
public static void EnsureFK<T>(
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, "ID", iID.Value);
}
15.5.2.4 SetAllPropsAsModified
Contents at a Glance Detailed Contents
In LINQ to SQL, the function used to attach a 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 Entity 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:
Code window 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:
Code window 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()
{
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 requires that the new child entity be added to the child collection in the parent entity. It is not also necessary to setup the foreign key in the new child to the parent. With NHibernate, it is. The function couldn't be simpler though. I only made it a function to make it easier to contrast against EnsureFKWithInteger(). Here is EnsureFKWithEntity():
Code window 064:
public static void EnsureFKWithEntity<T>(s.Action<T> delSet, T 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
Code window 065:
Please see Code Window 065 in the version of this article on periodnet.blogspot.com.
15.6.1.2 Entity Framework and NHibernate
Code window 066:
Please see Code Window 066 in the version of this article on periodnet.blogspot.com.
15.6.2 SPs: GetTransactionTimestamp and DeleteTransactionChildRecords
Contents at a Glance Detailed Contents
GetTransactionTimestamp() and DeleteTransactionChildRecords() are both simple wrappers for 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:
Code window 067:
Please see Code Window 067 in the version of this article on periodnet.blogspot.com.
15.6.2.2 Entity Framework
Wrapper functions for the Stored Procedures were created manually for Entity Framework:
Code window 068A:
Please see Code Window 068A in the version of this article on periodnet.blogspot.com.
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:
Code window 068B:
Please see Code Window 068B in the version of this article on periodnet.blogspot.com.
Note that creation of the command object in line 6 is easier than it 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
Code window 069:
Please see Code Window 069 in the version of this article on periodnet.blogspot.com.
15.6.3.2 Entity Framework
Code window 070:
Please see Code Window 070 in the version of this article on periodnet.blogspot.com.
15.6.3.3 NHibernate
Code window 071:
Please see Code Window 071 in the version of this article on periodnet.blogspot.com.
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
Code window 072:
Please see Code Window 072 in the version of this article on periodnet.blogspot.com.
15.6.4.2 Entity Framework
Code window 073:
Please see Code Window 073 in the version of this article on periodnet.blogspot.com.
15.6.4.3 NHibernate
Code window 074:
Please see Code Window 074 in the version of this article on periodnet.blogspot.com.
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
Code window 075:
Please see Code Window 075 in the version of this article on periodnet.blogspot.com.
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
Code window 076:
Please see Code Window 076 in the version of this article on periodnet.blogspot.com.
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
Code window 077:
Please see Code Window 077 in the version of this article on periodnet.blogspot.com.
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)
Code window 078:
Please see Code Window 078 in the version of this article on periodnet.blogspot.com.
Code window 079:
Please see Code Window 079 in the version of this article on periodnet.blogspot.com.
15.6.6.3 NHibernate (The Function)
Code window 080:
Please see Code Window 080 in the version of this article on periodnet.blogspot.com.
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 occurs 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 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 be 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 occurs 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 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 occurs 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 occurs 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 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 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
Code window 081:
Please see Code Window 081 in the version of this article on periodnet.blogspot.com.
15.6.7.2 Entity Framework
Code window 082:
Please see Code Window 082 in the version of this article on periodnet.blogspot.com.
15.6.7.3 NHibernate
Code window 083:
Please see Code Window 083 in the version of this article on periodnet.blogspot.com.
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 if 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 a 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.
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.
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 establish 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.
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.
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 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:
- Download the zip file.
- 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".
- Open INSTALL\Lek.sln in Visual Studio 2008.
- Make sure SQL Server 2005 is running.
- Open SQL Server Management Studio and create a database called Lek (the main database) and a database called LekDev (the testing database).
- In SQL Server Management Studio, run the DDL SQL in Solution Items\SchemaAdd.sql for each database.
- 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.. - Build the solution.
- Select from the menu "Test | Run | All Tests in Solution", and make sure all 94 tests pass.
- 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.
- 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.
- You will be shown the LEK dialog.
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 cases 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.
