c# - 如果字段为空，则检查记录是否存在返回始终为 false

Question

所以我有以下情况:

如您所见，有些字段为空，所以我想在插入记录之前检查它是否已经存在于表 goal 中，我要插入的记录包含完全相同的结构表中已有的记录。

这是我的代码:

public bool CheckGoalExist(Goal goal, Goal.GoalType type, int matchId)
{
    using (MySqlConnection connection = new DBConnection().Connect())
    {
        using (MySqlCommand command = new MySqlCommand())
        {
            command.Connection = connection;
            command.CommandText = "SELECT COUNT(*) FROM goal " +
                "WHERE player_marker_id = @player_marker_id AND " +
                "team_id = @team_id AND " +
                "player_assist_id = @player_assist_id AND " +
                "match_id = @match_id AND " +
                    "minute = @minute AND " +
                    "type = @type";

            command.Parameters.AddWithValue("@team_id", goal.TeamId);
            command.Parameters.AddWithValue("@player_marker_id", goal.MarkerPlayer.Id);
            command.Parameters.AddWithValue("@player_assist_id", goal.AssistPlayer?.Id);
            command.Parameters.AddWithValue("@match_id", matchId);
            command.Parameters.AddWithValue("@minute", goal.Minute);
            command.Parameters.AddWithValue("@type", GetGoalTypeId(type));

            return Convert.ToBoolean(command.ExecuteScalar());
        }
    }
}

这将返回 false 但 goal 的值是这样的:

TeamId = 95
MarkerPlayer.Id = 122
AssistPlaer = null
matchId = 2564940
Minute = 82'
Type = 5

为什么返回false？

Answer 1

如果 AssistPlaer 为 null，则不能使用 =。您需要先检查参数是否为 null。以下是使用 或 语句的常见方法:

command.CommandText = "SELECT COUNT(*) FROM goal " +
            "WHERE player_marker_id = @player_marker_id AND " +
            "team_id = @team_id AND " +
            "(@player_assist_id is null or player_assist_id = @player_assist_id) AND " +
            "match_id = @match_id AND " +
                "minute = @minute AND " +
                "type = @type";

您可能还需要对其他可能的 null 值执行此操作。