This is a followup to the last article Easy Conversion from Enterprise Architect XML SQL Queries to C# Objects.

Every time we execute a query that returns results from more than one table we have to use aliasing in the SQL statement to differentiate columns with the same name. See below a query that retrieves all connectors of an element along with the GUIDs of the connected elements (which are unfortunately not in the t_connector table):

SELECT [con].[Connector_Type], [con].[Name], [con].[Stereotype], 
  [tsrc].[ea_guid], [tsrc].[Name], 
  [tdst].[ea_guid], [tdst].[Name], 
  [src].*
FROM 
((((
  [t_connector] [con]
  INNER JOIN [t_object] [src] ON [con].[Start_Object_ID] = [src].[Object_ID]
)
  INNER JOIN [t_object] [dst] ON [con].[End_Object_ID] = [dst].[Object_ID]
)
  INNER JOIN [t_object] [tsrc] ON [con].[Start_Object_ID] = [tsrc].[Object_ID]
)
  INNER JOIN [t_object] [tdst] ON [con].[End_Object_ID] = [tdst].[Object_ID]
)
WHERE 
(
  [con].[Start_Object_ID] = [src].[Object_ID] OR [con].[End_Object_ID] = [src].[Object_ID]
)
AND 
(
  [src].[ea_guid] = '<Search Term>' OR [dst].[ea_guid] = '<Search Term>'
)

In order to still be able to use our SqlXmlDeserialiser I added a TableAlias property to our existing SqlXmlColumnAttribute:

[AttributeUsage(AttributeTargets.Property)]
public class SqlXmlColumnAttribute : Attribute
{
  public string Name { get; set; }

  public string TableAlias { get; set; }
}

Next we have to adjust the selector builder so it can handle the optional table alias:

public class SelectorBuilder
{
  private readonly Type type;

  public SelectorBuilder(Type type)
  {
    Contract.Requires(null != type);

    this.type = type;
  }

  public override string ToString()
  {
    const string COLUMN_DELIMITER = ", ";
    const string QUOTED_PARAM = "[{0}]";
    const string PARAM_0_AS_1 = "[{0}] AS [{1}]";
    const string PARAM_0_AS_1_WITH_TABLE_ALIAS = "[{0}].[{1}] AS [{2}]";
    const char SPACE_CHAR = ' ';

    var propertyInfos = type.GetProperties(BindingFlags.Instance | BindingFlags.FlattenHierarchy | BindingFlags.Public);

    var sb = new StringBuilder().Append("SELECT ");

    var iteration = 0;
    foreach (var propertyInfo in propertyInfos)
    {
      var dataMemberAttribute = propertyInfo.GetCustomAttribute<DataMemberAttribute>();
      if(null == dataMemberAttribute) continue;

      if (1 <= iteration)
      {
        sb.Append(COLUMN_DELIMITER);
      }
      ++iteration;


      var name = propertyInfo.Name;
      var displayName = dataMemberAttribute.Name;

      var eaMemberAttribute = propertyInfo.GetCustomAttribute<SqlXmlColumnAttribute>();
      var columnName = eaMemberAttribute?.Name;
      var tableName = eaMemberAttribute?.TableAlias;

      if (string.IsNullOrWhiteSpace(columnName) && string.IsNullOrWhiteSpace(displayName))
      {
        sb.AppendFormat(QUOTED_PARAM, name);
      }
      else
      {
        if (!string.IsNullOrWhiteSpace(columnName) && !string.IsNullOrWhiteSpace(displayName))
        {
          if (columnName != displayName)
          {
            if (string.IsNullOrWhiteSpace(tableName))
            {
              sb.AppendFormat(PARAM_0_AS_1, columnName, displayName);
            }
            else
            {
              sb.AppendFormat(PARAM_0_AS_1_WITH_TABLE_ALIAS, tableName, columnName, displayName);
            }
          }
          else
          {
            sb.AppendFormat(QUOTED_PARAM, columnName);
          }
        }
        else if (!string.IsNullOrWhiteSpace(columnName))
        {
          if (columnName != name)
          {
            if (string.IsNullOrWhiteSpace(tableName))
            {
              sb.AppendFormat(PARAM_0_AS_1, columnName, name);
            }
            else
            {
              sb.AppendFormat(PARAM_0_AS_1_WITH_TABLE_ALIAS, tableName, columnName, name);
            }
          }
          else
          {
            sb.AppendFormat(QUOTED_PARAM, columnName);
          }
        }
        // !string.IsNullOrWhiteSpace(displayName)
        else
        {
          sb.AppendFormat(QUOTED_PARAM, displayName);
        }
      }
    }
    return sb.Append(SPACE_CHAR).ToString();
  }
}

