Do i handle well database connections (Entity Framework)?

Question

I think i am doing something wrong in here so i will paste here so you can put me to the right track:

        public MembershipCreateStatus CreateUser(string username, string password, string mail,   bool autoemail, string fullname)
        {
            using (TemplateEntities ctx = new TemplateEntities())
            {
                using (TransactionScope tran = new TransactionScope())
                {
                    if (GetUser(username)!=null)
                        return MembershipCreateStatus.DuplicateUserName;
                    if (requiresUniqueEmail && !String.IsNullOrEmpty(GetUserNameByEmail(email)))
                        return MembershipCreateStatus.DuplicateEmail;                    
                    User userToCreate = new User
                    {
                        UserName=username,
                        PassWord=EncodePassword(password),
                        FullName=fullname,
                        Email=email,
                        AutoEmail=autoemail
                    };
                    try
                    {
                        ctx.Users.AddObject(userToCreate);
                        ctx.SaveChanges();
                        tran.Complete();
                        return MembershipCreateStatus.Success;
                    }
                    catch
                    {
                        return MembershipCreateStatus.UserRejected;
                    }
                }
            }
        }

        public override string GetUserNameByEmail(string email)
        {
            using (TemplateEntities ctx = new TemplateEntities())
            {
                return (from u in ctx.Users
                        where u.Email == email
                        select u.UserName).SingleOrDefault();
            }
        }

        public User GetUser(string username)
        {
            using (TemplateEntities ctx = new TemplateEntities())
            {
                return (from u in ctx.Users
                            where u.UserName == username
                            select u).FirstOrDefault();
            }
        }

1. 1Q)I am opening 3 connections to the database. Is it ok? or do i have to have only one connection and how to manage that correctly?
2. 2Q)Do i need transaction here and how to manage that correctly?

Answer 1

Re 3 connections; you might want to make those methods aware of a data-context, either by passing it is as an argument, or using partial classes or extension methods to add your method to the data-context (assuming it is the same type of data-context each time). That will maximise connection reuse and object-identity re-use.

Re the transaction; the submit should already be transactional. The question, then, is do I need my reads in the same transaction. And that depends :) Often it is ok not to, but you can get issues with phantom reads etc. Doing the reads inside a Serializable transaction (as you are) ensures that the read data will not change until the transaction commits. This can negatively impact throughput (especially if a distributed transaction gets involved, hence the desire to maximise connection reuse), however, and (in some cases, unless UPDLOCK is issued) introduce extra deadlocks.

So it is a trade-off. And it depends on your specific scenario.

As an example of re-writing those as to pass in the context:

public override string GetUserNameByEmail(TemplateEntities ctx, string email)
{
    return (from u in ctx.Users
            where u.Email == email
            select u.UserName).SingleOrDefault();
}

public User GetUser(TemplateEntities ctx, string username)
{
    return (from u in ctx.Users
            where u.UserName == username
            select u).FirstOrDefault();
}

And now you can pass in your context; you could perhaps overload the method if you want to still offer this as an option without passing in the context explicitly:

public User GetUser(string username)
{
    using(var ctx = new TemplateEntities()) { return GetUser(ctx, username);}
}