LINQ & Views
-
Hi all I was looking for some input on this architectural experiment.. Currently we have a .NET application backended in SQL and using LINQ and a 3-tier architecture. All changes to the DB need to be audited, all deletes don't actually delete but flag a "Deleted" field. There is also the issue of the messiness around disconnected LINQ objects, rather than mapping to and from non-LINQ business objects. When it needs to move objects around it just does a copy of the objects properties, via reflection, to an object relevant to the current data context and then submits that. Looking at this today i had a quick play around using views and triggers and LINQ.... In our current system; One of our objects, a "billable item", had two related "charges". So in our DBML this was represented as a billableItem object with a relationship to charge1 & charge2. This required a mapper in our data layer to handle deletes and via a Delete() method which would do the required "flagging" then updating rather than a standard deletion, it would then have to do the same to the two charge items. Then the business layer would audit this change, with several lines in the audit table saying that the billableItem changed and the 2 charges had changed. In doing all this the code seemed to have made several database hits and used reflection(incurring a minor performance hit along the way).... So I created a view, that showed the billableItem and the two charges (current and pending) in one table (sans the charge's ID's and the items FK for them). Straight off this struck me as a good way to cut the amount of auditing data we needed to log, now we just needed to log that this line in this view changed. not that line X in billableItems and line Y & Z in charges had changed. Now the support guys can look at a single audit entry and tie it to a single line in a single table. Secondly now our DBML is easier to look at and objects reflect actual business objects not DB tables. BUT it was a view of multiple tables and as such LINQ did not like inserting and deleting :doh: So I created some "instead of" triggers. Now on insert it creates a 2 charges and then an item, puts the charge ID's in the items FK's, pulls the rest of the data from the "inserted" that was intercepted and then puts a single audit in the audit table. On Delete it flags the item and 2 charges as deleted and puts a single audit in the audit table. On Update it adds a line in the audit table for each changed field. One thing I have seemingly lost is the