Querying AvailabilityReplica.MemberState can return incorrect values

The following code may print different values for MemberState when fetched using SMO compared to running the SQL query directly.

using System;
using Microsoft.SqlServer.Management.Smo;
using System.Data;
using Microsoft.Data.SqlClient;

namespace SMOTEST
{
    internal class Program
    {
        class sqlCMD
        {
            public String m_ServerName { get; set; }
            public String m_AGName { get; set; }
            public String cmdMemberState()
            {
                String s = @"exec sp_executesql N'
            select* into #tmpag_availability_groups from master.sys.availability_groups
            select group_id, replica_id, replica_server_name, create_date, modify_date, endpoint_url, read_only_routing_url, primary_role_allow_connections, secondary_role_allow_connections, availability_mode, failover_mode, session_timeout, backup_priority, owner_sid, seeding_mode into #tmpar_availability_replicas from master.sys.availability_replicas

            SELECT
                arrc.replica_server_name, 
                COUNT(cm.member_name) AS node_count,
                SUM(cm.member_state) AS member_state_sum,
                SUM(cm.number_of_quorum_votes) AS quorum_vote_sum
            INTO #tmpar_availability_replica_cluster_info
            FROM
                (SELECT DISTINCT replica_server_name, node_name FROM master.sys.dm_hadr_availability_replica_cluster_nodes) AS arrc
            LEFT OUTER JOIN master.sys.dm_hadr_cluster_members AS cm ON UPPER(arrc.node_name) = UPPER(cm.member_name)
            GROUP BY arrc.replica_server_name

                SELECT

                CASE
                    WHEN arci.member_state_sum IS NULL OR arci.node_count = 0 THEN 3
                    WHEN arci.member_state_sum = 0 THEN 0
                    WHEN arci.member_state_sum < arci.node_count THEN 2
                    WHEN arci.member_state_sum = arci.node_count THEN 1
                    ELSE 3
                END
                       AS[MemberState]
                FROM
                #tmpag_availability_groups AS AG
                INNER JOIN #tmpar_availability_replicas AS AR ON (AR.replica_server_name IS NOT NULL) AND (AR.group_id=AG.group_id)
                LEFT OUTER JOIN #tmpar_availability_replica_cluster_info AS arci ON UPPER(AR.replica_server_name) = UPPER(arci.replica_server_name) 
                WHERE
                (AR.replica_server_name = @_msparam_0)and((AG.name = @_msparam_1))

                      DROP TABLE #tmpar_availability_replicas
    


                      DROP TABLE #tmpar_availability_replica_cluster_info
    


                      drop table #tmpag_availability_groups
    

                ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'" + m_ServerName + "',@_msparam_1=N'" + m_AGName +"'";
                return s;
            }
            public String cmdReplica()
            {
                String s = @"exec sp_executesql N'
                      select * into #tmpag_availability_groups from master.sys.availability_groups
    



                        select group_id, replica_id, replica_server_name,create_date, modify_date, endpoint_url, read_only_routing_url, primary_role_allow_connections, secondary_role_allow_connections, availability_mode,failover_mode, session_timeout, backup_priority, owner_sid, seeding_mode into #tmpar_availability_replicas from master.sys.availability_replicas
      


                      select group_id, replica_id, role,operational_state,recovery_health,synchronization_health,connected_state, last_connect_error_number,last_connect_error_description, last_connect_error_timestamp into #tmpar_availability_replica_states from master.sys.dm_hadr_availability_replica_states
    


                      select * into #tmpar_ags from master.sys.dm_hadr_availability_group_states
                      select ar.group_id, ar.replica_id, ar.replica_server_name, ar.availability_mode, (case when UPPER(ags.primary_replica) = UPPER(ar.replica_server_name) then 1 else 0 end) as role, ars.synchronization_health into #tmpar_availabilty_mode from #tmpar_availability_replicas as ar
                      left join #tmpar_ags as ags on ags.group_id = ar.group_id
                      left join #tmpar_availability_replica_states as ars on ar.group_id = ars.group_id and ar.replica_id = ars.replica_id
                      select am1.replica_id, am1.role, (case when (am1.synchronization_health is null) then 3 else am1.synchronization_health end) as sync_state, (case when (am1.availability_mode is NULL) or (am3.availability_mode is NULL) then null when (am1.role = 1) then 1 when (am1.availability_mode = 0 or am3.availability_mode = 0) then 0 else 1 end) as effective_availability_mode
                      into #tmpar_replica_rollupstate from #tmpar_availabilty_mode as am1 left join (select group_id, role, availability_mode from #tmpar_availabilty_mode as am2 where am2.role = 1) as am3 on am1.group_id = am3.group_id
                      drop table #tmpar_availabilty_mode
                      drop table #tmpar_ags
    


                      CREATE TABLE #ror_list_delimited (
                      replica_id uniqueidentifier,
                      read_only_routing_list nvarchar(max)
                      )
                      select replica_id,read_only_replica_id,routing_priority into #tmp_availability_read_only_routing_lists from sys.availability_read_only_routing_lists

