0

I have Model.

public class Lessee
    {
        public long Id { get; set; }

        public string LesseeName { get; set; }

        public bool? LogicalDeleteIn { get; set; }
    }

and another model with a navigation property to the first.

  public class RevenueReceived
    {
        public long Id { get; set; }

        ...

        public Lessee Lessee { get; set; }

    }

I then populate a list of Lessees and insert and that works fine.

   public List<Lessee> InsertLessees(List<Lessee> lessees)
        {
            using (var ctx = new DataContext())
            {
                ctx.Lessees.AddRange(lessees);
                ctx.SaveChanges();
                return lessees;
            }
        }

I then create a list of RevenueReceived and add the corresponding Lessee.

public List<RevenueReceived> GetRevenueReceived(List<Lessee> lessees)
        {
            List<RevenueReceived> retval = new List<RevenueReceived>();
            using (OleDbConnection con = new OleDbConnection(accessConnectionString))
            using (OleDbCommand Command = new OleDbCommand(" SELECT * from tblRevenueReceived", con))
            {
                con.Open();
                OleDbDataReader DB_Reader = Command.ExecuteReader();
                while (DB_Reader.Read())
                {
                    string lesseeName = DB_Reader.IsDBNull(DB_Reader.GetOrdinal("Company")) ? null : DB_Reader.GetString(DB_Reader.GetOrdinal("Company"));

                        retval.Add(new RevenueReceived
                    {
                        Id = 0,
                        ...
                        Lessee = string.IsNullOrEmpty(lesseeName) ? null :  lessees.FirstOrDefault(x => x.LesseeName == lesseeName)
                    }); 
                }
            }
            return retval;
        }

and finally I try and save the list of revenue received.

public void InsertRevenuesReceived(List<RevenueReceived> revenuesReceived)
        {
            using (var ctx = new DataContext())
            {
                ctx.RevenueReceived.AddRange(revenuesReceived);
                ctx.SaveChanges();
            }
        }

and I am receiving the error below. I am a bit confused why it is trying to insert into the lessees table again. is there a way to get to just insert the primary key but not have it try to insert back into lessees again?

Microsoft.EntityFrameworkCore.DbUpdateException HResult=0x80131500
Message=An error occurred while updating the entries. See the inner exception for details.
Source=Microsoft.EntityFrameworkCore.Relational StackTrace: at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple2 parameters) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func3 operation, Func3 verifySucceeded) at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable1 commandBatches, IRelationalConnection connection) at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList1 entries) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList1 entriesToSave) at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess) at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess) at Microsoft.EntityFrameworkCore.DbContext.SaveChanges() at accessdataimport.DataImportService.InsertRevenuesReceived(List`1 revenuesReceived) in C:\Users\c-bdelling\source\repos\Minerals\accessdataimport\DataImportService.cs:line 100 at accessdataimport.Program.Main(String[] args) in C:\Users\c-bdelling\source\repos\Minerals\accessdataimport\Program.cs:line 29

Inner Exception 1: SqlException: Cannot insert explicit value for identity column in table 'Lessees' when IDENTITY_INSERT is set to OFF.

Bryan Dellinger
  • 4,724
  • 7
  • 33
  • 79
  • Does this answer your question? [Cannot insert explicit value for identity column in table 'table' when IDENTITY\_INSERT is set to OFF](https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) – Johnathan Barclay Nov 13 '19 at 14:34
  • no I don't think so. I believe my problem is that it is trying to insert into the child table (Lessee) and I don't want it to. (as I already have inserted into Lessee) – Bryan Dellinger Nov 13 '19 at 14:36
  • Do you have an FK property for Lessee on your `RevenueReceived` model? e.g. `public long Lessee_Id { get; set; }` – Dawood Awan Nov 13 '19 at 14:36
  • 1
    Yeah, I don't think you need to set the Lessee property of each RevenueReceived object, just set the LesseeId instead – John M Nov 13 '19 at 14:38
  • no I was just doing it implicitly. but I could add one, I was just reading up on that. so I would update the fk and then set the child to unchanged? – Bryan Dellinger Nov 13 '19 at 14:39
  • thanks John I think I'll give that a shot – Bryan Dellinger Nov 13 '19 at 14:40
  • Just setting the FK should work - If you still face the same issue, let us know. – Dawood Awan Nov 13 '19 at 14:47
  • It looks like EF is not aware of lessee's existence, so it cannot do its "magic update". You can try to 1) Refactor your code and load lessee directly from the DB before adding into RevenueReceived; or 2) Attach the lessee entity into the DB Context inside the while loop. – Davidson Sousa Nov 13 '19 at 14:50

0 Answers0