CodeProject
"LEK" is a three-tier .NET WinForms accounting application that can be configured at run time to use either LINQ to SQL, Entity Framework or NHibernate to access data in either a "main" or "testing" SQL Server database.
<!-- MANAGED HEADING START ID=10 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CONTENTSATAGLANCE START -->
<!-- MANAGED CONTENTSATAGLANCE END --> <!-- MANAGED HEADING START ID=12 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Here is the screen that comes up when you launch LEK:
Here is the screen for creating and querying for accounting transactions:
<!-- MANAGED HEADING START ID=15 -->
<!-- MANAGED HEADING END --> <!-- MANAGED DETAILEDCONTENTS START -->
<!-- MANAGED DETAILEDCONTENTS END --> <!-- MANAGED HEADING START ID=20 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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 on 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.
<!-- MANAGED HEADING START ID=21 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=22 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
The conceptual data model:
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.
<!-- MANAGED HEADING START ID=23 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=24 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
A Participant is a family member.
<!-- MANAGED HEADING START ID=25 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=26 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
A DltAllocAccnt (Delta Allocation Account) is an expense or income account. Examples: groceries, gasoline, entertainment, salary, interest.
<!-- MANAGED HEADING START ID=27 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=28 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=29 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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).
<!-- MANAGED HEADING START ID=30 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=31 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=32 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
If Father goes to the store and buys something for Son and Mother then they each owe Father for it. But for the family as a whole the amount owed is “a wash” – Father is owed $5, Mother owes $3 and Son owes $2. As you can imagine, this could get complicated very quickly as the number of Participants grows. Rather than keeping track each possible combination of Participants owing each other, a virtual 3rd party is introduced called the “the wash”. In the above example Mother owes “the wash” $3, Son owes “the wash” $2 and Father is owed by “the wash” $5. Each of these would be a WashDlt. Each WashDlt is associated with a Transaction and a Participant. No two WashDlts have the same Participant and the same Transaction. All the WashDlts associated with the same Transaction sum to 0.
<!-- MANAGED HEADING START ID=35 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=40 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Here are some screen shots from LEK of a Transaction slightly more complicated than the one just described. Note that on the screens 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:
<!-- MANAGED HEADING START ID=45 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=50 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED HEADING START ID=55 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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":
<!-- MANAGED HEADING START ID=60 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=65 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=70 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
A WCF application in production is usually easiest to configure if IP address, ports, and other WCF setup information is stored in configuration files. For development, however, it is often easier to compile this into the application. Since the client and the server must agree on these settings, they are put into a shared namespace called ClientServer, which is only accessible from the WinFormServer and WinFormClient namespaces:
<!-- MANAGED HEADING START ID=75 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
UI code is hard to test and subject to the whims of whoever is setting the aesthetics for your application. As much as possible, the elements that control the appearance and directly interact with the user should be separated from the rest of the code. This will make it easier to write unit and integration tests for the rest of the code. In LEK, every form displayed to the user has two classes: one in the Presenter namespace called a “presenter” and one in the WinUI namespace called a “view”. The presenter sends commands to the view to show information. The view “paints” the information on to the screen. When the user interacts with one of the painted controls, the view sends a notification to the presenter that user wants to do something. The presenter takes the appropriate action and then sends a command to the view to update it if necessary.
The presenter only knows that the view it communicates with implements a specific interface. The view only knows that the presenter it interacts with implements a specific interface. These interfaces are defined in the UIInterface namespace. This allows the Presenter and WinUI namespaces to be decoupled. They depend on the UIInterface namespace but not on each other. The presenter has access to a factory that it asks to create its view. The presenter passes its view an interface reference to itself for the view pass events back through.
The view factory is defined in the WinUI namespace, but implements an interface of its own (for use by the presenters) that is defined in the UIInterface namespace. The view factory itself is created from within the WinFormClient or WinApp namespaces, which then initializes the presenters by passing them an interface reference to the view factory. In this sense the shell executables provide an “inversion of control” functionality: they supply factories to subordinate code that is ignorant of the concrete classes that implement the functionality that the subordinate code needs.
I have not created unit tests for the presenters, but this architecture would make it easy to do so. The presenters would be supplied with a view factory that would create automated views that implement the required interfaces. The automated views would expose an additional interface so that they could be programmatically directed by the test harness to simulate a user interaction.
<!-- MANAGED HEADING START ID=80 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
If you have worked on more than one project, then you probably have library of generic routines and types that you carry from project to project. In LEK the Util namespace is reserved for this sort of non-domain specific code. At this point in the elaboration of the architecture, all other shared code is reallocated to the GlobalType, UIType, and DomainUtil namespaces. The GlobalType namespace is for simple data types (little or no logic) that must be available to nearly all namespaces – client and server. The UIType namespace is for the simple data types that must pass between the presenters and the views (in the Presenter and WinUI namespaces), but that aren’t needed by the Service namespace. The DomainUtil namespace is for all other types and logic that need to be accessed by the Service namespace and the Presenter namespace.
<!-- MANAGED HEADING START ID=85 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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. 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 (generic ones), 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 restrictions will help ensure that all logic put into DomainUtil 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.
<!-- MANAGED HEADING START ID=90 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
A well-factored application with near complete unit test coverage is easier to maintain than one with less extensive unit test coverage. Changes can be made with greater confidence that mistakes that make it past the compiler will be caught when running the unit tests. Integration tests that go through several layers are great at catching mistakes as well, but if they are extensive enough to cover the majority of the code they will probably pass between two or more tiers and thus take much longer to run (and when problems surface, the tests won’t point you to the source of the problem). It only takes a few milliseconds to instantiate an object, call some functions on it, and then verify that return values and final state are as expected. But if that object internally calls functions that cross process boundaries (through WCF or database connections) then they will take orders of magnitude longer to execute. An application with hundreds of tests that make calls across process boundaries will take several minutes to run, and the root cause of failures will be hard to track down. Faced with this prospect, developers will typically write fewer tests, use them less often, and spend more time waiting for tests to complete.
A layered design is a great improvement over designs characterized by “spaghetti code”, but simple layering does not do much to make unit testing easier. Since the UI logic depends on the business logic, which in turn depends on the data access logic, testing UI logic often means test execution flow will cross process boundaries and thus exhibit the problems discussed above. One solution to this is mocking the lower layers, so that tests execute quickly and problems can be isolated to the layer under test and not the supporting code. But mocking takes time and adds complexity. Where possible, it is better to decouple code from the layers altogether. For example, instead of having a business object on the client that validates and transforms data and then passes this data to the backend to be persisted in the database, it is better to have business object that simply validates and transforms the data according to the business rules. This way testing the business object will not require waiting for data to cross tiers and will not require mocking. Mocking may still be required to test the code that is responsible for instantiating the business object, getting the data transfer object from it, and forwarding this on to the backend, but this will be much less code to test.
In the previous step of the elaboration of the architecture, the Service namespace was deemed to contain all the code for the server, which includes the code that interacts with the database. To reinforce the notion that as much of this code as possible should not depend on the database, a new namespace, called ServerUtil, is created which will not be allowed to communicate with the database. Your goal as a developer should be to locate as much server side code as possible in the ServerUtil namespace, where use of the database is forbidden, thus ensuring the most effective unit testing.
Likewise, in the previous step of the elaboration of the architecture, the Presenter namespace was deemed to contain all the code for the client except for the code responsible for painting the UI and interacting with the user. To reinforce the notion that as much of this code as possible should be independent of the back-end, a new namespace, called ClientUtil is created without any dependency on the ServiceInterface namespace. Your goal as a developer should be to locate as much client side code as possible in the ClientUtil namespace, where use of the server object or WCF server proxies are prevented, thus ensuring the most effective unit testing.
<!-- MANAGED HEADING START ID=95 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
An ORM tool will give you the ability to map classes to tables in the database. It will also provide an API for using objects of these classes to move data into or out of the database. A key architectural decision you will need to make is what namespaces in your application should have access to the ORM classes. This decision will depend, in part, on an even more fundamental decision: how closely should the object model you work with in code match the relational model of your database? If you think that a data model created exclusively for expressing the business and presentation logic of your application would be very similar to a data model created exclusively for moving data into and out of a relational database, then you should probably make your ORM classes available to all the namespaces in your application. This is especially true if the application is relatively simple and has a short estimated life time (months). On the other hand, if you think that a data model created exclusively for expressing the business and presentation logic of your application would be significantly different from a data model created exclusively for moving data into and out of a relational database, then you should probably be more guarded in exposing your ORM classes to the rest of your application. This is especially true if the application is relatively complex and has long life time (years).
Another factor to consider is how amenable the ORM tool is to generating ORM classes that don’t exactly match your database schema. Entity Framework and NHibernate offer a lot of capability to do this. If you are comfortable using some of the more advanced ORM features to map classes to your database that don’t exactly match up to your schema then these classes will be more suitable for use in the different layers of your application. You should also consider how you feel about allowing your classes to carry around functions and attributes only used by the ORM layer. NHibernate lets you map classes to the database with very few artifacts of the mapping: they are “POCO” (“Plain Old Class Objects”). This makes them more suitable for use in different layers of your application. Entity Framework (at least version 3.5) and LINQ to SQL, on the other hand, require mapped classes with many special functions and attributes used by the ORM. Ideally classes used by your presentation and business rules should not expose methods and attributes that are never used by these layers.
Due to the fact that LEK is meant to show how to use different ORM tools, and because I am attempting to model the architecture for a complex and long lived application, I have architected LEK to be very guarded in exposing the ORM classes to the rest of the application. All the ORM classes in LEK are in the Dal namespace (actually they each have their own, but that is in the next step). Dal is only accessible to the Service namespace (actually they each have their own one of these as well, but that is in the next step). The code in the Service namespace transforms the ORM objects into data transfer objects when reading data from the database and transforms data transfer objects into ORM objects when writing data to the database. Both the Service namespace and Presenter namespace make use of business objects from DomainUtil or ServerUtil or ClientUtil that implement the business logic of the application. The classes describing these business objects make up the true data model for the application. Typically, the business objects are initialized with the data transfer objects, and when asked to supply a snapshot of data for serialization to the database, they supply the data as data transfer objects. The Service namespace contains the (custom, handwritten) code that translates between data transfer objects and ORM objects. The ORM classes very closely match the actual database schema, and are structured such that the mapping to the database is as simple as possible.
<!-- MANAGED HEADING START ID=100 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Rather than the Dal, namespace LEK actually has a separate namespace for each of the 3 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.
<!-- MANAGED HEADING START ID=105 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED HEADING START ID=110 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
The primary objective of an ORM tool is to make it possible for you move data into and out of the database using objects rather than SQL. Each of the technologies allows you to specify a set of classes that define these objects as well as a specification of how the fields of the classes map to the fields in the database. Adding data to the database involves instantiating instances of these classes and then passing the instances into an API provided by the ORM tool. Pulling data from the database involves passing a query into the API and getting back instances of these objects. The query may be similar to SQL, but is in terms of the ORM classes.
<!-- MANAGED HEADING START ID=115 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=120 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=125 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=130 -->
<!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=135 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
For both LINQ to SQL and Entity Framework, using the wizard to add the classes required selecting the database that the classes would map to. Here is the SQL Server diagram of the database that was chosen in both cases:
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.
<!-- MANAGED HEADING START ID=140 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Here is the ERD for the ORM classes generated by the wizard for LINQ to SQL:
<!-- MANAGED HEADING START ID=145 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Here is the ERD for the ORM classes generated by the wizard for Entity Framework:
<!-- MANAGED HEADING START ID=150 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
The primary means of creating and modifying ORM classes and associated mapping information for LINQ to SQL and Entity Framework is through the Visual Studio IDE. For NHibernate, you must create and maintain the ORM classes and mapping information manually. However, the ORM classes and mapping information for NHibernate are much simpler than the corresponding code for LINQ to SQL and Entity Framework. In this section I will show the ORM class and associated mapping information for the simplest entity in LEK: Post. My intention is simply to show the kind of information in each of these files, and to allow you to compare the relative complexity of each. You probably shouldn't spend to much time at this point trying to interpret the code.
<!-- MANAGED HEADING START ID=155 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=160 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=001 -->
CodeWindow 001
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
[Table(Name="dbo.Post")]
public partial class Post : INotifyPropertyChanging, INotifyPropertyChanged
{
private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty);
private int _ID;
private System.DateTime _Instant;
private EntitySet<Transaction> _Transactions;
#region Extensibility Method Definitions
partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
partial void OnIDChanging(int value);
partial void OnIDChanged();
partial void OnInstantChanging(System.DateTime value);
partial void OnInstantChanged();
#endregion
public Post()
{
this._Transactions = new EntitySet<Transaction>(new Action<Transaction>(this.attach_Transactions), new Action<Transaction>(this.detach_Transactions));
OnCreated();
}
[Column(Storage="_ID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)]
public int ID
{
get
{
return this._ID;
}
set
{
if ((this._ID != value))
{
this.OnIDChanging(value);
this.SendPropertyChanging();
this._ID = value;
this.SendPropertyChanged("ID");
this.OnIDChanged();
}
}
}
[Column(Storage="_Instant", DbType="DateTime NOT NULL")]
public System.DateTime Instant
{
get
{
return this._Instant;
}
set
{
if ((this._Instant != value))
{
this.OnInstantChanging(value);
this.SendPropertyChanging();
this._Instant = value;
this.SendPropertyChanged("Instant");
this.OnInstantChanged();
}
}
}
[Association(Name="Post_Transaction", Storage="_Transactions", ThisKey="ID", OtherKey="PostID")]
public EntitySet<Transaction> Transactions
{
get
{
return this._Transactions;
}
set
{
this._Transactions.Assign(value);
}
}
public event PropertyChangingEventHandler PropertyChanging;
public event PropertyChangedEventHandler PropertyChanged;
protected virtual void SendPropertyChanging()
{
if ((this.PropertyChanging != null))
{
this.PropertyChanging(this, emptyChangingEventArgs);
}
}
protected virtual void SendPropertyChanged(String propertyName)
{
if ((this.PropertyChanged != null))
{
this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
}
}
private void attach_Transactions(Transaction entity)
{
this.SendPropertyChanging();
entity.Post = this;
}
private void detach_Transactions(Transaction entity)
{
this.SendPropertyChanging();
entity.Post = null;
}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=165 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=002 -->
CodeWindow 002
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
<?xml version="1.0" encoding="utf-8"?>
<Database Name="xLekDev" Class="DataClassesDataContext" xmlns="http://schemas.microsoft.com/linqtosql/dbml/2007">
<Connection Mode="AppSettings" ConnectionString="Data Source=xxMACHINENAMExx;Initial Catalog=xLekDev;Integrated Security=True" SettingsObjectName="DalLinqToSql.Properties.Settings" SettingsPropertyName="LekDevConnectionString" Provider="System.Data.SqlClient" />
<!--text removed -->
<Table Name="dbo.Post" Member="Posts">
<Type Name="Post">
<Column Name="ID" Type="System.Int32" DbType="Int NOT NULL IDENTITY" IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />
<Column Name="Instant" Type="System.DateTime" DbType="DateTime NOT NULL" CanBeNull="false" />
<Association Name="Post_Transaction" Member="Transactions" ThisKey="ID" OtherKey="PostID" Type="Transaction" />
</Type>
</Table>
<!--text removed -->
</Database>
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=170 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=175 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=003 -->
CodeWindow 003
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
[global::System.Data.Objects.DataClasses.EdmEntityTypeAttribute(NamespaceName="LekModel", Name="Post")]
[global::System.Runtime.Serialization.DataContractAttribute(IsReference=true)]
[global::System.Serializable()]
public partial class Post : global::System.Data.Objects.DataClasses.EntityObject
{
public static Post CreatePost(int id, global::System.DateTime instant)
{
Post post = new Post();
post.ID = id;
post.Instant = instant;
return post;
}
[global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(EntityKeyProperty=true, IsNullable=false)]
[global::System.Runtime.Serialization.DataMemberAttribute()]
public int ID
{
get
{
return this._ID;
}
set
{
this.OnIDChanging(value);
this.ReportPropertyChanging("ID");
this._ID = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
this.ReportPropertyChanged("ID");
this.OnIDChanged();
}
}
private int _ID;
partial void OnIDChanging(int value);
partial void OnIDChanged();
[global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(IsNullable=false)]
[global::System.Runtime.Serialization.DataMemberAttribute()]
public global::System.DateTime Instant
{
get
{
return this._Instant;
}
set
{
this.OnInstantChanging(value);
this.ReportPropertyChanging("Instant");
this._Instant = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
this.ReportPropertyChanged("Instant");
this.OnInstantChanged();
}
}
private global::System.DateTime _Instant;
partial void OnInstantChanging(global::System.DateTime value);
partial void OnInstantChanged();
[global::System.Data.Objects.DataClasses.EdmRelationshipNavigationPropertyAttribute("LekModel", "FK_Transaction_Post", "Transaction")]
[global::System.Xml.Serialization.XmlIgnoreAttribute()]
[global::System.Xml.Serialization.SoapIgnoreAttribute()]
[global::System.Runtime.Serialization.DataMemberAttribute()]
public global::System.Data.Objects.DataClasses.EntityCollection<Transaction> Transaction
{
get
{
return ((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.GetRelatedCollection<Transaction>("LekModel.FK_Transaction_Post", "Transaction");
}
set
{
if ((value != null))
{
((global::System.Data.Objects.DataClasses.IEntityWithRelationships)(this)).RelationshipManager.InitializeRelatedCollection<Transaction>("LekModel.FK_Transaction_Post", "Transaction", value);
}
}
}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=180 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=004 -->
CodeWindow 004
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx">
<!-- EF Runtime content -->
<edmx:Runtime>
<!-- SSDL content -->
<edmx:StorageModels>
<Schema Namespace="LekModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2005" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">
<EntityContainer Name="LekModelStoreContainer">
<!--text removed -->
<EntitySet Name="Post" EntityType="LekModel.Store.Post" store:Type="Tables" Schema="dbo" />
<!--text removed -->
</EntityContainer>
<!--text removed -->
<EntityType Name="Post">
<Key>
<PropertyRef Name="ID" />
</Key>
<Property Name="ID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
<Property Name="Instant" Type="datetime" Nullable="false" />
</EntityType>
<!--text removed -->
</Schema>
</edmx:StorageModels>
<!-- CSDL content -->
<edmx:ConceptualModels>
<Schema Namespace="LekModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">
<EntityContainer Name="LekEntities">
<!--text removed -->
<EntitySet Name="Post" EntityType="LekModel.Post" />
<!--text removed -->
</EntityContainer>
<!--text removed -->
<EntityType Name="Post">
<Key>
<PropertyRef Name="ID" />
</Key>
<Property Name="ID" Type="Int32" Nullable="false" />
<Property Name="Instant" Type="DateTime" Nullable="false" />
<NavigationProperty Name="Transaction" Relationship="LekModel.FK_Transaction_Post" FromRole="Post" ToRole="Transaction" />
</EntityType>
<!--text removed -->
</Schema>
</edmx:ConceptualModels>
<!-- C-S mapping content -->
<edmx:Mappings>
<Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS">
<EntityContainerMapping StorageEntityContainer="LekModelStoreContainer" CdmEntityContainer="LekEntities">
<!--text removed -->
<EntitySetMapping Name="Post">
<EntityTypeMapping TypeName="IsTypeOf(LekModel.Post)">
<MappingFragment StoreEntitySet="Post">
<ScalarProperty Name="ID" ColumnName="ID" />
<ScalarProperty Name="Instant" ColumnName="Instant" />
</MappingFragment>
</EntityTypeMapping>
</EntitySetMapping>
<!--text removed -->
</EntityContainerMapping>
</Mapping>
</edmx:Mappings>
</edmx:Runtime>
<!--text removed -->
</edmx:Edmx>
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=185 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=190 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=005 -->
CodeWindow 005
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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;}
}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=195 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=006 -->
CodeWindow 006
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="DalNH"
assembly="DalNH">
<!-- Mappings for class 'Post' -->
<class name="Post" table="Post">
<!-- Identity mapping -->
<id name="ID">
<column name="ID" />
<generator class="native" />
</id>
<!-- Simple mappings -->
<property name="Instant" />
</class>
</hibernate-mapping>
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=200 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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 an 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. Post could have a property for a collection of Transactions, but in LEK it does not.
- In the LINQ to SQL mappings, foreign keys in the database are also mapped to integer members or the ORM classes (this is not shown in the Post class above).
<!-- MANAGED HEADING START ID=205 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Since the ORM classes and mappings must be 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.
<!-- MANAGED HEADING START ID=210 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
The code window below shows all the contents of DalNH.cs. The format is somewhat compressed so that common elements line up vertically:
<!-- MANAGED CODEWINDOW START ID=007 -->
CodeWindow 007
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using nh=NHibernate;
using s=System;
namespace DalNH
{
public class Participant
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
byte[] _timestamp ;public virtual byte[] timestamp {get{return _timestamp ;}set{_timestamp =value;}}
string _strName ;public virtual string Name {get{return _strName ;}set{_strName =value;}}
}
public class NWAccnt
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
byte[] _timestamp ;public virtual byte[] timestamp {get{return _timestamp ;}set{_timestamp =value;}}
string _strName ;public virtual string Name {get{return _strName ;}set{_strName =value;}}
string _strDescrip ;public virtual string Descrip {get{return _strDescrip ;}set{_strDescrip =value;}}
bool _bBudgeted ;public virtual bool Budgeted {get{return _bBudgeted ;}set{_bBudgeted =value;}}
}
public class DltAllocAccnt
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
byte[] _timestamp ;public virtual byte[] timestamp {get{return _timestamp ;}set{_timestamp =value;}}
string _strName ;public virtual string Name {get{return _strName ;}set{_strName =value;}}
string _strDescrip ;public virtual string Descrip {get{return _strDescrip ;}set{_strDescrip =value;}}
bool _bBudgeted ;public virtual bool Budgeted {get{return _bBudgeted ;}set{_bBudgeted =value;}}
}
public class Ownership
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
NWAccnt _NWAccnt ;public virtual NWAccnt TheNWAccnt {get{return _NWAccnt ;}set{_NWAccnt =value;}}
Participant _Participant ;public virtual Participant TheParticipant {get{return _Participant ;}set{_Participant =value;}}
int _iShares ;public virtual int Shares {get{return _iShares ;}set{_iShares =value;}}
}
#region CodeWindow 005
public class Post
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
DateTime _dtInstant ;public virtual DateTime Instant {get{return _dtInstant ;}set{_dtInstant =value;}}
}
#endregion
public class Transaction
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
byte[] _timestamp ;public virtual byte[] timestamp {get{return _timestamp ;}set{_timestamp =value;}}
Post _Post ;public virtual Post ThePost {get{return _Post ;}set{_Post =value;}}
Participant _Participant ;public virtual Participant TheParticipant {get{return _Participant ;}set{_Participant =value;}}
string _strDescrip ;public virtual string Descrip {get{return _strDescrip ;}set{_strDescrip =value;}}
DateTime _dtInstant ;public virtual DateTime Instant {get{return _dtInstant ;}set{_dtInstant =value;}}
Iesi.Collections.Generic.ISet<Item> _Items = new Iesi.Collections.Generic.HashedSet<Item>();
Iesi.Collections.Generic.ISet<NWAccntDlt> _NWAccntDlts = new Iesi.Collections.Generic.HashedSet<NWAccntDlt>();
Iesi.Collections.Generic.ISet<WashDlt> _WashDlts = new Iesi.Collections.Generic.HashedSet<WashDlt>();
public virtual Iesi.Collections.Generic.ISet<Item> Items
{
get { return _Items; }
set { _Items = value; }
}
public virtual Iesi.Collections.Generic.ISet<NWAccntDlt> NWAccntDlts
{
get { return _NWAccntDlts; }
set { _NWAccntDlts = value; }
}
public virtual Iesi.Collections.Generic.ISet<WashDlt> WashDlts
{
get { return _WashDlts; }
set { _WashDlts = value; }
}
}
public class Item
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
Transaction _Transaction ;public virtual Transaction TheTransaction {get{return _Transaction ;}set{_Transaction =value;}}
DltAllocAccnt _DltAllocAccnt ;public virtual DltAllocAccnt TheDltAllocAccnt{get{return _DltAllocAccnt ;}set{_DltAllocAccnt=value;}}
string _strDescrip ;public virtual string Descrip {get{return _strDescrip ;}set{_strDescrip =value;}}
Iesi.Collections.Generic.ISet<DltAlloc> _DltAllocs = new Iesi.Collections.Generic.HashedSet<DltAlloc>();
public virtual Iesi.Collections.Generic.ISet<DltAlloc> DltAllocs
{
get { return _DltAllocs; }
set { _DltAllocs = value; }
}
}
public class DltAlloc
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
Item _Item ;public virtual Item TheItem {get{return _Item ;}set{_Item =value;}}
Participant _Participant ;public virtual Participant TheParticipant {get{return _Participant ;}set{_Participant =value;}}
int _iAmount ;public virtual int Amount {get{return _iAmount ;}set{_iAmount =value;}}
}
public class NWAccntDlt
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
NWAccnt _NWAccnt ;public virtual NWAccnt TheNWAccnt {get{return _NWAccnt ;}set{_NWAccnt =value;}}
Transaction _Transaction ;public virtual Transaction TheTransaction {get{return _Transaction ;}set{_Transaction =value;}}
int _iAmount ;public virtual int Amount {get{return _iAmount ;}set{_iAmount =value;}}
}
public class WashDlt
{
int _iID ;public virtual int ID {get{return _iID ;}set{_iID =value;}}
Transaction _Transaction ;public virtual Transaction TheTransaction {get{return _Transaction ;}set{_Transaction =value;}}
Participant _Participant ;public virtual Participant TheParticipant {get{return _Participant ;}set{_Participant =value;}}
int _iAmount ;public virtual int Amount {get{return _iAmount ;}set{_iAmount =value;}}
}
}
<!-- MANAGED CODEWINDOW END -->
NHibernate allowes ORM classes to be "POCO": Plain Old CLR Objects. They don't have to have any of the attributes or special functions that the ORM classes for LINQ to SQL and Entity Framework do. This makes them more suitable for use in many layers of your application. A NHibernate ORM class does have to have public setters for all the properties that map to fields in the database, and they must be virtual. These two requirements are enough to dissuade me from using them outside of the data access layer.
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.
<!-- MANAGED HEADING START ID=215 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=008 -->
CodeWindow 008
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="DalNH" assembly="DalNH">
<!--Participant--> <class name="Participant" table="Participant">
<!--Participant--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Participant--> <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--Participant--> <property name="Name" />
<!--Participant--> </class>
<!--NWAccnt--> <class name="NWAccnt" table="NWAccnt">
<!--NWAccnt--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--NWAccnt--> <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--NWAccnt--> <property name="Name" />
<!--NWAccnt--> <property name="Descrip" />
<!--NWAccnt--> <property name="Budgeted" />
<!--NWAccnt--> </class>
<!--DltAllocAccnt--> <class name="DltAllocAccnt" table="DltAllocAccnt">
<!--DltAllocAccnt--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--DltAllocAccnt--> <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--DltAllocAccnt--> <property name="Name" />
<!--DltAllocAccnt--> <property name="Descrip" />
<!--DltAllocAccnt--> <property name="Budgeted" />
<!--DltAllocAccnt--> </class>
<!--Ownership--> <class name="Ownership" table="Ownership">
<!--Ownership--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Ownership--> <many-to-one name="TheNWAccnt" class="NWAccnt" column="NWAccntID" not-null="true"/>
<!--Ownership--> <many-to-one name="TheParticipant" class="Participant" column="ParticipantID" not-null="true"/>
<!--Ownership--> <property name="Shares" />
<!--Ownership--> </class>
<!--Post--> <class name="Post" table="Post">
<!--Post--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Post--> <property name="Instant" />
<!--Post--> </class>
<!--Transaction--> <class name="Transaction" table="`Transaction`">
<!--Transaction--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Transaction--> <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--Transaction--> <many-to-one name="TheParticipant" class="Participant" column="ParticipantID"/>
<!--Transaction--> <many-to-one name="ThePost" class="Post" column="PostID"/>
<!--Transaction--> <property name="Descrip" />
<!--Transaction--> <property name="Instant" />
<!--Transaction--> <set name="Items" inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="Item"/> </set>
<!--Transaction--> <set name="NWAccntDlts" inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="NWAccntDlt"/> </set>
<!--Transaction--> <set name="WashDlts" inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="WashDlt"/> </set>
<!--Transaction--> </class>
<!--Item--> <class name="Item" table="Item">
<!--Item--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Item--> <many-to-one name="TheTransaction" class="Transaction" column="TransactionID" not-null="true" cascade="save-update"/>
<!--Item--> <many-to-one name="TheDltAllocAccnt" class="DltAllocAccnt" column="DltAllocAccntID" not-null="true"/>
<!--Item--> <property name="Descrip" />
<!--Item--> <set name="DltAllocs" inverse="true" lazy="true" cascade="save-update"> <key column="ItemID"/> <one-to-many class="DltAlloc"/> </set>
<!--Item--> </class>
<!--DltAlloc--> <class name="DltAlloc" table="DltAlloc">
<!--DltAlloc--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--DltAlloc--> <many-to-one name="TheItem" class="Item" column="ItemID" not-null="true" cascade="save-update"/>
<!--DltAlloc--> <many-to-one name="TheParticipant" class="Participant" column="ParticipantID" not-null="true"/>
<!--DltAlloc--> <property name="Amount" />
<!--DltAlloc--> </class>
<!--NWAccntDlt--> <class name="NWAccntDlt" table="NWAccntDlt">
<!--NWAccntDlt--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--NWAccntDlt--> <many-to-one name="TheNWAccnt" class="NWAccnt" column="NWAccntID" not-null="true"/>
<!--NWAccntDlt--> <many-to-one name="TheTransaction" class="Transaction" column="TransactionID" not-null="true" cascade="save-update"/>
<!--NWAccntDlt--> <property name="Amount" />
<!--NWAccntDlt--> </class>
<!--WashDlt--> <class name="WashDlt" table="WashDlt">
<!--WashDlt--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--WashDlt--> <many-to-one name="TheTransaction" class="Transaction" column="TransactionID" not-null="true" cascade="save-update"/>
<!--WashDlt--> <many-to-one name="TheParticipant" class="Participant" column="ParticipantID" not-null="true"/>
<!--WashDlt--> <property name="Amount" />
<!--WashDlt--> </class>
</hibernate-mapping>
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=220 -->
<!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=009 -->
CodeWindow 009
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
<?xml version="1.0"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="DalNH" assembly="DalNH">
<!--## Classes ##-->
<!--Participant--> <class name="Participant" table="Participant">
<!--NWAccnt--> <class name="NWAccnt" table="NWAccnt">
<!--DltAllocAccnt--> <class name="DltAllocAccnt" table="DltAllocAccnt">
<!--Ownership--> <class name="Ownership" table="Ownership">
<!--Post--> <class name="Post" table="Post">
<!--Transaction--> <class name="Transaction" table="`Transaction`">
<!--Item--> <class name="Item" table="Item">
<!--DltAlloc--> <class name="DltAlloc" table="DltAlloc">
<!--NWAccntDlt--> <class name="NWAccntDlt" table="NWAccntDlt">
<!--WashDlt--> <class name="WashDlt" table="WashDlt">
<!--## Primary Keys ##-->
<!--Participant--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--NWAccnt--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--DltAllocAccnt--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Ownership--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Post--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Transaction--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--Item--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--DltAlloc--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--NWAccntDlt--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--WashDlt--> <id name="ID"> <column name="ID" /> <generator class="native" /> </id>
<!--## Row Versions ##-->
<!--Participant--> <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--NWAccnt--> <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--DltAllocAccnt--> <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--Transaction--> <version name="timestamp" generated="always" unsaved-value="null" type="BinaryBlob"> <column name="timestamp" not-null="false" sql-type="timestamp"/> </version>
<!--## FKs That Can be Null ##-->
<!--Transaction--> <many-to-one name="TheParticipant" class="Participant" column="ParticipantID"/>
<!--Transaction--> <many-to-one name="ThePost" class="Post" column="PostID"/>
<!--## FKs to Parents that Don't Track Entity as a Child ##-->
<!--Ownership--> <many-to-one name="TheNWAccnt" class="NWAccnt" column="NWAccntID" not-null="true"/>
<!--NWAccntDlt--> <many-to-one name="TheNWAccnt" class="NWAccnt" column="NWAccntID" not-null="true"/>
<!--DltAlloc--> <many-to-one name="TheParticipant" class="Participant" column="ParticipantID" not-null="true"/>
<!--Ownership--> <many-to-one name="TheParticipant" class="Participant" column="ParticipantID" not-null="true"/>
<!--WashDlt--> <many-to-one name="TheParticipant" class="Participant" column="ParticipantID" not-null="true"/>
<!--Item--> <many-to-one name="TheDltAllocAccnt" class="DltAllocAccnt" column="DltAllocAccntID" not-null="true"/>
<!--## FKs to Parents that Track Entity as a Child ##-->
<!--Item--> <many-to-one name="TheTransaction" class="Transaction" column="TransactionID" not-null="true" cascade="save-update"/>
<!--NWAccntDlt--> <many-to-one name="TheTransaction" class="Transaction" column="TransactionID" not-null="true" cascade="save-update"/>
<!--WashDlt--> <many-to-one name="TheTransaction" class="Transaction" column="TransactionID" not-null="true" cascade="save-update"/>
<!--DltAlloc--> <many-to-one name="TheItem" class="Item" column="ItemID" not-null="true" cascade="save-update"/>
<!--## Children ##-->
<!--Transaction--> <set name="Items" inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="Item"/> </set>
<!--Transaction--> <set name="NWAccntDlts" inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="NWAccntDlt"/> </set>
<!--Transaction--> <set name="WashDlts" inverse="true" lazy="true" cascade="save-update"> <key column="TransactionID"/> <one-to-many class="WashDlt"/> </set>
<!--Item--> <set name="DltAllocs" inverse="true" lazy="true" cascade="save-update"> <key column="ItemID"/> <one-to-many class="DltAlloc"/> </set>
<!--## Simple Fields ##-->
<!--Participant--> <property name="Name" />
<!--NWAccnt--> <property name="Name" />
<!--NWAccnt--> <property name="Descrip" />
<!--NWAccnt--> <property name="Budgeted" />
<!--DltAllocAccnt--> <property name="Name" />
<!--DltAllocAccnt--> <property name="Descrip" />
<!--DltAllocAccnt--> <property name="Budgeted" />
<!--Ownership--> <property name="Shares" />
<!--Post--> <property name="Instant" />
<!--Transaction--> <property name="Descrip" />
<!--Transaction--> <property name="Instant" />
<!--Item--> <property name="Descrip" />
<!--DltAlloc--> <property name="Amount" />
<!--NWAccntDlt--> <property name="Amount" />
<!--WashDlt--> <property name="Amount" />
<!--## End Tag for Class ##-->
<!--Participant--> </class>
<!--NWAccnt--> </class>
<!--DltAllocAccnt--> </class>
<!--Ownership--> </class>
<!--Post--> </class>
<!--Transaction--> </class>
<!--Item--> </class>
<!--DltAlloc--> </class>
<!--NWAccntDlt--> </class>
<!--WashDlt--> </class>
</hibernate-mapping>
<!-- MANAGED CODEWINDOW END -->
Each section in the code window above is described below:
<!-- MANAGED HEADING START ID=225 -->
<!-- MANAGED HEADING END -->
Class elements are used to map ORM classes to a database tables.
<!-- MANAGED HEADING START ID=230 -->
<!-- MANAGED HEADING END -->
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)
<!-- MANAGED HEADING START ID=235 -->
<!-- MANAGED HEADING END -->
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.)
<!-- MANAGED HEADING START ID=240 -->
<!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=245 -->
<!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=250 -->
<!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=255 -->
<!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=260 -->
<!-- MANAGED HEADING END -->
Property elements are used to map ORM class properties to fields in the database that are not keys, timestamps, or child records.
<!-- MANAGED HEADING START ID=265 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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 DalEF namespace, and the ORM classes for NHibernate are in the DalNH namespace. I will refer these namespaces as the "Dal*" namespaces.
The LINQ to SQL ORM classes are only used in the ServiceLinqToSQL namespace, the Entity Framework ORM classes are only used in the ServiceEF namespace, and the NHibernate ORM classes are only used in the ServiceNH namespace. I will refer to these namespaces as the Service* namespaces.
<!-- MANAGED HEADING START ID=270 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=275 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
The Service class for each ORM implements an interface called IService. The client only knows that the object it uses for data access implements the IService interface. (I am using the term "client" rather loosely here to actually refer to classes from the Presenter namespace (see the diagram above).) The client itself is created from the hosting application run by the user (and defined in either the WinFormClient or WinApp namespaces). If LEK is running in a three-tier configuration, then the WCF server (WinFormServer namespace, in the middle tier) instantiates one of the Service classes via a factory, and exposes it via WCF. Each hosting application (WinFormClient namespace) will instantiate a WCF proxy to the server-side singleton and pass the proxy to its contained client (Presenter namespace). The proxy also implements IService. If LEK is running in a two-tier configuration, then the hosting application (WinApp namepace) instantiates one of the Service classes directly and passes it to its contained client. In either case, the client only knows that it has an object that implements IService. It does not know or care if the object is a proxy to a singleton on the server or a singleton in its own process. It also does not know or care if the object uses LINQ to SQL, Entity Framework, or NHibernate.
<!-- MANAGED CODEWINDOW START ID=010 -->
CodeWindow 010
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using s=System;
using si=ServiceInterface;
using ser=System.Runtime.Serialization;
using sm=System.ServiceModel;
using gt=GlobalType;
namespace ServiceInterface
{
[sm.ServiceContract]
public interface IService
{
[sm.OperationContract]int GetDltAllocAccntCount( int? iExpected);
[sm.OperationContract]int GetNWAccntCount( int? iExpected);
[sm.OperationContract]int GetParticipantCount( int? iExpected);
[sm.OperationContract]int GetOwnershipCount( int? iExpected);
[sm.OperationContract]int GetPostCount( int? iExpected);
[sm.OperationContract]int GetTransactionCount( int? iExpected);
[sm.OperationContract]int GetItemCount( int? iExpected);
[sm.OperationContract]int GetDltAllocCount( int? iExpected);
[sm.OperationContract]int GetNWAccntDltCount( int? iExpected);
[sm.OperationContract]int GetWashDltCount( int? iExpected);
[sm.OperationContract]void Test();
[sm.OperationContract]void ClearDatabase();
[sm.OperationContract]void InitDatabase(string str);
[sm.OperationContract]DTO.Participant[] GetParticipants();
[sm.OperationContract]gt.Participant[] Get2WayParticipants();
[sm.OperationContract]gt.NWAccnt[] Get2WayNWAccnts();
[sm.OperationContract]gt.DltAllocAccnt[] Get2WayDltAllocAccnts();
[sm.OperationContract]DTO.Cache GetCache();
[sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void UpdateParticipants( gt.Participant[] arr);
[sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void UpdateNWAccnts( gt.NWAccnt[] arr);
[sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void UpdateDltAllocAccnts(gt.DltAllocAccnt[] arr);
[sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void UpdateOwnerships(DTO.Ownership[] arr);
#region CodeWindow 044
[sm.OperationContract]DTO.Transaction[] GetTransactions(DTO.TransPred aTransPred, int? iParticipantID);
#endregion
[sm.OperationContract]DTO.Transaction GetTransaction(int iTransactionID);
[sm.OperationContract]void VerifyAllTransactions();
[sm.OperationContract]DTO.ItemEx[] GetItemsInTransactions(DTO.TransPred aTransPred);
[sm.OperationContract]DTO.Summary GetSummaryOfTransactions(DTO.TransPred aTransPred, s.DateTime dtBudgetStartDate, int iMonthlyBudgetAmount);
[sm.OperationContract]DTO.NWAccntDltEx[] GetNWAccntDltsOfTransactions(int iNWAccntID, DTO.TransPred aTransPred);
[sm.OperationContract]DTO.DltAlloc[] GetDltAllocsOfTransactions(int iDltAllocAccntID, DTO.TransPred aTransPred);
[sm.OperationContract]void Post();
[sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void DeleteTransaction(int iTransactionID, gt.RowVersion oTransactionRowVersion);
#region CodeWindow 056
[sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]int SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance);
#endregion
[sm.OperationContract][sm.FaultContract(typeof(DTO.ConcurrencyFault))]void ResetItemsInTransactions(DTO.ItemUpdate[] arrItemUpdates);
}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=280 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=285 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED CODEWINDOW START ID=011 -->
CodeWindow 011
<!--style="width:650px; height:150px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
[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;
}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=290 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> 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.
<!-- MANAGED CODEWINDOW START ID=012 -->
CodeWindow 012
<!--style="width:650px; height:150px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
[ser.DataContract]
public class Participant
{
[ser.DataMember]bool _bNew;
[ser.DataMember]int? _iID;
[ser.DataMember]RowVersion _RowVersion;
[ser.DataMember]string _strName;
[ser.DataMember]protected bool _bModified;
public int? ID { get { return _iID; } }
public string Name
{
get
{
return _strName;
}
set
{
_strName = value;
if(_iID.HasValue)
_bModified = true;
}
}
public bool Modified
{
get { return _bModified; }
}
public bool New
{
get { return _bNew; }
}
public RowVersion GetRowVersion(){return _RowVersion;}
public override string ToString()
{
return Name;
}
public Participant(int iID, RowVersion oRowVersion, string strName)
{
_iID = iID;
_RowVersion = oRowVersion;
_strName = strName;
_bNew = false;
}
public Participant(string strName )
{
_strName = strName;
_bNew = true;
}
public Participant()
:this("?")
{
}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=295 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=300 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=305 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=310 -->
<!-- MANAGED HEADING END -->
The version of the Get2WayParticipants function in the service class for LINQ to SQL is shown below:
<!-- MANAGED CODEWINDOW START ID=013 -->
CodeWindow 013
<!--style="width:650px; height:225px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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();
}
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=315 -->
<!-- MANAGED HEADING END -->
The Entity Framework version of the function differs from the LINQ to SQL version in that the creation of the GlobalType.Participant objects happens outside of the framework rather than within it. The version of Entity Framework that comes with .NET 3.5 SP1 is not capable of projecting results into objects by calling a constructor for those objects with parameters. So, instead, the results are projected into an anonymous type, and these are translated into GlobalType.Participants via a LINQ to Objects expression:
<!-- MANAGED CODEWINDOW START ID=014 -->
CodeWindow 014
<!--style="width:650px; height:325px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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();
}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=320 -->
<!-- MANAGED HEADING END -->
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.
<!-- MANAGED CODEWINDOW START ID=015 -->
CodeWindow 015
<!--style="width:650px; height:275px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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();
}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=325 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Microsoft's SQL Server Profiler is very handy for checking the SQL that the ORM is generating. If you are writing a LINQ statements or creating NHibernate Criteria objects that are different from those you have created before, you will probably want to check that the SQL being generated is what you expect.
<!-- MANAGED HEADING START ID=330 -->
<!-- MANAGED HEADING END -->
The SQL generated for the LINQ to SQL version of Get2WayParticipants() is straight forward:
<!-- MANAGED CODEWINDOW START ID=016 -->
CodeWindow 016
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
SELECT
[t0].[ID] AS [iID],
[t0].[timestamp],
[t0].[Name] AS [strName]
FROM
[dbo].[Participant] AS [t0]
ORDER BY
[t0].[Name]
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=335 -->
<!-- MANAGED HEADING END -->
The SQL generated for the Entity Framework version of Get2WayParticipants() is a bit more complicated, but produces the same results:
<!-- MANAGED CODEWINDOW START ID=017 -->
CodeWindow 017
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=340 -->
<!-- MANAGED HEADING END -->
The SQL generated for the NHibernate version of Get2WayParticipants() is nearly identical to that created for LINQ to SQL:
<!-- MANAGED CODEWINDOW START ID=018 -->
CodeWindow 018
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
SELECT
this_.ID as ID9_0_,
this_.timestamp as timestamp9_0_,
this_.Name as Name9_0_
FROM
Participant this_
ORDER BY
this_.Name asc
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=345 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Each Service class has a public function called UpdateParticipants() which is responsible for updating the database from a passed in collection of GlobalType.Participant objects. The passed in collection can contain unmodified Participants, modified Participants, and new Participants. The function will ignore the unchanged Participants, update the modified Participants, and add the new Participants. If the RowVersion field of a modified GlobalType.Participant object contains a different value than the corresponding timestamp field in the database, then LEK will assume that the record changed since the snapshot was taken (in the call to Get2WayParticipants()), and will throw a specially designed exception.
As mentioned earlier, the Particpant, NWAccnt, and DltAllocAccnt entities are very similar. The GlobalType versions of these are actually all derived from a common base class called VerySimpleTwoWayDTO. This document, however, presents GlobalType.Participant as a single, non-derived class. Similarly in the source code for LEK UpdateParticpants(), UpdateNWAccnts() and UpdateDltAllocAccnts() are all shell functions, that pass on their arguments, along with several delegates, to a private functions named UpdateVerySimpleDTO(), which does all the work. This document, however, will present UpdateParticipants() as representative of all three functions, and will present it as doing nearly all the work without the aid of a generic implementation function.
<!-- MANAGED HEADING START ID=350 -->
<!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=355 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=019 -->
CodeWindow 019
<!--style="width:650px; height:150px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
[ser.DataContract]
public class ConcurrencyFault
{
[ser.DataMember] public string Description{get; private set;}
public ConcurrencyFault(string strDescription)
{
Description = strDescription;
}
}
<!-- MANAGED CODEWINDOW END -->
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:
<!-- MANAGED CODEWINDOW START ID=020 -->
CodeWindow 020
<!--style="width:650px; height:150px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=360 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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):
<!-- MANAGED CODEWINDOW START ID=021 -->
CodeWindow 021
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
public static s.Data.Linq.Binary CreateORMRowVersion(gt.RowVersion aRowVersion)
{
if(aRowVersion==null)
return null;
return new System.Data.Linq.Binary(aRowVersion.GetCopyOfData());
}
<!-- MANAGED CODEWINDOW END -->
So, having covered everything that UpdateParticipants() depends on, here, at long last, is the LINQ to SQL version of UpdateParticipants():
<!-- MANAGED CODEWINDOW START ID=022 -->
CodeWindow 022
<!--style="width:650px; height:500px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
}
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=365 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=023 -->
CodeWindow 023
<!--style="width:650px; height:150px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
<!-- MANAGED CODEWINDOW END -->
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[]):
<!-- MANAGED CODEWINDOW START ID=024 -->
CodeWindow 024
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
public static Byte[] CreateORMRowVersion(gt.RowVersion aRowVersion)
{
if(aRowVersion==null)
return null;
return aRowVersion.GetCopyOfData();
}
<!-- MANAGED CODEWINDOW END -->
Updating an Entity Framework ORM that you have just queried is easy: you just update the fields and then call SaveChanges() on the context. As with LINQ to SQL, this will only work if you queried for the object with the same context that you are using to save the object (probably meaning that you just queried for it). If, instead you will be updating the database with an ORM entity that you created yourself (or with one that you queried earlier using another context) then you must "attach" it to the context. With LINQ to SQL, attaching an ORM object that contains updated data required a call to the context's Attach() function with the AsModified parameter set to true. A Entity Framework context (in .NET 3.5 SP1) also has an "Attach" function, but it does not have an AsModified parameter to set. Instead you must loop through the meta data for the ORM object specifically indicating which fields have modified data. If your ORM object has dozens of fields, only a few of which will have updated data, then this is fine, 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:
<!-- MANAGED CODEWINDOW START ID=025 -->
CodeWindow 025
<!--style="width:650px; height:250px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
}
<!-- MANAGED CODEWINDOW END -->
By relying on the above helper functions, the Entity Framework version of UpdateParticipants() can assume a form very similar to that of LINQ to SQL version:
<!-- MANAGED CODEWINDOW START ID=026 -->
CodeWindow 026
<!--style="width:650px; height:550px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
}
}
<!-- MANAGED CODEWINDOW END -->
As with the LINQ to SQL version, the function loops through all the passed in GlobalType.Participants, adding the new ones, updating the database with the modified ones, and ignoring the rest. If Entity Framework notices that the timestamp field of a record being updated is different from the timestamp field of the object being used to update the record then the Entity Framework will throw a System.Data.OptimisticConcurrencyException. This exception will be caught, translated into a (non-ORM specific) s.ServiceModel.FaultException<DTO.ConcurrencyFault> and re-thrown.
<!-- MANAGED HEADING START ID=370 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=027 -->
CodeWindow 027
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
public static Byte[] CreateORMRowVersion(gt.RowVersion aRowVersion)
{
if(aRowVersion==null)
return null;
return aRowVersion.GetCopyOfData();
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED CODEWINDOW START ID=028 -->
CodeWindow 028
<!--style="width:650px; height:500px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
}
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=375 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
The code windows below show the SQL generated by each ORM when UpdateParticipants() is passed one updated Participant and one new Participant. I obtained the SQL from a SQL Server Profiler trace. I reformatted the code a bit and added the comments.
<!-- MANAGED HEADING START ID=380 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=029 -->
CodeWindow 029
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
-- INSERT TSQL
INSERT INTO [dbo].[Participant]([Name])
VALUES (@p0)
SELECT [t0].[ID], [t0].[timestamp]
FROM [dbo].[Participant] AS [t0]
WHERE [t0].[ID] = (SCOPE_IDENTITY())
-- INSERT PARAMS
-- varchar(5) @p0='Misty'
-- UPDATE TSQL
UPDATE [dbo].[Participant]
SET [Name] = @p2
WHERE ([ID] = @p0) AND ([timestamp] = @p1)
SELECT [t1].[timestamp]
FROM [dbo].[Participant] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[ID] = @p3)
-- UPDATE PARAMS
-- int @p0=2
-- timestamp @p1=0x000000000001334F
-- varchar(5) @p2='Debra'
-- int @p3=2
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=385 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=030 -->
CodeWindow 030
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
-- UPDATE TSQL
update [dbo].[Participant]
set [Name] = @0
where (([ID] = @1) and ([timestamp] = @2))
select [timestamp]
from [dbo].[Participant]
where @@ROWCOUNT > 0 and [ID] = @1
-- UPDATE PARAMS
-- varchar(5) @0='Debra',
-- int @1=2,
-- binary(8) @2=0x0000000000013371
-- INSERT TSQL
insert [dbo].[Participant]([Name])
values (@0)
select [ID], [timestamp]
from [dbo].[Participant]
where @@ROWCOUNT > 0 and [ID] = scope_identity()
-- INSERT PARAMS
-- varchar(5) @0='Misty'
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=390 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=031 -->
CodeWindow 031
<!--style="width:650px; height:125px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
-- INSERT TSQL, PART 1
INSERT INTO Participant (Name) VALUES (@p0);
select SCOPE_IDENTITY()
-- INSERT PARAMS, PART 1
-- nvarchar(5) @p0=N'Misty'
-- INSERT TSQL, PART 2
SELECT participan_.timestamp as timestamp9_
FROM Participant participan_
WHERE participan_.ID=@p0
-- INSERT PARAMS, PART 2
-- int @p0=5
-- UPDATE TSQL, PART 1
UPDATE Participant
SET Name = @p0
WHERE ID = @p1 AND timestamp = @p2
-- UPDATE PARAMS, PART 1
-- nvarchar(5) @p0=N'Debra',
-- int @p1=2,
-- varbinary(8) @p2=0x0000000000013383
-- UPDATE TSQL, PART 2
SELECT participan_.timestamp as timestamp9_
FROM Participant participan_
WHERE participan_.ID=@p0
-- UPDATE PARAMS, PART 2
-- int @p0=2
<!-- MANAGED CODEWINDOW END -->
<!-- MANAGED HEADING START ID=395 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Consider what would be required to create a collection of Transaction entities that complies with some criteria like date range or post status. You should be able to specify the criteria for the Transactions using the ORM specific syntax, submit this to the ORM, and get back a collection of Transaction entities complete with all their descendent entities. The child descendents entities for a Transaction include NWAccntDlts, WashDlts, and Items. The grandchild descendents for a Transaction are the DltAllocs for each of the Transaction's (child) Items. The default set of ORMs created for LINQ to SQL and Entity Framework, and the custom ORMs already described for NHibernate include members for all of these descendent entities. However, issuing a typical query on each of these ORMs to return a collection of Transaction entities will not automatically return a set of entities with all these descendant initialized. By default the ORMs provide "lazy loading" - associated entities are not loaded until you specifically ask for them. So, after issuing a simple query for Transactions we could walk the list of Transaction entities and access each of the descendant entities in order to load them from the database. However, this could result in a LOT of queries being issued against the database. If Transactions have an average of two NWAccntDlts, four WashDlts, four Items, and four DltAllocs for each Item and if just 10 Transactions are returned that would be a total of 261 separate queries being issued against the database (1 query for the list of Transactions, 20 queries for the NWAccntDlts, 40 queries for the WashDlts, 40 queries for the Items, and 160 queries for the DltAllocs.) Clearly we want to avoid that.
I believe that, for each ORM, it is possible to specify the ORM class mappings such that the framework will "eager" load specified descendent entities. It is easy to imagine how that would work for a simple parent-child relationship: The ORM framework would join the tables involved for a result set of the child entities with the parent fields tacked on to each child. To convert this result set into entities the framework would loop through the result set creating a child entity for each result and a parent entity for each result with a unique set of parent fields. You can probably imagine that the SQL for a more complex set of relationships like those we have for Transactions with several descendant entities would be quite a bit more complex. Furthermore there would be much more redundant data returned, as each descendent would probably need to carry parent entity fields along with it.
Another strategy that could be employed by the ORM would be to first obtain the list of top level objects using a simple query, and then, for each top level object issue a query for each type of descendant. In the example scenario described above this would result in 41 queries in all - much better, but still a lot of hits to the database.
A better strategy yet would be if the ORM would make a single query for each entity type and then weave the results together into entities. In the example scenario described this would result in just 4 queries, and that would be independent of the number of Transactions returned (still only 4 queries even if 100 Transactions met the criteria instead of only 10).
I do not know if any of the three ORMs can be configured to utilize this last strategy just described. If they can this would be an advanced usage of the ORM, and one that I am not yet knowledgeable enough to describe. But I can implement this strategy myself fairly easily, and thus allow the ORMs to keep their rudimentary, and accessible, configuration in place.
So to recap, the goal is to generate a list of Transaction entities that complies with some criteria and that have all their descendent data. To do this five separate queries will be executed:
- A query to return the Transactions that match the criteria.
- A query to return the NWAccntDlts for the matched Transactions.
- A query to return the WashDlts for the matched Transactions.
- A query to return the Items for the matched Transactions.
- A query to return the DltAllocs for the Items for the matched Transactions.
Each query will sort the results by the Transaction date and ID. A routine will be executed that takes these 5 result streams and steps through them in a coordinated manner, creating the collection of Transaction entities that include all the descendant data.
The biggest challenges for this endeavor, and the one that will have the most significance to you in your own work, is how to minimize the amount of redundant code in spite of having may different possible Transaction criteria. The criteria possibilities will include Transactions that:
- Occurred before a specified date.
- Occurred on or after a specified date.
- Occurred within a specified date range.
- Have a specified ID.
- Have an ID within a specified range.
- Have a specified Post ID.
- Have a Post ID within a specified range.
- Have a Post ID that is less than or equal to a specified value.
- Have a Post ID that is greater than or equal to a specified value or that is not posted (NULL Post ID)
- Are posted (have a non-null Post ID).
- Are not posted (have a null Post ID).
<!-- MANAGED HEADING START ID=400 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=032 -->
CodeWindow 032
<!--(nada) --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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;}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=405 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=410 -->
<!-- MANAGED HEADING END -->
In LINQ to SQL and Entity Framework filtering is typically performed with LINQ statements:
<!-- MANAGED CODEWINDOW START ID=033 -->
CodeWindow 033
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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();
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=415 -->
<!-- MANAGED HEADING END -->
In NHibernate segregateable filtering is performed using an NHibernate Criteria object:
<!-- MANAGED CODEWINDOW START ID=034 -->
CodeWindow 034
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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>();
}
<!-- MANAGED CODEWINDOW END -->
Note that a Criteria object has an entity type intrinsically associated with it, but this type isn't known to the compiler. If you created an ICriteria based on City, the compiler wouldn't prevent you from from calling Add<State>(..). In this respect LINQ is safer than NHibernate.
<!-- MANAGED HEADING START ID=420 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=425 -->
<!-- MANAGED HEADING END -->
In LINQ, if you are trying to filter a object based on attributes of a parent object it can be as simple as the following:
<!-- MANAGED CODEWINDOW START ID=035 -->
CodeWindow 035
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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();
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=430 -->
<!-- MANAGED HEADING END -->
In NHibernate, filtering a object based on attributes of a parent is a bit trickier, partially due to limitations of the API syntax. Each of the three functions below will compile, but only the third will execute:
<!-- MANAGED CODEWINDOW START ID=036 -->
CodeWindow 036
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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>();
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=435 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=440 -->
<!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=037 -->
CodeWindow 037
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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;
}
<!-- MANAGED CODEWINDOW END -->
In the above code window, IStateReferer is the interface that expresses what will be required in a criteria setting function based on State. CityState implements the interface and is convenient when querying for Cities that are in a State that complies with some criteria. StateWrapper implements the interface and is convenient when querying for States that comply with some criteria. GetWithStateFilter is the criteria setting function based on the IStateReferer interface.
<!-- MANAGED HEADING START ID=445 -->
<!-- MANAGED HEADING END -->
Segregating (factoring) out the criteria logic into its own function in NHibernate is very simple:
<!-- MANAGED CODEWINDOW START ID=038 -->
CodeWindow 038
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
public static void AddStateFilterBasedOn_oStateAlias(nh.ICriteria aCriteria)
{
State oStateAlias = null;
aCriteria.Add(()=>oStateAlias.Area > 4);
aCriteria.Add(()=>oStateAlias.Perimiter > 30);
}
<!-- MANAGED CODEWINDOW END -->
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 this this function that has an alias of the correct type setup, but that alias uses a different local variable name, then the function will compile but won't execute.
<!-- MANAGED HEADING START ID=450 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=455 -->
<!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=039 -->
CodeWindow 039
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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();
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=460 -->
<!-- MANAGED HEADING END -->
Calling the criteria setting function for NHibernate requires that an alias be setup using a local variable with the same type and the same name as a corresponding local variable in the criteria setting function:
<!-- MANAGED CODEWINDOW START ID=040 -->
CodeWindow 040
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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>();
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=465 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=470 -->
<!-- MANAGED HEADING END -->
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.
<!-- MANAGED CODEWINDOW START ID=041 -->
CodeWindow 041
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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();
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=475 -->
<!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=042 -->
CodeWindow 042
<!--nada --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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>();
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=480 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
In LEK, Transaction criteria are specified in TransPred objects:
<!-- MANAGED CODEWINDOW START ID=043 -->
CodeWindow 043
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
[ser.DataContract]
[ser.KnownType(typeof(OptionalDateRangeTransPred))]
[ser.KnownType(typeof(BeforeTransPred ))]
[ser.KnownType(typeof(OnOrAfterTransPred ))]
[ser.KnownType(typeof(DateRangeTransPred ))]
[ser.KnownType(typeof(IDTransPred ))]
[ser.KnownType(typeof(IDRangeTransPred ))]
[ser.KnownType(typeof(PostIDTransPred ))]
[ser.KnownType(typeof(PostIDRangeTransPred ))]
[ser.KnownType(typeof(MaxPostIDTransPred ))]
[ser.KnownType(typeof(MinPostIDTransPred ))]
[ser.KnownType(typeof(PostedTransPred ))]
[ser.KnownType(typeof(UnPostedTransPred ))]
public abstract class TransPred
{
}
#region TransPred derived classes
[ser.DataContract]
public class OptionalDateRangeTransPred : TransPred
{
[ser.DataMember]public s.DateTime? OnOrAfter{get; private set;}
[ser.DataMember]public s.DateTime? Before {get; private set;}
public OptionalDateRangeTransPred(
s.DateTime? dtOnOrAfter ,
s.DateTime? dtBefore )
{
OnOrAfter = dtOnOrAfter ;
Before = dtBefore ;
}
}
[ser.DataContract]
public class BeforeTransPred : TransPred
{
[ser.DataMember]public s.DateTime Before{get; private set;}
public BeforeTransPred(s.DateTime dtBefore)
{
Before = dtBefore;
}
}
[ser.DataContract]
public class OnOrAfterTransPred : TransPred
{
[ser.DataMember]public s.DateTime OnOrAfter{get; private set;}
public OnOrAfterTransPred(s.DateTime dtOnOrAfter)
{
OnOrAfter = dtOnOrAfter;
}
}
[ser.DataContract]
public class DateRangeTransPred : TransPred
{
[ser.DataMember]public s.DateTime OnOrAfter {get; private set;}
[ser.DataMember]public s.DateTime Before {get; private set;}
public DateRangeTransPred(
s.DateTime dtOnOrAfter ,
s.DateTime dtBefore )
{
OnOrAfter = dtOnOrAfter ;
Before = dtBefore ;
}
}
[ser.DataContract]
public class IDTransPred : TransPred
{
[ser.DataMember]public int ID{get; private set;}
public IDTransPred(int iID)
{
ID = iID;
}
}
[ser.DataContract]
public class IDRangeTransPred : TransPred
{
[ser.DataMember]public int SmallestID {get; private set;}
[ser.DataMember]public int LargestID {get; private set;}
public IDRangeTransPred(
int iSmallestID ,
int iLargestID )
{
SmallestID = iSmallestID ;
LargestID = iLargestID ;
}
}
[ser.DataContract]
public class PostIDTransPred : TransPred
{
[ser.DataMember]public int ID{get; private set;}
public PostIDTransPred(int iID)
{
ID = iID;
}
}
[ser.DataContract]
public class PostIDRangeTransPred : TransPred
{
[ser.DataMember]public int SmallestID {get; private set;}
[ser.DataMember]public int LargestID {get; private set;}
public PostIDRangeTransPred(
int iSmallestID ,
int iLargestID )
{
SmallestID = iSmallestID ;
LargestID = iLargestID ;
}
}
[ser.DataContract]
public class MaxPostIDTransPred : TransPred
{
[ser.DataMember]public int MaxID{get; private set;}
public MaxPostIDTransPred(int iMaxID)
{
MaxID = iMaxID;
}
}
[ser.DataContract]
public class MinPostIDTransPred : TransPred
{
[ser.DataMember]public int MinID{get; private set;}
public MinPostIDTransPred(int iMinID)
{
MinID = iMinID;
}
}
[ser.DataContract]
public class PostedTransPred : TransPred
{
}
[ser.DataContract]
public class UnPostedTransPred : TransPred
{
}
#endregion
<!-- MANAGED CODEWINDOW END -->
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:
<!-- MANAGED CODEWINDOW START ID=044 -->
CodeWindow 044
<!--style="width:650px; height:75px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
[sm.OperationContract]DTO.Transaction[] GetTransactions(DTO.TransPred aTransPred, int? iParticipantID);
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=485 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Recall that GetTransactions() will be implemented by querying separately for WashDlts, NWAccntDlts, Items, DltAllocs and Transactions ("the five queries"). The query for the Transactions will ensure that the Transactions match the criteria, and the query for each of the four descendant entities will ensure that the ancestor Transaction object for each child object will match the criteria. Each of the five streams for the the five queries will be sorted by Transaction date first and Transaction id second. Finally a splicing algorithm will be used to iterate through these steams in a coordinated manner, creating full initialized Transaction objects by combing Transaction entity objects with their child entity objects.
<!-- MANAGED HEADING START ID=490 -->
<!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=045 -->
CodeWindow 045
<!--style="width:650px; height:150px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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;}
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=495 -->
<!-- MANAGED HEADING END -->
No supporting code is required in NHibernate to enable the five queries to use a common criteria setting function.
<!-- MANAGED HEADING START ID=500 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=505 -->
<!-- MANAGED HEADING END -->
Here is the criteria setting function for LINQ to SQL:
<!-- MANAGED CODEWINDOW START ID=046 -->
CodeWindow 046
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
IQueryable<T> AppendWhereClauses<T>(IQueryable<T> query, DTO.TransPred aTransPred) where T : ITransComposit
{
aTransPred = ConvertIfConvPred(aTransPred);
if( aTransPred == null ) {;}
else if(aTransPred is DTO.BeforeTransPred ) query = from composite in query where composite.eTransaction.Instant < ((DTO.BeforeTransPred )aTransPred).Before select composite;
else if(aTransPred is DTO.OnOrAfterTransPred) query = from composite in query where composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred )aTransPred).OnOrAfter select composite;
else if(aTransPred is DTO.DateRangeTransPred){ query = from composite in query where composite.eTransaction.Instant < ((DTO.DateRangeTransPred )aTransPred).Before select composite;
query = from composite in query where composite.eTransaction.Instant >=((DTO.DateRangeTransPred )aTransPred).OnOrAfter select composite;}
else if(aTransPred is DTO.IDTransPred ) query = from composite in query where composite.eTransaction.ID ==((DTO.IDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.IDRangeTransPred ){ query = from composite in query where composite.eTransaction.ID <=((DTO.IDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.ID >=((DTO.IDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.PostIDTransPred ) query = from composite in query where composite.eTransaction.PostID ==((DTO.PostIDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where composite.eTransaction.PostID <=((DTO.PostIDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.PostID >=((DTO.PostIDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.MaxPostIDTransPred) query = from composite in query where composite.eTransaction.PostID <=((DTO.MaxPostIDTransPred )aTransPred).MaxID select composite;
else if(aTransPred is DTO.MinPostIDTransPred) query = from composite in query where(!composite.eTransaction.PostID.HasValue
|| composite.eTransaction.PostID >=((DTO.MinPostIDTransPred )aTransPred).MinID) select composite;
else if(aTransPred is DTO.PostedTransPred ) query = from composite in query where composite.eTransaction.PostID.HasValue select composite;
else if(aTransPred is DTO.UnPostedTransPred ) query = from composite in query where !composite.eTransaction.PostID.HasValue select composite;
else throw new Exception();
return query;
}
<!-- MANAGED CODEWINDOW END -->
Notice that the signature and structure is very similar to the criteria setting function shown previously for the example State and City entities.
<!-- MANAGED HEADING START ID=510 -->
<!-- MANAGED HEADING END -->
This very similar function for Entity Framework compiles:
<!-- MANAGED CODEWINDOW START ID=047 -->
CodeWindow 047
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
IQueryable<T> AppendWhereClauses<T>( IQueryable<T> query, DTO.TransPred aTransPred) where T : ITransComposit
{
aTransPred = ConvertIfConvPred(aTransPred);
if( aTransPred == null ) {;}
else if(aTransPred is DTO.BeforeTransPred ) query = from composite in query where composite.eTransaction.Instant < ((DTO.BeforeTransPred )aTransPred).Before select composite;
else if(aTransPred is DTO.OnOrAfterTransPred) query = from composite in query where composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred )aTransPred).OnOrAfter select composite;
else if(aTransPred is DTO.DateRangeTransPred){ query = from composite in query where composite.eTransaction.Instant < ((DTO.DateRangeTransPred )aTransPred).Before select composite;
query = from composite in query where composite.eTransaction.Instant >=((DTO.DateRangeTransPred )aTransPred).OnOrAfter select composite;}
else if(aTransPred is DTO.IDTransPred ) query = from composite in query where composite.eTransaction.ID ==((DTO.IDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.IDRangeTransPred ){ query = from composite in query where composite.eTransaction.ID <=((DTO.IDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.ID >=((DTO.IDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.PostIDTransPred ) query = from composite in query where composite.eTransaction.Post.ID ==((DTO.PostIDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.MaxPostIDTransPred) query = from composite in query where composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred )aTransPred).MaxID select composite;
else if(aTransPred is DTO.MinPostIDTransPred) query = from composite in query where (composite.eTransaction.Post == null
|| composite.eTransaction.Post.ID >=((DTO.MinPostIDTransPred )aTransPred).MinID) select composite;
else if(aTransPred is DTO.PostedTransPred ) query = from composite in query where composite.eTransaction.Post != null select composite;
else if(aTransPred is DTO.UnPostedTransPred ) query = from composite in query where composite.eTransaction.Post == null select composite;
else throw new Exception();
return query;
}
<!-- MANAGED CODEWINDOW END -->
But unfortunately execution produces the following error:
"Unable to cast the type 'ServiceEF.WashDltJoinedToTrans' to type 'ServiceEF.ITransComposit'. LINQ to Entities only supports casting Entity Data Model primitive types."
To bad.
We are forced to fall back on a separate non-generic implementation for each of the five queries:
<!-- MANAGED CODEWINDOW START ID=048 -->
CodeWindow 048
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
IQueryable<WashDltJoinedToTrans> AppendWhereClauses( IQueryable<WashDltJoinedToTrans> query, DTO.TransPred aTransPred)
{
aTransPred = ConvertIfConvPred(aTransPred);
if( aTransPred == null ) {;}
else if(aTransPred is DTO.BeforeTransPred ) query = from composite in query where composite.eTransaction.Instant < ((DTO.BeforeTransPred )aTransPred).Before select composite;
else if(aTransPred is DTO.OnOrAfterTransPred) query = from composite in query where composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred )aTransPred).OnOrAfter select composite;
else if(aTransPred is DTO.DateRangeTransPred){ query = from composite in query where composite.eTransaction.Instant < ((DTO.DateRangeTransPred )aTransPred).Before select composite;
query = from composite in query where composite.eTransaction.Instant >=((DTO.DateRangeTransPred )aTransPred).OnOrAfter select composite;}
else if(aTransPred is DTO.IDTransPred ) query = from composite in query where composite.eTransaction.ID ==((DTO.IDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.IDRangeTransPred ){ query = from composite in query where composite.eTransaction.ID <=((DTO.IDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.ID >=((DTO.IDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.PostIDTransPred ) query = from composite in query where composite.eTransaction.Post.ID ==((DTO.PostIDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.MaxPostIDTransPred) query = from composite in query where composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred )aTransPred).MaxID select composite;
else if(aTransPred is DTO.MinPostIDTransPred) query = from composite in query where (composite.eTransaction.Post == null
||composite.eTransaction.Post.ID >=((DTO.MinPostIDTransPred )aTransPred).MinID) select composite;
else if(aTransPred is DTO.PostedTransPred ) query = from composite in query where composite.eTransaction.Post != null select composite;
else if(aTransPred is DTO.UnPostedTransPred ) query = from composite in query where composite.eTransaction.Post == null select composite;
else throw new Exception();
return query;
}
IQueryable<NWAccntDltJoinedToTrans> AppendWhereClauses( IQueryable<NWAccntDltJoinedToTrans> query, DTO.TransPred aTransPred)
{
aTransPred = ConvertIfConvPred(aTransPred);
if( aTransPred == null ) {;}
else if(aTransPred is DTO.BeforeTransPred ) query = from composite in query where composite.eTransaction.Instant < ((DTO.BeforeTransPred )aTransPred).Before select composite;
else if(aTransPred is DTO.OnOrAfterTransPred) query = from composite in query where composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred )aTransPred).OnOrAfter select composite;
else if(aTransPred is DTO.DateRangeTransPred){ query = from composite in query where composite.eTransaction.Instant < ((DTO.DateRangeTransPred )aTransPred).Before select composite;
query = from composite in query where composite.eTransaction.Instant >=((DTO.DateRangeTransPred )aTransPred).OnOrAfter select composite;}
else if(aTransPred is DTO.IDTransPred ) query = from composite in query where composite.eTransaction.ID ==((DTO.IDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.IDRangeTransPred ){ query = from composite in query where composite.eTransaction.ID <=((DTO.IDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.ID >=((DTO.IDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.PostIDTransPred ) query = from composite in query where composite.eTransaction.Post.ID ==((DTO.PostIDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.MaxPostIDTransPred) query = from composite in query where composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred )aTransPred).MaxID select composite;
else if(aTransPred is DTO.MinPostIDTransPred) query = from composite in query where (composite.eTransaction.Post == null
||composite.eTransaction.Post.ID >=((DTO.MinPostIDTransPred )aTransPred).MinID) select composite;
else if(aTransPred is DTO.PostedTransPred ) query = from composite in query where composite.eTransaction.Post != null select composite;
else if(aTransPred is DTO.UnPostedTransPred ) query = from composite in query where composite.eTransaction.Post == null select composite;
else throw new Exception();
return query;
}
IQueryable<ItemJoinedToTrans> AppendWhereClauses( IQueryable<ItemJoinedToTrans> query, DTO.TransPred aTransPred)
{
aTransPred = ConvertIfConvPred(aTransPred);
if( aTransPred == null ) {;}
else if(aTransPred is DTO.BeforeTransPred ) query = from composite in query where composite.eTransaction.Instant < ((DTO.BeforeTransPred )aTransPred).Before select composite;
else if(aTransPred is DTO.OnOrAfterTransPred) query = from composite in query where composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred )aTransPred).OnOrAfter select composite;
else if(aTransPred is DTO.DateRangeTransPred){ query = from composite in query where composite.eTransaction.Instant < ((DTO.DateRangeTransPred )aTransPred).Before select composite;
query = from composite in query where composite.eTransaction.Instant >=((DTO.DateRangeTransPred )aTransPred).OnOrAfter select composite;}
else if(aTransPred is DTO.IDTransPred ) query = from composite in query where composite.eTransaction.ID ==((DTO.IDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.IDRangeTransPred ){ query = from composite in query where composite.eTransaction.ID <=((DTO.IDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.ID >=((DTO.IDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.PostIDTransPred ) query = from composite in query where composite.eTransaction.Post.ID ==((DTO.PostIDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.MaxPostIDTransPred) query = from composite in query where composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred )aTransPred).MaxID select composite;
else if(aTransPred is DTO.MinPostIDTransPred) query = from composite in query where (composite.eTransaction.Post == null
||composite.eTransaction.Post.ID >=((DTO.MinPostIDTransPred )aTransPred).MinID) select composite;
else if(aTransPred is DTO.PostedTransPred ) query = from composite in query where composite.eTransaction.Post != null select composite;
else if(aTransPred is DTO.UnPostedTransPred ) query = from composite in query where composite.eTransaction.Post == null select composite;
else throw new Exception();
return query;
}
IQueryable<DltAllocJoinedToItemJoinedToTrans> AppendWhereClauses( IQueryable<DltAllocJoinedToItemJoinedToTrans> query, DTO.TransPred aTransPred)
{
aTransPred = ConvertIfConvPred(aTransPred);
if( aTransPred == null ) {;}
else if(aTransPred is DTO.BeforeTransPred ) query = from composite in query where composite.eTransaction.Instant < ((DTO.BeforeTransPred )aTransPred).Before select composite;
else if(aTransPred is DTO.OnOrAfterTransPred) query = from composite in query where composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred )aTransPred).OnOrAfter select composite;
else if(aTransPred is DTO.DateRangeTransPred){ query = from composite in query where composite.eTransaction.Instant < ((DTO.DateRangeTransPred )aTransPred).Before select composite;
query = from composite in query where composite.eTransaction.Instant >=((DTO.DateRangeTransPred )aTransPred).OnOrAfter select composite;}
else if(aTransPred is DTO.IDTransPred ) query = from composite in query where composite.eTransaction.ID ==((DTO.IDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.IDRangeTransPred ){ query = from composite in query where composite.eTransaction.ID <=((DTO.IDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.ID >=((DTO.IDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.PostIDTransPred ) query = from composite in query where composite.eTransaction.Post.ID ==((DTO.PostIDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.MaxPostIDTransPred) query = from composite in query where composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred )aTransPred).MaxID select composite;
else if(aTransPred is DTO.MinPostIDTransPred) query = from composite in query where (composite.eTransaction.Post == null
||composite.eTransaction.Post.ID >=((DTO.MinPostIDTransPred )aTransPred).MinID) select composite;
else if(aTransPred is DTO.PostedTransPred ) query = from composite in query where composite.eTransaction.Post != null select composite;
else if(aTransPred is DTO.UnPostedTransPred ) query = from composite in query where composite.eTransaction.Post == null select composite;
else throw new Exception();
return query;
}
IQueryable<TransactionWrapper> AppendWhereClauses( IQueryable<TransactionWrapper> query, DTO.TransPred aTransPred)
{
aTransPred = ConvertIfConvPred(aTransPred);
if( aTransPred == null ) {;}
else if(aTransPred is DTO.BeforeTransPred ) query = from composite in query where composite.eTransaction.Instant < ((DTO.BeforeTransPred )aTransPred).Before select composite;
else if(aTransPred is DTO.OnOrAfterTransPred) query = from composite in query where composite.eTransaction.Instant >=((DTO.OnOrAfterTransPred )aTransPred).OnOrAfter select composite;
else if(aTransPred is DTO.DateRangeTransPred){ query = from composite in query where composite.eTransaction.Instant < ((DTO.DateRangeTransPred )aTransPred).Before select composite;
query = from composite in query where composite.eTransaction.Instant >=((DTO.DateRangeTransPred )aTransPred).OnOrAfter select composite;}
else if(aTransPred is DTO.IDTransPred ) query = from composite in query where composite.eTransaction.ID ==((DTO.IDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.IDRangeTransPred ){ query = from composite in query where composite.eTransaction.ID <=((DTO.IDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.ID >=((DTO.IDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.PostIDTransPred ) query = from composite in query where composite.eTransaction.Post.ID ==((DTO.PostIDTransPred )aTransPred).ID select composite;
else if(aTransPred is DTO.PostIDRangeTransPred){query = from composite in query where composite.eTransaction.Post.ID <=((DTO.PostIDRangeTransPred )aTransPred).LargestID select composite;
query = from composite in query where composite.eTransaction.Post.ID >=((DTO.PostIDRangeTransPred )aTransPred).SmallestID select composite;}
else if(aTransPred is DTO.MaxPostIDTransPred) query = from composite in query where composite.eTransaction.Post.ID <=((DTO.MaxPostIDTransPred )aTransPred).MaxID select composite;
else if(aTransPred is DTO.MinPostIDTransPred) query = from composite in query where (composite.eTransaction.Post == null
||composite.eTransaction.Post.ID >=((DTO.MinPostIDTransPred )aTransPred).MinID) select composite;
else if(aTransPred is DTO.PostedTransPred ) query = from composite in query where composite.eTransaction.Post != null select composite;
else if(aTransPred is DTO.UnPostedTransPred ) query = from composite in query where composite.eTransaction.Post == null select composite;
else throw new Exception();
return query;
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=515 -->
<!-- MANAGED HEADING END -->
Here is the criteria setting function for NHibernate:
<!-- MANAGED CODEWINDOW START ID=049 -->
CodeWindow 049
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
void AddTransactionCriteria(nh.ICriteria aCriteria, DTO.TransPred aTransPred, bool bIncludeSort)
{
aTransPred = ConvertIfConvPred(aTransPred);
dal.Transaction oTransactionAlias = null;
if( aTransPred == null ) {;}
else if(aTransPred is DTO.BeforeTransPred ) aCriteria.Add(()=>oTransactionAlias.Instant < ((DTO.BeforeTransPred )aTransPred).Before);
else if(aTransPred is DTO.OnOrAfterTransPred) aCriteria.Add(()=>oTransactionAlias.Instant >= ((DTO.OnOrAfterTransPred )aTransPred).OnOrAfter);
else if(aTransPred is DTO.DateRangeTransPred){ aCriteria.Add(()=>oTransactionAlias.Instant < ((DTO.DateRangeTransPred )aTransPred).Before);
aCriteria.Add(()=>oTransactionAlias.Instant >= ((DTO.DateRangeTransPred )aTransPred).OnOrAfter);}
else if(aTransPred is DTO.IDTransPred ) aCriteria.Add(()=>oTransactionAlias.ID == ((DTO.IDTransPred )aTransPred).ID);
else if(aTransPred is DTO.IDRangeTransPred){ aCriteria.Add(()=>oTransactionAlias.ID <= ((DTO.IDRangeTransPred )aTransPred).LargestID);
aCriteria.Add(()=>oTransactionAlias.ID >= ((DTO.IDRangeTransPred )aTransPred).SmallestID);}
else if(aTransPred is DTO.PostIDTransPred) aCriteria.Add(()=>oTransactionAlias.ThePost.ID == ((DTO.PostIDTransPred )aTransPred).ID);
else if(aTransPred is DTO.PostIDRangeTransPred){aCriteria.Add(()=>oTransactionAlias.ThePost.ID <= ((DTO.PostIDRangeTransPred)aTransPred).LargestID);
aCriteria.Add(()=>oTransactionAlias.ThePost.ID >= ((DTO.PostIDRangeTransPred)aTransPred).SmallestID);}
else if(aTransPred is DTO.MaxPostIDTransPred) aCriteria.Add(()=>oTransactionAlias.ThePost.ID <= ((DTO.MaxPostIDTransPred )aTransPred).MaxID);
else if(aTransPred is DTO.MinPostIDTransPred)
{
nh.Criterion.AbstractCriterion orExpressions = nh.Criterion.Restrictions.Or(
SqlExpression.IsNull(()=>oTransactionAlias.ThePost),
SqlExpression.CriterionFor(()=>oTransactionAlias.ThePost.ID >= ((DTO.MinPostIDTransPred)aTransPred).MinID));
aCriteria.Add(orExpressions);
}
else if(aTransPred is DTO.PostedTransPred ) aCriteria.Add(SqlExpression.IsNotNull( ()=>oTransactionAlias.ThePost));
else if(aTransPred is DTO.UnPostedTransPred ) aCriteria.Add(SqlExpression.IsNull( ()=>oTransactionAlias.ThePost));
else throw new Exception();
if(bIncludeSort)
{
aCriteria.AddOrder(()=>oTransactionAlias.Instant, nh.Criterion.Order.Desc);
aCriteria.AddOrder(()=>oTransactionAlias.ID , nh.Criterion.Order.Desc);
}
}
<!-- MANAGED CODEWINDOW END -->
Notice that the signature and structure are very similar to the NHibernate criteria setting function shown previously for the example State and City entities.
<!-- MANAGED HEADING START ID=520 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END --> <!-- MANAGED HEADING START ID=525 -->
<!-- MANAGED HEADING END -->
Here are the five queries for LINQ to SQL:
<!-- MANAGED CODEWINDOW START ID=050 -->
CodeWindow 050
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
IList<dal.WashDlt> GetWashDltDAOsForTransactions( dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
var query =
from eWashDlt in ctx.WashDlts
join eTransaction in ctx.Transactions on eWashDlt.TransactionID equals eTransaction.ID
select new WashDltJoinedToTrans{eWashDlt=eWashDlt, eTransaction=eTransaction};
query = AppendWhereClauses(query, aTransPred);
return (
from eComposite in query
orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eWashDlt.ParticipantID ascending
select eComposite.eWashDlt).ToList();
}
IList<dal.NWAccntDlt> GetNWAccntDltDAOsForTransactions( dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
var query =
from eNWAccntDlt in ctx.NWAccntDlts
join eTransaction in ctx.Transactions on eNWAccntDlt.TransactionID equals eTransaction.ID
select new NWAccntDltJoinedToTrans{eNWAccntDlt=eNWAccntDlt, eTransaction=eTransaction};
query = AppendWhereClauses(query, aTransPred);
return (
from eComposite in query
orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eNWAccntDlt.NWAccntID ascending
select eComposite.eNWAccntDlt).ToList();
}
IList<dal.Item> GetItemDAOsForTransactions( dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
var query =
from eItem in ctx.Items
join eTransaction in ctx.Transactions on eItem.TransactionID equals eTransaction.ID
select new ItemJoinedToTrans{eItem=eItem, eTransaction=eTransaction};
query = AppendWhereClauses(query, aTransPred);
return (
from eComposite in query
orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eItem.ID ascending
select eComposite.eItem).ToList();
}
IList<dal.DltAlloc> GetDltAllocDAOsForTransactions( dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
var query =
from eDltAlloc in ctx.DltAllocs
join eItem in ctx.Items on eDltAlloc.ItemID equals eItem.ID
join eTransaction in ctx.Transactions on eItem.TransactionID equals eTransaction.ID
select new DltAllocJoinedToItemJoinedToTrans{eDltAlloc=eDltAlloc, eItem=eItem, eTransaction=eTransaction};
query = AppendWhereClauses(query, aTransPred);
return (
from eComposite in query
orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eItem.ID ascending, eComposite.eDltAlloc.ParticipantID ascending
select eComposite.eDltAlloc).ToList();
}
IList<dal.Transaction> GetTransactionDAOsForTransactions( dal.DataClassesDataContext ctx, DTO.TransPred aTransPred)
{
var query = from dal.Transaction eTransaction in ctx.Transactions select new TransactionWrapper{eTransaction=eTransaction};
query = AppendWhereClauses(query, aTransPred);
return (from eWrapper in query orderby eWrapper.eTransaction.Instant descending, eWrapper.eTransaction.ID descending select eWrapper.eTransaction).ToList();
}
<!-- MANAGED CODEWINDOW END -->
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..
<!-- MANAGED HEADING START ID=530 -->
<!-- MANAGED HEADING END -->
Here are the five queries for Entity Framework:
<!-- MANAGED CODEWINDOW START ID=051 -->
CodeWindow 051
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
IList<dal.WashDlt> GetWashDltDAOsForTransactions( dal.LekEntities ctx, DTO.TransPred aTransPred)
{
var query =
from eWashDlt in ctx.WashDlt
select new WashDltJoinedToTrans{eWashDlt=eWashDlt, eTransaction=eWashDlt.Transaction};
query = AppendWhereClauses(query, aTransPred);
return (
from eComposite in query
orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eWashDlt.Participant.ID ascending
select eComposite.eWashDlt).ToList();
}
IList<dal.NWAccntDlt> GetNWAccntDltDAOsForTransactions( dal.LekEntities ctx, DTO.TransPred aTransPred)
{
var query =
from eNWAccntDlt in ctx.NWAccntDlt
select new NWAccntDltJoinedToTrans{eNWAccntDlt=eNWAccntDlt, eTransaction=eNWAccntDlt.Transaction};
query = AppendWhereClauses(query, aTransPred);
return (
from eComposite in query
orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eNWAccntDlt.NWAccnt.ID ascending
select eComposite.eNWAccntDlt).ToList();
}
IList<dal.Item> GetItemDAOsForTransactions( dal.LekEntities ctx, DTO.TransPred aTransPred)
{
var query =
from eItem in ctx.Item
select new ItemJoinedToTrans{eItem=eItem, eTransaction=eItem.Transaction};
query = AppendWhereClauses(query, aTransPred);
return (
from eComposite in query
orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eItem.ID ascending
select eComposite.eItem).ToList();
}
IList<dal.DltAlloc> GetDltAllocDAOsForTransactions( dal.LekEntities ctx, DTO.TransPred aTransPred)
{
var query =
from eDltAlloc in ctx.DltAlloc
select new DltAllocJoinedToItemJoinedToTrans{eDltAlloc=eDltAlloc, eItem=eDltAlloc.Item, eTransaction=eDltAlloc.Item.Transaction};
query = AppendWhereClauses(query, aTransPred);
return (
from eComposite in query
orderby eComposite.eTransaction.Instant descending, eComposite.eTransaction.ID descending, eComposite.eItem.ID ascending, eComposite.eDltAlloc.Participant.ID ascending
select eComposite.eDltAlloc).ToList();
}
IList<dal.Transaction> GetTransactionDAOsForTransactions( dal.LekEntities ctx, DTO.TransPred aTransPred)
{
var query = from dal.Transaction eTransaction in ctx.Transaction select new TransactionWrapper{eTransaction=eTransaction};
query = AppendWhereClauses(query, aTransPred);
return (from eWrapper in query orderby eWrapper.eTransaction.Instant descending, eWrapper.eTransaction.ID descending select eWrapper.eTransaction).ToList();
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=535 -->
<!-- MANAGED HEADING END -->
Here are the five queries for NHibernate:
<!-- MANAGED CODEWINDOW START ID=052 -->
CodeWindow 052
<!--style="width:650px; height:300px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
IList<dal.WashDlt> GetWashDltDAOsForTransactions( nh.ISession ctx, DTO.TransPred aTransPred)
{
dal.Transaction oTransactionAlias =null;
nh.ICriteria aCriteria = ctx.CreateCriteria(typeof(dal.WashDlt));
aCriteria.CreateAlias<dal.WashDlt>(o=>o.TheTransaction, ()=>oTransactionAlias);
AddTransactionCriteria(aCriteria, aTransPred, true);
aCriteria.AddOrder<dal.WashDlt>(o=>o.TheParticipant.ID, nh.Criterion.Order.Asc);
return aCriteria.List<dal.WashDlt>();
}
IList<dal.NWAccntDlt> GetNWAccntDltDAOsForTransactions( nh.ISession ctx, DTO.TransPred aTransPred)
{
dal.Transaction oTransactionAlias =null;
nh.ICriteria aCriteria = ctx.CreateCriteria(typeof(dal.NWAccntDlt));
aCriteria.CreateAlias<dal.NWAccntDlt>(o=>o.TheTransaction, ()=>oTransactionAlias);
AddTransactionCriteria(aCriteria, aTransPred, true);
aCriteria.AddOrder<dal.NWAccntDlt>(o=>o.TheNWAccnt.ID, nh.Criterion.Order.Asc);
return aCriteria.List<dal.NWAccntDlt>();
}
IList<dal.Item> GetItemDAOsForTransactions( nh.ISession ctx, DTO.TransPred aTransPred)
{
dal.Transaction oTransactionAlias =null;
nh.ICriteria aCriteria = ctx.CreateCriteria(typeof(dal.Item));
aCriteria.CreateAlias<dal.Item>(o=>o.TheTransaction, ()=>oTransactionAlias);
AddTransactionCriteria(aCriteria, aTransPred, true);
aCriteria.AddOrder<dal.Item>(o=>o.ID, nh.Criterion.Order.Asc);
return aCriteria.List<dal.Item>();
}
IList<dal.DltAlloc> GetDltAllocDAOsForTransactions( nh.ISession ctx, DTO.TransPred aTransPred)
{
dal.Transaction oTransactionAlias =null;
nh.ICriteria aCriteria = ctx.CreateCriteria(typeof(dal.DltAlloc));
dal.Item oItemAlias =null;
aCriteria = aCriteria .CreateAlias<dal.DltAlloc >( o=>o.TheItem ,()=>oItemAlias );
aCriteria = aCriteria .CreateAlias<dal.DltAlloc >( o=>o.TheItem.TheTransaction ,()=>oTransactionAlias );
AddTransactionCriteria(aCriteria, aTransPred, true);
aCriteria.AddOrder<dal.DltAlloc>(o=>o.TheItem.ID , nh.Criterion.Order.Asc);
aCriteria.AddOrder<dal.DltAlloc>(o=>o.TheParticipant.ID , nh.Criterion.Order.Asc);
return aCriteria.List<dal.DltAlloc>();
}
IList<dal.Transaction> GetTransactionDAOsForTransactions( nh.ISession ctx, DTO.TransPred aTransPred)
{
dal.Transaction oTransactionAlias =null;
nh.Criterion.DetachedCriteria aDetCriteria = nhl.DetachedCriteria<dal.Transaction>.Create(()=>oTransactionAlias);
nh.ICriteria aCriteria = aDetCriteria.GetExecutableCriteria(ctx);
AddTransactionCriteria(aCriteria, aTransPred, true);
return aCriteria.List<dal.Transaction>();
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=540 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=545 -->
<!-- MANAGED HEADING END -->
The SQL generated by LINQ to SQL couldn't be much simpler:
<!-- MANAGED CODEWINDOW START ID=053 -->
CodeWindow 053
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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]
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=550 -->
<!-- MANAGED HEADING END -->
The SQL generated by Entity Framework is more complex:
<!-- MANAGED CODEWINDOW START ID=054 -->
CodeWindow 054
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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
<!-- MANAGED CODEWINDOW END -->
I am not sure why the Entity Framework uses left outer joins when inner joins will due. This query is significantly slower than the LINQ to SQL version on my machine when executed against a database with a few thousand records in it (yes, I really do use this system). If the LINQ statement is written to contain joins like the LINQ to SQL version is then the generated SQL is even more complex and even slower. I suspect that the complexity of the generated SQL has something to do with an attempt to create SQL that will work on a variety of database servers. Or maybe the complex query is better at handling certain error conditions. I really can't say. I hope that the version of Entity Framework due out with .NET 4.0 will generate simpler and faster SQL for this sort of query.
<!-- MANAGED HEADING START ID=555 -->
<!-- MANAGED HEADING END -->
The SQL generated by NHibernate is nearly as simple as that generated by LINQ to SQL:
<!-- MANAGED CODEWINDOW START ID=055 -->
CodeWindow 055
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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
<!-- MANAGED CODEWINDOW END -->
However, it does unnecessarily include fields for Item and Transaction entities, which were not requested in the NHibernate Criteria query.
<!-- MANAGED HEADING START ID=560 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
To save a new Transaction or to modify an existing Transaction, a LEK client calls the SaveTransaction() function declared on the ServiceInterface.IService interface:
<!-- MANAGED CODEWINDOW START ID=056 -->
CodeWindow 056
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
[sm.OperationContract]
[sm.FaultContract(typeof(DTO.ConcurrencyFault))]
int SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance);
<!-- MANAGED CODEWINDOW END -->
This function is implemented by each of the three Service classes (one for each ORM). For each of the ORM's the function deals with three distinct entity types:
- DTO.Transaction: for transferring data between tiers
- DominUtil.Transaction: for enforcing the business rules of a Transaction
- Dal*.Transaction (ORM class): for packaging up Transaction data to be sent to the database, or Transaction data read from the database.
DomainUtil.Transaction is in a namespace that is accessible to both client and server, and is used to validate Transaction data on both the client and the server. The client creates a DomainUtil.Transaction object from the data collected by the UI. The client will then transform this into a DTO.Transaction before sending it to the server. DTO.Transaction objects are optimized for data transmission. They contain no logic, and only the data essential to describe a Transaction to the server (account IDs are included but not account names.) Once the server receives the DTO.Transaction, it will transform it back into a DomainUtil.Transaction, possibly re-validate the data, and then transform it into a Dal*.Transaction (an ORM entity object) to save it to the database.
Saving a new Transaction to the database, or an existing Transaction with modified child records, requires more than just a single ORM entity object. NWAccntDlts, WashDlts, Items, and DltAllocs will need to be saved as well. If the Transaction is new this is a simple matter: all the child entities will need to be added. If the Transaction is an edit to a Transaction already in the database then saving has the potential to be much more complex. In that case the child entities could be new, edited, or unchanged. Also, in that case, the Transaction entity would need to carry an indication of child entities that were part of it but no longer are - deleted entities.
The Transaction ORM class for each ORM includes members for the child entity objects. These child objects, in coordination with an ORM context, keep track of whether they are new, modified, unchanged, or deleted. Saving an ORM Transaction object complete with child objects in any of these change states is easy if all the additions, edits and deletions occurred during the lifetime of an existing ORM context, and if that ORM context was used to retrieve the data prior to making the changes. In that case you just tell the ORM context to save the entity and it handles all of the details. Context lifetimes however are supposed to be short, and in a n-tier application entity objects typically pick up their changes on the client were they may be for any amount of time (waiting on the user). Also, when we don't particularly like the public interface exposed by the ORM classes we may not want the client to even have the ORM entity objects.
In LEK the client deals with entity objects made specifically for the client - without any dependency on an ORM (the classes are in the DomainUtil namespace). Furthermore when it sends data to the server it sends it as objects specifically made for transferring data (the classes are in the DTO namespace). For the server to properly deal with entity data like that it must simulate on the server what happened on the client. One way to do this would be for the server to retrieve a fresh copy of all the Transaction data (as ORM entities) from the database and then "replay" what happened on the client. Of coarse, to do this, the server would have to receive data transfer objects that included state tracking information (what's new, what's changed, what's deleted). Also, timestamps or row versions would need to be employed so that the server could tell if the database data changed since the snapshot was formed and sent to the client. Having all this information at hand the server would walk the object graph of both the ORM entities fresh from the database and the entities received from the client. It would walk both these object graphs in a coordinated manner, updating the ORM entities based on the entities received from the client. It would then submit the modified Transaction ORM entity (with child entities) to the ORM context just used to retrieve them, which would properly handle all additions, updates, and deletions.
The main reason this approach was not taken in LEK was because I don't want the server to have to query for each of the objects to "replay" the changes on. This would take time and server resources, and if I can get something equally functional that is more efficient (I think I did), that would be preferable.
So, if we already have all the data we need from the client (including what is new and what is modified), why do we need to query it from the database? Well, the short answer is that you don't, or at least you shouldn't. In each ORM updating the database from an object that is created, rather than recently queried requires that the created object be "attached" to the context. This is straight forward in NHibernate, a bit difficult in LINQ to SQL (I think each child object must be attached individually), but nearly impossible in the version of Entity Framework that comes with .NET 3.5 SP1. The problems with doing that in Entity Framework have to do with what are considered to be the valid state transitions for entities. Changing the state of a child entity from "unchanged" (the state it gets when attached) to "added" is not allowed. (Supposedly Entity Framework in .NET 4.0 won't have this problem.)
The problems some of the ORMs have with attaching are mostly with attaching a mixture of new and modified child entities of a modified parent entity. Adding an object graph in which everything is new is not a problem in any of the ORMs. Attaching a object graph in which the root is modified and all of the child objects are new is easy in NHibernate and LINQ to SQL, but I couldn't get it to work in Entity Framework. (Accepting suggestions!)
<!-- MANAGED HEADING START ID=565 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=570 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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).
<!-- MANAGED HEADING START ID=575 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.)
<!-- MANAGED HEADING START ID=580 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
In addition to typical ORM operations, LEK uses two stored procedures when it updates a Transaction. Both are related to the way that concurrency is addressed. Rather than using a timestamp or row version for each entity that makes up a Transaction, LEK only uses timestamp on the Transaction entity. Anytime any change is made to a Transaction, even if the Transaction entity itself is unchanged and only a single descendant entity is modified (like a DltAlloc), LEK updates the Transaction entity in the database. A timestamp field is setup in the database in the Transaction table, so the database will then update this timestamp field. The Transaction timestamp field will thus be a stand-in for the version of the Transaction as a whole.
<!-- MANAGED HEADING START ID=590 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=057 -->
CodeWindow 057
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
CREATE PROCEDURE [dbo].[GetTransactionTimestamp]
@ID int,
@Timestamp timestamp OUTPUT
AS
SET @Timestamp = (
SELECT timestamp FROM dbo.[Transaction]
WHERE ID = @ID
)
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=595 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Probably the typical way child objects are deleted using an ORM is to query the ORM for the full object graph of the object being deleted, delete the child objects from the graph, and then submit it back to the ORM context. The ORM will then delete each database entity for which the corresponding ORM entity was deleted from the graph, so long as an defined concurrency related fields don't indicate that the ORM had a stale version of the object. In LEK however, since the timestamp field in the Transaction entity will be used to track the version of the Transaction as a whole, there is no need to perform a concurrency check on each of the child entities. We can instead just delete them all in one shot - so long as we don't mind recreating the ones that didn't have to be deleted in the first place.
The DeleteTransactionChildRecords stored procedure does the job:
<!-- MANAGED CODEWINDOW START ID=058 -->
CodeWindow 058
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
CREATE PROCEDURE [dbo].[DeleteTransactionChildRecords]
@TransactionID int
AS
BEGIN
-- SET NOCOUNT ON;
DELETE DltAlloc FROM DltAlloc INNER JOIN Item ON DltAlloc.ItemID = Item.ID WHERE Item.TransactionID = @TransactionID
DELETE FROM WashDlt WHERE WashDlt.TransactionID = @TransactionID
DELETE FROM NWAccntDlt WHERE NWAccntDlt.TransactionID = @TransactionID
DELETE FROM Item WHERE Item.TransactionID = @TransactionID
END
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=600 -->
<!-- MANAGED HEADING END -->
In a sense, this section is about code to make up for deficiencies in an ORM. The top level functions, presented later, are very similar for each ORM. The reason that they can be is because most the differences are accounted for in the functions described in this section.
<!-- MANAGED HEADING START ID=605 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
LINQ to SQL is Microsoft's first pass at a production ready ORM. They didn't delve to deeply into any hard issues or attempt to achieve something that would work on every database. However, for doing simple things, it is very easy to use. When it comes to updating the database, the other ORMs don't do anything in a manner simpler than the way LINQ to SQL does it, so there is no code to present for LINQ to SQL in this section.
<!-- MANAGED HEADING START ID=610 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=615 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=059 -->
CodeWindow 059
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=620 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=060 -->
CodeWindow 060
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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;
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=625 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=061 -->
CodeWindow 061
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=630 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
In LINQ to SQL, the function used to attach freshly created ORM entity to the context has a parameter to indicate whether or not the object should be considered modified. NHibernate provides the function Update() to do the same thing. With Entity Framework we must indicate each field that has updated data. This is great if the entity object has 30 fields and only one is going to be updated. For the simple case where we want them all updated however, the Enity Framework way is more complicated than it has to be. SetAllPropsAsModified() provides the simple typical operation where we just want Entity Framework to consider nearly all the fields to contain modified data:
<!-- MANAGED CODEWINDOW START ID=062 -->
CodeWindow 062
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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);
}
}
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=635 -->
<!-- MANAGED HEADING END -->
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().
<!-- MANAGED HEADING START ID=640 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=063 -->
CodeWindow 063
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
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});
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=645 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
In both Entity Framework and LINQ to SQL setting up a new entity that is mapped as a child to another entity only required that the new child entity be added to the child collection in the parent entity. It was not also necessary to setup the foreign key in the new child to the parent. With NHibernate you do. The function couldn't be simpler though. I only made it a function to make it easier to contrast against EnsureFKWithInteger(). Here is EnsureFKWithEntity():
<!-- MANAGED CODEWINDOW START ID=064 -->
CodeWindow 064
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
public static void EnsureFKWithEntity<T>(s.Action<T> delSet, T parent)
{
delSet(parent);
}
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=650 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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 your browser is showing scroll bars for the windows you may want to scroll each version of a function such that the first line shown in each has the same number.
<!-- MANAGED HEADING START ID=655 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=660 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=065 -->
CodeWindow 065
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: public static s.Data.Linq.Binary CreateORMRowVersion(gt.RowVersion aRowVersion)
2: {
3: if(aRowVersion==null)
4: return null;
5: return new System.Data.Linq.Binary(aRowVersion.GetCopyOfData());
6: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=665 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=066 -->
CodeWindow 066
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: public static Byte[] CreateORMRowVersion(gt.RowVersion aRowVersion)
2: {
3: if(aRowVersion==null)
4: return null;
5: return aRowVersion.GetCopyOfData();
6: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=670 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
GetTransactionTimestamp() and DeleteTransactionChildRecords() are both simple wrappers for like stored procedures with the same name. (See the Stored Procedure section above for a discussion of the need for these two functions.
<!-- MANAGED HEADING START ID=675 -->
<!-- MANAGED HEADING END -->
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:
<!-- MANAGED CODEWINDOW START ID=067 -->
CodeWindow 067
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: gt.RowVersion GetTransactionTimestamp(int iTransactionID, dal.DataClassesDataContext ctx)
2: {
3: s.Data.Linq.Binary timestamp = null;
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17: int iRC = ctx.GetTransactionTimestamp(iTransactionID, ref timestamp);
18: if(iRC != 0)
19: throw new Exception();
20:
21:
22: return new gt.RowVersion(timestamp.ToArray());
23: }
24: void DeleteTransactionChildRecords(int iTransactionID, dal.DataClassesDataContext ctx)
25: {
26:
27:
28:
29:
30:
31:
32:
33:
34: ctx.DeleteTransactionChildRecords(iTransactionID);
35: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=680 -->
<!-- MANAGED HEADING END -->
Wrapper functions for the stored procedures were created manually for Entity Framework:
<!-- MANAGED CODEWINDOW START ID=068a -->
CodeWindow 068a
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: gt.RowVersion GetTransactionTimestamp(int iTransactionID, dal.LekEntities ctx)
2: {
3: byte[] bytes = null;
4: s.Data.EntityClient.EntityConnection aEntityConnection = (s.Data.EntityClient.EntityConnection)ctx.Connection;
5: s.Data.Common.DbConnection conn = aEntityConnection.StoreConnection;
6: s.Data.IDbCommand cmd = conn.CreateCommand();
7:
8: cmd.CommandText = "GetTransactionTimestamp";
9: cmd.CommandType = System.Data.CommandType.StoredProcedure;
10:
11: cmd.Parameters.Add(new s.Data.SqlClient.SqlParameter("@ID", iTransactionID));
12:
13: s.Data.SqlClient.SqlParameter output = new System.Data.SqlClient.SqlParameter("@Timestamp", s.Data.SqlDbType.Timestamp);
14: output.Direction = System.Data.ParameterDirection.Output;
15: cmd.Parameters.Add(output);
16:
17: int iRC = cmd.ExecuteNonQuery();
18: if(iRC!=-1)
19: throw new s.Exception();
20:
21: bytes= (byte[])output.Value;
22: return new gt.RowVersion(bytes);
23: }
24: void DeleteTransactionChildRecords(int iTransactionID, dal.LekEntities ctx)
25: {
26: s.Data.EntityClient.EntityConnection aEntityConnection = (s.Data.EntityClient.EntityConnection)ctx.Connection;
27: s.Data.Common.DbConnection conn = aEntityConnection.StoreConnection;
28: s.Data.IDbCommand cmd = conn.CreateCommand();
29:
30: cmd.CommandText = "DeleteTransactionChildRecords";
31: cmd.CommandType = System.Data.CommandType.StoredProcedure;
32: cmd.Parameters.Add(new s.Data.SqlClient.SqlParameter("@TransactionID", iTransactionID));
33:
34: cmd.ExecuteNonQuery();
35: }
<!-- MANAGED CODEWINDOW END -->
Note that obtaining the connection object to create the command object required a bit of "digging" (lines 4, 5, 27, 28)
<!-- MANAGED HEADING START ID=685 -->
<!-- MANAGED HEADING END -->
Wrapper functions for the stored procedures were also created manually for NHibernate, and is nearly identical to the one for Entity Framework:
<!-- MANAGED CODEWINDOW START ID=068b -->
CodeWindow 068b
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: gt.RowVersion GetTransactionTimestamp(int iTransactionID, nh.ISession ctx)
2: {
3: byte[] bytes= null;
4:
5:
6: s.Data.IDbCommand cmd = ctx.Connection.CreateCommand();
7: ctx.Transaction.Enlist(cmd);
8: cmd.CommandText = "GetTransactionTimestamp";
9: cmd.CommandType = System.Data.CommandType.StoredProcedure;
10:
11: cmd.Parameters.Add(new s.Data.SqlClient.SqlParameter("@ID", iTransactionID));
12:
13: s.Data.SqlClient.SqlParameter output = new System.Data.SqlClient.SqlParameter("@Timestamp", s.Data.SqlDbType.Timestamp);
14: output.Direction = System.Data.ParameterDirection.Output;
15: cmd.Parameters.Add(output);
16:
17: int iRC = cmd.ExecuteNonQuery();
18: if(iRC!=-1)
19: throw new s.Exception();
20:
21: bytes= (byte[])output.Value;
22: return new gt.RowVersion(bytes);
23: }
24: void DeleteTransactionChildRecords(int iTransactionID, nh.ISession ctx)
25: {
26:
27:
28: s.Data.IDbCommand cmd = ctx.Connection.CreateCommand();
29: ctx.Transaction.Enlist(cmd);
30: cmd.CommandText = "DeleteTransactionChildRecords";
31: cmd.CommandType = System.Data.CommandType.StoredProcedure;
32: cmd.Parameters.Add(new s.Data.SqlClient.SqlParameter("@TransactionID", iTransactionID));
33:
34: cmd.ExecuteNonQuery();
35: }
<!-- MANAGED CODEWINDOW END -->
Note that creation of the command object in line 6 is easier than in was in Entity Framework. Also note that, unlike with Entity Framework, it is possible to explicitly enlist the command into the transaction (line 7 and 29).
<!-- MANAGED HEADING START ID=690 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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).
<!-- MANAGED HEADING START ID=695 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=069 -->
CodeWindow 069
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: dal.Transaction CreateTransactionReadyToAttach(int iTransactionID, gt.RowVersion aRowVersion)
2: {
3: dal.Transaction oDal = new DalLinqToSql.Transaction();
4: oDal.ID = iTransactionID;
5:
6: oDal.timestamp = Helper.CreateORMRowVersion(aRowVersion);
7: return oDal;
8: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=700 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=070 -->
CodeWindow 070
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: dal.Transaction CreateTransactionReadyToAttach(int iTransactionID, gt.RowVersion aRowVersion)
2: {
3: dal.Transaction oDal = new DalEF.Transaction();
4: oDal.ID = iTransactionID;
5: oDal.EntityKey = Helper.CreateEntityKey(iTransactionID,typeof(dal.LekEntities).Name + "." + typeof(dal.Transaction).Name);
6: oDal.timestamp = Helper.CreateORMRowVersion(aRowVersion);
7: return oDal;
8: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=705 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=071 -->
CodeWindow 071
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: dal.Transaction CreateTransactionReadyToAttach(int iTransactionID, gt.RowVersion aRowVersion)
2: {
3: dal.Transaction oDal = new DalNH.Transaction();
4: oDal.ID = iTransactionID;
5:
6: oDal.timestamp = Helper.CreateORMRowVersion(aRowVersion);
7: return oDal;
8: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=710 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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:
<!-- MANAGED HEADING START ID=715 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=072 -->
CodeWindow 072
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: bool TransactionRowVersionMatches(int iTransactionID, dal.DataClassesDataContext ctx, gt.RowVersion aRowVersion)
2: {
3: gt.RowVersion oCurrentRowVersion = GetTransactionTimestamp(iTransactionID, ctx);
4: return gt.RowVersion.ValueEquals(oCurrentRowVersion, aRowVersion);
5: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=720 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=073 -->
CodeWindow 073
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: bool TransactionRowVersionMatches(int iTransactionID, dal.LekEntities ctx, gt.RowVersion aRowVersion)
2: {
3: gt.RowVersion oCurrentRowVersion = GetTransactionTimestamp(iTransactionID, ctx);
4: return gt.RowVersion.ValueEquals(oCurrentRowVersion, aRowVersion);
5: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=725 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=074 -->
CodeWindow 074
<!--none --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: bool TransactionRowVersionMatches(int iTransactionID, nh.ISession ctx, gt.RowVersion aRowVersion)
2: {
3: gt.RowVersion oCurrentRowVersion = GetTransactionTimestamp(iTransactionID, ctx);
4: return gt.RowVersion.ValueEquals(oCurrentRowVersion, aRowVersion);
5: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=730 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
SetDALObjectFromDomainObject() sets all the fields of an ORM Transaction entity (including child entity collections) from a business object Transaction entity (DomainUtil namespace).
<!-- MANAGED HEADING START ID=735 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=075 -->
CodeWindow 075
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: void SetDALObjectFromDomainObject(du.ITransaction oDmn, dal.Transaction oDal, dal.DataClassesDataContext ctx)
2: {
3: oDal.Descrip = oDmn.Descrip;
4: oDal.Instant = oDmn.Instant;
5: oDal.ParticipantID = oDmn.ParticipantID;
6: foreach (DTO.Item aItem in oDmn.Items)
7: {
8: dal.Item aDALItem = new dal.Item();
9: aDALItem.Descrip = aItem.Descrip;
10: aDALItem.DltAllocAccntID = aItem.DltAllocAccntID;
11:
12: for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
13: {
14: int iParticipantAmount = aItem.ParticipantAmounts[iParticipant];
15: if (iParticipantAmount == 0)
16: continue;
17: dal.DltAlloc aDALDltAlloc = new dal.DltAlloc();
18: aDALDltAlloc.Amount = iParticipantAmount;
19: int iParticipantID = oDmn.GetParticipantID(iParticipant);
20: aDALDltAlloc.ParticipantID = iParticipantID;
21:
22: aDALItem.DltAllocs.Add(aDALDltAlloc);
23: }
24: oDal.Items.Add(aDALItem);
25: }
26: foreach (DTO.NWAccntDlt aNWAccntDlt in oDmn.NWAccntDlts)
27: {
28: dal.NWAccntDlt aDALNWAccntDlt = new dal.NWAccntDlt();
29: aDALNWAccntDlt.Amount = aNWAccntDlt.Amount;
30: aDALNWAccntDlt.NWAccntID = aNWAccntDlt.NWAccntID;
31:
32: oDal.NWAccntDlts.Add(aDALNWAccntDlt);
33: }
34: for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
35: {
36: int iParticipantWashAmount = oDmn.GetWashAmount(iParticipant);
37: if (iParticipantWashAmount == 0)
38: continue;
39: dal.WashDlt aDALWashDlt = new dal.WashDlt();
40: aDALWashDlt.Amount = iParticipantWashAmount;
41: int iParticipantID = oDmn.GetParticipantID(iParticipant);
42: aDALWashDlt.ParticipantID = iParticipantID;
43:
44: oDal.WashDlts.Add(aDALWashDlt);
45: }
46: }
<!-- MANAGED CODEWINDOW END -->
Note that in lines 5, 10, 20, 30, and 42 a foreign key is setup simply by setting the mapped foreign key integer field.
<!-- MANAGED HEADING START ID=740 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=076 -->
CodeWindow 076
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: void SetDALObjectFromDomainObject(du.ITransaction oDmn, dal.Transaction oDal, dal.LekEntities ctx)
2: {
3: oDal.Descrip = oDmn.Descrip;
4: oDal.Instant = oDmn.Instant;
5: Helper.EnsureFK(oDal.ParticipantReference, ctx, oDmn.ParticipantID);
6: foreach (DTO.Item aItem in oDmn.Items)
7: {
8: dal.Item aDALItem = new dal.Item();
9: aDALItem.Descrip = aItem.Descrip;
10: Helper.EnsureFK(aDALItem.DltAllocAccntReference, ctx, aItem.DltAllocAccntID);
11:
12: for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
13: {
14: int iParticipantAmount = aItem.ParticipantAmounts[iParticipant];
15: if (iParticipantAmount == 0)
16: continue;
17: dal.DltAlloc aDALDltAlloc = new dal.DltAlloc();
18: aDALDltAlloc.Amount = iParticipantAmount;
19: int iParticipantID = oDmn.GetParticipantID(iParticipant);
20: Helper.EnsureFK(aDALDltAlloc.ParticipantReference, ctx, iParticipantID);
21:
22: aDALItem.DltAlloc.Add(aDALDltAlloc);
23: }
24: oDal.Item.Add(aDALItem);
25: }
26: foreach (DTO.NWAccntDlt aNWAccntDlt in oDmn.NWAccntDlts)
27: {
28: dal.NWAccntDlt aDALNWAccntDlt = new dal.NWAccntDlt();
29: aDALNWAccntDlt.Amount = aNWAccntDlt.Amount;
30: Helper.EnsureFK(aDALNWAccntDlt.NWAccntReference, ctx, aNWAccntDlt.NWAccntID);
31:
32: oDal.NWAccntDlt.Add(aDALNWAccntDlt);
33: }
34: for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
35: {
36: int iParticipantWashAmount = oDmn.GetWashAmount(iParticipant);
37: if (iParticipantWashAmount == 0)
38: continue;
39: dal.WashDlt aDALWashDlt = new dal.WashDlt();
40: aDALWashDlt.Amount = iParticipantWashAmount;
41: int iParticipantID = oDmn.GetParticipantID(iParticipant);
42: Helper.EnsureFK(aDALWashDlt.ParticipantReference, ctx, iParticipantID);
43:
44: oDal.WashDlt.Add(aDALWashDlt);
45: }
46: }
<!-- MANAGED CODEWINDOW END -->
Note that in lines 5, 10, 20, 30, and 42 setting up a foreign key requires use of the custom EnsureFK() function, discussed earlier.
<!-- MANAGED HEADING START ID=745 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=077 -->
CodeWindow 077
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: void SetDALObjectFromDomainObject(du.ITransaction oDmn, dal.Transaction oDal, nh.ISession ctx)
2: {
3: oDal.Descrip = oDmn.Descrip;
4: oDal.Instant = oDmn.Instant;
5: Helper.EnsureFKWithInteger<DalNH.Participant>(o=>oDal.TheParticipant=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, oDmn.ParticipantID);
6: foreach (DTO.Item aItem in oDmn.Items)
7: {
8: dal.Item aDALItem = new dal.Item();
9: aDALItem.Descrip = aItem.Descrip;
10: Helper.EnsureFKWithInteger<DalNH.DltAllocAccnt>(o=>aDALItem.TheDltAllocAccnt=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, aItem.DltAllocAccntID);
11: Helper.EnsureFKWithEntity<DalNH.Transaction >(o=>aDALItem.TheTransaction =o, oDal);
12: for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
13: {
14: int iParticipantAmount = aItem.ParticipantAmounts[iParticipant];
15: if (iParticipantAmount == 0)
16: continue;
17: dal.DltAlloc aDALDltAlloc = new dal.DltAlloc();
18: aDALDltAlloc.Amount = iParticipantAmount;
19: int iParticipantID = oDmn.GetParticipantID(iParticipant);
20: Helper.EnsureFKWithInteger<DalNH.Participant >(o=>aDALDltAlloc.TheParticipant=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, iParticipantID);
21: Helper.EnsureFKWithEntity<DalNH.Item >(o=>aDALDltAlloc.TheItem =o, aDALItem);
22: aDALItem.DltAllocs.Add(aDALDltAlloc);
23: }
24: oDal.Items.Add(aDALItem);
25: }
26: foreach (DTO.NWAccntDlt aNWAccntDlt in oDmn.NWAccntDlts)
27: {
28: dal.NWAccntDlt aDALNWAccntDlt = new dal.NWAccntDlt();
29: aDALNWAccntDlt.Amount = aNWAccntDlt.Amount;
30: Helper.EnsureFKWithInteger<DalNH.NWAccnt>(o=>aDALNWAccntDlt.TheNWAccnt=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, aNWAccntDlt.NWAccntID);
31: Helper.EnsureFKWithEntity<DalNH.Transaction >(o=>aDALNWAccntDlt.TheTransaction =o, oDal);
32: oDal.NWAccntDlts.Add(aDALNWAccntDlt);
33: }
34: for (int iParticipant = 0; iParticipant < oDmn.ParticipantCount; iParticipant++)
35: {
36: int iParticipantWashAmount = oDmn.GetWashAmount(iParticipant);
37: if (iParticipantWashAmount == 0)
38: continue;
39: dal.WashDlt aDALWashDlt = new dal.WashDlt();
40: aDALWashDlt.Amount = iParticipantWashAmount;
41: int iParticipantID = oDmn.GetParticipantID(iParticipant);
42: Helper.EnsureFKWithInteger<DalNH.Participant>(o=>aDALWashDlt.TheParticipant=o, (o,i)=>o.ID=i, (o,rv)=>o.timestamp=rv, iParticipantID);
43: Helper.EnsureFKWithEntity<DalNH.Transaction >(o=>aDALWashDlt.TheTransaction =o, oDal);
44: oDal.WashDlts.Add(aDALWashDlt);
45: }
46: }
<!-- MANAGED CODEWINDOW END -->
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.
<!-- MANAGED HEADING START ID=750 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=755 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=078 -->
CodeWindow 078
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: dal.Transaction LLSaveTransaction(du.ITransaction oDmn, bool bAssumeInBalance, dal.DataClassesDataContext ctx)
2: {
3: if (!bAssumeInBalance)
4: {
5: if (0 != oDmn.GetAmountNWDltsExceedAllocDltsBy())
6: throw new ArgumentException("transaction not in balance");
7: }
8: dal.Transaction oDAL = null;
9: if (oDmn.ID.HasValue)
10: {
11: if(!TransactionRowVersionMatches(oDmn.ID.Value, ctx, oDmn.TheRowVersion))
12: throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
13: DeleteTransactionChildRecords(oDmn.ID.Value, ctx);
14:
15: #region spacer
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26: #endregion
27:
28:
29:
30:
31:
32: oDAL = CreateTransactionReadyToAttach(oDmn.ID.Value, oDmn.TheRowVersion);
33: ctx.Transactions.Attach(oDAL, true);
34: SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
35:
36:
37:
38:
39:
40: }
41: else
42: {
43: oDAL = new dal.Transaction();
44: SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
45: ctx.Transactions.InsertOnSubmit(oDAL);
46: }
47:
48: return oDAL;
49: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=760 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=079 -->
CodeWindow 079
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: dal.Transaction LLSaveTransaction(du.ITransaction oDmn, bool bAssumeInBalance, dal.LekEntities ctx)
2: {
3: if (!bAssumeInBalance)
4: {
5: if (0 != oDmn.GetAmountNWDltsExceedAllocDltsBy())
6: throw new ArgumentException("transaction not in balance");
7: }
8: dal.Transaction oDAL=null;
9: if (oDmn.ID.HasValue)
10: {
11: if(!TransactionRowVersionMatches(oDmn.ID.Value, ctx, oDmn.TheRowVersion))
12: throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
13: DeleteTransactionChildRecords(oDmn.ID.Value, ctx);
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38: oDAL = ctx.Transaction.Where(o=>o.ID == oDmn.ID.Value).ToList().FirstOrDefault();
39: SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
40: }
41: else
42: {
43: oDAL = new dal.Transaction();
44: SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
45: ctx.AddToTransaction(oDAL);
46: }
47:
48: return oDAL;
49: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=765 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=080 -->
CodeWindow 080
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: dal.Transaction LLSaveTransaction(du.ITransaction oDmn, bool bAssumeInBalance, nh.ISession ctx)
2: {
3: if (!bAssumeInBalance)
4: {
5: if (0 != oDmn.GetAmountNWDltsExceedAllocDltsBy())
6: throw new ArgumentException("transaction not in balance");
7: }
8: dal.Transaction oDAL = null;
9: if (oDmn.ID.HasValue)
10: {
11: if(!TransactionRowVersionMatches(oDmn.ID.Value, ctx, oDmn.TheRowVersion))
12: throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
13: DeleteTransactionChildRecords(oDmn.ID.Value, ctx);
14:
15:
16:
17:
18:
19:
20:
21:
22:
23: oDAL = CreateTransactionReadyToAttach(oDmn.ID.Value, oDmn.TheRowVersion);
24: SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
25: ctx.Update(oDAL);
26:
27: #region spacer
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39: #endregion
40: }
41: else
42: {
43: oDAL = new dal.Transaction();
44: SetDALObjectFromDomainObject(oDmn, oDAL, ctx);
45: ctx.Save(oDAL);
46: }
47:
48: return oDAL;
49: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=770 -->
<!-- MANAGED HEADING END -->
To set up the context to save a modified Transaction, the LINQ to SQL version of the function first creates the ORM entity (line 32), then attaches it to the context (line 33), and finally sets all the fields in the ORM entity (line 34). (Notice that the order is different than for NHibernate.) Creation of the entity and field set up occur via helper functions already discussed. Attaching the entity to the context occur via a call to the wizard-generated "Attach" function.
To set up the context to save a new Transaction the LINQ to SQL version of the function first creates a new ORM entity (line 43), then sets all the fields in the ORM entity (line 44), and finally notifies the context about the new entity (line 45). Field setup occurs via a helper function already discussed. Notifying the context about the new object occur via a call to the wizard-generated InsertOnSubmit() function. Except for the name of the function used to notify the context about the new object, the block is the same as in the the Entity Framework and NHibernate versions.
<!-- MANAGED HEADING START ID=775 -->
<!-- MANAGED HEADING END -->
To set up the context to save a modified Transaction, the Entity Framework version of the function first queries for the object (line 38), and then sets all the fields in the returned object (line 39) via a helper function already discussed. Simple, but not efficient. I would much prefer to not query for the object, and instead create one "home made" and attach it. For various reasons (see comments on lines 16-22 and 29-31) I could not get this to work. (Ideas?) This will probably easier in the version of Entity Framework that will ship with .NET 4.0.
To set up the context to save a new Transaction the Entity Framework version of the function first creates a new ORM entity (line 43), then sets all the fields in the ORM entity (line 44), and finally notifies the context about the new entity (line 45). Field setup occurs via a helper function already discussed. Notifying the context about the new object occur via a call to the context's (wizard-generated) AddToTransaction() function. Except for the name of the function used to notify the context about the new object the block is the same as in the the LINQ to SQL and NHibernate versions.
<!-- MANAGED HEADING START ID=780 -->
<!-- MANAGED HEADING END -->
To set up the context to save a modified Transaction, the NHibernate version of the function first creates the ORM entity (line 23), then sets all the fields in the ORM entity (line 24), and finally attaches it to the context (line 25). (Notice that the order is different than for LINQ to SQL.) Creation of the entity and field set up occur via helper functions already discussed. Attaching the entity to the context occur via a call to the context's "Update" function.
To set up the context to save a new Transaction the NHibernate version of the function first creates a new ORM entity (line 43), then sets all the fields in the ORM entity (line 44), and finally notifies the context about the new entity (line 45). Field setup occurs via a helper function already discussed. Notifying the context about the new object occur via a call to the context's Save() function. Except for the name of the function used to notify the context about the new object the block is the same as in the the LINQ to SQL and Entity Framework versions.
<!-- MANAGED HEADING START ID=785 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
Finally we come to the top level function for saving a new or updated Transaction. SaveTransaction() wraps the lower level (LLSaveTransaction()) in a try-catch block so that any ORM specific concurrency exception can be replaced with the custom exception made to work with with WCF. The try-catch block is in turn wrapped in a using statement that establishes a transaction (in the regular sense of the word) that will be rolled back if anything goes wrong. The transaction scope block is in turn wrapped in a using statement that creates the ORM context. The three versions are nearly identical except for the fact that the Entity Framework version explicitly opens a connection to the database (lines 11-13).
<!-- MANAGED HEADING START ID=790 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=081 -->
CodeWindow 081
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: public int SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance)
2: {
3: du.ITransaction oDmn = du.TransactionFactory.CreateDomainTransation(aTransaction);
4: int iID;
5: using (var ctx = new dal.DataClassesDataContext(CONN))
6: using (s.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
7: {
8:
9: try
10: {
11:
12:
13:
14:
15: dal.Transaction oDal= LLSaveTransaction(oDmn, bAssumeInBalance, ctx);
16: ctx.SubmitChanges();
17: iID = oDal.ID;
18: tran.Complete();
19: }
20: catch(s.Data.Linq.ChangeConflictException)
21: {
22:
23:
24:
25:
26: throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
27: }
28:
29:
30:
31:
32:
33: }
34: return iID;
35: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=795 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=082 -->
CodeWindow 082
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: public int SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance)
2: {
3: du.ITransaction oDmn = du.TransactionFactory.CreateDomainTransation(aTransaction);
4: int iID;
5: using (var ctx = new dal.LekEntities(CONN))
6: using (s.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
7: {
8: s.Data.Common.DbConnection conn =null;
9: try
10: {
11: s.Data.EntityClient.EntityConnection aEntityConnection = (s.Data.EntityClient.EntityConnection)ctx.Connection;
12: conn = aEntityConnection.StoreConnection;
13: conn.Open();
14:
15: dal.Transaction oDal= LLSaveTransaction(oDmn, bAssumeInBalance, ctx);
16: ctx.SaveChanges();
17: iID = oDal.ID;
18: tran.Complete();
19: }
20: catch(s.Data.OptimisticConcurrencyException)
21: {
22:
23:
24:
25:
26: throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
27: }
28: finally
29: {
30: if(conn!=null && conn.State == System.Data.ConnectionState.Open)
31: conn.Close();
32: }
33: }
34: return iID;
35: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=800 -->
<!-- MANAGED HEADING END --> <!-- MANAGED CODEWINDOW START ID=083 -->
CodeWindow 083
<!--style="width:650px; height:200px;" --> <!-- code formatted by http://manoli.net/csharpformat/ -->
1: public int SaveTransaction(DTO.Transaction aTransaction, bool bAssumeInBalance)
2: {
3: du.ITransaction oDmn = du.TransactionFactory.CreateDomainTransation(aTransaction);
4: int iID;
5: using (var ctx = dal.SessionFactory.OpenSession(CONN))
6: using (s.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
7: {
8:
9: try
10: {
11:
12:
13:
14:
15: dal.Transaction oDal= LLSaveTransaction(oDmn, bAssumeInBalance, ctx);
16: ctx.Flush();
17: iID = oDal.ID;
18: tran.Complete();
19: }
20: catch(nh.StaleObjectStateException)
21: {
22:
23:
24:
25:
26: throw su.ServerUtil.CreateConcurrencyFaultException(typeof(dal.Transaction).Name);
27: }
28:
29:
30:
31:
32:
33: }
34: return iID;
35: }
<!-- MANAGED CODEWINDOW END --> <!-- MANAGED HEADING START ID=805 -->
<!-- MANAGED HEADING END -->
The creation of the context (line 5) occurs via construction of a DataClassesDataContext for LINQ to SQL, via construction of a (wizard-generated) LekEntitites for Entity Framework, and via a call to OpenSession() for NHibernate.
In contrast to the versions for LINQ to SQL and NHibernate, the Entity Framework version of SaveTransaction() explicitly opens the database connection (line 13). This would not be necessary of there were no stored procedure calls. The first database operation performed by LLSaveTransactions() is a call to the GetTransactionTimestamp() stored procedure. For LINQ to SQL execution of the stored procedure is via a wizard-generated wrapper function, that, like other ORM related tasks, shields us from having to worry about opening database connections. For NHibernate we do have to write most of the code to call the stored procedure, but the database connection (from which the stored procedure command is obtained) is already open at the point when the stored procedure is called (I am not sure when NHibernate opens it). For Entity Framework execution of the stored procedure also occurs via code that explicitly sets up the stored procedure call. However, unlike with NHibernate the connection obtainable from the context will not necessarily be open. I suspect that NHibernate opens the connection when the session is created, and Entity Framework only opens it when an ORM action occurs that requires it. Since no ORM action is executed prior to the stored procedure call, the connection will not be open unless our code explicitly opens it.
The actual write to the database (line 16) is triggered by the SubmitChanges() function of the LINQ to SQL context, the SaveChanges() function of the Entity Framework context, and the Flush() function on the NHibernate context.
If a concurrency problem occurs (line 15), LINQ to SQL throws a System.Data.Linq.ChangeConflictExcepion, Entity Framework throws System.Data.OptimisticConcurrencyException, and NHibernate throws a NHibernate.StaleObjectStateException. Each of the three versions has a catch for the appropriate exception type (line 20), and then translates this into a WCF friendly ConcurrencyFaultException (line 26)
<!-- MANAGED HEADING START ID=810 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=815 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=820 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=825 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
LEK provides the "Summary" view to show the total amount of all the changes made to each account over a period of time. Some examples of information obtainable from this view are: the amount spent on groceries over the last year for a Participant, the total amount deposited into a savings account over the last year for a Participant, the change in a Participant's Net Worth over the last year, the change to the amount that a Participant owes "the pot:" (wash) over the last year. Such values can be shown for any time period (not just year). If the first few Transactions in the system establishes the current values in all NWAccnts, and the time period is set to start prior to the first Transaction, then the Summary view will show the actual value of all NWAccnts for each Participant. The "Summary" view shows all amounts for each Participant and for the family as a whole. The GetSummaryOfTransactions() function serves up the data consumed by the "Summary" view.
<!-- MANAGED HEADING START ID=830 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
<!-- MANAGED HEADING START ID=835 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
All of the source files are available as a zip file here.
The zip file contains a everything needed to build the application using Visual Studio 2008. Besides the files in the zip, you will need Microsoft Visual Studio 2008 with .NET Framework 3.5 SP1. To run the application you will also need a SQL Server 2005 database.
Here are all the steps to download, build and run the application:
- 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.
<!-- MANAGED HEADING START ID=845 -->
Contents at a Glance Detailed Contents <!-- MANAGED HEADING END -->
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip
