none
[LINQ TO SQL] Insert data into database foreign key always null RRS feed

  • Question

  • Hi All,

    I encountered an issue regarding foreign key value always null when I insert data into database. 

    I created database using LINQ TO SQL. There are three tables in my database, Province, City, Area. And Province has 1: M relationship with City, City has 1: M relationship with Area. Code below,

    Database:

    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    
    namespace ImportAreaInfoApp.Models
    {
        [Database(Name = "ExpressDb")]
        class ExpressDbContext : DataContext
        {
            private const string ConnectionString = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=ExpressDb;Integrated Security=True;" +
                                                     "Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";
    
            public Table<Province> Provinces;
    
            public Table<City> Cities;
    
            public Table<Area> Areas;
    
            public ExpressDbContext()
                : base(ConnectionString)
            {
    
            }
        }
    }

    Province Table:

    using System.Collections.Generic;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    
    namespace ImportAreaInfoApp.Models
    {
        [Table(Name = "Province")]
        class Province
        {
            [Column(IsPrimaryKey = true, Name = "Id", IsDbGenerated = true, CanBeNull = false)]
            public int Id { get; set; }
    
            [Column]
            public string Name { get; set; }
    
            private EntitySet<City> _cities = new EntitySet<City>();
    
            [Association(Name= "FK_City_Province", Storage = "_cities", OtherKey = "_provinceId", ThisKey = "Id")]
            public ICollection<City> Cities
            {
                get { return _cities; }
                set { _cities.Assign(value); }
            }
        }
    }

    City Table:

    using System.Collections.Generic;
    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    
    namespace ImportAreaInfoApp.Models
    {
        [Table(Name = "City")]
        class City
        {
            [Column(IsPrimaryKey = true, Name = "Id", IsDbGenerated = true, CanBeNull = false)]
            public int Id { get; set; }
    
            [Column(Name = "Name")]
            public string Name { get; set; }
    
            [Column(Name = "Province")]
            private int? _provinceId;
    
            private EntityRef<Province> _province = new EntityRef<Province>();
    
            [Association(Name = "FK_City_Province", IsForeignKey = true, Storage = "_province", ThisKey = "_provinceId")]
            public Province Province
            {
                get { return _province.Entity; }
                set { _province.Entity = value; }
            }
    
            private EntitySet<Area> _areas = new EntitySet<Area>();
    
            [Association(Name = "FK_Area_City", Storage = "_areas", OtherKey = "_cityId", ThisKey = "Id")]
            public ICollection<Area> Areas
            {
                get { return _areas; }
                set { _areas.Assign(value); }
            }
        }
    }

    Area Table:

    using System.Data.Linq;
    using System.Data.Linq.Mapping;
    
    namespace ImportAreaInfoApp.Models
    {
        [Table(Name = "Area")]
        class Area
        {
            [Column(IsPrimaryKey = true, Name = "Id", IsDbGenerated = true, CanBeNull = false)]
            public int Id { get; set; }
    
            [Column(Name = "Name")]
            public string Name { get; set; }
    
            [Column(Name = "City")]
            private int? _cityId;
    
            private EntityRef<City> _city = new EntityRef<City>();
    
            [Association(Name = "FK_Area_City", Storage = "_city", IsForeignKey = true, ThisKey = "_cityId")]
            public City City
            {
                get { return _city.Entity; }
                set { _city.Entity = value; }
            }
        }
    }


    OK, I got the data from a json string, it works fine. And insert data like the code below,

    using System.Collections.Generic;
    using System.Runtime.Serialization.Json;
    using System.IO;
    using ImportAreaInfoApp.Models;
    
    namespace ImportAreaInfoApp
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (FileStream stream = new FileStream(@"Data\data.json", FileMode.Open, FileAccess.Read))
                {
                    DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(List<ProvinceData>));
    
                    List<ProvinceData> data = (List<ProvinceData>)serializer.ReadObject(stream);
    
                    using (ExpressDbContext dbContext = new ExpressDbContext())
                    {
                        if(dbContext.DatabaseExists())
                        {
                            dbContext.DeleteDatabase();
                        }
    
                        dbContext.CreateDatabase();
    
                        List<Province> provinces = new List<Province>();
    
                        // Create Data
                        foreach (ProvinceData provinceData in data)
                        {
                            Province province = new Province() { Name = provinceData.name };
    
                            foreach (CityData cityData in provinceData.city)
                            {
                                City city = new City() { Name = cityData.name };
    
                                foreach (string areaData in cityData.area)
                                {
                                    Area area = new Area() { Name = areaData };
    
                                    city.Areas.Add(area);
                                }
    
                                province.Cities.Add(city);
                            }
    
                            provinces.Add(province);
                        }
    
                        dbContext.Provinces.InsertAllOnSubmit(provinces);
    
                        dbContext.SubmitChanges();
                    }
                }
            }
        }
    }

    And then I check my data in database table, e.g. there is no data in Province Column in City Table,

    How to fix this issue? Please point out my issue.

    I uploaded code to OneDrive, you can download from the link below,

    https://1drv.ms/u/s!ApkDLxe9-L_2gzgvD98qGdNkgtet

    Thanks,


    The future belongs to those who believe in the beauty of their dreams.

    Wednesday, November 16, 2016 3:57 PM

