Monday, May 28, 2018

Reason why NHibernate persist "wrong" data from DateTime property to timestamptz field

namespace TestNpgsql
{
    using System;
    using Npgsql;
    using NpgsqlTypes;
    using System.Data;

    class Program
    {
        static readonly string utcString = "2018-05-04T17:37:00.0000000Z";

        static readonly DateTime local = DateTime.Parse(utcString);
        static readonly DateTime utc = DateTime.Parse(utcString).ToUniversalTime();

        static string insertTest = "insert into test(a, b, c, d) values(:a, :b, :c, :d)";
        
        static void Main(string[] args)
        {                    
            using (var conn = 
                   new NpgsqlConnection("Server=127.0.0.1;Database=test;User Id=postgres;Password=opensesame93;")
                  )

            {
                conn.Open();
                
                Console.WriteLine($"utc string {utcString}");
                Console.WriteLine($"local value is {local} kind is {local.Kind}");
                Console.WriteLine($"utc value is {utc} kind is {utc.Kind}");
                
                var cmd = new NpgsqlCommand(@"insert into test(a, b, c, d) values(:a, :b, :c, :d)", conn);
                
                
                Insert(conn);
                //    "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"                
                                                                
                
                Insert(conn, DbType.DateTimeOffset); 
                //     "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"
                
                Insert(conn, DbType.DateTime); // now I see why NHibernate is "wrong"
                //    "2018-05-05 01:37:00"    "2018-05-04 17:37:00"    "2018-05-05 01:37:00+08"    "2018-05-04 17:37:00+08"
                
                
                Insert(conn, NpgsqlDbType.TimestampTZ);
                //    "2018-05-05 01:37:00"    "2018-05-05 01:37:00"    "2018-05-05 01:37:00+08"    "2018-05-05 01:37:00+08"
                
                Insert(conn, NpgsqlDbType.Timestamp);
                //    "2018-05-05 01:37:00"    "2018-05-04 17:37:00"    "2018-05-05 01:37:00+08"    "2018-05-04 17:37:00+08"
            }                                         
        }
        
         
        static void Insert(NpgsqlConnection conn)
        {
            var cmd = new NpgsqlCommand(insertTest, conn);

            cmd.Parameters.AddWithValue("a", local);
            cmd.Parameters.AddWithValue("b", utc);
            cmd.Parameters.AddWithValue("c", local);
            cmd.Parameters.AddWithValue("d", utc);

            cmd.ExecuteNonQuery();
        }
        
        static void Insert(NpgsqlConnection conn, DbType dbType)
        {            
            var cmd = new NpgsqlCommand(insertTest, conn);
            
            cmd.Parameters.AddRange(new[]
            {
                new NpgsqlParameter { ParameterName = "a", DbType = dbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "b", DbType = dbType, Value = utc },                    
                new NpgsqlParameter { ParameterName = "c", DbType = dbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "d", DbType = dbType, Value = utc },                    
            });

            cmd.ExecuteNonQuery();
        }
        

        static void Insert(NpgsqlConnection conn, NpgsqlDbType npgsqlDbType)
        {
            var cmd = new NpgsqlCommand(insertTest, conn);     
            
            Console.WriteLine($"local value is {local} kind is {local.Kind}");
            Console.WriteLine($"utc value is {utc} kind is {utc.Kind}");

            cmd.Parameters.AddRange(new[]
            {
                new NpgsqlParameter { ParameterName = "a", NpgsqlDbType = npgsqlDbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "b", NpgsqlDbType = npgsqlDbType, Value = utc },                    
                new NpgsqlParameter { ParameterName = "c", NpgsqlDbType = npgsqlDbType, Value = local },                    
                new NpgsqlParameter { ParameterName = "d", NpgsqlDbType = npgsqlDbType, Value = utc },                    
            });

            cmd.ExecuteNonQuery();
        }

              
    }
}

/*
 
-- drop table test;
create table test
(
    id int generated by default as identity primary key,
    a timestamp not null,
    b timestamp not null,
    c timestamptz not null,
    d timestamptz not null
);
 
 */


UPDATE June 1

The latest version of Npgsql, version 4.0, has TimeZone=UTC parameter to connection string.

Without UTC parameter.

test=# select * from test;
 id |          a          |          b          |           c            |           d            
----+---------------------+---------------------+------------------------+------------------------
  1 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
  2 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  3 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
  4 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  5 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-04 17:37:00+08
(5 rows)


The TimeZone=UTC connection string parameter fixes the DateTime + timestamptz problem in NHibernate. The InitializeParameter fix won't be needed anymore.

AS of the time of this writing, NHibernate has an embedded Npgsql 3.2.7. Just add Npgsql 4.0 to an NHibernate project so you'll be able to use TimeZone=UTC in the connection string.

test=# select * from test;
 id |          a          |          b          |           c            |           d            
----+---------------------+---------------------+------------------------+------------------------
  1 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08
  2 | 2018-05-04 17:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  3 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08
  4 | 2018-05-04 17:37:00 | 2018-05-04 17:37:00 | 2018-05-05 01:37:00+08 | 2018-05-05 01:37:00+08
  5 | 2018-05-05 01:37:00 | 2018-05-04 17:37:00 | 2018-05-05 09:37:00+08 | 2018-05-05 01:37:00+08






