Windows Phone 7.1 - Many-To-One relation on the same table - DB

Question

I'm trying to create a DB that has a Many-To-One relation between an object to itself. (I have a user and the user should have a list of other users that he knows...)

I have 2 questions:

I know that in order to do a Many-To-One relation between different table you create an entitySet on the side that should have the list and an entityRef on the other side. One thing I did not undertood in this process : In the object from the "Many" side - there are to things that are added- a id (which is the primaryKey to the "one" side, and also a object of the "one" side type. Why do I need to do so-to create an object? can't I just do the key?

code Example:

(one country-namy cities- Why there is a counrty object and alse a country Id in the city class???)

    [Table]
    public class Country
     {
    private EntitySet<City> citiesRef;

    public Country()
    {
        this.citiesRef = new EntitySet<City>(this.OnCityAdded, this.OnCityRemoved);
    }

    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int ID
    {
        get;
        set;
    }

    [Column(CanBeNull = false)]
    public string Name
    {
        get;
        set;
    }

    [Association(Name = "FK_Country_Cities", Storage = "citiesRef", ThisKey = "ID", OtherKey = "CountryID")]
    public EntitySet<City> Cities
    {
        get
        {
            return this.citiesRef;
        }
    }

    private void OnCityAdded(City city)
    {
        city.Country = this;
    }

    private void OnCityRemoved(City city)
    {
        city.Country = null;
    }
}



    [Table]
    public class City
    {
    private Nullable<int> countryID;
    private EntityRef<Country> countryRef = new EntityRef<Country>();

    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int ID
    {
        get;
        set;
    }

    [Column(CanBeNull = false)]
    public string Name
    {
        get;
        set;
    }

    [Column(Storage = "countryID", DbType = "Int")]
    public int CountryID
    {
        get
        {
            return this.countryID;
        }
        set
        {
            this.countryID = value;
        }
    }

    [Association(Name = "FK_Country_Cities", Storage = "countryRef", ThisKey = "CountryID", OtherKey = "ID", IsForeignKey = true)]
    public Country Country
    {
        get
        {
            return this.countryRef.Entity;
        }
        set
        {
            Country previousValue = this.countryRef.Entity;
            if (((previousValue != value) || (this.countryRef.HasLoadedOrAssignedValue == false)))
            {
                if ((previousValue != null))
                {
                    this.countryRef.Entity = null;
                    previousValue.Cities.Remove(this);
                }
                this.countryRef.Entity = value;
                if ((value != null))
                {
                    value.Cities.Add(this);
                    this.countryID = value.ID;
                }
                else
                {
                    this.countryID = default(Nullable<int>);
                }
            }
        }
    }
}

How do I create that Many-To-One relation that I want (from User to User)? I've tried something and it does not get me anywhere.. (Also -I don't really understand what I'm doing there- because basically the results are that each user will have a id,friendId and a friendList and I don't want the friend ID...

 [Table]
  public class User
  {
    [Column (IsPrimaryKey=true)]
    public int Id { get; set; }
    [Column]
    public string Name { get; set; }
    [Column]
    public string Gender { get; set; }

private EntitySet<User> friends;//the list of users the user knows
[Association(Storage = "friends", ThisKey = "Id", OtherKey = "MyFriendId")]
public EntitySet<User> Friends
{
    get { return friends; }
    set { friends.Assign(value); }         
}

public User(int _id, string _name, string _gender)
{
    Id = _id;
    Name = _name;
    Gender = _gender;  
    Friends = new EntitySet<User>();

}

[Column(CanBeNull = false)]
public int MyFriendId { get; set; }//the other user id
EntityRef<User> myFriend;//the other user object-why??
[Association(Storage = "myFriend", ThisKey = "MyFriendId", OtherKey = "Id", IsForeignKey = true)]
public User MyFriend
{
    get
    { return myFriend.Entity; }
    set
    { myFriend.Entity = value; }
}

} }

Any help? Thanks a lot!

John · Answer 1 · 2013-05-23T22:23:47.127

0

I suspect you are trying to be to concise. add a second table to hold the store of related users.

columns

ID, IDOfUserWithRelations & RelateduserID

QED.

edited May 23 '13 at 22:23

answered May 23 '13 at 21:08

John

1,714
21
41

Do you mean I sould have a table(lets call it UserRelations) that has 2 users? (meaning in the UserRelations table I will have many rows of user A with alot of other users?) wouldnt it be many to many then? – nati May 23 '13 at 23:27
yes. it may look like this: 1 - USER1 - USER 2; 2 - USER1 - USER 3; 2 - USER1 - USER 4; 3 - USER2 - USER 5; 4 - USER2 - USER 6; etc – John May 24 '13 at 15:32

Windows Phone 7.1 - Many-To-One relation on the same table - DB

1 Answers1