How can I see the insert query of specific table ?

Question

How can I see the insert query of specific table ?

EntityEntry<BookingAgreement> fbObj = await _dbContext.BookingAgreements.AddAsync(booking);   
            await _dbContext.SaveChangesAsync();  

following is the logic to add BookingAgreements

I want to get the query behind it. Can anyone guide me please ?

await _dbContext.BookingAgreements.AddAsync(booking);

Thank you.

I need the query of it because I am unable to catch the exception over it.

Error is somewhere else and this code doesn't related to the error.

I am getting this errror :

SqlException: The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_Counties_Countries". The conflict occurred in database "test", table "dbo.Countries", column 'Id'.  
The statement has been terminated.  

and this is the model class.

public class BookingAgreement  
    {  
        [Key]  
        public int Id { get; set; }  
        public System.DateTime BookingStart { get; set; }  
        public System.DateTime BookingEnd { get; set; }  
        public System.TimeSpan StartTime { get; set; }  
        public System.TimeSpan EndTime { get; set; }  
        public int FranchiseId { get; set; }  
        public int FleetId { get; set; }  
        public Nullable<int> BKRenterId { get; set; }  
        public bool IsReportGenerated { get; set; }  
        public byte StepsCompleted { get; set; }  
        public string? BookingId { get; set; }  
        public string? ReservationId { get; set; }  
        public string? RentalAgreementId { get; set; }  
        public Nullable<short> VehicleUsedFor { get; set; }  
        public string? OperatorLicence { get; set; }  
        public string? ClearanceCode { get; set; }  
        public decimal ExcessAmount { get; set; }  
        public string? ClearanceCodeAdditionalDriv { get; set; }  
        public Nullable<decimal> ExcessAmountAdditionalDriv { get; set; }  
        public bool IsClearanceVerified { get; set; }  
        public Nullable<double> BKDiscount { get; set; }  
        public Nullable<int> AgreementStatus { get; set; }  
        public Nullable<bool> IsTravelingOutsideUk { get; set; }  
        public string? OtherCountries { get; set; }  
        public Nullable<int> RentalType { get; set; }  
        public Nullable<int> RenterType { get; set; }  
        public Nullable<short> RenterCompanyType { get; set; }  
        public Nullable<bool> IsVehicleDelivered { get; set; }  
        public Nullable<bool> IsReturnedOnSameAddress { get; set; }  
        public string? PickUpAddress { get; set; }  
        public string? ReturnAddress { get; set; }  
        public string? OtherReturnAddress { get; set; }  
        public Nullable<int> TariffId { get; set; }  
        public Nullable<int> SpecialRateId { get; set; }  
        public Nullable<bool> IsChargeShortWeekend { get; set; }  
        public Nullable<bool> IsChargeLongWeekend { get; set; }  
        public Nullable<bool> ChargeHalfDay { get; set; }  
        public Nullable<bool> IsDamageProvided { get; set; }  
        public bool IsRenterAgreement { get; set; }  
        public Nullable<bool> BKIsOwnInsurance { get; set; }  
        public Nullable<int> ExpectedMileage { get; set; }  
        public Nullable<decimal> TotalHireCharge { get; set; }  
        public Nullable<decimal> BKFreeMiles { get; set; }  
        public Nullable<decimal> BKExpensePerMile { get; set; }  
        public Nullable<decimal> BKCollisionDamage { get; set; }  
        public Nullable<decimal> BKDamage { get; set; }  
        public Nullable<decimal> BKTheft { get; set; }  
        public Nullable<decimal> BKOverHeight { get; set; }  
        public string? BKBookingNotes { get; set; }  
        public Nullable<int> BKFleetCurrentMileage { get; set; }  
        public Nullable<int> BKMileageOut { get; set; }  
        public Nullable<int> BKMileageIn { get; set; }  
        public string? BKDamageOut { get; set; }  
        public string? BKDamageIn { get; set; }  
        public Nullable<int> Condition { get; set; }  
        public string? AppliedRates { get; set; }  
        public Nullable<bool> Paid { get; set; }  
        public Nullable<bool> OnceInvoiced { get; set; }  
        public Nullable<int> InterimInvoiceCount { get; set; }  
        public string? PBNumber { get; set; }  
        public Nullable<decimal> Excess { get; set; }  
        public Nullable<int> StatusId { get; set; }  
        public Nullable<System.DateTime> CreatedOn { get; set; }  
        public Nullable<System.DateTime> ModifiedOn { get; set; }  
        public Nullable<decimal> BookingDays { get; set; }  
        public Nullable<decimal> VATPercentage { get; set; }  
        public Nullable<decimal> BkOpeningHrsSurcharge { get; set; }  
        public Nullable<int> DrivenMileage { get; set; }  
        public Nullable<int> ParentRAId { get; set; }  
        public decimal NetTotal { get; set; }  
        public decimal VatTotal { get; set; }  
        public decimal GrossTotal { get; set; }  
        public decimal GrandInsurableRevenue { get; set; }  
        public decimal GrandTotalWithoutExtra { get; set; }  
        public decimal GrandInsurableRevWithoutExtra { get; set; }  
        public Nullable<decimal> TariffNetPrice { get; set; }  
        public Nullable<bool> OnceMailed { get; set; }  
        public Nullable<System.DateTime> LastBookingEnd { get; set; }  
        public Nullable<System.TimeSpan> LastEndTime { get; set; }  
        public Nullable<decimal> BkOpeningHrsSurchargeNet { get; set; }  
        public Nullable<decimal> BkOpeningHrsSurchargeVat { get; set; }  
        public Nullable<decimal> DailyHireCharge { get; set; }  
        public Nullable<decimal> BkNetExtraDrivenMilesExcess { get; set; }  
        public Nullable<decimal> BkExtraDrivenMilesExcessVAT { get; set; }  
        public Nullable<decimal> BkTotalExtraDrivenMilesExcess { get; set; }  
        public Nullable<decimal> ExcessMilesDays { get; set; }  
        public Nullable<decimal> BkWebWeekendSurcharge { get; set; }  
        public Nullable<int> InsertedUserId { get; set; }  
        public Nullable<int> UpdateUserId { get; set; }  
        public Nullable<System.DateTime> InsertedDate { get; set; }  
        public Nullable<System.DateTime> UpdatedDate { get; set; }  
        public string? InsertedUserName { get; set; }  
        public string? UpdatedUserName { get; set; }  
        public Nullable<decimal> SecurityDeposit { get; set; }  
        public Nullable<int> SecurityDepositRuleId { get; set; }  
        public string? OrderNumber { get; set; }  
        public Nullable<decimal> ChargedDays { get; set; }  
        public Nullable<int> ExchangeSeqNo { get; set; }  
        public Nullable<bool> ConfirmThroughMobileApp { get; set; }  
        public Nullable<System.DateTime> ConfirmedDate { get; set; }  
        public Nullable<bool> IsVisibleForMobileApp { get; set; }  
        public string? OwnerSignatureFileName { get; set; }  
        public string? OwnerSignatureFilePath { get; set; }  
        public string? OwnerSignatureFileUrl { get; set; }  
        public string? OwnerSignatureFileType { get; set; }  
        public Nullable<System.DateTime> OwnerSignatureDate { get; set; }  
        public bool IsRASkipped { get; set; }  
        public Nullable<int> SkippedRAUserId { get; set; }  
        public string? SkippedRAUser { get; set; }  
        public Nullable<System.DateTime> SkippedRADate { get; set; }  
        public Nullable<decimal> ManualSuggestedDeposit { get; set; }  
        public bool IsManualSuggestedDeposit { get; set; }  
        public Nullable<decimal> DailyRateIncVAT { get; set; }  
        public Nullable<decimal> DailyRateExclVAT { get; set; }  
        public bool IsCustomizeRateEnabled { get; set; }  
        public Nullable<System.DateTime> CustomizeRateDate { get; set; }  
        public bool IsExtSendToCustomerEnabled { get; set; }  
        public Nullable<System.DateTime> ExtSendToCustomerDate { get; set; }  
        public Nullable<decimal> TotalHireChargeBeforeCustomizeRate { get; set; }  
        public Nullable<decimal> DailyHireChargeBeforeCustomizeRate { get; set; }  
        public Nullable<decimal> AccurateDailyHireCharge { get; set; }  
        public Nullable<decimal> AccurateNetCollisionDamage { get; set; }  
        public Nullable<decimal> AccurateNetTotalCollisionDamage { get; set; }  
        public Nullable<decimal> AccurateGrossDailyCollisionDamage { get; set; }  
        public Nullable<decimal> AccurateGrossTotalCollisionDamage { get; set; }  
        public Nullable<int> IsOnPaymentStep { get; set; }  
        public Nullable<decimal> BKTotalFreeMiles { get; set; }  
        public virtual ICollection<BKCreditNote>? BKCreditNotes { get; set; }  
        public virtual ICollection<BKExtra>? BKExtras { get; set; }  
        public virtual ICollection<BKInvoice>? BKInvoices { get; set; }  
  

       [ForeignKey("BKRenterId")]  
       public virtual BKRenter? BKRenter { get; set; }  

  
        [ForeignKey("FleetId")]  
        public virtual Fleet? Fleet { get; set; }  
        public virtual ICollection<BookingAgreementsExtendHistory>? BookingAgreementsExtendHistories { get; set; }  
        public virtual ICollection<FleetIncident>? FleetIncidents { get; set; }  
        public virtual ICollection<BKPayment>? BKPayments { get; set; }  
        [ForeignKey("FranchiseId")]  
        public virtual Franchise? Franchise { get; set; }  
        public virtual ICollection<AgreementRenter>? AgreementRenters { get; set; }  
    }  