Sunday, May 27, 2018

Persist UTC date correctly with NHibernate

I encountered a problem with NHibernate where the UTC date from a DateTime property is not correctly persisting to timestamptz. The utc 2018-05-04T17:37:00.000Z was persisted as 2018-05-04 17:37:00+08 to timestamptz field, which is incorrect.

The solution is to override the DateTime type and make NHibernate treat it as DateTimeOffset.

namespace AspNetCoreExample.Infrastructure.NHibernateNpgsqlInfra
{
    using System.Data.Common;

    using NHibernate.SqlTypes;
    using Npgsql;

    public class NpgsqlDriverExtended : NHibernate.Driver.NpgsqlDriver
    {        
        // this gets called when an SQL is executed
        protected override void InitializeParameter(DbParameter dbParam, string name, SqlType sqlType)
        {            
            if (sqlType is NpgsqlExtendedSqlType && dbParam is NpgsqlParameter)
            {
                this.InitializeParameter(dbParam as NpgsqlParameter, name, sqlType as NpgsqlExtendedSqlType);
            }
            else
            {
                base.InitializeParameter(dbParam, name, sqlType);
                
                if (sqlType.DbType == System.Data.DbType.DateTime)
                {
                    dbParam.DbType = System.Data.DbType.DateTimeOffset;
                }
            }
        }

        // NpgsqlExtendedSqlType is used for Jsonb
        protected virtual void InitializeParameter(NpgsqlParameter dbParam, string name, NpgsqlExtendedSqlType sqlType)
        {
            if (sqlType == null)
            {
                throw new NHibernate.QueryException(string.Format("No type assigned to parameter '{0}'", name));
            }

            dbParam.ParameterName = FormatNameForParameter(name);
            dbParam.DbType        = sqlType.DbType;
            dbParam.NpgsqlDbType  = sqlType.NpgDbType;
        }               
    }
}

With the code above, the utc 2018-05-04T17:37:00.000Z is now correctly persisting as 2018-05-05 01:37:00+08.

Sample wiring of NpgsqlDriver: https://github.com/MichaelBuen/AspNetCoreExample/blob/b78b97f085730cfb9494c3ec15085cccf7f761be/AspNetCoreExample.Ddd.Mapper/_TheMapper.cs#L32


If you are using Npgsql version 4.0 with NHibernate, you don't need this fix anymore. With Npgsql version 4.0, you can just add TimeZone=UTC to NHibernate's connection string. An example: http://www.ienablemuch.com/2018/06/utc-all-things-with-nhibernate-datetime-postgres-timestamptz.html

NHibernate manual mapping-by-code

namespace TestNH
{
    using System;
    using NHibernate.Cfg;

    class Program
    {
        static async System.Threading.Tasks.Task Main(string[] args)
        {
            var cfg = new NHibernate.Cfg.Configuration();

            cfg.DataBaseIntegration(c =>
            {
                c.Driver<NHibernate.Driver.NpgsqlDriver>();
                c.Dialect<NHibernate.Dialect.PostgreSQLDialect>();

                c.ConnectionString = "Server=localhost; Port=5432; Database=test; User Id=postgres; Password=opensesame93";   

                c.LogFormattedSql = true;
                c.LogSqlInConsole = true;

            });


            var mapper = new NHibernate.Mapping.ByCode.ModelMapper();
            mapper.AddMapping<TheTimeMapping>();
            
            var mapping = mapper.CompileMappingForAllExplicitlyAddedEntities();

            cfg.AddMapping(mapping);

            var sf = cfg.BuildSessionFactory();


            using (var session = sf.OpenSession())
            using (var tx = session.BeginTransaction())
            {
                var utcString = "2018-05-04T17:37:00.0000000Z";

                var local = DateTime.Parse(utcString);
                var utc = DateTime.Parse(utcString).ToUniversalTime();

                Console.WriteLine($"local value is {local} kind is {local.Kind}");
                Console.WriteLine($"utc value is {utc} kind is {utc.Kind}");                

                var t = new Test
                {
                    A = local,
                    B = utc,
                    C = local,
                    D = utc
                };
                                              
                await session.PersistAsync(t);

                await tx.CommitAsync();
            }                       
        }
    }


    public class Test
    {
        public virtual int Id { get; set; }

        public virtual DateTime A { get; set; }
        public virtual DateTime B { get; set; }
        public virtual DateTime C { get; set; }
        public virtual DateTime D { get; set; }
    }

    public class TheTimeMapping: NHibernate.Mapping.ByCode.Conformist.ClassMapping<Test>
    {
        public TheTimeMapping()
        {
            Id(x => x.Id, id =>
            {                
                id.Generator(
                    NHibernate.Mapping.ByCode.Generators.Sequence, 
                    generatorMapping => generatorMapping.Params(new { sequence = "test_id_seq"})
                );               
            });

            Property(p => p.A);
            Property(p => p.B);
            Property(p => p.C);
            Property(p => p.D);            
        }
    }

    /*
        create table test
        (
            id int generated by default as identity primary key,
            a timestamp not null,
            b timestamp not null,
            c timestamptz not null,
            d timestamptz not null
        );
     */
}



