skip to Main Content

I have some data from Facebook API and I need to store them on Azure SQL Db.
I created the models and I’m trying to set Foreign Keys to link the tables but I always have some errors.

My models:

public class FacebookDataUser
{
    [Key]
    [JsonProperty("id")]
    public string FacebookDataUserId { get; set; }
    public string name { get; set; }
    public string birthday { get; set; }
    public string email { get; set; }
    public virtual Hometown hometown { get; set; }
    public virtual Location location { get; set; }
    public virtual Events events { get; set; }
    public virtual Likes likes { get; set; }
    public virtual Age_Range age_range { get; set; }
    public string gender { get; set; }
}

public class Hometown
{
    [Key]
    [JsonProperty("id")]
    public string HometownId { get; set; }
    public string name { get; set; }

    public string FacebookDataUserId { get; set; }
    public FacebookDataUser FacebookDataUser { get; set; }
}

public class Location
{
    [Key]
    [JsonProperty("id")]
    public string LocationId { get; set; }
    public string name { get; set; }

    public string FacebookDataUserId { get; set; }
    public FacebookDataUser FacebookDataUser { get; set; }
}

public class Events
{
    [Key]
    public string EventsId { get; set; }
    public Datum[] data { get; set; }

    public string FacebookDataUserId { get; set; }
    public FacebookDataUser FacebookDataUser { get; set; }
}

public class Datum
{
    [Key]
    public string DatumId { get; set; }
    public string description { get; set; }
    public string name { get; set; }
    public DateTime start_time { get; set; }
    public string PlaceId { get; set; }
    public Place Place { get; set; }
    public int attending_count { get; set; }
    public string type { get; set; }
    public string rsvp_status { get; set; }
    public DateTime end_time { get; set; }

    public string FacebookDataUserId { get; set; }
    public FacebookDataUser FacebookDataUser { get; set; }
}

public class Place
{
    [Key]
    public string PlaceId { get; set; }
    public string name { get; set; }
    public string LocationEventId { get; set; }
    public LocationEvent location { get; set; }

    public string DatumId { get; set; }
    public Datum Datum { get; set; }
}

public class LocationEvent
{
    [Key]
    public string LocationEventId { get; set; }
    public string city { get; set; }
    public string country { get; set; }
    public float latitude { get; set; }
    public float longitude { get; set; }
    public string state { get; set; }
    public string street { get; set; }
    public string zip { get; set; }

    public string FacebookDataUserId { get; set; }
    public FacebookDataUser FacebookDataUser { get; set; }
}

public class Likes
{
    // Doesn't have ID for Likes, but I need to have a Key in all classes.
    // If I don't have, I get an exception
    [Key]
    public string LikesId { get; set; }
    public Datum1[] data { get; set; }

    public string FacebookDataUserId { get; set; }
    public FacebookDataUser FacebookDataUser { get; set; }
}

public class Datum1
{
    [Key]
    public string Datum1Id { get; set; }
    public string category { get; set; }
    public string name { get; set; }
    public int fan_count { get; set; }
    public string website { get; set; }
    public string LocationId { get; set; }
    public LocationEvent location { get; set; }
    public string[] emails { get; set; }

    public string FacebookDataUserId { get; set; }
    public FacebookDataUser FacebookDataUser { get; set; }
}


public class Age_Range
{
    // Doesn't have ID for Age_Range, but I need to have a Key in all classes.
    // If I don't have, I get an exception
    [Key]
    public string Age_RangeId { get; set; }
    public int min { get; set; }

    public string FacebookDataUserId { get; set; }
    public FacebookDataUser FacebookDataUser { get; set; }
}

I get this exception:

Unable to determine the principal end of an association between the types ‘ApiGroma.Models.Age_Range’ and ‘ApiGroma.Models.FacebookDataUser’. The principal end of this association must be explicitly configured using either the relationship fluent API or data annotations.

If I add [Required] on Age_Range, I get this exception from Facebook API:

