Updating 2 records in the database when it should ideally update 1

Question

I want to update a a record in a table from my service in .net core. I am pulling the record with a LinQ query and and trying to update a value for the record. There is just one unique record which is pulled by the LinQ query.

Below is the LinQ query:

var updateRecord = (from a in dbContext?.Refresh
                where a.Column1 == refresh.Column1 && a.Column2 == refresh.Column2 
                select a).FirstOrDefault();

I am updating a value in column8 in a table named Refresh in the code below:

 updateRecord.column8 = value;
 await dbContext.SaveChangesAsync();

There are 2 records in the table with the same Column1 value but their Column2 value is different and the LinQ query also pulls up only 1 record. So, ideally it should update just 1 record in the db. But it still updates 2 records, when it should update 1.

Can I please get help on this?

EDIT: Below is the table Structure:

[Column1] [varchar](100) NOT NULL,
[Column2] [varchar](100) NOT NULL,
[Column3] [varchar](100) NOT NULL,
[Column4] [varchar](100) NOT NULL,
[Column5] [bit] NOT NULL,
[Column6] [varchar](100) NULL,
[Column7] [datetime2](7),
[Column8] [varchar](255) NULL

Sample Data:

Column8 : fc79107c-c3d0-4cec-a2ed-e79fd5dfaa85

Answer 1

There are 2 records in the table with the same Column1 value but their Column2 value is different and the LinQ query also pulls up only 1 record. So, ideally it should update just 1 record in the db. But it still updates 2 records, when it should update 1.

Can I please get help on this?

Well, based on my test and findings your query is correct and extracting the expected result, but the main issue is in updateRecord.column8 = value; linq cannot parse the correct subsequent SQL query and it will always considering column1 in terms of update table row because it doesn't find the primary key and considering column1 as primary key as a result updating the full table rows. As you can see the update log below:

Another important point is that, you have two records, so we cannot say which one is unique or different, first one or last one, so this concern remains always.

How to resolve:

Let's say, you have following table structure:

You can use raw sql functionality in order to resolve your issue because linq cannot parse the way you need the query to be executed. You can try following way:

SQL stored procedure:

CREATE PROCEDURE UpdateUniqueRecord
    @Column8 nvarchar(50)   
AS   
    UPDATE Refresh
    SET column8 = @Column8
    WHERE Column2 = (SELECT TOP 1 Column2 FROM Refresh 
                     WHERE Column2 != Column1)

    SELECT TOP 1 Column1, Column2, Column3, column8   
    FROM Refresh 
    WHERE Column2 != Column1
GO  

Call stored procedure in controller:

public async Task<IActionResult> Index()
{
    var UpdatedValue = "New updated column value";
    var executeSp = string.Format("EXEC UpdateUniqueRecord '{0}'", UpdatedValue);
    var sqlresult = _context.Refresh.FromSqlRaw(executeSp).ToList();
    return Ok(sqlresult);
}

Output: