Get specific columns from the database with EF

advertisements

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.


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();