SQL Server – Update Table with INNER JOIN

update Link
set Link.Tags = LinkGroup.Name
from Link
inner join LinkGroup on GroupId = LinkGroup.Id


Quick and Easy ASP.NET Identity Multitenancy


Multitenancy is when multiple applications share an environment. In our scenario this environment is the identity management library ASP.NET Identity v2.2 where we want to allow multiple organisations to store two different users with the same username in a single database.

Out of the box ASP.NET does not allow for multitenancy. With the ASP.NET Identity default behaviour, all organisations would share a single user in the database, they cannot create one each. This is a problem when the organisations are separate entities with no knowledge of each other and should not be sharing data. For example if a user updates their password within, this will also change their password in Not good.

Some people recommend the work around of prepending usernames with an identifier for each tenant, however there is a way to extend ASP.NET identity to make it truly multitenanted.


Extending from the default Core and Entity Framework packages of ASP.NET Identity we can add a new claim for the concept of Tenant Id. With a bit of work we can use this claim to allow for duplicate usernames within a single ASP.NET Identity database.

The following package is necessary for this solution:

Install-Package Microsoft.AspNet.Identity.EntityFramework

This package will also pull in the necessary dependancies of Microsoft.AspNet.Identity.Core and EntityFramework.


To start with we’ll need to add the claim of TenantId (you can rename this as fits your business requirements) by extending the IdentityUser class. Whilst this is conceptually a claim, we will take advantage of the AspNetUser table and add TenantId as a property, as we will be querying by this property a fair bit. For simplicity I have added the TenantId as an int however a non-iterative alternative would be to use a string.

