Users added (via SQL trigger) from another database not ahowing in /OrchardLocal/Admin/Users

Topics: Administration, Core
Jan 21, 2015 at 3:41 PM
Edited Jan 21, 2015 at 4:07 PM
In our membership database we have a trigger that fires when a new user is added. This trigger writes user-related information to our Orchard database (on same server) and populates the following tables correctly:
  • Orchard_Framework_ContentItemRecord
  • Orchard_Framework_ContentItemVersionRecord
  • Orchard_Users_UserPartRecord
It does not populate Orchard_Roles_UserRolesPartRecord but to be sure this wasn't the issue after the trigger had fired I added the inserted record's Id to Orchard_Roles_UserRolesPartRecord and assigned a role.

The Problem

When I log in as an admin user and go to the Users page, none of the users added via the trigger are showing. For example, if there was 1 admin user and 1 user added via the trigger, on the Users page I would just see the admin user, however, the label on the page would read 'Showing 1 - 2 of 2 users'.

I have stepped through the code on the AdminController,
var users = Services.Query<UserPart, UserPartRecord>();
users object will contain 2 users as above. When this line it hit:
var results = users.Slice(pager.GetStartIndex(), pager.PageSize).ToList();
results object will only have 1 user, the admin user. I'm guessing that I'm missing an entry in some table. To be sure I created a new user in Orchard and inspected the generated SQL in SQL profiler and from what I can see no other tables need to be populated, however it seems that this is the case. Based on what I've said , can you identify anything I'm missing?

Note:
I have also used the import/export module to export users and for the two user records in question following are results (where orchard_test is user added via trigger):
  <Data>
    <User Id="/User.UserName=admin" Status="Published">
      <UserPart Email="" EmailStatus="Approved" HashAlgorithm="SHA1" NormalizedUserName="admin" Password="H0+uhIHB0dI2QSPETgqFM9dyRV4=" PasswordFormat="Hashed" PasswordSalt="H4jlZb9da7SJQj6hyQnxBA==" RegistrationStatus="Approved" UserName="admin" />
      <UserRolesPart Roles="" />
    </User>
    <User Id="/User.UserName=orchard_test" Status="Published">
      <UserPart Email="orchard_test@test.com" EmailStatus="Approved" HashAlgorithm="SHA1" NormalizedUserName="orchard_test" Password="sbCvRgQj8cRlc7YMAiW17fhTQlfXhZWHoaWyK40/DSY=" PasswordFormat="Hashed" PasswordSalt="PQSlEKDdfcbi04EpyKXfqQ==" RegistrationStatus="Approved" UserName="orchard_test" />
      <UserRolesPart Roles="" />
    </User>
  </Data>
Jan 21, 2015 at 5:38 PM
Yes, that table is populated by the trigger:
<Data><UserPart UserName="orchard_test" Email="orchard_test@test.com" NormalizedUserName="orchard_test" HashAlgorithm="SHA1" PasswordFormat="Hashed" Password="+B1E7gMR5ij++QvAuVFH2KOBdwK+zhwyjZw7zJaZVSw=" PasswordSalt="PWoDchP+Id8udZEfdlaL9Q==" RegistrationStatus="Approved" EmailStatus="Approved"/></Data>
and ContentType_Id is set to 2.

Orchard_Framework_ContentItemVersionRecord and Orchard_Users_UserPartRecord are also populated correctly by the trigger. Based on my approach and question above, is this the correct approach to take? Should it work?

Thanks.
Jan 21, 2015 at 6:05 PM
Edited Jan 22, 2015 at 9:23 AM
As per example in OP with 1 admin user and 1 user added via trigger:

users.Count() returns 2 (this is why on Admin/Users label reads two users although only one rendered)
pager.GetStartIndex() returns 0
pager.PageSizer returns 10
results.Count() returns 1
Jan 21, 2015 at 6:29 PM
ContentType_Id is set to 2 for both users.
Developer
Jan 21, 2015 at 6:34 PM
To take a step back, writing directly to the Orchard DB is not a good idea because a) it's error-prone (are you sure you create exactly as the ContentManager would?), b) can produce stale data visible to users (since you modify data behind the back of NHibernate, including it's 1st and 2nd level caches) and c) is very unsafe towards upgrades.

I'd suggest you to instead send data from the trigger to a Web API endpoint or even call into a local executable implemented with Orchard Application Host that can use Orchard's services to access the same Orchard DB.
Marked as answer by matthewosullivan on 1/21/2015 at 1:52 PM
Jan 21, 2015 at 9:14 PM
Thanks both for the replies.

@JasperD, by importing do you mean using the Orchard.ImportExport module?

We could potentially also use DefaultMembershipProvider and just query the membership database (ASP Universal Providers) directly as it's on the same server as Orchard's database.
Jan 22, 2015 at 3:54 PM
FYI, got this working.

The problem was Orchard_Users_UserPartRecord.Id not having same value as Orchard_Framework_ContentItemRecord.Id and this was caused by an incorrect hierarchy of inserts in the trigger.

The correct sequence in the trigger (or any scripted insert) is:
  • Orchard_Framework_ContentItemRecord
  • Orchard_Users_UserPartRecord
  • Orchard_Framework_ContentItemVersionRecord