Now we have to modify the FormatQuery method so that not just cuts the prefixed SELECT * away but actually everything until the first * character:

[DataContract(Name = "Row", Namespace = "")]
public abstract class SqlXmlResultBase : ISqlXmlResult
{
  protected const string SELECT_PREFIX = "SELECT ";
  protected const string STAR_SELECTOR = "*";
  protected const int STAR_SELECTOR_LENGTH = 1;

  public static string CreateSelector(Type type)
  {
    return new SelectorBuilder(type).ToString();
  }

  // Only override this method if you intend to cache the SelectorBuilder results.
  // All other methods must stay as they are.
  public virtual string FormatQuery(string query)
  {
    return FormatQuery(query, new SelectorBuilder(GetType()).ToString());
  }

  protected string FormatQuery(string query, string selector)
  {
    Contract.Requires(query.StartsWith(SELECT_PREFIX));

    var index = query.IndexOf(STAR_SELECTOR, StringComparison.Ordinal) + STAR_SELECTOR_LENGTH;

    var result = selector + query.Substring(index);
    return result;
  }

  public string FormatQuery(string query, object searchTerm)
  {
    var fullQuery = FormatQuery(query);
    var result = null == searchTerm || string.IsNullOrWhiteSpace(searchTerm.ToString())
      ? fullQuery
      : fullQuery.Replace(Queries.SearchTerm, searchTerm.ToString());
    return result;
  }
}

So all in all we can define a SQL DTO like this that supports the deserialisation from different tables with different aliases (for example from the SQL query at the top):

[DataContract(Name = "Row", Namespace = "")]
public class ConnectorDto : SqlXmlResultBase
{
  private const string CONNECTOR_ALIAS = "con";
  private const string CLIENT_ALIAS = "tsrc";
  private const string SUPPLIER_ALIAS = "tdst";

  [DataMember(Name = "ID", IsRequired = true, Order = 0)]
  [SqlXmlColumn(Name = "Connector_ID", TableAlias = CONNECTOR_ALIAS)]
  public int Id { get; set; }

  [DataMember(Name = "TYPE0", IsRequired = true, Order = 1)]
  [SqlXmlColumn(Name = "Connector_Type", TableAlias = CONNECTOR_ALIAS)]
  public string Type { get; set; }

  [DataMember(Name = "CLIENTID", IsRequired = true, Order = 2)]
  [SqlXmlColumn(Name = "Start_Object_ID", TableAlias = CONNECTOR_ALIAS)]
  public string ClientIdAsString
  {
    get => ClientId.ToString();
    set => ClientId = int.Parse(value);
  }
  public int ClientId { get; set; }

  [DataMember(Name = "SUPPLIERID", IsRequired = true, Order = 3)]
  [SqlXmlColumn(Name = "End_Object_ID", TableAlias = CONNECTOR_ALIAS)]
  public string SupplierIdAsString
  {
    get => SupplierId.ToString();
    set => SupplierId = int.Parse(value);
  }
  public int SupplierId { get; set; }

  [DataMember(Name = "NOTES0", IsRequired = false, Order = 4)]
  [SqlXmlColumn(Name = "Notes", TableAlias = CONNECTOR_ALIAS)]
  public string Description { get; set; }

  [DataMember(Name = "EA_GUID0", IsRequired = true, Order = 5)]
  [SqlXmlColumn(Name = "ea_guid", TableAlias = CONNECTOR_ALIAS)]
  public Guid Guid { get; set; }

  [DataMember(Name = "STEREOTYPE0", IsRequired = false, Order = 6)]
  [SqlXmlColumn(Name = "Stereotype", TableAlias = CONNECTOR_ALIAS)]
  public string Stereotype { get; set; }

  [DataMember(Name = "NAME0", IsRequired = false, Order = 7)]
  [SqlXmlColumn(Name = "Name", TableAlias = CONNECTOR_ALIAS)]
  public string Name { get; set; }

  [DataMember(Name = "EA_GUID1", IsRequired = true, Order = 8)]
  [SqlXmlColumn(Name = "ea_guid", TableAlias = CLIENT_ALIAS)]
  public Guid ClientGuid { get; set; }

  [DataMember(Name = "EA_GUID2", IsRequired = true, Order = 9)]
  [SqlXmlColumn(Name = "ea_guid", TableAlias = SUPPLIER_ALIAS)]
  public Guid SupplierGuid { get; set; }

  private static readonly Lazy<string> _selector = 
    new Lazy<string>(() => CreateSelector(typeof(ConnectorDto)));

  public override string FormatQuery(string query)
  {
    return FormatQuery(query, _selector.Value);
  }
}

That’s all for now – have fun.