Answers

  • Hi Yang,Chenfei,

    I download your project and reproduce your issue on my side, The cause of issue is you violate the foreign key constraints,  you could add related Province when you create a City object.

    please modify your code like this:

    using System.Collections.Generic; using System.Runtime.Serialization.Json; using System.IO; using ImportAreaInfoApp.Models; namespace ImportAreaInfoApp { class Program { static void Main(string[] args) { using (FileStream stream = new FileStream(@"Data\data.json", FileMode.Open, FileAccess.Read)) { DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(List<ProvinceData>)); List<ProvinceData> data = (List<ProvinceData>)serializer.ReadObject(stream); using (ExpressDbContext dbContext = new ExpressDbContext()) { if(dbContext.DatabaseExists()) { dbContext.DeleteDatabase(); } dbContext.CreateDatabase(); List<Province> provinces = new List<Province>(); // Create Data foreach (ProvinceData provinceData in data) { Province province = new Province() { Name = provinceData.name }; foreach (CityData cityData in provinceData.city) {

    //add related Province object to City object.

    City city = new City() { Name = cityData.name, Province = province }; foreach (string areaData in cityData.area) {

    //add related City object to Area object. Area area = new Area() { Name = areaData,City = city }; city.Areas.Add(area); } province.Cities.Add(city); } provinces.Add(province); } dbContext.Provinces.InsertAllOnSubmit(provinces); dbContext.SubmitChanges(); } } } } }

     

    I hope this can be helpful.

    Best Regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, November 17, 2016 7:09 AM
    Moderator

All replies

  • Hi Yang,Chenfei,

    I download your project and reproduce your issue on my side, The cause of issue is you violate the foreign key constraints,  you could add related Province when you create a City object.

    please modify your code like this:

    using System.Collections.Generic; using System.Runtime.Serialization.Json; using System.IO; using ImportAreaInfoApp.Models; namespace ImportAreaInfoApp { class Program { static void Main(string[] args) { using (FileStream stream = new FileStream(@"Data\data.json", FileMode.Open, FileAccess.Read)) { DataContractJsonSerializer serializer = new DataContractJsonSerializer(typeof(List<ProvinceData>)); List<ProvinceData> data = (List<ProvinceData>)serializer.ReadObject(stream); using (ExpressDbContext dbContext = new ExpressDbContext()) { if(dbContext.DatabaseExists()) { dbContext.DeleteDatabase(); } dbContext.CreateDatabase(); List<Province> provinces = new List<Province>(); // Create Data foreach (ProvinceData provinceData in data) { Province province = new Province() { Name = provinceData.name }; foreach (CityData cityData in provinceData.city) {

    //add related Province object to City object.

    City city = new City() { Name = cityData.name, Province = province }; foreach (string areaData in cityData.area) {

    //add related City object to Area object. Area area = new Area() { Name = areaData,City = city }; city.Areas.Add(area); } province.Cities.Add(city); } provinces.Add(province); } dbContext.Provinces.InsertAllOnSubmit(provinces); dbContext.SubmitChanges(); } } } } }

     

    I hope this can be helpful.

    Best Regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, November 17, 2016 7:09 AM
    Moderator
  • Hi Cole,

    Thanks a lot. This solved my issue.

    Thanks,


    The future belongs to those who believe in the beauty of their dreams.

    Thursday, November 17, 2016 7:45 AM