Strange issue with .NET core on SQL Server 2019

[zydjohn]

Hello:

I just upgraded my database from SQL Server 2017 to SQL Server 2019. When I did the data migration from 2017 DB to 2019 DB, I did a full backup, and restore database. There was one data table has around 1,000 records, only 4 of those 1,000 records I input them by hand using T-SQL command, like this:

INSERT INTO TABLE1 VALUES('key1', 'key2', 1, 'A')
INSERT INTO TABLE1 VALUES('key1', 'key2', 2, 'B')
INSERT INTO TABLE1 VALUES('key1', 'key2', 3, 'C')
INSERT INTO TABLE1 VALUES('key1', 'key2', 4, 'D')

The data table structure is like this:
CREATE TABLE [dbo].[DataRecordType](
[Key1] [nvarchar](10) NOT NULL,
[Key2] [nvarchar](10) NOT NULL,
[Serie] [int] NOT NULL,
[Name] [nvarchar](100) NOT NULL,
CONSTRAINT [PK_DataRecordType] PRIMARY KEY CLUSTERED
([Key1] ASC, [Key2] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
) ON [PRIMARY]
GO

After the data migration, I run my C# .NET Core 3.0 console program to process the migrated data. Now, I found very strange issue:

for most of the other data tables, everything looks nice. But this data table, which has 4 manually input records have such interesting behavoir.



If I use T-SQL command in SQL Server Management Studio 18.4 to see them, like this:

SELECT * FROM Table1 WHERE where Key1 = 'key1' and Key2 = 'key2'


Then I can see all those records meet the conditions, about 200 records are returned.

But if I use Microsoft.EntityFrameworkCore Version 3.0 to retrieve them,

My code in C# like this:


public static List<DataRecordType> Fetch_Data()
{
List<DataRecordType> data = new List<DataRecordType>();
try
{
using MyDB context = new MyDB();
if (context.datarecords.Count() >= 1)
{
data = context.datarecords.toList();
}
}
catch (DbUpdateException ex)
{
Console.WriteLine("[Fetch_Data()] Exception: {0}!", ex.Message);
}
return (data);
}



In my program.cs, I call the DB function to get my data table records:

var x0 = Fetch_Data();

vary0 = x0.Where(x => x.Key1 == "key1"&& x.Key2 == "Key2").ToList();

The all the records (~1000) appear in x0; but only 4 records which I input by hand appear in y0, even I can see around 200 records appear in T-SQL command in SQL Server Management Studio. The other records are missing, the only common features for those missing records are they are migrated from SQL Server 2017 database using first Backup Database command, then restore by SQL Server Management Studio (Version 18.4). But those 4 records were also migrated from SQL Server 2017, but I insert them by hand. The other records were generated by C# .NET core code.

Any idea, what could go wrong.

PS: I can see more, if I input some records by hands which meet the key requirements.

I am using Visual Studio 2019 Version 16.3.8 with .NET Core 3.0 on Windows 10 (Version 1903). SQL Server I am using is SQL Server 2019 (Version 15.0.2000.5)

Continue reading...