Outputs:
local value is 05/05/2018 01:37:00 kind is Local
utc value is 05/04/2018 17:37:00 kind is Utc


Database:



Saturday, May 19, 2018

Proper way to cache entities from cached query with NHibernate

Despite the Profile entity is cached, it's possible that the CityFk property below cannot get a cached City and it will get the City from the database instead. Causing N + 1 problem.

public static async Task<ProfileDto> GetProfileDto(IDdd ddd, int userId)
{
    var profile = await ddd.GetAsync<Profile>(userId);

    var user = profile?.User ?? await ddd.GetAsync<IdentityDomain.User>(userId);
                
    var dto = new ProfileDto
    {
        Username        = user.UserName,
        AddressLine1    = profile?.AddressLine1,
        AddressLine2    = profile?.AddressLine2,
        StateFk         = profile?.City?.State?.Id ?? 0,
        CityFk          = profile?.City?.Id ?? 0,
        ZipCode         = profile?.ZipCode,
        Email           = user.Email,
        Telephone       = profile?.Telephone,
        MobilePhone     = profile?.MobilePhone
    };

    return dto;
}


This code produces City and State list.

The citiesQuery is cached, but it can not produce cache for City entities.

public static async Task<IEnumerable<StateCityDto>> GetListWithStateAsync(IDdd ddd)
{    
    var citiesQuery =
        from c in ddd.Query<City>().FetchOk(fc => fc.State)
        select new
        {
            Id = c.Id,
            Name = c.Name,
            StateId = c.State.Id,
            StateName = c.State.Name
        };


    var citiesList = await citiesQuery.CacheableOk().ToListAsyncOk();

    var distinctStates =
        citiesList.Select(x => new { x.StateId, x.StateName }).Distinct();


    var stateCityListDto =
        from state in distinctStates
        orderby state.StateName
        select new StateCityDto
        {
            Id = state.StateId,
            Name = state.StateName,
            Cities = (
                from city in citiesList
                where city.StateId == state.StateId
                orderby city.Name
                select new CityDto { Id = city.Id, Name = city.Name }
            ).ToList()
        };


    return stateCityListDto.ToList();
}


After saving the Profile:
NHibernate: 
     UPDATE
         job.profile 
     SET
         city_fk = :p0 
     WHERE
         user_fk = :p1;
     :p0 = 999 [Type: Int32 (0:0:0)], :p1 = 1 [Type: Int32 (0:0:0)]


When GetProfileDto is called again:

NHibernate: 
     SELECT
         profile0_.user_fk as user1_1_0_,
         profile0_.address_line_1 as address2_1_0_,
         profile0_.address_line_2 as address3_1_0_,
         profile0_.city_fk as city4_1_0_,
         profile0_.zip_code as zip5_1_0_,
         profile0_.telephone as telephone6_1_0_,
         profile0_.mobile_phone as mobile7_1_0_ 
     FROM
         job.profile profile0_ 
     WHERE
         profile0_.user_fk=:p0;
     :p0 = 1 [Type: Int32 (0:0:0)]
 NHibernate: 
     SELECT
         city0_.id as id1_0_0_,
         city0_.name as name2_0_0_,
         city0_.state_fk as state3_0_0_ 
     FROM
         job.city city0_ 
     WHERE
         city0_.id=:p0;
     :p0 = 999 [Type: Int32 (0:0:0)]



ProfileDto should get be able to get the City from cached City entities. The SQL log shows otherwise though.

The citiesQuery query in GetListWithStateAsync above, despite the query is cached, that query cannot produce the cache for City entities.

To make GetListWithStateAsync's citiesQuery produce cache for City entities, don't select individual fields of City from the query, to wit:


public static async Task<IEnumerable<StateCityDto>> GetListWithStateAsync(IDdd ddd)
{
    // The citiesQuery is cached. And it also produces cache for City entities.
    var citiesQuery = ddd.Query<City>().FetchOk(c => c.State);
                            
    var citiesList = await citiesQuery.CacheableOk().ToListAsyncOk();

    var distinctStates =
        citiesList.Select(x => new 
            { 
                StateId = x.State.Id, 
                StateName = x.State.Name
            }).Distinct();


    var stateCityListDto =
        from state in distinctStates
        orderby state.StateName
        select new StateCityDto
        {
            Id = state.StateId,
            Name = state.StateName,
            Cities = (
                from city in citiesList
                where city.State.Id == state.StateId
                orderby city.Name
                select new CityDto { Id = city.Id, Name = city.Name }
            ).ToList()
        };


    return stateCityListDto.ToList();
}


After saving the Profile:
NHibernate: 
     UPDATE
         job.profile 
     SET
         city_fk = :p0 
     WHERE
         user_fk = :p1;
     :p0 = 992 [Type: Int32 (0:0:0)], :p1 = 1 [Type: Int32 (0:0:0)]


When GetProfileDto is called again:
NHibernate: 
     SELECT
         profile0_.user_fk as user1_1_0_,
         profile0_.address_line_1 as address2_1_0_,
         profile0_.address_line_2 as address3_1_0_,
         profile0_.city_fk as city4_1_0_,
         profile0_.zip_code as zip5_1_0_,
         profile0_.telephone as telephone6_1_0_,
         profile0_.mobile_phone as mobile7_1_0_ 
     FROM
         job.profile profile0_ 
     WHERE
         profile0_.user_fk=:p0;
     :p0 = 1 [Type: Int32 (0:0:0)]


