I received this error once I try to update table with the same value (carNumber), my condition is to update where the actual return date field is null.
For some reason it's look like the query return 2 rows, but actually there is only one. I'm using EF. This is the function:
public void updateStatus(int carNumber1, string acctualDate1)
{
DateTime accReturn = DateTime.Parse(acctualDate1);
var orderCar1 = db.CarRentalFields.FirstOrDefault(carNum =>
(carNum.CarNumber == carNumber1 && carNum.ActualReturnDate == null));
orderCar1.ActualReturnDate = accReturn ;
db.SaveChanges();
The error raise when trying to db.saveChanges()
the table from the db, the car number is 1000 - print screen
please let me know how can I solve this issue.
CodePudding user response：
This error occurs when EF cannot resolve the PK for your entity. In most cases for simple entities, EF conventions can work out the PK, but in your case you are using a composite key so this needs to be configured. Depending on how you are mapping your entities you can do this either in:
- an EDMX
- in the DbContext.OnModelCreating
- using an
EntityTypeConfigurationdeclaration - using attributes within the entity itself
Since we don't know how your entities are configured, you can verify this as the cause by using the attribute approach within your entity as a test. If you are using an EDMX the entity classes will be generated so you will want to replace this with configuration within the EDMX. (Cannot really help you there because I don't use the dang things :D )
You will probably have something like:
public class CarRentalFields
{
[Column("start_day")]
public DateTime StartDay { get; set; }
[Column("return_date")]
public DateTime ReturnDate { get; set; }
[Column("user_id")]
public int UserId { get; set; }
[Column("car_number")]
public DateTime CarNumber { get; set; }
// ... more columns...
}
You may even have a [Key] attribute on one of these fields, such as CarNumber. If there is a PK mapped in the entity the issue is that it isn't specific enough to uniquely identify the row. When EF goes to update one entity, it is checking for, and expecting to update only one row in the table. It's finding more than one row will be affected so it fails.
Append the attributes for the [Key] with the column order so it is recognized as a composite key.
public class CarRentalFields
{
[Key, Column(Name="start_day", Order=1)]
public DateTime StartDay { get; set; }
[Key, Column(Name="return_date", Order=2)]
public DateTime ReturnDate { get; set; }
[Key, Column(Name="user_id", Order=3)]
public int UserId { get; set; }
[Key, Column(Name="car_number", Order=4)]
public DateTime CarNumber { get; set; }
// ... more columns...
}
Provided these 4 columns are guaranteed to be a unique constraint on the table, EF will be satisfied when only one row is updated when it builds it's UPDATE SQL statement.
Note again that if this works and you are using an EDMX, you will need to review and modify your EDMX mapping to make the appropriate changes since that entity class could be regenerated, losing your extra attributes. (I believe the generated entity classes from an EDMX have a comment header warning you that it is a generated class, so that is an indicator to watch out for.)
Update: My primary suspect in this would be that the table does not actually have a matching PK defined, either running a different PK combination, or more likely no PK given the nature of those fields. EF can operated on tables that have no PK defined, but it does require a Key definition that ensures records can be uniquely identified. The error you are seeing happens when that key definition is not unique enough. (I.e. if you are updating car 1, and selecting a row that has: car_number = 1, start_day = 2021-11-21, return_day = 2021-11-22, user_id = 0 The issue is that more than one row has that combination in the DB. If the DB you are checking doesn't have more than one matching row then your application is almost certainly pointing at a different database than you are checking.
Things you can do to verify this:
- get the runtime connection string and see if it matches the DB you are checking:
Before you run your query, add the following:
// EF6
var connectionString = db.Database.Connection.ConnectionString;
// EF Core 5
var connectionString = db.Database.GetConnectionString();
- Have a look at the data you are actually querying:
.
var cars = db.CarRentalFields.Where(carNum =>
(carNum.CarNumber == carNumber1 && carNum.ActualReturnDate == null)).ToList();
While this query might return only 1 record, that is not the cause of the problem. What you want is the CarNumber, StartDate, ReturnDate, and UserId for this record:
var car = db.CarRentalFields
.Where(carNum => carNum.CarNumber == carNumber1
&& carNum.ActualReturnDate == null)
.Select(x => new
{
x.CarNumber,
x.StartDay,
x.ReturnDate,
x.UserId
}).Single(); // Expect our 1 record here...
var cars = db.CarRentalFields
.Where(x => x.CarNumber == car.CarNumber
&& x.StartDay = car.StartDay
&& x.ReturnDate = car.ReturnDate
&& x.UserId = car.UserId)
.ToList(); // Get rows that match our returned Key fields.
These queries select the assumed PK values for the car record you mean to update, then search cars for matching records with the expected Key fields. My money would be on that while the top query returns 1 record, the bottom query returns two rows, meaning while only 1 record has a #null ActualReturnDate value, your Key is not unique enough for the contents of this table.
CodePudding user response：
problem solved by add a new column to the car_rental_fields table, id column that include Identity. as I understand from here and from the web, there is an issue with complicated pk. in my solution the id isn't a primary key, but it's make the logic for linq to update the correct column. thanks' for all the people that involved in this issue.