navigation property does not fetch data between two tables-CodePudding

Im using .netcore 5,code first,I have a table which is :

          public class PaymentEntity
        {
            public int ID { get; set; } = default!;
            
            public string? QID { get; set; } = default!;

            public string? Status { get; set; } = default!;

            public int? PlatformId { get; set; } = default!;

            public int? CustomerId { get; set; } = default!;

            public string? UpdatedAmount { get; internal set; } = default!;

        }

here is my second table which is flagged customer:

          public class FlaggedCustomer
        {
            public int Id { get; set; }
            public int? CustomerId { get; set; }
            public string? Comments { get; set; }
            public DateTimeOffset Created { get; set; }
            public DateTimeOffset Modified { get; set; }
            public PaymentEntity? Paymententity { get; set; }

        }

every time a new payment comes,if some conditions are not meet,i need to flag the customer and store it in the flagged customer table,here is how i store:

 var newFlaggedCustomer= new FlaggedCustomer()
                        {
                            CustomerId = newCustomer.CustomerId,
                            Created = DateTimeOffset.UtcNow,
                            Modified = DateTimeOffset.UtcNow,
                            Comments = "some comments "
                        };
                        dbContext.FlaggedCustomer.Add(newFlaggedCustomer);
                        dbContext.SaveChanges();

customer can have many payments but only one customerid can be in the flagged customer,i mean if you have customer id of 2,you can find many payment of customer id 2 in the paymententity but if he is flagged only one customer id 2 can be found on the flagged customer,the thing is the customer is flagged but in the db(FlaggedCustomer) table the field PaymentHistory is null, and when i navigate using Include,returns null,i want to see which payment in the payment history made the customer flagged..any help will be appreciated

CodePudding user response：

I don't know how to saved your data about the FlaggedCustomer and PaymentEntity, but in my opinion, whenever a payment is generated, it will be inserted into the PaymentEntity table then you need to check if FlaggedCustomer should be inserted a new item or just be updated. So there should be some code similar to this:

 //insert payment entity anyway
_context.Add<PaymentEntity>(payment);
//check and insert or update FlaggedCustomer
if (_context.Customers.Any(e => e.Id == cus.Id))
{
    var customer = _context.Customers.First(a => a.Id == cus.Id);
    customer.Modified = DateTimeOffset.UtcNow;
}
else
{
    _context.Add<FlaggedCustomer>(cus);
}
_context.SaveChanges();

Then when query the payment data to check the payment history, I think we need to use include if you set the foreign key or join query here which may similar to:

var queryResult = from flag_customer in _context.Customers
  join payment_entity in _context.Payment on flag_customer.CustomerId equals payment_entity.CustomerId
  select new
  { 
      CustomerId = flag_customer.CustomerId,
      CreateTime = flag_customer.Created,
      ModifiedTime = flag_customer.Modified,
      PaymentStatus = payment_entity.Status,
      PaymentAmount = payment_entity.UpdatedAmount
  };