Without selecting the individual fields of the city in GetListWithStateAsync, the GetProfileDto is now able to get the city from the cached cities produced by cached city query.



Happy coding!

Saturday, May 12, 2018

NHibernate 5 IQueryable's DML use with caution

public class User
{
    // ...properties here

    public IEnumerable<ExternalLogin> ExternalLogins { get; protected set; } = new List<ExternalLogin>();

    public void DeleteAllExternalLogins()
    {
        await this.ExternalLogins.AsQueryable()
        .DeleteAsync(new System.Threading.CancellationToken());
    }
}    

public class ExternalLogin
{
    protected User User { get; }

    internal ExternalLogin(User applicationUser) => this.User = applicationUser;
    
    public int Id { get; protected set; }

    public string LoginProvider { get; internal protected set; } // provider: facebook, google, etc

    public string ProviderKey { get; internal protected set; } // user's id from facebook, google, etc

    public string DisplayName { get; internal protected set; } // seems same as provider   
}        



The above DML will not put a user id filter, instead it will just issue this:

delete from external_login;


Happy Coding!

Tuesday, May 8, 2018

Using NHibernate and Postgres on ASP.NET Core 2.0 Identity with external login to Facebook, Google, Twitter

This post shows the code necessary to make ASP.NET Core Identity work with NHibernate and Postgres. Including the external logins such as Facebook, Google, Twitter.


Working code can be downloaded from https://github.com/MichaelBuen/AspNetCoreExample


Important codes:

AspNetCoreExample.Infrastructure/_DDL.txt
AspNetCoreExample.Ddd/IdentityDomain/User.cs
AspNetCoreExample.Ddd/IdentityDomain/Role.cs
AspNetCoreExample.Identity/Data/UserStore.cs
AspNetCoreExample.Identity/Data/RoleStore.cs
AspNetCoreExample.Identity/Startup.cs

Database:

create schema identity;
create extension citext;

create table identity.user
(
    id                      int  generated by default as identity  primary key,
    user_name               citext  not null,
    normalized_user_name    citext  not null,
    email                   citext,
    normalized_email        citext,
    email_confirmed         boolean  not null,
    password_hash           text,
    phone_number            text,
    phone_number_confirmed  boolean  not null,
    two_factor_enabled      boolean  not null,

    security_stamp          text,
    concurrency_stamp       text,

    lockout_end             timestamp with time zone,
    lockout_enabled         boolean  not null  default false,
    access_failed_count     int  not null  default 0
);


create table identity.external_login
(
    user_fk             int  not null  references identity.user(id), 

    id                  int  generated by default as identity  primary key, 

    login_provider      text  not null, 
    provider_key        text  not null, 
    display_name        text  not null
);



create unique index ix_identity_user__normalized_user_name ON identity.user (normalized_user_name);


create unique index ix_identity_user__normalized_email ON identity.user (normalized_email);




create table identity.role
(
    id                  int  generated by default as identity  primary key,

    name                citext  not null,
    normalized_name     citext  not null,

    concurrency_stamp   text
);

create table ix_identity_role__normalized_name ON identity.role (normalized_name);



CREATE TABLE identity.user_role
(
    user_fk     int  not null  references identity.user(id),
    role_fk     int  not null  references identity.role(id),

    primary key (user_fk, role_fk)
);


DDD Models (user and role)

Identity User model:

namespace AspNetCoreExample.Ddd.IdentityDomain
{
    using System;
    using System.Collections.Generic;
    using System.Collections.ObjectModel;
    using System.Linq;
    using System.Threading.Tasks;

    using Microsoft.AspNetCore.Identity;

    public class User : IdentityUser<int>
    {
        /// <summary>
        ///  One-to-many to external logins
        /// </summary>
        /// <value>The external logins.</value>
        public IEnumerable<ExternalLogin> ExternalLogins { get; protected set; } = new Collection<ExternalLogin>();

        /// <summary>
        /// Many-to-many between Users and Roles
        /// </summary>
        /// <value>The roles.</value>
        public IEnumerable<Role> Roles { get; protected set; } = new Collection<Role>();


        public User(string userName) : base(userName) { }

        public User(string userName, string email)
        {
            this.UserName = userName;
            this.Email = email;
        }


        public void AddExternalLogin(string loginProvider, string providerKey, string providerDisplayName)
        {
            var el = new ExternalLogin(this)
            {
                LoginProvider = loginProvider,
                ProviderKey = providerKey,
                DisplayName = providerDisplayName
            };

            this.ExternalLogins.AsCollection().Add(el);
        }


        public async Task RemoveExternalLoginAsync(string loginProvider, string providerKey)
        {
            var externalLogin =
                await this.ExternalLogins.AsQueryable()
                .SingleOrDefaultAsyncOk(el => el.LoginProvider == loginProvider && el.ProviderKey == providerKey);

            if (externalLogin != null)
            {
                this.ExternalLogins.AsCollection().Remove(externalLogin);
            }
        }

