I have moved my production code from an on-premises server to an Azure server. I noticed that most queries are returning 0 results, but when counting the rows, they return the correct count. After investigating using SQL Profiler, I noticed that EF Core is adding a “WHERE 1 = 0” condition to some queries, even though this was not added in the code.

I researched the issue and found answers suggesting that this could be due to differences between the backend model and the database schema (e.g., nullable fields in the database but not in the model). However, I double-checked class by class and table by table, and everything is identical.

In the following code, it correctly returns the total record count as 2, but the result set is an empty array (even though the database contains two records).

Here is one of services as example:

public async Task<PagedResultDTO<ApplicationOrganizationDTO>> GetAll(PaginationInputDTO<Guid> input)
{
    try
    {
        var query = _context.ApplicationOrganizations.AsQueryable();

      
        if (!string.IsNullOrEmpty(input.SortBy))
        {
            //this will based on slected column
            query = input.SortType.Equals("DESC")? query.OrderByDescending(x => x.EnglishName):query.OrderBy(x=>x.EnglishName);
        }

        // Calculate the number of records to skip and take
        int recordsToSkip = (input.PageNumber - 1) * input.PageSize;
        int recordsToTake = input.PageSize;

        // Retrieve the records based on the pagination parameters
        var records = await query.Skip(recordsToSkip).Take(recordsToTake).ToListAsync();

        var result = new PagedResultDTO<ApplicationOrganizationDTO>
        {
            Results = _mapper.Map<List<ApplicationOrganizationDTO>>(records),
            TotalRecords = await query.CountAsync()
        };

        return result;
    }
    catch (Exception ex)
    {
        throw;
    }
}

Here is the generated query from SQL Profiler:

SELECT [a].[Id], [a].[ActivationDurationInDays], [a].[ActivationStatus], [a].[ArabicName], [a].[CreationDate], [a].[EnglishName], [a].[LastEditDate], [a].[PhoneNumber], [a].[ShortName], [a].[Website]
FROM [ApplicationOrganizations] AS [a]
WHERE 0 = 1

I believe the fixes shouldn’t be by the code, because same code and same database are working perfectly on-premises environment but on not on Azure.