public class ApplicationUser : IdentityUser {
    public int TenantId { get; set; }


Next we’ll implement the UserStore for our new user that is aware of our new property. Here we are using a property within our UserStore class to set our TenantId, allowing us to override the base implementation with our multi-tenanted implementation.

public class ApplicationUserStore<TUser> : UserStore<TUser> 
  where TUser : ApplicationUser {
    public ApplicationUserStore(DbContext context)
      : base(context) {

    public int TenantId { get; set; }

Now we’ll need to make some of the methods aware of the TenantId:

public override Task CreateAsync(TUser user) {
    if (user == null) {
        throw new ArgumentNullException("user");

    user.TenantId = this.TenantId;
    return base.CreateAsync(user);
public override Task<TUser> FindByEmailAsync(string email) {
    return this.GetUserAggregateAsync(u => u.Email.ToUpper() == email.ToUpper() 
        && u.TenantId == this.TenantId);
public override Task<TUser> FindByNameAsync(string userName) {
    return this.GetUserAggregateAsync(u => u.UserName.ToUpper() == userName.ToUpper() 
        && u.TenantId == this.TenantId);


Whilst the default UserValidator has hardcoded checks for duplicate user names, our new implementation of the UserStore methods FindByNameAsync and FindByEmailAsync will allow for the correct multi-tenanted behaviour (assuming you have set a TenantId within the UserStore). This means we can take full advantage of the default UserValidator and extend it if necessary.


Now here’s an awkward bit. The ASP.NET Identity team have again hardcoded a check for duplicate usernames within the IdentityDbContext class, however this time it is both within the ValidateEntity method and in the EF database schema itself using an index.

The index can be solved by extending the OnModelCreating method to change the unique index based on username to also look for our TenantId (a composite index). This saves us losing this useful index and optimises our database for multitenancy. You can do this with the following override method:

public class ApplicationUserDbContext<TUser> : IdentityDbContext<TUser> 
  where TUser : ApplicationUser {
    public ApplicationUserDbContext(string nameOrConnectionString)
      : base(nameOrConnectionString) {

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {

        var user = modelBuilder.Entity<TUser>();

        user.Property(u => u.UserName)
            .HasColumnAnnotation("Index", new IndexAnnotation(
                new IndexAttribute("UserNameIndex") { IsUnique = true, Order = 1}));

        user.Property(u => u.TenantId)
            .HasColumnAnnotation("Index", new IndexAnnotation(
                new IndexAttribute("UserNameIndex") { IsUnique = true, Order = 2 }));

The ValidateEntity method is a bit more tricky however, as we will have to reimplement the entire method in order to remove the hardcoded username checks:

protected override DbEntityValidationResult ValidateEntity(
  DbEntityEntry entityEntry, IDictionary<object, object> items) {
    if (entityEntry != null && entityEntry.State == EntityState.Added) {
        var errors = new List<DbValidationError>();
        var user = entityEntry.Entity as TUser;

        if (user != null) {
            if (this.Users.Any(u => string.Equals(u.UserName, user.UserName) 
              && u.TenantId == user.TenantId)) {
                errors.Add(new DbValidationError("User", 
                  string.Format("Username {0} is already taken for AppId {1}", 
                    user.UserName, user.TenantId)));

            if (this.RequireUniqueEmail 
              && this.Users.Any(u => string.Equals(u.Email, user.Email) 
              && u.TenantId == user.TenantId)) {
                errors.Add(new DbValidationError("User", 
                  string.Format("Email Address {0} is already taken for AppId {1}", 
                    user.UserName, user.TenantId)));
        else {
            var role = entityEntry.Entity as IdentityRole;

            if (role != null && this.Roles.Any(r => string.Equals(r.Name, role.Name))) {
                errors.Add(new DbValidationError("Role", 
                  string.Format("Role {0} already exists", role.Name)));
        if (errors.Any()) {
            return new DbEntityValidationResult(entityEntry, errors);

    return new DbEntityValidationResult(entityEntry, new List<DbValidationError>());


All that remains now is to initialise the classes. Don’t forget you will need to supply the TenantId each time you new up the context. See the below example (note the use of ‘example’, these classes are all disposable…).

var context = new ApplicationUserDbContext<ApplicationUser>("DefaultConnection");
var userStore = new ApplicationUserStore<ApplicationUser>(context) { TenantId = 1 };
var userManager = new UserManager<ApplicationUser, string>(userStore);

Final Notes and Source Code

As you can see, it’s a fair amount of work to multitenant ASP.NET Identity, involving hardcoded overrides and database schema modifications. It all depends on your business requirements whether or not it is worth going the distance with full multitenancy or to just start modifying strings by prepending/appending usernames.


We all hate it when someone posts a guide and leaves out some of the most important steps. For this reason all of the above code will compile as a working solution, however I have also uploaded the same classes to GitHub this time including all the necessary constructors and generic classes that enable you to keep all of the extensibility features of the default ASP.NET Identity implementation.


  • Blog Example Code – Full source code for the above methodology with full generic classes and constructors necessary to use the extensibility features of ASP.NET Identity

Changing IIS to Not Stop Worker Process in IIS 7.0+

In IIS version 7.0 and above, the worker process terminates after a period of inactivity by default. Because of this, if Secret Server is in its own application pool, the application pool will stop after a period of no requests.

To change IIS to not stop the worker process, follow these steps:

  1. Open IIS (Start > inetmgr).
  2. Select Application Pools from the left-hand navigation pane
  3. Locate the application pool Secret Server is running as. (To determine this, expand Sites at the left, then find the website Secret Server is running on. Click on the Secret Server website or virtual directory (if it is running on one) and click Basic Settings on the right panel. This will indicate Secret Server’s application pool).
  4. Right-click the application pool, and then click Advanced Settings.
    • User-added image
  5. Under the Process Model section, set the Idle Time-out (minutes) option to 0
    • ​​User-added image
  6. Under the Recycling section, set the Regular Time Interval (minutes) option to 0
    • ​​User-added image
  7. Check the Specific Times setting by selecting it and either (1) clicking the arrow to see if there is time specified there or (2) clicking the  button to make sure there are no values there. Click OK.
    • User-added image
  8. Click OK to apply updated settings.

2018 电影链接









select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

delete from people
where   peopleName in (select peopleName    from people group by peopleName      having count(peopleName) > 1)
and   peopleId not in (select min(peopleId) from people group by peopleName     having count(peopleName)>1)

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)


update tableName set [Title]=Right([Title],(len([Title])-1)) where Title like ‘村%’


update tableName set [Title]=left([Title],(len([Title])-1)) where Title like ‘%村’

update vitae set ispass=-1
where peopleId in (select peopleId from vitae group by peopleId


Install IIS and PHP

Applies To: Windows Server 2012, Windows Server 2012 R2

In this step of building a PHP website, you install IIS and FastCGI, download and install PHP and the WinCache extension, and upload your PHP application.

The following list shows the tasks required to complete this step:

When you are done, make sure that IIS and the PHP are installed, and your PHP application has been added to your website. Then go on to Step 2: Configure PHP Settings.

You can use the Web Platform Installer (Web PI) to install IIS, and applications that run on IIS. Web PI installs the latest versions of available Web Platform offerings with just a few simple clicks. Using Web PI, you can download and install any new tools or updates, including PHP. To learn more about the Web PI, see Learn more and install the Web PI.

If you do not use Web PI to install IIS, you can install IIS manually. To install IIS manually, use the following steps:

  1. On the Start page, click the Server Manager tile, and then click OK.
  2. In Server Manager, select Dashboard, and click Add roles and features.
  3. In the Add Roles and Features Wizard, on the Before You Begin page, click Next.
  4. On the Select Installation Type page, select Role-based or Feature-based Installation and click Next
  5. On the Select Destination Server page, select Select a server from the server pool, select your server, and click Next.
  6. On the Select Server Roles page, select Web Server (IIS), and then click Next.
  7. On the Select Features page, note the preselected features that are installed by default, and then select CGI. This selection also installs FastCGI, which is recommended for PHP applications.
  8. Click Next.
  9. On the Web Server Role (IIS) page, click Next.
  10. On the Select Role Services page, note the preselected role services that are installed by default, and then click Next.
    You only have to install the IIS 8 default role services for a static-content web server.
  11. On the Confirm Installation Selections page, confirm your selections, and then click Install.
  12. On the Installation Progress page, confirm that your installation of the Web Server (IIS) role and required role services completed successfully, and then click Close.
  13. To verify that IIS installed successfully, type the following into a web browser:


    You should see the default IIS Welcome page.

  1. On the Start page, type Control Panel, and then click the Control Panel icon in the search results.
  2. In Control Panel, click Programs, and then click Turn Windows features on or off.
  3. In the Windows Features dialog box, click Internet Information Services, note the preselected features that are installed by default, and then select CGI. This selection also installs FastCGI, which is recommended for PHP applications.
  4. Click OK.
  5. To verify that IIS installed successfully, type the following into a web browser:


    You see the default IIS Welcome page.

The preferred method to install PHP on a Windows or Windows Server computer is to use Web Platform Installer (Web PI).

  1. Open a browser to the following website: Microsoft Web Platform Installer 3.0.
  2. Click Download It Now, and then click Run.
  3. At the top of the Web Platform Installer window, click Products.
  4. Click Frameworks, and then select the current version of PHP. (At this writing, the current version is PHP 5.3.13.)
  5. Click Install.

    The Web Platform Installation page displays the version of PHP and its dependencies that will be installed.

  6. Click I Accept.

    Web PI installs the PHP packages.

  7. Click Finish.

If you decide to download PHP and install it manually, the procedures in this section guide you the following tasks:

  • Download PHP and the WinCache extension.
  • Install PHP and WinCache.
  • Add the PHP installation folder to the Path environment variable.
  • Set up a handler mapping for PHP.
  • Add default document entries for PHP.
  • Test your PHP installation.

To keep this procedure simple, install the WinCache extension but do not configure it. You will configure and test WinCache in Step 2: Configure PHP Settings.

  1. Open your browser to Windows for PHP Download Page and download the PHP 5.3 non-thread-safe zip package.
    The PHP 5.4 version does not work with the WinCache extension version 1.1. Use PHP 5.3 until this problem is resolved.
  2. Download the WinCache extension ( from the List of Windows Extensions for PHP.
  3. Extract all files in the PHP .zip package to a folder of your choice, for example C:\PHP\.
  4. Extract the WinCache .zip package to the PHP extensions folder (\ext), for example C:\PHP\ext.

    The WinCache .zip package contains one file (Php_wincache.dll).

  5. Open Control Panel, click System and Security, click System, and then click Advanced system settings.
  6. In the System Properties window, select the Advanced tab, and then click Environment Variables.
  7. Under System variables, select Path, and then click Edit.
  8. Add the path to your PHP installation folder to the end of the Variable value, for example ;C:\PHP. Click OK.
  9. Open IIS Manager, select the hostname of your computer in the Connections panel, and then double-click Handler Mappings.
  10. In the Action panel, click Add Module Mapping.
  11. In Request path, type *.php.
  12. From the Module menu, select FastCgiModule.
  13. In the Executable box, type the full path to Php-cgi.exe, for example C:\PHP\Php-cgi.exe.
  14. In Name, type a name for the module mapping, for example FastCGI.
  15. Click OK.
  16. Select the hostname of your computer in the Connections panel, and double-click Default Document.
  17. In the Action panel, click Add. Type Index.php in the Name box, and then click OK.
  18. Click Add again. Type Default.php in the Name box, and then click OK.
  1. Open a text editor, for example Notepad, as Administrator.
  2. In a new file, type the following text: <?php phpinfo(); ?>
  3. Save the file as C:\inetpub\wwwroot\Phpinfo.php.
  4. Open a browser and enter the following URL: http://localhost/phpinfo.php

    A nicely formatted webpage is displayed showing the current PHP settings.

Once you have IIS and PHP installed, you can add a PHP application to your web server. This section describes how to set up your PHP application on an IIS web server with PHP installed. It does not explain how to develop a PHP application.

  1. Open IIS Manager.
    • For Windows Server 2012, on the Start page click the Server Manager tile, and then click OK. On the Server Manager Dashboard, click the Tools menu, and then click Internet Information Services (IIS) Manager.
    • For Windows 8, on the Start page type Control Panel, and then click the Control Panel icon in the search results. On the Control Panel screen, click System and Security, clickAdministrative Tools, and then click Internet Information Services (IIS) Manager.
  2. In the Connections pane, right-click the Sites node in the tree, and then click Add Website.
  3. In the Add Website dialog box, type a friendly name for your website in the Site name box.
  4. If you want to select a different application pool than the one listed in the Application Pool box, click Select. In the Select Application Pool dialog box, select an application pool from the Application Pool list and then click OK.
  5. In the Physical path box, type the physical path of the website’s folder, or click the browse button () to navigate the file system to find the folder.
  6. If the physical path that you entered in step 5 is to a remote share, click Connect as to specify credentials that have permission to access the path. If you do not use specific credentials, select the Application user (pass-through authentication) option in the Connect As dialog box.
  7. Select the protocol for the website from the Type list.
  8. The default value in the IP address box is All Unassigned. If you must specify a static IP address for the website, type the IP address in the IP address box.
  9. Type a port number in the Port text box.
  10. Optionally, type a host header name for the website in the Host Header box.
  11. If you do not have to make any changes to the site, and you want the website to be immediately available, select the Start Web site immediately check box.
  12. Click OK.

Windows Custom ISO w/VirtIO Drivers

Building the ISO

  1. Get the latest binary VirtIO drivers for Windows, packaged as an ISO file, from
  2. Make a folder: c:\custom.
  3. Extract your Windows Server 2008r2 ISO to: c:\custom\server2008r2 with a compression tool such as 7zip
  4. Extract the VirtIO ISO to c:\custom\server2008r2\virtio.
  5. Use ImgBurn or equivalent ISO mastering tool to build your custom ISO. These instructions apply to ImgBurn.
    • mode menu > build
    • select your source: click the icon of a folder with a magnifying glass and select c:\custom\server2008r2
    • select your destination (output): same icon under destination field c:\custom\server2008r2_virtio.iso
    • Options tab > filesystem: UDF
    • Labels tab > UDF: Server2008r2_virtio
    • Advanced tab > bootable disc sub-tab > check make image bootable
      • Emulation Type: none
      • Boot Image: C:\custom\server2008r2\boot\
      • Platform ID: 80×86
      • Developer ID: Microsoft Corporation
      • Load Segment: leave the default
      • Sectors to load: 8
      • Build (folder arrow to disc file icon).

You now have a Windows ISO with built-in VirtIO drivers ready for use as a custom Vultr ISO.


At first, no drive is present. This is normal. Click “Load Driver”.


  • For Server 2008, use VISTA.
  • For Server 2008r2, use WIN7.
  • For Server 2012, use WIN8.

For example, using 2008r2 on a 64 bit VPS:

Browse to virtio > WIN7 > AMD64 > OK.


Select “Red Hat VirtIO SCSI” driver.


Now the drive is visible.


Additional Steps

Configuring Network Connectivity
  1. After you log in for the first time on your Windows VPS via View Console, you will be greeted by the Server Manager.
    On the upper right part of the menu, click on Tools then choose Computer Management. A new window should open.
  2. On the left pane of that new window (should be named Computer Management), select Device Manager.
  3. You should notice 3 Devices that are marked with Yellow ! signs (4 if you chose to enable Private Networking).
    Right-click on Ethernet Controller and choose Update Driver Software...
  4. Two choices should appear, choose the one below, which is Browse my computer for driver software.
  5. Click Browse... and navigate to D:\virtio\WIN8\AMD64, then click Next.
  6. You will see a pop-up confirmation to verify that you want to install Red Hat VirtIO Ethernet Adapter, just click Install.
  7. Your VPS should now have Internet Connectivity! Do steps 3-6 again for any more Unrecognized Devices on your system.
Getting Windows RDP to Work (optional)
  1. Though you can do just fine with Vultr’s View Console. You can’t copy / paste text to or from it, for that you might want to installWindows RDP (Remote Desktop Protocol). To do so, open Server Manager.
  2. Click on Manage, then choose Add Roles and Features.
  3. It is safe to keep clicking Next until you get to Server Roles section of it.
  4. Scroll down a bit, and find Remote Desktop Services, click the check-box beside it to select. Then click Next.
  5. You can skip the Features part for now, so just click Next again.
  6. Now on Role Services, click the check-box beside Remote Desktop Session Host.
  7. A pop-up should appear, just click Add Features, then click Next one last time.
  8. Confirm your installation by clicking Install. Your VPS should now be installing Windows RDP.
  9. Once the installation finishes, you can reboot your VPS to apply the changes. And you’re done! You should now be able to connect to your VPS via Windows RDP, using your IP Address, User name (default is Administrator) and Password.




IMG_0221 IMG_0220