        public async Task<IList<string>> GetRoleNamesAsync() =>
            await this.Roles.AsQueryable().Select(r => r.Name).ToListAsyncOk();

        public async Task AddRole(Role roleToAdd)
        {
            var isExisting = await this.Roles.AsQueryable().AnyAsyncOk(role => role == roleToAdd);

            if (!isExisting)
            {
                this.Roles.AsCollection().Add(roleToAdd);
            }
        }

        public async Task RemoveRoleAsync(string roleName)
        {
            string normalizedRoleName = roleName.ToUpper();

            var role =
                await this.Roles.AsQueryable()
                .Where(el => el.NormalizedName == normalizedRoleName)
                .SingleOrDefaultAsyncOk();

            if (role != null)
            {
                this.Roles.AsCollection().Remove(role);
            }
        }

        public async Task<bool> IsInRole(string roleName) =>
            await this.Roles.AsQueryable()
            .AnyAsyncOk(role => role.NormalizedName == roleName.ToUpper());


        public void SetTwoFactorEnabled(bool enabled) => this.TwoFactorEnabled = enabled;

        public void SetNormalizedEmail(string normalizedEmail) => this.NormalizedEmail = normalizedEmail;

        public void SetEmailConfirmed(Boolean confirmed) => this.EmailConfirmed = confirmed;

        public void SetPhoneNumber(string phoneNumber) => this.PhoneNumber = phoneNumber;

        public void SetPhoneNumberConfirmed(Boolean confirmed) => this.PhoneNumberConfirmed = confirmed;

        public void SetPasswordHash(string passwordHash) => this.PasswordHash = passwordHash;

        public void SetEmail(string email) => this.Email = email;

        public void SetNormalizedUserName(string normalizedUserName) => this.NormalizedUserName = normalizedUserName;

        public void SetUserName(string userName) => this.UserName = userName;

        public void UpdateFromDetached(User user)
        {
            this.UserName = user.UserName;
            this.NormalizedUserName = user.NormalizedUserName;
            this.Email = user.Email;
            this.NormalizedEmail = user.NormalizedEmail;
            this.EmailConfirmed = user.EmailConfirmed;
            this.PasswordHash = user.PasswordHash;
            this.PhoneNumber = user.PhoneNumber;
            this.PhoneNumberConfirmed = user.PhoneNumberConfirmed;
            this.TwoFactorEnabled = user.TwoFactorEnabled;
        }


        public async static Task<User> FindByLoginAsync(
            IQueryable<User> users, string loginProvider, string providerKey
        ) =>
            await users.SingleOrDefaultAsyncOk(au =>
                au.ExternalLogins.Any(el => el.LoginProvider == loginProvider && el.ProviderKey == providerKey)
            );

        public async Task<IList<UserLoginInfo>> GetUserLoginInfoListAsync() =>
            await this.ExternalLogins.AsQueryable()
            .Select(el =>
                new UserLoginInfo(
                    el.LoginProvider,
                    el.ProviderKey,
                    el.DisplayName
                )
            )
            // The cache of a user's external logins gets trashed when another user updates his/her external logins.
            // Explore how to make collection caching more robust. Disable for the meantime.
            // .CacheableOk() 
            .ToListAsyncOk();
    }

    public class ExternalLogin
    {
        /// 
        /// Many-to-one to a user
        /// 
        /// The user.
        protected User User { get; set; }

        internal ExternalLogin(User applicationUser) => this.User = applicationUser;

        // Was:
        // public int Id { get; protected set; }

        // Below is better as we don't need to expose primary key of child entities
        // But the above could be useful if we want to directly update, delete 
        // based on Id, for performance concern. 
        protected int Id { get; set; }

        public string LoginProvider { get; internal protected set; } // provider: facebook, google, etc

        public string ProviderKey { get; internal protected set; } // user's id from facebook, google, etc

        public string DisplayName { get; internal protected set; } // seems same as provider               
    }
}



Role model:

namespace AspNetCoreExample.Ddd.IdentityDomain
{
    using System.Collections.Generic;
    using System.Collections.ObjectModel;
    using System.Linq;
    using System.Threading.Tasks;
    
    using Microsoft.AspNetCore.Identity;

    public class Role : IdentityRole<int>
    {
        /// <summary>
        /// Many-to-many between Roles and Users
        /// </summary>
        /// <value>The users.</value>
        public IEnumerable<User> Users { get; protected set; } = new Collection<User>();


        public Role(string roleName) : base(roleName) { }


        public void UpdateFromDetached(Role role)
        {
            this.Name = role.Name;
            this.NormalizedName = role.NormalizedName;
        }

        public void SetRoleName(string roleName) => this.Name = roleName;

        public void SetNormalizedName(string normalizedName) => this.NormalizedName = normalizedName;

        public static async Task<IList<User>> GetUsersByRoleNameAsync(IQueryable<User> users, string normalizedRoleName)
        {
            var criteria =
                    from user in users
                    where user.Roles.AsQueryable().Any(role => role.NormalizedName == normalizedRoleName)
                    select user;

            return await criteria.ToListAsyncOk();
        }
    }
}

Data stores (user store and role store)


User store:

