none
Issue With FKey/Pkey Error On EntityFirst LinqToSQL Class, What am i doing wrong? RRS feed

  • Question

  • Please help with the following code, this needs some help with the Pkey Fkey error what i am getting. also the last one does not work great when i want the count of citylocalities.

     

    Kind Regards

    Vinay.

    http://stackoverflow.com/questions/7125675/linq-to-sql-entity-first-pkey-error-and-error-on-select-back

    [Table(Name = "City")]
    public class City
    {
      [Column(AutoSync = AutoSync.OnInsert, IsDbGenerated = true, IsPrimaryKey = true)]
      public int CityId { get; set; }
    
      [Column(CanBeNull = false)]
      public string CityName { get; set; }
    
      [Column(CanBeNull = false)]
      public bool HasLocations { get; set; }
    
      private EntitySet<CityLocality> _cityLocalities = new EntitySet<CityLocality>();
    
      [Association(Name = "City_CityLocality", Storage = "_cityLocalities", ThisKey = "CityId", OtherKey = "CityId")]
      public EntitySet<CityLocality> CitiyLocalities
      {
        get { return _cityLocalities; }
        set { _cityLocalities.Assign(value);}
      }
    
    }
    
    
    [Table(Name = "CityLocality")]
    public class CityLocality
    {
      [Column(AutoSync = AutoSync.OnInsert, IsPrimaryKey = true, IsDbGenerated = true)]
      public int LocalityId { get; set; }
    
      [Column(CanBeNull = false)]
      public string LocalityName { get; set; }
    
      [Column(CanBeNull = false)]
      public int CityId { get; set; }
    
      [Column(CanBeNull = false)]
      public string PostalIndexNumber { get; set; }
    
      private EntityRef<City> _city = default(EntityRef<City>);
    
      [AssociationAttribute(Name = "City_CityLocality", Storage = "_city", ThisKey = "CityId", OtherKey = "CityId", IsForeignKey = true)]
      public City City
      {
        get { return this._city.Entity; }
        set { _city.Entity = value; }
      }
    }
    
    
    
    
    public class SqlCityRepository : ICityRepository
    {
      private Table<City> _citys;
    
      public SqlCityRepository(string connectionString)
      {
        _citys = (new DataContext(connectionString)).GetTable<City>();
    
      }
    
      public IQueryable<City> Cities
      {
        get { return _citys; }
      }
    
      public void Add(City city)
      {
        if(city.CityId == 0)
        {
        _citys.InsertOnSubmit(city);
    
        }
        else
        {
        _citys.Attach(city);
        _citys.Context.Refresh(RefreshMode.KeepCurrentValues,city);
        }
        _citys.Context.SubmitChanges();
      }
    
      public City GetById(int cityId)
      {
        return (from c in _citys where c.CityId == cityId select c).FirstOrDefault();
      }
    
      public IQueryable<City> IQGetById(int cityId)
      {
        return (from c in _citys where c.CityId == cityId select c);
      }
    }
    
    
    public class SqlCityLocalityRepository : ICityLocalityRepository
    {
      private Table<CityLocality> _cityLocalities;
    
      public SqlCityLocalityRepository(string connectionString)
      {
        _cityLocalities = (new DataContext(connectionString)).GetTable<CityLocality>();
    
      }
    
      public IQueryable<CityLocality> CityLocalities
      {
        get { return _cityLocalities; }
      }
    
      public void Add(CityLocality cityLocality)
      {
        if(cityLocality.LocalityId == 0)
        {
        _cityLocalities.InsertOnSubmit(cityLocality);
        }
        else
        {
        _cityLocalities.Attach(cityLocality);
        _cityLocalities.Context.Refresh(RefreshMode.KeepCurrentValues, cityLocality);
        }
        _cityLocalities.Context.SubmitChanges();
      }
    
    
      public CityLocality GetById(int cityLocalityId)
      {
        return (from cl in _cityLocalities where cl.LocalityId == cityLocalityId select cl).FirstOrDefault();
      }
    }
    
    
    //Test code 
    var localityRepository = new SqlCityLocalityRepository(_connectionSting);
    var cityRepository = new SqlCityRepository(_connectionSting);
    
    var city = new City
    {
    CityName = "BagChenDil",
    HasLocations = true,
    };
    
    var cityLocality1 = new CityLocality
          {
          CityId = city.CityId,
          LocalityName = "TestLocality1",
          PostalIndexNumber = "560095"
    
          };
    
    var cityLocality2 = new CityLocality
          {
          CityId = city.CityId,
          LocalityName = "TestLocality1",
          PostalIndexNumber = "560095"
          };
    
    
    using (var scope = new TransactionScope())
    {
    //When i do the add it is not adding the locaitons also.
    //city.CitiyLocalities.Add(cityLocality1);
    //city.CitiyLocalities.Add(cityLocality2);
    //Fkey error : The INSERT statement conflicted with the FOREIGN KEY constraint "FK_CityLocality_City"
    
    cityRepository.Add(city);
    var banChenDil = (from c in cityRepository.Cities select c).FirstOrDefault();
    
    Assert.AreEqual(banChenDil.CityName, city.CityName);
    Assert.GreaterThan(banChenDil.CityId, 0);
    
    cityLocality1.CityId = cityLocality2.CityId = city.CityId;
    localityRepository.Add(cityLocality1);
    localityRepository.Add(cityLocality2);
    
    //What is Wrong here in the line. even though 
    var clocations = from cl in cityRepository.IQGetById(city.CityId) select new {cl.CitiyLocalities, cl.CityId};
    Assert.AreEqual(2,clocations.ToList().Count);
    //The above line says specified cast is invalid.
    
    scope.Dispose();
    }
    



    bhushanvinay
    Sunday, August 21, 2011 11:50 AM