"modelState": {
        "facebookDataUser.age_range.FacebookDataUser": [
            "The FacebookDataUser field is required."

So, I tried to fill the values of Foreign Keys “by hand” before the method Add in my [HttpPost] method.

facebookDataUser.hometown.FacebookDataUserId = facebookDataUser.FacebookDataUserId;
facebookDataUser.location.FacebookDataUserId = facebookDataUser.FacebookDataUserId;
facebookDataUser.age_range.FacebookDataUserId = facebookDataUser.FacebookDataUserId;
facebookDataUser.likes.FacebookDataUserId = facebookDataUser.FacebookDataUserId;
facebookDataUser.events.FacebookDataUserId = facebookDataUser.FacebookDataUserId;

db.FacebookDataUsers.Add(facebookDataUser);

But I keep receiving the exception.
What’s the proper way to do this?

It’s been 2 days since I began looking for a solution, reading Microsoft blogs and others, but I can’t fix this.

OBS: I am creating the database inside the context class.

Database.SetInitializer<MobileServiceContext>(new CreateDatabaseIfNotExists<MobileServiceContext>());

2

Answers


  1. Chosen as BEST ANSWER

    So, after some changes and compairing the codes, i got it working.

    public class FacebookDataUser
        {
            [Key,JsonProperty("id")]
            public string FacebookDataUserId { get; set; }
            public string name { get; set; }
            public string birthday { get; set; }
            public string email { get; set; }
            public virtual Hometown Hometown { get; set; }
            public virtual Location Location { get; set; }
            public virtual Events Events { get; set; }
            public virtual Likes Likes { get; set; }
            public virtual Age_Range Age_Range { get; set; }
            public string gender { get; set; }
        }
    
        public class Hometown
        {
            [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int HometownId { get; set; }
            public string id { get; set; }
            public string name { get; set; }
    
        }
    
        public class Location
        {
            [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int LocationId { get; set; }
            public string id { get; set; }
            public string name { get; set; }
    
        }
    
        public class Events
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int EventsId { get; set; }
            [JsonProperty("data")]
            public ICollection<EventData> EventDatas { get; set; }
        }
    
        public class EventData
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int EventDataId { get; set; }
            public string description { get; set; }
            public string name { get; set; }
            [Column(TypeName = "datetime2")]
            public DateTime start_time { get; set; }
            public virtual Place Place { get; set; }
            public int attending_count { get; set; }     
            public string type { get; set; }
            public string rsvp_status { get; set; }
            [Column(TypeName = "datetime2")]
            public DateTime end_time { get; set; }
        }
    
        public class Place
        {
            [Key,DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int PlaceId { get; set; }
            public string id { get; set; }
            public string name { get; set; }
            [JsonProperty("location")]
            public virtual LocationEvent LocationEvent { get; set; }
        }
    
        public class LocationEvent
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int LocationEventId { get; set; }
            public string city { get; set; }
            public string country { get; set; }
            public float latitude { get; set; }
            public float longitude { get; set; }
            public string state { get; set; }
            public string street { get; set; }
            public string zip { get; set; }
        }
    
        public class Likes
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int LikesId { get; set; }
            [JsonProperty("data")]
            public virtual ICollection<LikesData> LikesData { get; set; }
        }
    
        public class LikesData
        {
            [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int LikesDataId { get; set; }
            public string id { get; set; }
            public string category { get; set; }
            public string name { get; set; }
            public int fan_count { get; set; }
            public string website { get; set; }
            [JsonProperty("location")]
            public virtual LocationEvent LocationEvent { get; set; }
            [JsonProperty("emails")]
            public virtual ICollection<string> emails { get; set; }
        }
    
        public class Age_Range
        {
            [Key]
            [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public int Age_RangeId { get; set; }
            public int min { get; set; }
        }
    

  2. As I mentioned in the comments you must have your users into the database before inserting data to other tables related to those users (foreign keys).
    Insert into table with foreign key

    @EDIT: as promised, here is some code. I recommend you updating your table to accept null values in public virtual Hometown hometown { get; set; } and others.

    public class FacebookDataUser
    {
        public string FacebookDataUserId { get; set; } // You already have the primary key you need
        public string name { get; set; }
        public string birthday { get; set; }
        public string email { get; set; }
        public virtual Hometown hometown { get; set; }
        public virtual Location location { get; set; }
        public virtual Events events { get; set; }
        public virtual Likes likes { get; set; }
        public virtual Age_Range age_range { get; set; }
        public string gender { get; set; }
    
        public void InsertUser(FacebookDataUser Data, Likes MoreData)
        {
            using (SqlConnection myCon = new SqlConnection("connection_string"))
            {
                using (SqlCommand query = new SqlCommand("INSERT INTO users_table (@ID, ...) VALUES (ID, ...)", myCon))
                {
                    query.Parameters.AddWithValue("@ID", Data.FacebookDataUserId);
                    // add more parameters...
                    try
                    {
                        myCon.Open();
                        query.ExecuteNonQuery();
                    }
                    catch(Exception e)
                    {
                        throw e;
                    }
                    finally
                    {
                        myCon.Close();
                    }
                }
                using (SqlCommand query = new SqlCommand("INSERT INTO likes_table (..., @USERID) VALUES (..., USERID)", myCon))
                {
                    // add more parameters...
                    query.Parameters.AddWithValue("@USERID", Data.FacebookDataUserId); // you won't get any exception related to the foreign key because this user is already in the parent table
                    try
                    {
                        myCon.Open();
                        query.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        throw e;
                    }
                    finally
                    {
                        myCon.Close();
                    }
                }
            }
        }
    }
    

    I didn’t run a query to get the user ID as I mentioned because you already have it, just organizing the way you run your methods should be enough.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search