namespace AspNetCoreExample.Identity.Data
{
    using System;
    using System.Collections.Generic;
    using System.Threading;
    using System.Threading.Tasks;

    using Microsoft.AspNetCore.Identity;

    using AspNetCoreExample.Ddd.Connection;
    using AspNetCoreExample.Ddd.IdentityDomain;


    public class UserStore :
        IUserStore<User>,
        IUserEmailStore<User>,
        IUserPhoneNumberStore<User>,
        IUserTwoFactorStore<User>,
        IUserPasswordStore<User>,
        IUserRoleStore<User>,
        IUserLoginStore<User>
    {
        IDatabaseFactory DbFactory { get; }

        public UserStore(IDatabaseFactory dbFactory) => this.DbFactory = dbFactory;

        async Task<IdentityResult> IUserStore<User>.CreateAsync(User user, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                await ddd.PersistAsync(user);

                await ddd.CommitAsync();
            }

            return IdentityResult.Success;
        }

        async Task<IdentityResult> IUserStore<User>.DeleteAsync(User user, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                await ddd.DeleteAggregateAsync(user);

                await ddd.CommitAsync();
            }

            return IdentityResult.Success;
        }

        async Task<User> IUserStore<User>.FindByIdAsync(string userId, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                var user = await ddd.GetAsync<User>(int.Parse(userId));

                return user;
            }
        }

        async Task<User> IUserStore<User>.FindByNameAsync(
            string normalizedUserName, CancellationToken cancellationToken
        )
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDdd())
            {
                var au =
                    await ddd.Query<User>()
                            .SingleOrDefaultAsyncOk(u => u.NormalizedUserName == normalizedUserName);

                return au;
            }
        }


        Task<string> IUserStore<User>.GetNormalizedUserNameAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.NormalizedUserName);


        Task<string> IUserStore<User>.GetUserIdAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.Id.ToString());

        Task<string> IUserStore<User>.GetUserNameAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.UserName);


        Task IUserStore<User>.SetNormalizedUserNameAsync(
            User user, string normalizedName, CancellationToken cancellationToken
        )
        {
            user.SetNormalizedUserName(normalizedName);
            return Task.FromResult(0);
        }

        Task IUserStore<User>.SetUserNameAsync(User user, string userName, CancellationToken cancellationToken)
        {
            user.SetUserName(userName);
            return Task.FromResult(0);
        }

        async Task<IdentityResult> IUserStore<User>.UpdateAsync(User user, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();


            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                var au = await ddd.GetAsync<User>(user.Id);

                au.UpdateFromDetached(user);

                await ddd.CommitAsync();
            }

            return IdentityResult.Success;
        }

        Task IUserEmailStore<User>.SetEmailAsync(User user, string email, CancellationToken cancellationToken)
        {
            user.SetEmail(email);
            return Task.FromResult(0);
        }

        Task<string> IUserEmailStore<User>.GetEmailAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.Email);

        Task<bool> IUserEmailStore<User>.GetEmailConfirmedAsync(
            User user, CancellationToken cancellationToken
        ) => Task.FromResult(user.EmailConfirmed);

        Task IUserEmailStore<User>.SetEmailConfirmedAsync(
            User user, bool confirmed, CancellationToken cancellationToken
        )
        {
            user.SetEmailConfirmed(confirmed);
            return Task.FromResult(0);
        }

        async Task<User> IUserEmailStore<User>.FindByEmailAsync(
            string normalizedEmail, CancellationToken cancellationToken
        )
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDdd())
            {
                var au = await ddd.Query<User>()
                            .SingleOrDefaultAsyncOk(u => u.NormalizedEmail == normalizedEmail);

                return au;
            }
        }

        Task<string> IUserEmailStore<User>.GetNormalizedEmailAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.NormalizedEmail);

        Task IUserEmailStore<User>.SetNormalizedEmailAsync(
            User user, string normalizedEmail, CancellationToken cancellationToken
        )
        {
            user.SetNormalizedEmail(normalizedEmail);
            return Task.FromResult(0);
        }

        Task IUserPhoneNumberStore<User>.SetPhoneNumberAsync(
            User user, string phoneNumber, CancellationToken cancellationToken
        )
        {
            user.SetPhoneNumber(phoneNumber);
            return Task.FromResult(0);
        }

        Task<string> IUserPhoneNumberStore<User>.GetPhoneNumberAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.PhoneNumber);


        Task<bool> IUserPhoneNumberStore<User>.GetPhoneNumberConfirmedAsync(
            User user, CancellationToken cancellationToken
        ) => Task.FromResult(user.PhoneNumberConfirmed);

        Task IUserPhoneNumberStore<User>.SetPhoneNumberConfirmedAsync(
            User user, bool confirmed, CancellationToken cancellationToken
        )
        {
            user.SetPhoneNumberConfirmed(confirmed);
            return Task.FromResult(0);
        }

        Task IUserTwoFactorStore<User>.SetTwoFactorEnabledAsync(
            User user, bool enabled, CancellationToken cancellationToken
        )
        {
            user.SetTwoFactorEnabled(enabled);
            return Task.FromResult(0);
        }

        Task<bool> IUserTwoFactorStore<User>.GetTwoFactorEnabledAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.TwoFactorEnabled);


        Task IUserPasswordStore<User>.SetPasswordHashAsync(
            User user, string passwordHash, CancellationToken cancellationToken
        )
        {
            user.SetPasswordHash(passwordHash);
            return Task.FromResult(0);
        }

        Task<string> IUserPasswordStore<User>.GetPasswordHashAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.PasswordHash);


        Task<bool> IUserPasswordStore<User>.HasPasswordAsync(User user, CancellationToken cancellationToken)
            => Task.FromResult(user.PasswordHash != null);

        async Task IUserRoleStore<User>.AddToRoleAsync(User user, string roleName, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                var roleByName =
                    await ddd.Query<Role>()
                    .SingleOrDefaultAsyncOk(role => role.Name == roleName);

                if (roleByName == null)
                {
                    roleByName = new Role(roleName);
                    ddd.Persist(roleByName);
                }

                var userGot = await ddd.GetAsync<User>(user.Id);

                await userGot.AddRole(roleByName);

                await ddd.CommitAsync();
            }
        }

        async Task IUserRoleStore<User>.RemoveFromRoleAsync(
            User user, string roleName, CancellationToken cancellationToken
        )
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                var userLoaded = await ddd.GetAsync<User>(user.Id);

                await userLoaded.RemoveRoleAsync(roleName);

                await ddd.CommitAsync();
            }
        }

        async Task<IList<string>> IUserRoleStore<User>.GetRolesAsync(User user, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDdd())
            {
                var userGot = await ddd.GetAsync<User>(user.Id);

                return await userGot.GetRoleNamesAsync();
            }
        }

        async Task<bool> IUserRoleStore<User>.IsInRoleAsync(
            User user, string roleName, CancellationToken cancellationToken
        )
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDdd())
            {
                var userGot = await ddd.GetAsync<User>(user.Id);

                return await userGot.IsInRole(roleName);
            }
        }

        async Task<IList<User>> IUserRoleStore<User>.GetUsersInRoleAsync(
            string roleName, CancellationToken cancellationToken
        )
        {
            cancellationToken.ThrowIfCancellationRequested();


            using (var ddd = this.DbFactory.OpenDdd())
            {
                string normalizedRoleName = roleName.ToUpper();

                var usersList = await Role.GetUsersByRoleNameAsync(ddd.Query<User>(), normalizedRoleName);

                return usersList;
            }
        }

        async Task IUserLoginStore<User>.AddLoginAsync(
            User user, UserLoginInfo login, CancellationToken cancellationToken
        )
        {
            cancellationToken.ThrowIfCancellationRequested();

            if (user == null)
                throw new ArgumentNullException(nameof(user));

            if (login == null)
                throw new ArgumentNullException(nameof(login));



            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                var au = await ddd.GetAsync<User>(user.Id);

                au.AddExternalLogin(login.LoginProvider, login.ProviderKey, login.ProviderDisplayName);

                await ddd.CommitAsync();
            }


        }

        async Task<User> IUserLoginStore<User>.FindByLoginAsync(
            string loginProvider, string providerKey, CancellationToken cancellationToken
        )
        {
            using (var ddd = this.DbFactory.OpenDdd())
            {
                var user = await User.FindByLoginAsync(ddd.Query<User>(), loginProvider, providerKey);
                return user;
            }
        }

        async Task<IList<UserLoginInfo>> IUserLoginStore<User>.GetLoginsAsync(
            User user, CancellationToken cancellationToken
        )
        {
            using (var ddd = this.DbFactory.OpenDdd())
            {
                var au = await ddd.GetAsync<User>(user.Id);

                var list = await au.GetUserLoginInfoListAsync();

                return list;
            }
        }

        async Task IUserLoginStore<User>.RemoveLoginAsync(
            User user, string loginProvider, string providerKey, CancellationToken cancellationToken
        )
        {
            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                var au = await ddd.GetAsync<User>(user.Id);

                await au.RemoveExternalLoginAsync(loginProvider, providerKey);

                await ddd.CommitAsync();
            }
        }

        public void Dispose()
        {
            // Nothing to dispose.
        }

    }
}


