开发者

Petapoco deleting datas while having one to many relations?

In my project we are using petapoco as our ORM for data access. But i face some difficulties while deleting datas which have one to many relations. I have two tables named user and group. Here one user can be a memberof more than one group. so the user and group table is related by using another table named UserGroup which is not exposed in our project as a sepe开发者_开发问答rate model. So how can i delete the users from a group using petapoco?

My user model looks like this

[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; }
}

My group model looks like this

[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; }
}

Any i have done deletion like this

    /// <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);
        }
    }

Do anyone give me a help in resolving this?


Instead of using db.Query you would want to use 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);
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