Getting specific columns from Database with EF

Question

I have this database :

Users:
-idUser
-login
-password
-email

Relationship:
-idUser
-idFriend

And in my models i have this :

public class User
{
    [Key, ScaffoldColumn(false), Display(Name = "ID user")]
    public int idUser { set; get; }
    [StringLength(16), Display(Name = "Login")]
    public string login{ set; get; }
    [StringLength(16), Display(Name = "Password")]
    public string password{ set; get; }
    [StringLength(64), Display(Name = "Email")]
    public string email{ set; get; }
    public virtual ICollection<User> friends{ get; set; }

    public User()
    {
        friends = new List<User>();
    }
}

And I created this code in my context to enable the self referencing many to many relationships :

public DbSet<User> Users{ get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        modelBuilder.Entity<User>().HasMany(m => m.friends).WithMany().Map(m =>
        {
            m.MapLeftKey("idUser");
            m.MapRightKey("idFriend");
            m.ToTable("Relationship");
        });       
    }

And this part of code in my controller to respond to users requests :

[AcceptVerbs("POST")]
public User getMyInformations([FromBody] String[] data)
{
    string login = data[0];
    string password = data[1];
    Utilisateur me = null;
    User user = db.Users.Where<User>(u => u.login.Equals(login)).FirstOrDefault<User>();
    if (user != null)
    {
        if (user.password.Equals(password))
        {
            me = user;
        }
    }
    return me;
}

And finally i added this instruction in Global.asax to avoid Self referencing loop :

GlobalConfiguration.Configuration.Formatters.JsonFormatter.SerializerSettings.Re‌ferenceLoopHandling = ReferenceLoopHandling.Ignore;

Every thing work fine but i have a little problem : when I want to send Json to client side the Json looks like this :

{"friends":[{"friends":[],"idUser":2,"login":"user2","password":"1234",
 "email":"[email protected]"],"idUser":1,"login":"user1","password":"0000",
 "email":"[email protected]",}

but i dont like to send confidential information about the friends collection like the password to the user ! I think I can solve this by adding this code in my controller :

if (user.password.Equals(password))
{
    me = user;
    //adding this code :
    foreach (User friend in me.friends)
    {
        friend.password = null;
        //friend.email= null;
    }
}

but I'm looking for most significant way to do it ! I hope there is a way to prevent EF from getting some columns from the database.

Martin Shishkov · Accepted Answer · 2015-04-11 14:59:32Z

2

erkaner's answer is correct although you can do it with anonymous object as well

var user = db.Users
                  .Where(u => u.login.Equals(login))
                  .Select(usr => new {
                      login = usr.login,
                      email = usr.email,
                      //basically include what you want
                  })                  
.FirstOrDefault();

edited Apr 11, 2015 at 14:59

answered Apr 11, 2015 at 14:46

Martin Shishkov

2,8675 gold badges29 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Pim Over a year ago

This is what I'd do too. Though note that you can remove the User type specifier on the Where method and the member names in the anonymous type - they can both be inferred by the compiler.

Martin Shishkov Over a year ago

Ah yes I forgot that, sorry. Fixed! :)

Martin Shishkov · Accepted Answer · 2015-04-12 15:56:56Z

1

Well if you want to specify what to select for the user's friends you could simply add that:

var user = db.Users
             .Where(u => u.login.Equals(login))
             .Select(usr => new {
                 login = usr.login,
                 email = usr.email,
                 friends = usr.Friends.Select(fr => new {
                     // your friend info here Ex.:
                     email = fr.email,
                 })
              }).FirstOrDefault();

edited Apr 12, 2015 at 15:56

answered Apr 12, 2015 at 9:40

Martin Shishkov

2,8675 gold badges29 silver badges44 bronze badges

1 Comment

youssef1700 Over a year ago

Thanks, now its working like what I'm expecting ! note that you have to remove the "ToList()" method because it's raising an exception : "LINQ to Entities does not recognize the method System.Collections.Generic.List..... method, and this method cannot be translated into a store expression."

renakre · Accepted Answer · 2015-04-11 14:36:40Z

1

You can create new view class for User that does not contain the password property, lets say it is called UserView class.

UserView user = db.Users
              .Where<User>(u =>u .login.Equals(login))
              .Select(uw => new UserView () 
                  {
                      login = u.login,
                      email = u.email,
                      //do not include password here
                  }
              )                  
              .FirstOrDefault<User>();

Hope this helps!

answered Apr 11, 2015 at 14:36

renakre

8,3218 gold badges55 silver badges114 bronze badges

Comments

youssef1700 · Accepted Answer · 2015-04-11 16:26:17Z

0

Thanks for replying but I found better solution for my case, the answers of Martin Shishkov and erkaner didn't solved my problem because they are just preventing EF of getting the user password not the passwords in friends collection. I've created a view in my database called UserViews which contains the public informations of the users (in this case UserViews doesn't contain password attribute) and I've created a model called UserView having the public attributes too, and I've changed the friends collection in User model like this :

public virtual ICollection<UserView> friends{ get; set; }

and it works like what I'm expecting !

edited Apr 11, 2015 at 16:26

answered Apr 11, 2015 at 16:16

youssef1700

10911 bronze badges

Collectives™ on Stack Overflow

Getting specific columns from Database with EF

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related