Regarding calling of BeginTransaction()

Jun 3, 2014 at 7:44 AM
Hi All,

Can anyone help me when I need to initiate the BeginTransaction(). I have not seen any samples helping in this regard. And also, can anyone let me know how a Parent-Child insert works.

I have a tables called UserProfile and UserInRole. I am trying to insert into DB and I am getting referential conflict. Below are the table schema, my ef code and db code.

Table Schema:
CREATE TABLE [dbo].[UserInRoles](
[UserInRoleId] [int] IDENTITY(1,1) NOT NULL,
[RoleId] [int] NOT NULL,
[UserId] [int] NOT NULL,
[CreatedBy] [nvarchar](max) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](max) NULL,
[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_UserInRoles] PRIMARY KEY CLUSTERED
(
[UserInRoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[UserProfiles](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[UserName] [nvarchar](56) NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[PhoneNumber] [nvarchar](20) NULL,
[MobileNumber] [nvarchar](20) NULL,
[Email] [nvarchar](50) NOT NULL,
[PhotoUrl] [nvarchar](256) NULL,
[ConfirmationToken] [nvarchar](128) NULL,
[IsActive] [bit] NULL,
[Password] [nvarchar](128) NOT NULL,
[PasswordChangedDate] [datetime] NULL,
[PasswordVerificationToken] [nvarchar](128) NULL,
[PasswordVerificationTokenExpirationDate] [datetime] NULL,
[LastPasswordFailureDate] [datetime] NULL,
[PasswordFailuresSinceLastSuccess] [int] NULL,
[IsDeleted] [bit] NOT NULL,
[CreatedBy] [nvarchar](100) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ModifiedBy] [nvarchar](100) NULL,
[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_UserProfiles] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[UserInRoles] WITH CHECK ADD CONSTRAINT [FK_RoleUserInRole] FOREIGN KEY([RoleId])
REFERENCES [dbo].[Roles] ([RoleId])
GO
ALTER TABLE [dbo].[UserInRoles] CHECK CONSTRAINT [FK_RoleUserInRole]
GO
ALTER TABLE [dbo].[UserInRoles] WITH CHECK ADD CONSTRAINT [FK_UserProfileUserInRole] FOREIGN KEY([UserId])
REFERENCES [dbo].[UserProfiles] ([UserId])
GO
ALTER TABLE [dbo].[UserInRoles] CHECK CONSTRAINT [FK_UserProfileUserInRole]
GO

EF Code:
I went about creating the entities using the DB first approach.
public partial class UserInRole : EntityBase
{
    public int UserInRoleId { get; set; }
    public int UserId { get; set; }
    public string CreatedBy { get; set; }
    public System.DateTime CreatedOn { get; set; }
    public string ModifiedBy { get; set; }
    public Nullable<System.DateTime> ModifiedOn { get; set; }

   public virtual UserProfile UserProfile { get; set; }
 }
public partial class UserProfile : EntityBase
{
    public UserProfile()
    {
        this.UserInRoles = new HashSet<UserInRole>();
        this.Roles = new List<Role>();
    }

    public int UserId { get; set; }
    public string UserName { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PhoneNumber { get; set; }
    public string MobileNumber { get; set; }
    public string Email { get; set; }
    public string PhotoUrl { get; set; }
    public string ConfirmationToken { get; set; }
    public Nullable<bool> IsActive { get; set; }
    public string Password { get; set; }
    public Nullable<System.DateTime> PasswordChangedDate { get; set; }
    public string PasswordVerificationToken { get; set; }
    public Nullable<System.DateTime> PasswordVerificationTokenExpirationDate { get; set; }
    public Nullable<System.DateTime> LastPasswordFailureDate { get; set; }
    public Nullable<int> PasswordFailuresSinceLastSuccess { get; set; }
    public bool IsDeleted { get; set; }
    public string CreatedBy { get; set; }
    public System.DateTime CreatedOn { get; set; }
    public string ModifiedBy { get; set; }
    public Nullable<System.DateTime> ModifiedOn { get; set; }

     public virtual ICollection<UserInRole> UserInRoles { get; set; }
}
Save()
public int SaveUser(UserProfile user)
    {
        //validate user
        var errors = ValidateUser(user);
        if (errors.Count() > 0)
        {
            this.ValidationErrors = errors;
            return user.UserId;
        }
        var password = user.Password;
        var hashedPassword = CreateHash(password);
        user.Password = hashedPassword; //we store the hashed password
        var isNew = user.UserId == default(int);
        if (isNew)
        {
            List<UserInRole> lstUserInRoles = new List<UserInRole>();

            foreach (var item in user.Roles)
            {
                UserInRole userInRole = new UserInRole();
                userInRole.RoleId = item.RoleId;
                userInRole.CreatedBy = this.OperationContext.CurrentUserName;
                lstUserInRoles.Add(userInRole);
            }
            user.UserInRoles = lstUserInRoles;
            user.CreatedBy = this.OperationContext.CurrentUserName;
            UnitOfWork.Repository<UserProfile>().Insert(user) ;
            UnitOfWork.Commit();
        }
        else
        {
            user.ModifiedBy = this.OperationContext.CurrentUserName;
            UnitOfWork.Repository<UserProfile>().Update(user);
            UnitOfWork.Commit();
        }

        //TODO: send email to user with passoword

        return user.UserId;
You help is much appreciated.
Thanks in advance.

Regards,
Srini.
Jun 3, 2014 at 10:27 AM
Hi, Srini,

Regarding calling of BeginTransaction you have a sample of code in https://genericunitofworkandrepositories.codeplex.com/wikipage?title=Quick%20Samples%20in%20LINQPad
var productRepository = new Repository<Product>(this);
var product2 = await productRepository.FindAsync(7);
product2.Dump();

// Begin transaction
unitOfWork.BeginTransaction();

try{
    product2.ProductName = "Chai4";
    product2.ObjectState = ObjectState.Modified; // always set the ObjectState
    
    // <Do other transactions here>
    
    productRepository.Update(product2);
    
    var changes = await unitOfWork.SaveChangesAsync();
    changes.Dump("changes");
    
    // Commit Transaction
    unitOfWork.Commit();
}
catch{
    // Rollback transaction
    unitOfWork.Rollback();
}

product2 = await productRepository.FindAsync(7);
product2.Dump();
Marked as answer by lelong37 on 6/3/2014 at 10:59 AM