title: How to retrieve an audit trail across two SQL Server tables and put them back in the right order?
body: <p><strong>Background Context:</strong>  </p>
  
  <hr>
  
  <p>i have a database table called <strong>Project</strong> and a table called <strong>ProjectHistory</strong> with the same schema.  I am using nhibernate so i also have a domain object called <strong>Project</strong> and a domain object called <strong>ProjectHistory</strong> (which derived from <strong>Project</strong>).</p>
  
  <p><strong>Goal</strong><br>
  <hr>
  My goal is to retrieve data across these tables and put them back in order as the events that happened and have C# code output a typical audit trail screen of last n number of changes</p>
  
  <p><strong>Details</strong><br>
  <hr>
  Both tables have the following fields and data types</p>
  
  <ul>
  <li>Id -(int )- <strong>primary key in Project, NOTE: no PK in the history table</strong></li>
  <li>LastUpdated (datetime)</li>
  <li>Name (varchar)</li>
  <li>Description (varchar)</li>
  <li>Timestamp (timestamp)</li>
  </ul>
  
  <p>the goal of the <strong>Projecthistory</strong> table is that whenever i do an update to the <strong>Project</strong> table, the <strong>ProjectHistory</strong> table get inserted a new row with the old record so i can have a full audit trail.</p>
  
  <p>I accomplish this through using this trigger:</p>
  
  <pre><code> ALTER TRIGGER ProjectTrigger
   ON Project
   AFTER UPDATE
   AS
      SET NOCOUNT ON
  
      insert into ProjectHistory
      select * from deleted
  </code></pre>
  
  <p>My issue is that i now need some C# code to put together an audit history where a user select the most recent n number of events.</p>
  
  <p><strong>Example:</strong>
  <hr>
  Here is an example of events:</p>
  
  <ol>
  <li>On Jan 10 - create project 1 (nothing in history yet)</li>
  <li>On Jan 11 - create project 2</li>
  <li>On Jan 15 - edit project 2 ( will put entry in history table of project 2 with Jan 11 date as lastupdated by)</li>
  <li>On Jan 25 - edit project 1 (will put entry in history table of project 1 with Jan 10 date on it)</li>
  </ol>
  
  <p><strong>so, as you can see if i sorted descending by lastupdated field in the history table, it was put the result from event 4 before the result from event 3 which is out of order.</strong></p>
  
  <p><strong>Code</strong></p>
  
  <hr>
  
  <p>I have tried the following code to try to put them together but I will explain the flaw below:</p>
  
  <pre><code>   public IEnumerable&lt;Project&gt; GetHistory&lt;Project, ProjectHistory&gt;(int numberOfChanges)
      {
          IEnumerable&lt;Project&gt; current;
          IEnumerable&lt;Project&gt; history;
              current = Session.Query&lt;Project&gt;()
                  .OrderByDescending(r =&gt; r.LastUpdated)
                  .Take(numberOfChanges);
  
               history = Session.Query&lt;ProjectHistory&gt;()
              .OrderByDescending(r =&gt; r.LastUpdated).Cast&lt;Project&gt;();
  
              IEnumerable&lt;Project&gt; all = current.Concat(history);
              return all.OrderByDescending(r =&gt; r.Id).ThenByDescending(r =&gt; r.LastUpdated);
          }
      } 
  </code></pre>
  
  <p>My main issues with the above code are:</p>
  
  <ol>
  <li><p>The <strong>LastUpdated</strong> time on the history records are going to reflect the <strong>LastUpdated</strong> time of the <strong>previous</strong> Project update so i can't sort desc on that field in the history table and assume i will get a correct descending order of events.  </p>
  
  <ol>
  <li>I could use the <strong>timestamp</strong> field for sorting (as that was the actual time that the record was entered) but it doesn't seem like you can sort on that field using C# nhibernate because that field is just a binary(8) so it translates to a byte[] in C# which doesn't support IComparable.</li>
  </ol></li>
  <li><p>I can use <strong>lastUpdated</strong> field on the Project table because sorting by <strong>lastupdate</strong> field will get me the latest recent events in order.  I thought of looping through just the <strong>project</strong> table and then doing a separate query per each project to grab the latest history project but them this doesn't support a case where multiple changes to the same project id occurred.  The issue is i can't figure out a way to get the data in the correct order.</p></li>
  </ol>
  
  <p>My main point is that I want to accomplish what I think is a pretty generic thing that must be solved already so i must be just missing the basic pattern here.</p>
  
  <p>I already have code that can compare one <strong>Project</strong> object with another <strong>project</strong> object and return the field diffs  (which works perfect) but I just need a suggestion on figuring out how i get this data out of the database across these tables <strong>in the right order</strong> (either from my query or code after the fact) so I can show "the last n number of changes" and then use my logic to show the field diff for each of those changes.</p>