--- last executed flow

see debug view is giving this

BookingAgreement {Id: -2147482647} Added  
  Id: -2147482647 PK Temporary  
  AccurateDailyHireCharge: <null>  
  AccurateGrossDailyCollisionDamage: <null>  
  AccurateGrossTotalCollisionDamage: <null>  
  AccurateNetCollisionDamage: <null>  
  AccurateNetTotalCollisionDamage: <null>  
  AgreementStatus: <null>  
  AppliedRates: <null>  
  BKBookingNotes: <null>  
  BKCollisionDamage: 0  
  BKDamage: 750.0000  
  BKDamageIn: <null>  
  BKDamageOut: 'See Check Slip'  
  BKDiscount: 0  
  BKExpensePerMile: 10.0000  
  BKFleetCurrentMileage: <null>  
  BKFreeMiles: 200  
  BKIsOwnInsurance: 'False'  
  BKMileageIn: <null>  
  BKMileageOut: <null>  
  BKOverHeight: 1500.0000  
  BKRenterId: 12867 FK  
  BKTheft: 1500.0000  
  BKTotalFreeMiles: 0  
  BkExtraDrivenMilesExcessVAT: 0  
  BkNetExtraDrivenMilesExcess: 0  
  BkOpeningHrsSurcharge: 0  
  BkOpeningHrsSurchargeNet: 0  
  BkOpeningHrsSurchargeVat: 0  
  BkTotalExtraDrivenMilesExcess: 0  
  BkWebWeekendSurcharge: 0  
  BookingDays: 1  
  BookingEnd: '7/5/2022 12:00:00 AM'  
  BookingId: '111'  
  BookingStart: '7/4/2022 12:00:00 AM'  
  ChargeHalfDay: 'False'  
  ChargedDays: 1  
  ClearanceCode: <null>  
  ClearanceCodeAdditionalDriv: <null>  
  Condition: <null>  
  ConfirmThroughMobileApp: <null>  
  ConfirmedDate: <null>  
  CreatedOn: <null>  
  CustomizeRateDate: <null>  
  DailyHireCharge: 32  
  DailyHireChargeBeforeCustomizeRate: <null>  
  DailyRateExclVAT: <null>  
  DailyRateIncVAT: <null>  
  DrivenMileage: 0  
  EndTime: '10:00:00'  
  Excess: <null>  
  ExcessAmount: 0  
  ExcessAmountAdditionalDriv: <null>  
  ExcessMilesDays: <null>  
  ExchangeSeqNo: <null>  
  ExpectedMileage: 1  
  ExtSendToCustomerDate: <null>  
  FleetId: 15210 FK  
  FranchiseId: 16282 FK  
  GrandInsurableRevWithoutExtra: 0  
  GrandInsurableRevenue: 0  
  GrandTotalWithoutExtra: 0  
  GrossTotal: 0  
  InsertedDate: <null>  
  InsertedUserId: <null>  
  InsertedUserName: <null>  
  InterimInvoiceCount: <null>  
  IsChargeLongWeekend: 'False'  
  IsChargeShortWeekend: 'False'  
  IsClearanceVerified: 'False'  
  IsCustomizeRateEnabled: 'False'  
  IsDamageProvided: 'False'  
  IsExtSendToCustomerEnabled: 'False'  
  IsManualSuggestedDeposit: 'False'  
  IsOnPaymentStep: <null>  
  IsRASkipped: 'False'  
  IsRenterAgreement: 'False'  
  IsReportGenerated: 'False'  
  IsReturnedOnSameAddress: 'True'  
  IsTravelingOutsideUk: 'False'  
  IsVehicleDelivered: <null>  
  IsVisibleForMobileApp: <null>  
  LastBookingEnd: <null>  
  LastEndTime: <null>  
  ManualSuggestedDeposit: <null>  
  ModifiedOn: <null>  
  NetTotal: 0  
  OnceInvoiced: <null>  
  OnceMailed: <null>  
  OperatorLicence: <null>  
  OrderNumber: <null>  
  OtherCountries: <null>  
  OtherReturnAddress: <null>  
  OwnerSignatureDate: <null>  
  OwnerSignatureFileName: <null>  
  OwnerSignatureFilePath: <null>  
  OwnerSignatureFileType: <null>  
  OwnerSignatureFileUrl: <null>  
  PBNumber: <null>  
  Paid: <null>  
  ParentRAId: <null>  
  PickUpAddress: 'F-16282'  
  RentalAgreementId: <null>  
  RentalType: 1  
  RenterCompanyType: <null>  
  RenterType: 1  
  ReservationId: <null>  
  ReturnAddress: <null>  
  SecurityDeposit: <null>  
  SecurityDepositRuleId: <null>  
  SkippedRADate: <null>  
  SkippedRAUser: <null>  
  SkippedRAUserId: <null>  
  SpecialRateId: <null>  
  StartTime: '10:00:00'  
  StatusId: 1  
  StepsCompleted: 1  
  TariffId: 13561  
  TariffNetPrice: 32  
  TotalHireCharge: 32  
  TotalHireChargeBeforeCustomizeRate: <null>  
  UpdateUserId: <null>  
  UpdatedDate: <null>  
  UpdatedUserName: <null>  
  VATPercentage: 0  
  VatTotal: 0  
  VehicleUsedFor: 1  
  AgreementRenters: <null>  
  BKCreditNotes: <null>  
  BKExtras: <null>  
  BKInvoices: <null>  
  BKPayments: <null>  
  BKRenter: <null>  
  BookingAgreementsExtendHistories: <null>  
  Fleet: <null>  
  FleetIncidents: <null>  
  Franchise: <null>  

