Petapoco 在具有一对多关系的同时删除数据？

Question

在我的项目中，我们使用 petapoco 作为数据访问的 ORM。但是我在删除具有一对多关系的数据时遇到一些困难。我有两个名为用户和组的表。这里，一个用户可以是多个组的成员。因此，用户和组表通过使用另一个名为 UserGroup 的表进行关联，该表在我们的项目中未作为单独的模型公开。那么如何使用 petapoco 从组中删除用户呢？

我的用户模型看起来像这样

[TableName("User")]
[PrimaryKey("UserId")]
[ExplicitColumns]
public class User
{
    [Column("UserId")]
    public int Id { get; set; }

    [Column]
    [Required]
    public Guid Identifier { get; set; }

    [Column("UserStatusId")]
    [Required]
    public UserStatus UserStatus { get; set; }

    [Column("RoleId")]
    [Required]
    public Role Role { get; set; }

    [Column]
    public int? SchoolId { get; set; }

    [Column]
    public int? LanguageId { get; set; }

    [Column]
    public int? LevelId { get; set; }

    [Column("TitleId")]
    public Title Title { get; set; }

    [Column]
    public int? GenderId { get; set; }

    [Column]
    [Required]
    [StringLength(35)]
    public string Username { get; set; }

    [StringLength(100)]
    public string Password { get; set; }

    [Column]
    [StringLength(100)]
    public string PasswordEncrypted { get; set; }

    [Column]
    [StringLength(10)]
    public string PasswordSalt { get; set; }

    [Column]
    //[Required]
    [DataType(DataType.EmailAddress)]
    [StringLength(255)]
    public string Email { get; set; }

    [Column]
    [Required]
    [StringLength(35)]
    public string FirstName { get; set; }

    [Column]
    [Required]
    [StringLength(35)]
    public string LastName { get; set; }

    public string FullName
    { 
        get
        {
            return string.Concat(FirstName, " ", LastName);
        }
    }

    public string FormalName
    {
        get
        {
            if ((int)Title != 0)
            {
                return string.Concat(Title, " ", LastName);
            }
            else
            {
                return LastName;
            }
        }
    }

    [Column]
    public DateTime? DateOfBirth { get; set; }

    [Column]
    public bool Subscribed { get; set; }

    [Column]
    public bool TermsAgreed { get; set; }

    [Column]
    public DateTime? TermsAgreedDate { get; set; }

    [Column]
    public DateTime CreatedDate { get; set; }

    [Column]
    public DateTime ModifiedDate { get; set; }

    [Column]
    public DateTime? LoginDate { get; set; }

    public School School { get; set; }

    public Gender Gender { get; set; }

    public Language Language { get; set; }

    public Level Level { get; set; }

    public IList<Group> Groups { get; set; }
}

我的组模型看起来像这样

[TableName("Group")]
[PrimaryKey("GroupId")]
[ExplicitColumns]
public class Group
{
    [Column("GroupId")]
    public int Id { get; set; }

    [Column]
    public int OwnerUserId { get; set; }

    [Column]
    public string Name { get; set; }

    [Column]
    public DateTime CreatedDate { get; set; }

    [Column]
    public DateTime ModifiedDate { get; set; }

    public User OwnerUser { get; set; }

    public IList<User> Users { get; set; }
}

我已经完成了这样的删除

    /// <summary>
    /// Delete users from groups
    /// </summary>
    /// <param name="userIds">user Id list</param>
    /// <param name="groupId">group Id</param>
    public void DeleteUsersFromGroup(List<int> userIds, int groupId)
    {
        using (Database db = this.GetDatabase())
        {
            var sql = Sql.Builder.Append(
                @"DELETE [UserGroup]
                LEFT OUTER JOIN [User] ON [UserGroup].[UserId] = [User].[UserId]
                WHERE [UserGroup].[UserId] IN (@0) AND [UserGroup].[GroupId] = @1",
                userIds,
                groupId);
            db.Query<User>(sql);
        }
    }

有人可以帮助我解决这个问题吗？

Answer 1

您不想使用 db.Query 而想使用 db.Execute ：

db.Execute(@"DELETE [UserGroup]
            LEFT OUTER JOIN [User] ON [UserGroup].[UserId] = [User].[UserId]
            WHERE [UserGroup].[UserId] IN (@0) AND [UserGroup].[GroupId] = @1",
            userIds,
            groupId);