Role store:

namespace AspNetCoreExample.Identity.Data
{
    using System.Threading;
    using System.Threading.Tasks;

    using Microsoft.AspNetCore.Identity;
    using Microsoft.Extensions.Configuration;

    using AspNetCoreExample.Ddd.Connection;
    using AspNetCoreExample.Ddd.IdentityDomain;

    public class RoleStore : IRoleStore<Role>
    {
        IDatabaseFactory DbFactory { get; }

        public RoleStore(IConfiguration configuration, IDatabaseFactory dbFactory) => this.DbFactory = dbFactory;

        async Task<IdentityResult> IRoleStore<Role>.CreateAsync(Role role, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var db = this.DbFactory.OpenDddForUpdate())
            {
                await db.PersistAsync(role);

                await db.CommitAsync();
            }

            return IdentityResult.Success;
        }

        async Task<IdentityResult> IRoleStore<Role>.UpdateAsync(Role role, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDddForUpdate())
            {
                var roleGot = await ddd.GetAsync<Role>(role.Id);

                roleGot.UpdateFromDetached(role);

                await ddd.CommitAsync();
            }

            return IdentityResult.Success;
        }

        async Task<IdentityResult> IRoleStore<Role>.DeleteAsync(Role role, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var db = this.DbFactory.OpenDddForUpdate())
            {
                await db.DeleteAggregateAsync(role);

                await db.CommitAsync();
            }

            return IdentityResult.Success;
        }

        Task<string> IRoleStore<Role>.GetRoleIdAsync(Role role, CancellationToken cancellationToken) =>
            Task.FromResult(role.Id.ToString());


        Task<string> IRoleStore<Role>.GetRoleNameAsync(Role role, CancellationToken cancellationToken) =>
            Task.FromResult(role.Name);


        Task IRoleStore<Role>.SetRoleNameAsync(Role role, string roleName, CancellationToken cancellationToken)
        {
            role.SetRoleName(roleName);
            return Task.FromResult(0);
        }

        Task<string> IRoleStore<Role>.GetNormalizedRoleNameAsync(Role role, CancellationToken cancellationToken) =>
            Task.FromResult(role.NormalizedName);

        Task IRoleStore<Role>.SetNormalizedRoleNameAsync(
            Role role, string normalizedName, CancellationToken cancellationToken
        )
        {
            role.SetNormalizedName(normalizedName);
            return Task.FromResult(0);
        }

        async Task<Role> IRoleStore<Role>.FindByIdAsync(string roleId, CancellationToken cancellationToken)
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDdd())
            {
                var role = await ddd.GetAsync<Role>(roleId);

                return role;
            }
        }

        async Task<Role> IRoleStore<Role>.FindByNameAsync(
            string normalizedRoleName, CancellationToken cancellationToken
        )
        {
            cancellationToken.ThrowIfCancellationRequested();

            using (var ddd = this.DbFactory.OpenDdd())
            {
                var role =
                    await ddd.Query<Role>()
                    .SingleOrDefaultAsyncOk(r => r.NormalizedName == normalizedRoleName);

                return role;
            }
        }

        public void Dispose()
        {
            // Nothing to dispose.
        }
    }
}

