Strange issue with .NET core on SQL Server 2019 RRS feed

  • Question

  • 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,
    ([Key1] ASC, [Key2] ASC)
    ) ON [PRIMARY]

    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>();
        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)

    • Moved by CoolDadTx Tuesday, November 12, 2019 2:46 PM EF related
    Tuesday, November 12, 2019 2:32 AM


  • I see something that is contradictory in your code:

    You create the table with ... PRIMARY KEY CLUSTERED ([Key1] ASC, [Key2] ASC)

    So the combination of Key1 and Key2 is the primary key and therefore it should not allow duplicates. However, the data that you are inserting uses the same pair of values for Key1 and Key2 (and, in fact, according to your description there are 200 rows containing the same values). This should be impossible. The database would not allow these repetitions if the primary key was indeed the one that you showed.

    This leads me to think that there is some mix-up in your environment. Perhaps you defined your EF code based on the definition that has this PK, but the database was not created with the same PK, or some other similar problem. This will confuse EF and make it retrieve the wrong results.

    • Proposed as answer by John Boncek Tuesday, November 12, 2019 4:17 PM
    • Marked as answer by zydjohn Saturday, November 16, 2019 9:52 PM
    Tuesday, November 12, 2019 12:10 PM