Still data don't have country nor county.

Can it be possible that before this statement, there is some flow and db.savechangeasync is taking/considering that value ?

Can I cancel everything before calling these lines

EntityEntry<BookingAgreement> fbObj = await _dbContext.BookingAgreements.AddAsync(booking);   
            await _dbContext.SaveChangesAsync();  

Answer 1

In my experience the easiest thing to do is have your DBA enable profiling while you run the query. However in the debugger newer versions of EF allow you to see the query that will run. There are a couple of different ways of doing it. The returned IQueryable object can be moused over to get the underlying SQL query. There is also the ToQueryString method. For more extensive stuff you can use the LogTo method when setting up the DbContext to log to someplace.

For an EntityEntry<T> like you get back from AddAsync use the DebugView property to see what the command will likely look like.

Note that AddAsync doesn't actually do anything at the DB level. It simply adds the given item to the list of entities that will be inserted into the DB later. The actual SQL query doesn't run until you call SaveChangesAsync and it is here you would need to capture exceptions.

   try  
   {  
      await _dbContext.SaveChangesAsync();  
   } catch (Exception e)  
   {  
      ...  
   };  

I don't think looking at any of this will actually help you though. The error itself is pretty clear - you are attempting to update an entry in your Countries table and because of a FK constraint it won't work. The challenge is figuring out why this update is happening. In your entity you provided there are no references to countries so, unless you have a bad configuration for the entity, it is coming from one of your navigation properties. See the problem with EF is it tries to be too helpful. If, for example, you add a booking and the booking's BKRenter property is set then it will also want to add the BKRenter to the database. It looks to see if that object is already being tracked by the DB and if not inserts it as well. It will do this for all your nav properties. Hence when you are inserting an entity that has child entities you only need to add the root entity and EF will auto-add the rest.