Wireup:

namespace AspNetCoreExample.Identity
{
    using System.Threading.Tasks;

    using Microsoft.AspNetCore.Builder;
    using Microsoft.AspNetCore.Hosting;
    using Microsoft.AspNetCore.Identity;
    using Microsoft.Extensions.Configuration;
    using Microsoft.Extensions.DependencyInjection;

    using AspNetCoreExample.Ddd.Connection;
    using AspNetCoreExample.Identity.Data;
    using AspNetCoreExample.Identity.Services;

    public class Startup
    {
        IConfiguration Configuration { get; }

        public Startup(IConfiguration configuration) => this.Configuration = configuration;

        string ConnectionString => this.Configuration.GetConnectionString("DefaultConnection");

        (string appId, string appSecret) FacebookOptions
            => (this.Configuration["Authentication:Facebook:AppId"],
                this.Configuration["Authentication:Facebook:AppSecret"]);

        (string clientId, string clientSecret) GoogleOptions
            => (this.Configuration["Authentication:Google:ClientId"],
                this.Configuration["Authentication:Google:ClientSecret"]);

        (string consumerKey, string consumerSecret) TwitterOptions
            => (this.Configuration["Authentication:Twitter:ConsumerKey"],
                this.Configuration["Authentication:Twitter:ConsumerSecret"]);

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton<NHibernate.ISessionFactory>(serviceProvider =>
                AspNetCoreExample.Ddd.Mapper.TheMapper.BuildSessionFactory(this.ConnectionString)
            );

            services.AddSingleton<IDatabaseFactory, DatabaseFactory>();

            services.AddTransient<Microsoft.AspNetCore.Identity.IUserStore<Ddd.IdentityDomain.User>, UserStore>();

            services.AddTransient<Microsoft.AspNetCore.Identity.IRoleStore<Ddd.IdentityDomain.Role>, RoleStore>();

            services.AddIdentity<Ddd.IdentityDomain.User, Ddd.IdentityDomain.Role>().AddDefaultTokenProviders();

            services.AddAuthentication()
                    .AddFacebook(options => (options.AppId, options.AppSecret) = this.FacebookOptions)
                    .AddGoogle(options => (options.ClientId, options.ClientSecret) = this.GoogleOptions)
                    .AddTwitter(options => (options.ConsumerKey, options.ConsumerSecret) = this.TwitterOptions)
                    ;


            services.ConfigureApplicationCookie(config =>
            {
                config.Events = new Microsoft.AspNetCore.Authentication.Cookies.CookieAuthenticationEvents
                {
                    OnRedirectToLogin = ctx =>
                    {
                        if (ctx.Request.Path.StartsWithSegments("/api"))
                        {
                            ctx.Response.StatusCode = (int)System.Net.HttpStatusCode.Unauthorized;
                        }
                        else
                        {
                            ctx.Response.Redirect(ctx.RedirectUri);
                        }
                        return Task.FromResult(0);
                    }
                };
            });


            // Add application services.
            services.AddTransient<IEmailSender, EmailSender>();

            services.AddMvc();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseBrowserLink();
                app.UseDatabaseErrorPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }

            app.UseStaticFiles();

            app.UseAuthentication();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}


Happy Coding!