Same EF Core query with the same data set and different result in every run

Question

I have very weird issue below is a service which called by controller, it's returning different result without any change on passed parameter or on the data set

so for example in first run it shows me the found result as 15 and not found is 25, and without changing anything in the next run shows me total different result, and every time I ran the code it shows different result without changes on the data

Here is the code

public async Task<List<KeyValuePair<string, double>>> GetTotals(DateTime? startDate, DateTime? endDate, string? parentGroup, string? childGroup, string? location)
{
    List<KeyValuePair<string, double>> result = new List<KeyValuePair<string, double>>();

    try
    {
        SqlParameter[] param = StoredProcedureHelpers.GetAsSQLParameters(startDate, endDate, parentGroup, childGroup, location);

        string sql = "EXEC MyDb.spGetResult @StartDate, @EndDate, @parentGroup, @childGroup, @Location";

        var queryResult = await _context.VwResults.FromSqlRaw(sql, param).AsNoTracking().ToListAsync();

        queryResult = queryResult.AsEnumerable()
                                 .Where(x => !string.IsNullOrEmpty(x.Category) && 
                                             x.Category.Trim().ToUpper().Equals("Category1") &&
                                             !string.IsNullOrEmpty(x.FinalResultValue) &&
                                             (x.FinalResultValue.Trim().Equals("Found") || x.FinalResultValue.Trim().Equals("Not Found"))).ToList();

        var distinctedList = queryResult.DistinctBy(x => x.TextId);
        var totalResutls = distinctedList.Count();

        var found = distinctedList.Count(x => x.FinalResultValue?.Trim().ToUpper() == "Found".ToUpper());
        var notFound = distinctedList.Count(x => x.FinalResultValue?.Trim().ToUpper() == "Not Found".Trim().ToUpper());
     
        result.Add(new KeyValuePair<string, double>(TOTAL_TEST_RESULTS, totalResutls));
        result.Add(new KeyValuePair<string, double>("Found", found));
        result.Add(new KeyValuePair<string, double>("Not_Found", notFound));
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }

    return result;
}

Update:

Here is my Stored Procedure query:

ALTER PROCEDURE [MyDb].[spGetResult]   
    @StartDate DATE,   
    @EndDate DATE,
    @parentGroup VARCHAR(100) = NULL,
    @childGroup VARCHAR(100) = NULL,
    @Location VARCHAR(100) = NULL
AS   

SET NOCOUNT ON;  
SELECT  distinct 
        s.SCode AS SCode,
        s.text_id AS Text_ID,

        sp.Location,
       
        s.date AS Date,
        [dbo].[CASES].[DESCRIPTION] AS childGroup,
        t.group_name AS Lab,
        [dbo].[CASES].[DESCRIPTION] AS Result1,
        a.c_analyte_group AS Category,
        a.analysis_type AS parentGroup,
        [dbo].[CASES].[DESCRIPTION] AS Final_Result,                         
        r.FORMATTED_ENTRY,
        r.C_FINAL_ID_ENTRY AS FinalResult_Value
FROM [dbo].[SAMPLE]  AS s 

INNER JOIN [dbo].[TEST] AS t ON t.SCode = s.SCode
INNER JOIN [dbo].[RESULT] AS r ON r.test_number = t.test_number
INNER JOIN [dbo].[ANALYSIS] AS a  ON a.name = r.analysis
INNER JOIN [dbo].[CASES]  on [dbo].[CASES].[NAME] = r.alias_name

 
WHERE (s.date >= @StartDate AND s.date <= @EndDate)
    
AND (@parentGroup IS NULL OR  t.quot_group  LIKE '%'+@parentGroup+'%' OR t.ANALYSIS  LIKE '%'+@parentGroup+'%') 
AND (@childGroup IS NULL OR [dbo].[CASES].[DESCRIPTION] LIKE '%'+@childGroup+'%')
AND (@Location IS NULL OR sp.location LIKE '%'+@Location+'%')
    AND t.group_name = 'CH888'
    AND s.template in ( 'YTIO', 'WWWWW')
    AND a.active = 'AYT'
    AND a.c_analyte_group in ('Category1','Category2')

     AND t.status in ('NON Activated'); 

Answer 1

After delving deeply into the code and data, I identified the issue lying within the data itself, or more specifically, in the stored procedure. The problem arose from the utilization of the "LIKE" operator for result comparison, leading to the discovery of duplicate entries.

For instance, if we were searching for both "ABCDEFG" and "EFG," the "EFG" result would erroneously appear within the results for "ABCDEFG," creating the illusion of duplicate data during the initial check. To address this, we have replaced the "LIKE" operator with "=" in the stored procedure.

I provided this solution even if the issue did not directly stem from the code, just in case someone encounters a similar problem in the future.