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);
精彩评论