0

Consider the situation.
I have a userlogin table. the userlogin has the following fields.
userid(identity(1,1)), username(unique), password(string) I have another table, userRole with following fields.
userid(fk referencing userlogin), role(string)

Now suppose I want to add an admin user to my empty application database. What I am currently doing is: 

// Check Userlogin if it contains adminuser1 as username, if not, add adminuser1 with password xyz. 

UserLogin login = new UserLogin();  
login.username = "adminuser1";  
login.password = "xyz";  
context.UserLogins.Add(login);  
context.SaveChanges();  

// query again from database to get the userid   
Userlogin user = context.UserLogins.Single(l => (l.username == "adminuser1") && (l.password == "xyz"));  
int userid = user.userid;  
UserRole admin = new UserRole();  
admin.userid = userid;  
admin.role = "admin";  
context.UserRoles.Add(admin);  
context.SaveChanges();

I want to make it a less troublesome, if we can get the userid of userRecently Added, without making another request.

I mean I want to do this if it is possible. 

UserLogin login = new UserLogin();  
login.username = "adminuser1";  
login.password = "xyz";  
UserLogin user = context.UserLogins.Add(login);  
UserRole admin = new UserRole();  
admin.userid = user.userid;  
admin.role = "admin";  
context.UserRoles.Add(admin);  
context.SaveChanges();

Update

I also wanted to know if there is some way to do
context.UserLogins.Single(l => l == login);
instead of 

context.UserLogins.Single(l => (l.username == "adminuser1") && (l.password=="xyz"));

because I use the same method in large classes in many fields.

Majid Parvin
  • 4,499
  • 5
  • 29
  • 47
Pradeep Singh
  • 432
  • 5
  • 11
  • 1
    kindly have a look at https://stackoverflow.com/questions/17523568/entity-framework-retrieve-id-before-savechanges-inside-a-transaction – Arvind Maurya May 24 '19 at 07:51
  • Thanks, but I also wanted to know if there is some way to do context.UserLogins.Single(l=>l==login); instead of context.UserLogins.Single(l=>(l.username=="adminuser1")&& (l.password=="xyz"));...because, I use the same method in large classes with many fields. – Pradeep Singh May 24 '19 at 08:25

3 Answers3

0

It can be different based on your needs but you can have something like:

public class UserRole
{
    public int Id { get; set; }
    public string role { get; set; }
}

public class UserLogin
{
    public int Id { get; set; }
    public string username { get; set; }
    public string password { get; set; }
    public UserRole Role { get; set; }
}

and then use them like:

var login = new UserLogin
{
    username = "adminuser1",
    password = "xyz"
};
var admin = context.UserRoles.Single(_=> _.role == "admin");
if (admin == null)
{
    admin = new UserRole
    {
        role = "admin"
    };
}
login.Role = admin;  
context.UserLogins.Add(login);  
context.SaveChanges();
Majid Parvin
  • 4,499
  • 5
  • 29
  • 47
0

Your models' relationship seems wrong but based on your information you can have this:

var login = context.UserLogins.Single(_ => _.username == "adminuser1");
if (login == null)
{
  login = new UserLogin();
  login.username = "adminuser1";  
  login.password = "xyz";
  context.UserLogins.Add(login); 
  context.SaveChanges();
}
else
{
   // this user already exists.
}

var admin = context.UserRoles.Single(_ => _.role == "admin");
if (admin == null)
{
  admin.userid = login.userid;  
  admin.role = "admin"; 
  context.UserRoles.Add(admin);  
  context.SaveChanges();
}
else
{
  // the role already exists.
}

context.UserLogins.Single(l => l == login); would not work for you! you have to query DB based on your model key, not whole model data!

Majid Parvin
  • 4,499
  • 5
  • 29
  • 47
  • lets say a situation with your code that "adminuser1" doesnot exist. Then the code inside if statement executes, after making changes to the database, the login variable is still null while you try to access its property login.userid – Pradeep Singh May 24 '19 at 09:19
  • further, same userlogin can have different roles, that's why I think the database is correct. However, I am using design model from database, so there is no point in getting a wrong model as long as database is correct. – Pradeep Singh May 24 '19 at 09:23
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/193873/discussion-between-pradeep-singh-and-majid-parvin). – Pradeep Singh May 24 '19 at 09:27
  • Thanks @Majid for updating the question and your efforts in the answers. However, I found my own answer could satisfy me. – Pradeep Singh May 31 '19 at 14:56
0

For the question

What's the return value of DBSet.Add(object o)

The answer is: it will return the same object o(i.e. without the userid). Simply because userid is an identity column and relies on the database, its value is only available after context.SaveChanges() is called. Since Add() method only registers that a change will take place after SaveChanges() is called.

For the answer to update,
Instead of using

context.UserLogins.Single(l => (l.username == "adminuser1") && (l.password=="xyz"));

For classes that have many fields, I can check if there are any unique columns. For example. I could use, simply

context.UserLogins.Single(l => l.username == "adminuser1");

Just because, username(unique) is specified in the question.

I would rather recommend people use a single Stored Procedure. The calling of context.SaveChanges() and the context.xyz.Single() require opening database connection multiple times. For optimising performance you can use Stored Procedures, as they require only one connection per task. For more information.

Understang Performance Considerations

As I am using database first approach, I found this link also helpful.

Use Stored Procedure in Entity Framework

Thanks :)

Pradeep Singh
  • 432
  • 5
  • 11