Answers

  • Thank you John.

    Mine is not a rocket science problem,

    This is whilst i am learning Linq i come across a small problem,

    the right way to do this is to associate the 3rd option and it worked.

    Thank you John,

    Transaction scope did not cause issues to this.

    Kind Regards

    Vinay.


    bhushanvinay
    Thursday, August 25, 2011 8:14 PM

All replies

  • Try the same code without setting "CityId = city.CityId" when declaring cityLocality1 and cityLocality2. You shouldn't need to set the Id values when you use the associative properties.

    Monday, August 22, 2011 9:02 AM
  • Hi bhushanvinay,

    Welcome!

    Try to use @John's suggestion and let us know your feedback.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, August 22, 2011 9:44 AM
    Moderator
  • Still the same error. with foreign key when i comment out the follwoing code.

     

     var cityLocality1 = new CityLocality
                      {
                      
                        LocalityName = "TestLocality2",
                        PostalIndexNumber = "560095"
    
                      };
    
          var cityLocality2 = new CityLocality
                      {
                        
                        LocalityName = "TestLocality3",
                        PostalIndexNumber = "560095"
                      };
    
        
          using (var scope = new TransactionScope())
          {
    
            city.CitiyLocalities.Add(cityLocality1);
            city.CitiyLocalities.Add(cityLocality2);
    
            cityRepository.Add(city);
          }
    

    didn't work. (:


    bhushanvinay
    Tuesday, August 23, 2011 7:00 PM
  • Sorry, I really can't see the source of the problem. I'm sure that you are not performing a hidden InsertOnSubmit() on the localities - so that's not the problem.

     

    I have a couple of thoughts which *might* (only might) help a bit.

    1. I don't see the need for the transaction scope in the last example because as far as I can see there is only .SubmitChanges() which is buried inside cityRepository.Add().

    2. The transaction scope doesn't use a .Commit() - this won't be the cause of the foreign key problem though.

    3. Could you try associating the localities with the city in a different way? It shouldn't make the slightest bit of difference but try it just in case.

    cityLocality1.City = city;

    cityLocality2.City = city;

    cityRepository.Add(city);

     

    Sorry if this doesn't help.

    Tuesday, August 23, 2011 9:11 PM
  • Thank you John.

    Mine is not a rocket science problem,

    This is whilst i am learning Linq i come across a small problem,

    the right way to do this is to associate the 3rd option and it worked.

    Thank you John,

    Transaction scope did not cause issues to this.

    Kind Regards

    Vinay.


    bhushanvinay
    Thursday, August 25, 2011 8:14 PM