This is a weird one, never seen anything like it before. I have an old project I haven't touched in a while and its setup using Linq to Entities with code-first. Every entity I attempt to select data from works, but the SQL being generated doesn't contain a where clause. (I can see what's generated in SQL Profiler) So the application is basically selecting all rows from the tables and then filtering on the server which is not good. My project is .Net Core 2.2 using Microsoft.EntityFrameworkCore assembly version 2.2.6.0

I feel like it may be a misconfiguration of something but I don't see anything wrong. Anyone seen anything like this before?

Whatever this issue is it also presents me with this exception when trying to save a Country entity; which makes no sense because the CountryId is the primary key in the code-first setup below and I'm definitely not assigning it.

Cannot insert explicit value for identity column in table 'Country' when IDENTITY_INSERT is set to OFF.

I've tried different variations of selections and none of them generate a where clause.

var country = await _taxContext.Countries
    .FirstOrDefaultAsync(a => a.TwoLetterISOCode.Equals(country.jurisCode, StringComparison.OrdinalIgnoreCase))
    .ConfigureAwait(false);

-- generates this sql
SELECT [a].[CountryId], [a].[Name], [a].[TwoLetterISOCode]
FROM [dbo].[Country] AS [a]

var exists = await _taxContext.Countries
    .AnyAsync(a => a.TwoLetterISOCode.Equals(country.jurisCode, StringComparison.OrdinalIgnoreCase))
    .ConfigureAwait(false);

-- generates this sql
SELECT [a].[TwoLetterISOCode]
FROM [dbo].[Country] AS [a]

This is my Country entity POCO

public class Country
{
    public byte CountryId { get; private set; }
    public string Name { get; set; }
    public string TwoLetterISOCode { get; set; }

    public ICollection<Nexus> Nexi { get; set; }
}

My configuration for Country

class CountryConfiguration : IEntityTypeConfiguration<Country>
{
    public void Configure(EntityTypeBuilder<Country> builder)
    {
        builder.ToTable(nameof(Country), "dbo");

        builder.HasKey(ci => ci.CountryId);

        builder.Property(ci => ci.Name)
            .HasColumnType("nvarchar(75)");

        builder.Property(ci => ci.TwoLetterISOCode)
            .HasColumnType("char(2)");

        builder.HasMany(ci => ci.Nexi)
            .WithOne(ci => ci.Country);
    }
}

and my DbContext

public class TaxContext : DbContext
{
    public TaxContext(DbContextOptions<TaxContext> options) : base(options)
    {
    }

    public DbSet<Region> Regions { get; set; }
    public DbSet<Country> Countries { get; set; }
    public DbSet<Nexus> Nexi { get; set; }
    public DbSet<TaxRate> FallbackRates { get; set; }
    public DbSet<CircuitBreakerLog> CircuitBreakerLogs { get; set; }
    public DbSet<AppLog> AppLogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .ApplyConfiguration(new NexusConfiguration())
            .ApplyConfiguration(new RegionConfiguration())
            .ApplyConfiguration(new CountryConfiguration())
            .ApplyConfiguration(new TaxRateConfiguration())
            .ApplyConfiguration(new CircuitBreakerLogConfiguration())
            .ApplyConfiguration(new AppLogConfiguration());
    }
}

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<TaxContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
}

CodePudding user response：

You should see warnings in your output that EF is evaluating your query locally. This is because of your string comparison. While I will support using string.Equals, it won't support the overload you used. EF doesn't know how to deal with the StringComparison.OrdinalIgnoreCase since it relies on the database collation for casing, accents etc. The solution is to use the simple overload or a basic ==:

.FirstOrDefaultAsync(a => a.TwoLetterISOCode.Equals(country.jurisCode))

Or

.FirstOrDefaultAsync(a => a.TwoLetterISOCode == country.jurisCode)