                      INSERT INTO #ror_list_delimited
                      SELECT DISTINCT ars.replica_id, a.delimited
                      FROM #tmpar_availability_replicas AS ars
                      CROSS APPLY
                      (
                        SELECT STUFF(final_list.read_only_routing_list,1,1,'''') FROM (
                        SELECT CASE 
                        WHEN CHARINDEX('','', STUFF(priority_grouped_list.priority_group, 1, 1, '''')) = 0 THEN priority_grouped_list.priority_group
                        ELSE '',('' + STUFF(priority_grouped_list.priority_group, 1, 1, '''') + '')'' 
                          END 
                        FROM (
                        SELECT (SELECT  '',N''''''+ ars2.replica_server_name +''''''''
                        FROM #tmp_availability_read_only_routing_lists AS rrlists
                        INNER JOIN #tmpar_availability_replicas AS ars2
                        ON ars2.replica_id = rrlists.read_only_replica_id
                        WHERE rrlists.routing_priority = prioritylist.routing_priority
                        AND rrlists.replica_id = ars.replica_id
                        FOR XML PATH('''') ) AS priority_group
                        FROM
                        (SELECT  distinct(routing_priority)
                        FROM #tmp_availability_read_only_routing_lists where replica_id = ars.replica_id) AS prioritylist(routing_priority)) priority_grouped_list
                        FOR XML PATH('''') ) AS final_list(read_only_routing_list) ) AS a(delimited)
    


                      select replica_id,join_state into #tmpar_availability_replica_cluster_states from master.sys.dm_hadr_availability_replica_cluster_states
    


                SELECT
                AR.replica_server_name AS [Name],
                AR.replica_id AS [UniqueId],
                ISNULL(arstates.role, 3) AS [Role],
                ISNULL(arstates.operational_state, 6) AS [OperationalState],
                ISNULL(arstates.recovery_health, 2) AS [RollupRecoveryState],
                (case when arrollupstates.sync_state = 3 then 3 when (arrollupstates.effective_availability_mode = 1 or arrollupstates.role = 1) then arrollupstates.sync_state when arrollupstates.sync_state = 2 then 1 else 0 end) AS [RollupSynchronizationState],
                ISNULL(arstates.connected_state, 2) AS [ConnectionState],
                ISNULL(AR.create_date, 0) AS [CreateDate],
                ISNULL(AR.modify_date, 0) AS [DateLastModified],
                ISNULL(AR.endpoint_url, N'''') AS [EndpointUrl],
                ISNULL(AR.read_only_routing_url, '''') AS [ReadonlyRoutingConnectionUrl],
                ISNULL(rorlists.read_only_routing_list, '''') AS [ReadonlyRoutingListDelimited],
                ISNULL(AR.primary_role_allow_connections, 4) AS [ConnectionModeInPrimaryRole],
                ISNULL(AR.secondary_role_allow_connections, 3) AS [ConnectionModeInSecondaryRole],
                ISNULL(AR.availability_mode, 2) AS [AvailabilityMode],
                ISNULL(AR.failover_mode, 3) AS [FailoverMode],
                ISNULL(AR.session_timeout, -1) AS [SessionTimeout],
                ISNULL(arstates.last_connect_error_number,-1) AS [LastConnectErrorNumber],
                ISNULL(arstates.last_connect_error_description, N'''') AS [LastConnectErrorDescription],
                ISNULL(AR.backup_priority, -1) AS [BackupPriority],
                ISNULL(arstates.last_connect_error_timestamp, 0) AS [LastConnectErrorTimestamp],
                suser_sname(AR.owner_sid) AS [Owner],
                ISNULL(arcs.join_state, 99) AS [JoinState],
                ISNULL(AR.seeding_mode, 1) AS [SeedingMode]
                FROM
                #tmpag_availability_groups AS AG
                INNER JOIN #tmpar_availability_replicas AS AR ON (AR.replica_server_name IS NOT NULL) AND (AR.group_id=AG.group_id)
                LEFT OUTER JOIN #tmpar_availability_replica_states AS arstates ON AR.replica_id = arstates.replica_id
                LEFT OUTER JOIN #tmpar_replica_rollupstate AS arrollupstates ON AR.replica_id = arrollupstates.replica_id
                LEFT OUTER JOIN #ror_list_delimited AS rorlists ON AR.replica_id = rorlists.replica_id
                LEFT OUTER JOIN #tmpar_availability_replica_cluster_states AS arcs ON AR.replica_id = arcs.replica_id
                WHERE
                (AR.replica_server_name=@_msparam_0)and((AG.name=@_msparam_1))

                      DROP TABLE #tmpar_availability_replicas
    


                      DROP TABLE #tmpar_availability_replica_states
    


                      DROP TABLE #tmpar_replica_rollupstate
    


                      DROP TABLE #ror_list_delimited
                      DROP TABLE #tmp_availability_read_only_routing_lists
    


                      DROP TABLE #tmpar_availability_replica_cluster_states
    


                      drop table #tmpag_availability_groups
    

                ',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'"+ m_ServerName+"',@_msparam_1=N'" + m_AGName + "'";
                return s;
            }
        }
        static void Main(string[] args)
        {

           sqlCMD m_sqlCMD = new sqlCMD();

            Console.WriteLine("Enter Server Name :");
            String sName = Console.ReadLine();

                Server m_server = new Server(sName);
                m_server.ConnectionContext.LoginSecure = true;
                try
                {
                    int iNumber = m_server.BuildNumber;
                    Console.WriteLine("Build Number : " + iNumber);
                } catch (Exception e)
                {
                    Console.WriteLine("Cannot connect to server");
                    return;
                }
                
                
                AvailabilityGroupCollection AGs = m_server.AvailabilityGroups;
                
                if (AGs.Count > 0)
                {
                    //AvailabilityReplicaCollection replicas = AGs["A"].AvailabilityReplicas;
                    
                    foreach (AvailabilityGroup AG in AGs)
                    {
                    Console.WriteLine("AG Name : " + AG.Name);
                    m_sqlCMD.m_AGName = AG.Name;
                    
                        foreach (AvailabilityReplica replica in AG.AvailabilityReplicas)
                        {
                        m_sqlCMD.m_ServerName = replica.Name;
                            Console.WriteLine("Name : " + replica.Name);
                            Console.WriteLine("State :  " + replica.MemberState);
                            Console.WriteLine("Role : " + replica.Role);
                            
                            SqlDataReader rdr = m_server.ConnectionContext.ExecuteReader(m_sqlCMD.cmdMemberState());
                            while (rdr.Read())
                            {
                                Console.WriteLine(" Query State : " + rdr["MemberState"].ToString());
                            }
                            rdr.Close();
                            rdr = m_server.ConnectionContext.ExecuteReader(m_sqlCMD.cmdReplica());
                            while (rdr.Read())
                            {
                                Console.WriteLine("Query Role : " + rdr["Role"].ToString());
                            }
                            rdr.Close();
                        }
                    }

                }
            //}
            Console.WriteLine("Finished... press any key");
            Console.ReadKey();
            
        }
    }
}

The workaround is to call m_server.SetDefaultInitFields(typeof(AvailabilityReplica), nameof(AvailabilityReplica.MemberState) before accessing the collections.

Comienza con la reproducción de C# proporcionada y compara AvailabilityReplica.MemberState de SMO con la consulta SQL directa para el mismo servidor y grupo de disponibilidad. Sigue la ruta de consulta de AvailabilityReplica y el cálculo de MemberState mostrado en la reproducción; se considera terminado cuando ambas rutas de acceso devuelven el mismo valor para el escenario indicado.