Generate Insert and Update Query Using Entity (snake_case)

code.cs

private static List<(string name, string paramName, object value)> GenerateNameValuePair(object obj, params string[] ignoredProperties)
{
  List<(string name, string paramName, object value)> pairs = new List<(string name, string paramName, object value)>();

  var props = obj.GetType().GetProperties();

  foreach (var prop in props)
  {
    if (!IsPrimitiveType(prop.PropertyType) || ignoredProperties.Contains(prop.Name)) continue;

    pairs.Add((prop.Name.ToSnakeCase(), $"@{prop.Name.ToSnakeCase()}", prop.GetValue(obj)));
  }

  return pairs;
}

private static bool IsPrimitiveType(Type type)
{
  return
    type.IsValueType ||
    type == typeof(string);
}

private static string GetTableName<T>() where T : class => typeof(T).Name.Substring(2).Pluralize().ToSnakeCase();

private static NpgsqlCommand GenerateInsertQuery<T>(T obj, params string[] ignoredProperties) where T : class
{
  NpgsqlCommand query = new NpgsqlCommand();

  var pairs = GenerateNameValuePair(obj, ignoredProperties);

  var allFields = string.Join(", ", pairs.Select(m => $@"""{m.name}"""));
  var parametersName = string.Join(", ", pairs.Select(m => m.paramName));

  var parameters = pairs.Select(m =>
  {
    var param = new NpgsqlParameter(m.paramName, m.value ?? DBNull.Value);

    if (m.value != null && m.value.GetType().IsEnum)
    {
      param.Value = (byte)m.value;
    }

    return param;
  }).ToArray();

  query.CommandText = $"INSERT INTO {GetTableName<T>()} ({allFields}) VALUES({parametersName})";
  query.Parameters.AddRange(parameters);

  return query;
}

private static NpgsqlCommand GenerateUpdateQuery<T>(T obj, Expression<Func<T, object>> keyExpression, params string[] ignoredProperties) where T : class
{
  NpgsqlCommand query = new NpgsqlCommand();

  var pairs = GenerateNameValuePair(obj, ignoredProperties);

  string allFields = string.Join(", ", pairs.Select(m => $@"""{m.name}"" = {m.paramName}"));
  NpgsqlParameter[] parameters = pairs.Select(m =>
  {
    var param = new NpgsqlParameter(m.paramName, m.value ?? DBNull.Value);

    if (m.value != null && m.value.GetType().IsEnum)
    {
      param.Value = (byte)m.value;
    }

    return param;
  }).ToArray();

  string primaryKeyFieldName = ((keyExpression.Body as UnaryExpression).Operand as MemberExpression).Member.Name.ToSnakeCase();
  object primaryKeyParamName = pairs.First(m => m.name == primaryKeyFieldName).paramName;

  query.CommandText = $"UPDATE {GetTableName<T>()} SET {allFields} WHERE {primaryKeyFieldName} = {primaryKeyParamName}";
  query.Parameters.AddRange(parameters);
  return query;
}

private static NpgsqlCommand GenerateUpdateQuery<T>(T obj, string[] primaryKeyProps, params string[] ignoredProperties) where T : class
{
  NpgsqlCommand query = new NpgsqlCommand();

  List<(string name, string paramName, object value)> pairs = GenerateNameValuePair(obj, ignoredProperties);

  string allFields = string.Join(", ", pairs.Select(m => $@"""{m.name}"" = {m.paramName}"));
  NpgsqlParameter[] parameters = pairs.Select(m =>
  {
    var param = new NpgsqlParameter(m.paramName, m.value ?? DBNull.Value);

    if (m.value != null && m.value.GetType().IsEnum)
    {
      param.Value = (byte)m.value;
    }

    return param;
  }).ToArray();

  List<string> primaryKeyConditions = new List<string>();

  foreach (var primaryKeyProp in primaryKeyProps)
  {
    string primaryKeyFieldName = primaryKeyProp.ToSnakeCase();
    string primaryKeyParamName = pairs.First(m => m.name == primaryKeyFieldName).paramName;

    primaryKeyConditions.Add($"{primaryKeyFieldName} = {primaryKeyParamName}");
  }

  string primaryKeyConditionStr = string.Join(" AND ", primaryKeyConditions);

  query.CommandText = $"UPDATE {GetTableName<T>()} SET {allFields} WHERE {primaryKeyConditionStr}";
  query.Parameters.AddRange(parameters);
  return query;
}