The problem comes in when you're dealing with things like lookup tables that are referenced for convenience but don't actually have a lifetime associated with the item being added. In this case you can run into issues.

   var booking = new BookAgreement() {  
      BKRender = new BKRenter() {  
          Country = new Country() { Id = 10 }  
      }  
   }  
     
   var entity = await _dbContext.BookingAgreements.AddAsync(booking);  

The booking, renter and country are all trying to be inserted (or updated). How you should actually build this up depends upon how you're using EF. Approach 1 is to ensure that all nav properties that shouldn't be added/updated come from the current context.

   var renter = await _dbContext.Renters.FindAsync(renterId);  
   var booking = new BookingAgreement() {  
      BKRenter = renter  
   }  
   await _dbContext.BookingAgreements.AddAsync(booking);  

Since EF is already tracking the renter it doesn't need to do anything else. But this seems like overkill to me so my preference is to simply set the foreign key ID on the entity and leave the nav property as default. Then EF won't try to add/update it but will still set the right values.

   var booking = new BookingAgreement() {  
      BKRenterId = renterId  
   }  
   await _dbContext.BookingAgreements.AddAsync(booking);  

Of course for this to work you need to expose the FK as well but that is often the case anyway.

Answer 2

See the following EF Core logging reference documentation.

Simple Logging