Verify.EntityFramework

Extends Verify to allow snapshot testing with EntityFramework.

See Milestones for release notes.

NuGet

Enable

Enable VerifyEntityFramework once at assembly load time:

EF Core

static IModel GetDbModel()
{
    var options = new DbContextOptionsBuilder<SampleDbContext>();
    options.UseSqlServer("fake");
    using var data = new SampleDbContext(options.Options);
    return data.Model;
}

[ModuleInitializer]
public static void Init()
{
    var model = GetDbModel();
    VerifyEntityFramework.Initialize(model);
}

^{snippet source | anchor}

The GetDbModel pattern allows an instance of the IModel to be stored for use when IgnoreNavigationProperties is called inside tests. This is optional, and instead can be passed explicitly to IgnoreNavigationProperties.

EF Classic

[ModuleInitializer]
public static void Init() =>
    VerifyEntityFrameworkClassic.Initialize();

^{snippet source | anchor}

Recording

Recording allows all commands executed by EF to be captured and then (optionally) verified.

Enable

Call EnableRecording() on DbContextOptionsBuilder.

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseSqlServer(connection);
builder.EnableRecording();
var data = new SampleDbContext(builder.Options);

^{snippet source | anchor}

EnableRecording should only be called in the test context.

Usage

To start recording call Recording.Start(). The results will be automatically included in verified file.

var company = new Company
{
    Name = "Title"
};
data.Add(company);
await data.SaveChangesAsync();

Recording.Start();

await data
    .Companies
    .Where(_ => _.Name == "Title")
    .ToListAsync();

await Verify();

^{snippet source | anchor}

Will result in the following verified file:

{
  ef: {
    Type: ReaderExecutedAsync,
    HasTransaction: false,
    Text:
select c.Id,
       c.Name
from   Companies as c
where  c.Name = N'Title'
  }
}

^{snippet source | anchor}

Sql entries can be explicitly read using Recording.Stop(), optionally filtered, and passed to Verify:

var company = new Company
{
    Name = "Title"
};
data.Add(company);
await data.SaveChangesAsync();

Recording.Start();

await data
    .Companies
    .Where(_ => _.Name == "Title")
    .ToListAsync();

var entries = Recording.Stop();
//TODO: optionally filter the results
await Verify(
    new
    {
        target = data.Companies.Count(),
        entries
    });

^{snippet source | anchor}

DbContext spanning

Recording.Start() can be called on different DbContext instances (built from the same options) and the results will be aggregated.

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseSqlServer(connectionString);
builder.EnableRecording();

await using var data1 = new SampleDbContext(builder.Options);
Recording.Start();
var company = new Company
{
    Name = "Title"
};
data1.Add(company);
await data1.SaveChangesAsync();

await using var data2 = new SampleDbContext(builder.Options);
await data2
    .Companies
    .Where(_ => _.Name == "Title")
    .ToListAsync();

await Verify();

^{snippet source | anchor}

{
  ef: [
    {
      Type: ReaderExecutedAsync,
      HasTransaction: false,
      Parameters: {
        @p0 (Int32): 0,
        @p1 (String): Title
      },
      Text:
set implicit_transactions off;

set nocount on;

insert  into Companies (Id, Name)
values                 (@p0, @p1)
    },
    {
      Type: ReaderExecutedAsync,
      HasTransaction: false,
      Text:
select c.Id,
       c.Name
from   Companies as c
where  c.Name = N'Title'
    }
  ]
}

^{snippet source | anchor}

Disabling Recording for an instance

var company = new Company
{
    Name = "Title"
};
data.Add(company);
await data.SaveChangesAsync();

Recording.Start();

await data
    .Companies
    .Where(_ => _.Name == "Title")
    .ToListAsync();
data.DisableRecording();
await data
    .Companies
    .Where(_ => _.Name == "Disabled")
    .ToListAsync();

await Verify();

^{snippet source | anchor}

{
  ef: {
    Type: ReaderExecutedAsync,
    HasTransaction: false,
    Text:
select c.Id,
       c.Name
from   Companies as c
where  c.Name = N'Title'
  }
}

^{snippet source | anchor}

Disabling Recording globally

Recording is attached by EnableRecording(). Pass recordCommands: false to Initialize to leave that interceptor unattached, which makes every subsequent EnableRecording() call a no-op:

VerifyEntityFramework.Initialize(data, recordCommands: false);

^{snippet source | anchor}

Only recording is disabled. The converters, and the queryable to SQL file converter, are still registered.

This is useful when another package records the same commands. Verify.SqlServer subscribes to the Microsoft.Data.SqlClient diagnostic listener and records under the name sql. Since EF Core executes its commands through SqlCommand, with both packages recording every command EF executes is captured twice: once as ef and once as sql. Disable one of the two:

VerifyEntityFramework.Initialize(model, recordCommands: false) keeps the sql entries.
VerifySqlServer.Initialize(recordCommands: false) keeps the ef entries, which also carry the command Type and transaction state. It has to be called before VerifierSettings.InitializePlugins(), otherwise plugin discovery initializes Verify.SqlServer first with recording enabled, and the explicit call throws Already Initialized.

InMemory

The InMemory provider executes no SQL, so there are no commands to record. Instead EnableRecording() records:

Each query, as the LINQ expression that EF executes, with captured variables extracted to Parameters.
Each SaveChanges, as the entities being added, modified, and deleted, in the same format as ChangeTracking.

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseInMemoryDatabase(databaseName);
builder.EnableRecording();
var data = new SampleDbContext(builder.Options);

^{snippet source | anchor}

Recording.Start();

data.Add(
    new Company
    {
        Id = 1,
        Name = "Title"
    });
await data.SaveChangesAsync();

await data
    .Companies
    .Where(_ => _.Name == "Title")
    .ToListAsync();

await Verify();

^{snippet source | anchor}

Will result in the following verified file:

{
  ef: [
    {
      Type: SaveChangesAsync,
      Added: {
        Company: {
          Id: 1,
          Name: Title
        }
      }
    },
    {
      Type: QueryAsync,
      Text:
DbSet<Company>()
    .Where(_ => _.Name == "Title")
    }
  ]
}

^{snippet source | anchor}

Queries compiled with EF.CompileQuery or EF.CompileAsyncQuery are not recorded, since after being compiled they execute without passing through the query compiler.

ChangeTracking

Added, deleted, and Modified entities can be verified by performing changes on a DbContext and then verifying the instance of ChangeTracking. This approach leverages the EntityFramework ChangeTracker.

Added entity

This test:

[Test]
public async Task Added()
{
    var options = DbContextOptions();

    await using var data = new SampleDbContext(options);
    var company = new Company
    {
        Name = "company name"
    };
    data.Add(company);
    await Verify(data.ChangeTracker);
}

^{snippet source | anchor}

Will result in the following verified file:

{
  Added: {
    Company: {
      Id: 0,
      Name: company name
    }
  }
}

^{snippet source | anchor}

Deleted entity

This test:

[Test]
public async Task Deleted()
{
    var options = DbContextOptions();

    await using var data = new SampleDbContext(options);
    data.Add(new Company
    {
        Name = "company name"
    });
    await data.SaveChangesAsync();

    var company = data.Companies.Single();
    data.Companies.Remove(company);
    await Verify(data.ChangeTracker);
}

^{snippet source | anchor}

Will result in the following verified file:

{
  Deleted: {
    Company: {
      Id: 0
    }
  }
}

^{snippet source | anchor}

Modified entity

This test:

[Test]
public async Task Modified()
{
    var options = DbContextOptions();

    await using var data = new SampleDbContext(options);
    var company = new Company
    {
        Name = "old name"
    };
    data.Add(company);
    await data.SaveChangesAsync();

    data.Companies.Single()
        .Name = "new name";
    await Verify(data.ChangeTracker);
}

^{snippet source | anchor}

Will result in the following verified file:

{
  Modified: {
    Company: {
      Id: 0,
      Name: {
        Original: old name,
        Current: new name
      }
    }
  }
}

^{snippet source | anchor}

Queryable

This test:

var queryable = data.Companies
    .Where(_ => _.Name == "company name");
await Verify(queryable);

^{snippet source | anchor}

Will result in the following verified files:

EF Core

CoreTests.Queryable.verified.txt

[
  {
    Name: company name
  }
]

^{snippet source | anchor}

CoreTests.Queryable.verified.sql

select c.Id,
       c.Name
from   Companies as c
where  c.Name = N'company name'

^{snippet source | anchor}

EF Classic

ClassicTests.Queryable.verified.txt

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Content] AS [Content]
    FROM [dbo].[Companies] AS [Extent1]
    WHERE N'value' = [Extent1].[Content]

^{snippet source | anchor}

AllData

This test:

await Verify(data.AllData())
    .AddExtraSettings(
        serializer =>
            serializer.TypeNameHandling = TypeNameHandling.Objects);

^{snippet source | anchor}

Will result in the following verified file with all data in the database:

[
  {
    $type: Company,
    Id: 1,
    Name: Company1
  },
  {
    $type: Company,
    Id: 4,
    Name: Company2
  },
  {
    $type: Company,
    Id: 6,
    Name: Company3
  },
  {
    $type: Company,
    Id: 7,
    Name: Company4
  },
  {
    $type: Employee,
    Id: 2,
    CompanyId: 1,
    Name: Employee1,
    Age: 25
  },
  {
    $type: Employee,
    Id: 3,
    CompanyId: 1,
    Name: Employee2,
    Age: 31
  },
  {
    $type: Employee,
    Id: 5,
    CompanyId: 4,
    Name: Employee4,
    Age: 34
  }
]

^{snippet source | anchor}

IgnoreNavigationProperties

IgnoreNavigationProperties extends SerializationSettings to exclude all navigation properties from serialization:

[Test]
public async Task IgnoreNavigationProperties()
{
    var options = DbContextOptions();

    await using var data = new SampleDbContext(options);

    var company = new Company
    {
        Name = "company"
    };
    var employee = new Employee
    {
        Name = "employee",
        Company = company
    };
    await Verify(employee)
        .IgnoreNavigationProperties();
}

^{snippet source | anchor}

Ignore globally

var options = DbContextOptions();
using var data = new SampleDbContext(options);
VerifyEntityFramework.IgnoreNavigationProperties();

^{snippet source | anchor}

WebApplicationFactory

To be able to use WebApplicationFactory for integration testing an identifier must be used to be able to retrieve the recorded commands. Start by enable recording with a unique identifier, for example the test name or a GUID:

protected override void ConfigureWebHost(IWebHostBuilder webBuilder)
{
    var dataBuilder = new DbContextOptionsBuilder<SampleDbContext>()
        .EnableRecording(name)
        .UseSqlServer(connectionString);
    webBuilder.ConfigureTestServices(
        _ => _.AddScoped(
            _ => dataBuilder.Options));
}

^{snippet source | anchor}

Then use the same identifier for recording:

var httpClient = factory.CreateClient();

Recording.Start(testName);

var companies = await httpClient.GetFromJsonAsync<Company[]>("/companies");

var entries = Recording.Stop(testName);

^{snippet source | anchor}

The results will not be automatically included in verified file so it will have to be verified manually:

await Verify(
    new
    {
        target = companies!.Length,
        sql = entries
    });

^{snippet source | anchor}

Descriptive Table Aliases

By default EF generates single character table aliases in SQL (eg c for Companies, e for Employees). UseDescriptiveTableAliases replaces these with the full table name, making recorded and verified SQL easier to read.

Enable

Call UseDescriptiveTableAliases() on DbContextOptionsBuilder.

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseSqlServer(connection);
builder.UseDescriptiveTableAliases();

Result

With descriptive aliases enabled, the generated SQL:

select   companies.Id,
         companies.Name,
         employees.Id,
         employees.Age,
         employees.CompanyId,
         employees.Name
from     Companies as companies
         left outer join
         Employees as employees
         on companies.Id = employees.CompanyId
order by companies.Name,
         companies.Id

Instead of the default:

select   c.Id,
         c.Name,
         e.Id,
         e.Age,
         e.CompanyId,
         e.Name
from     Companies as c
         left outer join
         Employees as e
         on c.Id = e.CompanyId
order by c.Name,
         c.Id

Descriptive Parameter Names

By default EF generates generic parameter names in SQL (eg @p0, @p1). UseDescriptiveParameterNames replaces these with the column name, making recorded and verified SQL easier to read. When the same column name appears across multiple tables in a batch, subsequent occurrences are prefixed with the entity type name (eg @Id for the first table, @EmployeeId for the second).

Enable

Call UseDescriptiveParameterNames() on DbContextOptionsBuilder.

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseSqlServer(connection);
builder.UseDescriptiveParameterNames();

Result

With descriptive parameter names enabled, an insert:

{
  ef: {
    Type: ReaderExecutedAsync,
    HasTransaction: false,
    Parameters: {
      @Id (Int32): 0,
      @Name (String): Title
    },
    Text:
set implicit_transactions off;

set nocount on;

insert  into Companies (Id, Name)
values                 (@Id, @Name)
  }
}

^{snippet source | anchor}

Instead of the default:

Parameters: {
  @p0 (Int32): 0,
  @p1 (String): Title
},
Text:
insert  into Companies (Id, Name)
values                (@p0, @p1)

Duplicate column names

When multiple tables in the same batch have columns with the same name, subsequent occurrences are prefixed with the entity type name:

{
  ef: {
    Type: ReaderExecutedAsync,
    HasTransaction: true,
    Parameters: {
      @Age (Int32): 25,
      @CompanyId (Int32): 100,
      @EmployeeId (Int32): 200,
      @EmployeeName (String): EmployeeName,
      @Id (Int32): 100,
      @Name (String): CompanyName
    },
    Text:
set nocount on;

insert  into Companies (Id, Name)
values                 (@Id, @Name);

insert  into Employees (Id, Age, CompanyId, Name)
values                 (@EmployeeId, @Age, @CompanyId, @EmployeeName)
  }
}

^{snippet source | anchor}

If the entity-prefixed name itself collides with an existing column name (eg Company + Id = CompanyId which is already a column on Employee), a counter suffix is used as a fallback.

Missing OrderBy

To detect and correct missing OrderBy clauses in EF queries, use EntityFramework.OrderBy.

Query complexity

To detect and limit overly large or expensive EF queries, for example unbounded results, huge Contains lists, or deeply nested includes, use EfQueryComplexity.

Anti-patterns

Queries that contain an anti-pattern throw when they are compiled. This works with any provider, and also applies to ToQueryString(), so verifying a Queryable also throws.

EnableRecording() enables this by default. For a context that does not use recording, use ThrowOnAntiPatterns():

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseSqlServer(connectionString);
builder.ThrowOnAntiPatterns();
var data = new SampleDbContext(builder.Options);

^{snippet source | anchor}

A context that uses UseInternalServiceProvider is not checked, since EF does not apply extension services to that provider.

These checks find queries that are written wrong, whatever data they run against. To limit how large or expensive a query can be, for example the number of values in a Contains list, the number of rows, or the number of includes, use EfQueryComplexity.

Opting out

For a single context:

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseInMemoryDatabase(nameof(EnableRecordingOptOut));
builder.EnableRecording(throwOnAntiPatterns: false);

^{snippet source | anchor}

For all contexts, at assembly load time and before any context is built:

VerifyEntityFramework.ThrowOnAntiPatternsByDefault = false;

^{snippet source | anchor}

To allow one of the EF warnings, use ConfigureWarnings. See below.

Ignored Include and tracking options

EF only applies Include and ThenInclude to the entities returned by a query, and only tracks those entities. When a query ends in a projection, or a scalar like Count or Any, that returns no entity, EF silently ignores Include, ThenInclude, AsNoTracking, AsNoTrackingWithIdentityResolution, and AsTracking. A projection already loads the related data it references, so the ignored operator only misleads the reader.

await ThrowsTask(() =>
        data.Companies
            .Include(_ => _.Employees)
            .Select(_ => new
            {
                _.Name,
                EmployeeCount = _.Employees.Count
            })
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
Include(_ => _.Employees) is ignored, since it is followed by Select, which returns no entity.
EF only applies Include to entities returned by the query, and a projection already loads the related data it references.
Remove it.
}

^{snippet source | anchor}

The operators are kept when an entity is returned, including inside a projection, for example Select(_ => new { Company = _, _.Name }).

Ignored query splitting

AsSplitQuery() and AsSingleQuery() only change how collections are loaded, by a collection Include or a collection in a projection. On a query that loads no collection they do nothing. A single collection is enough for AsSplitQuery() to have an effect, since it then avoids repeating the parent columns for each child row.

await Throws(() =>
        data.Employees
            .Include(_ => _.Company)
            .AsSplitQuery()
            .ToQueryString())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
AsSplitQuery() is ignored, since the query loads no collection.
Query splitting only changes how collection Includes and collections in a projection are loaded.
Remove it.
}

^{snippet source | anchor}

Discarded OrderBy

An OrderBy replaces any earlier ordering, so the earlier ordering is discarded. ThenBy was usually intended. An ordering followed by a row limiting operator, like Take or Skip, is kept. Queries inside lambdas, for example in a projection, are also checked.

await ThrowsTask(() =>
        data.Companies
            .OrderBy(_ => _.Name)
            .OrderBy(_ => _.Id)
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
OrderBy(_ => _.Name) is discarded, since it is followed by OrderBy(_ => _.Id).
Use ThenBy(_ => _.Id) to add a secondary ordering, or remove the first ordering.
}

^{snippet source | anchor}

An operator whose result does not depend on order, like Count, Any, All, Contains, Sum, Average, Min, or Max, also discards an ordering before it:

await ThrowsTask(() =>
        data.Companies
            .OrderBy(_ => _.Name)
            .CountAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Count compared to zero

_.Employees.Count() > 0 counts every matching row, when only whether one exists is needed. _.Employees.Any() stops at the first, and EF translates it to EXISTS. Comparisons with 0 or 1 that only test existence are detected, in either order, for Count(), LongCount(), and the Count property of a collection:

await ThrowsTask(() =>
        data.Companies
            .Where(_ => _.Employees.Count() > 0)
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
`_.Employees.Count() > 0` counts every row, when only whether one exists is needed.
Use `_.Employees.Any()`, which stops at the first.
}

^{snippet source | anchor}

Only comparisons inside a query are detected. query.Count() > 0 compares in C#, after the query has run.

Redundant Distinct

Distinct() is redundant when each row comes from a single entity and includes its primary key, since the key already makes each row unique. That covers a projection that selects every key property, the entity itself, or the key alone:

await ThrowsTask(() =>
        data.Companies
            .Where(_ => _.Name != "")
            .Select(_ => new
            {
                _.Id,
                _.Name
            })
            .Distinct()
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
Distinct() is redundant, since each row includes the key of Company (Id), so the rows are already unique.
Remove it.
}

^{snippet source | anchor}

Only sources that return each entity once are detected: an entity set, or a collection navigation, followed by operators like Where, OrderBy, Take, and Include. A Join, SelectMany, GroupBy, raw SQL, or temporal query can return an entity more than once, so its Distinct() is kept. So is Distinct() with a comparer.

GroupBy that only uses the Key

A GroupBy whose groups are only used for their Key returns the distinct keys, which Select(...).Distinct() states directly. That covers a Select that only reads Key or its members, and the GroupBy overload with a result selector that ignores the elements:

await ThrowsTask(() =>
        data.Employees
            .GroupBy(_ => _.CompanyId)
            .Select(_ => _.Key)
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
GroupBy(_ => _.CompanyId) only returns the distinct keys, since the groups are only used for their Key.
Use Select(_ => _.CompanyId).Distinct(), which states that directly.
}

^{snippet source | anchor}

A selector that uses the groups, for example _.Count(), is not detected.

Collection filter outside the Include

In Include(_ => _.Employees).Where(_ => _.Employees.Any(...)) the Where filters the companies, but the Include still loads every employee of each company returned. That is often meant as a filtered Include, like Include(_ => _.Employees.Where(...)). Filtering the parents by their children is also a correct query, so this check is opt in:

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseInMemoryDatabase(databaseName);
builder.ThrowOnAntiPatterns(_ => _.ThrowOnCollectionFilterOutsideInclude = true);

^{snippet source | anchor}

await ThrowsTask(() =>
        data.Companies
            .Include(_ => _.Employees)
            .Where(_ => _.Employees.Any(_ => _.Age > 30))
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
Where(_ => _.Employees.Any(_ => (_.Age > 30))) filters by Employees, but Include(_ => _.Employees) still loads all Employees of the rows returned.
To load only the matching Employees, filter inside the Include, for example Include(_ => _.Employees.Where(...)).
If filtering the rows by Employees is intended, allow it with ThrowOnCollectionFilterOutsideInclude = false.
}

^{snippet source | anchor}

A filtered Include, and a Where that does not read the included collection, are not detected.

Case conversion of a column

ToLower(), ToUpper(), ToLowerInvariant(), or ToUpperInvariant() on a column, in a filter, ordering, join, or predicate like Any or First, wraps the column in a function, so the database can not use an index on it. With SQL Server's default collation comparisons are case insensitive, so the conversion is redundant too.

This check is opt in, since whether the conversion is needed depends on the column's collation, and many databases are case sensitive by default:

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseInMemoryDatabase(databaseName);
builder.ThrowOnAntiPatterns(_ => _.ThrowOnColumnCaseConversion = true);

^{snippet source | anchor}

await ThrowsTask(() =>
        data.Companies
            .Where(_ => _.Name.ToLower() == "company1")
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message: `_.Name.ToLower()` in Where converts the column, so the database can not use an index on it. SQL Server compares case insensitively with its default collation, so compare the column directly. For a case sensitive column, use EF.Functions.Collate with a case insensitive collation.
}

^{snippet source | anchor}

A conversion of a variable is not detected, since EF sends it as a parameter, and neither is one in a projection, since it only changes the output. For a column with a case sensitive collation, use EF.Functions.Collate with a case insensitive collation.

Redundant null check

EF evaluates a member of a null navigation as null, and null compared to a non null constant is false. So in _.Owner != null && _.Owner.Name == "owner" the null check is redundant, and _.Owner!.Name == "owner" returns the same rows with simpler SQL.

The same applies to nullable scalars, like an int? or a string, including checks using HasValue:

await ThrowsTask(() =>
        data.Cars
            .Where(_ => _.OwnerId != null && _.OwnerId > 0)
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

await ThrowsTask(() =>
        data.Cars
            .Where(_ => _.Owner != null && _.Owner.Name == "owner")
            .ToListAsync())
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
The null check `_.Owner != null` is redundant, since `_.Owner.Name == "owner"` is false when _.Owner is null.
Remove the null check.
}

^{snippet source | anchor}

Only comparisons with a non null constant using ==, >, >=, <, or <= are detected. With !=, or a value that can be null, a null navigation can match, so the null check changes the result and is kept.

EF warnings

EF detects some anti-patterns itself, but only logs them. ThrowOnAntiPatterns() configures these to throw:

RelationalEventId.MultipleCollectionIncludeWarning: more than one collection Include in a single query, which multiplies the rows returned. Use AsSplitQuery(), or configure a query splitting behavior.
CoreEventId.RowLimitingOperationWithoutOrderByWarning: Take or Skip without OrderBy, which returns unpredictable rows.
CoreEventId.FirstWithoutOrderByAndFilterWarning: First without OrderBy or a filter.
CoreEventId.DistinctAfterOrderByWithoutRowLimitingOperatorWarning: Distinct after OrderBy, which erases the ordering.
CoreEventId.PossibleUnintendedReferenceComparisonWarning: entities compared by reference.
CoreEventId.PossibleUnintendedCollectionNavigationNullComparisonWarning: a collection navigation compared to null.
RelationalEventId.QueryPossibleUnintendedUseOfEqualsWarning: Equals between values of different types.
CoreEventId.NavigationBaseIncludeIgnored: an Include of a navigation that fix-up already populates.
RelationalEventId.BoolWithDefaultWarning: a bool property with a database-generated default and no sentinel value, for example HasDefaultValueSql("1"). EF treats false as unset, so it can never insert false.
RelationalEventId.ModelValidationKeyDefaultValueWarning: a key property with a database default, which EF treats as unset when it has the CLR default value.
RelationalEventId.OptionalDependentWithoutIdentifyingPropertyWarning: an optional dependent, sharing a table, with no required property, so EF can not tell an instance with all null values from a missing one.
CoreEventId.PossibleIncorrectRequiredNavigationWithQueryFilterInteractionWarning: a required navigation to an entity with a query filter. When the filter excludes that entity, the entities that require it disappear from queries too.
SqlServerEventId.DecimalTypeDefaultWarning: a decimal property with no precision or column type, whose values SQL Server silently truncates to the default precision.
RelationalEventId.OptionalDependentWithAllNullPropertiesWarning: logged by SaveChanges when it saves an optional dependent, sharing a table, whose properties are all null, so it can not be read back.

The model warnings are logged when the model is built, which happens once per context type. If a context type is first used without ThrowOnAntiPatterns(), later contexts reuse that model and are not checked.

Some of these are only logged by relational providers.

To allow one, call ConfigureWarnings after ThrowOnAntiPatterns():

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseSqlServer(connectionString);
builder.ThrowOnAntiPatterns();
builder.ConfigureWarnings(_ =>
    _.Ignore(CoreEventId.RowLimitingOperationWithoutOrderByWarning));

^{snippet source | anchor}

Runtime checks

Some anti-patterns are only visible while a context runs. Each has its own opt in flag on AntiPatternOptions, passed to ThrowOnAntiPatterns. The options are applied on top of those from an earlier call, so this works before or after EnableRecording():

var builder = new DbContextOptionsBuilder<SampleDbContext>();
builder.UseInMemoryDatabase(nameof(OptionsKeptByEnableRecording));
builder.EnableRecording();
builder.ThrowOnAntiPatterns(
    _ =>
    {
        _.ThrowOnSynchronousCalls = true;
        _.ThrowOnSingleRowSaves = true;
        _.SingleRowSavesThreshold = 5;
    });

^{snippet source | anchor}

Flag	Throws when	Threshold
`ThrowOnLazyLoading`	A navigation is lazy loaded, or lazy loading does nothing since the entity is detached. Each lazy load is a query, so reading a navigation in a loop runs one query per item. Checked whether or not Verify is recording.
`ThrowOnSynchronousCalls`	A query, `SaveChanges`, or raw SQL executes synchronously. Commands that EF runs itself, like migrations, are not checked. For InMemory only `SaveChanges` is checked.
`ThrowOnRepeatedQueries`	One context executes the same query SQL more than the threshold, which usually means a query in a loop (N+1). Relational providers only.	`RepeatedQueryThreshold`, 2
`ThrowOnRepeatedSaveChanges`	One context saves changes more times than the threshold. A `SaveChanges` with nothing to save is not counted.	`RepeatedSaveChangesThreshold`, 2
`ThrowOnSingleRowSaves`	One context has more `SaveChanges` calls that each save a single entity than the threshold.	`SingleRowSavesThreshold`, 2
`ThrowOnLoadThenModify`	A `SaveChanges` only deletes, or only makes the same change to, more entities of one type than the threshold. `ExecuteDelete` or `ExecuteUpdate` does that in one statement.	`LoadThenModifyThreshold`, 1

The thresholds are low, since test data is usually small: an N+1 over three rows runs only three queries.

Verify reads every navigation when it serializes an entity, so with ThrowOnLazyLoading use IgnoreNavigationProperties when verifying entities that lazy load. Entity Framework itself throws, by default, for a lazy load after the context is disposed.

Only while recording

A test usually runs setup, then the code under test, then assertions, all with one context, and the code under test is often only part of a unit of work. So by default the checks other than ThrowOnLazyLoading only count, and throw, while Verify is recording, which is the code under test. They use the recording that EnableRecording set up, including its identifier, or otherwise the default recording:

// setup, which is not counted
for (var id = 1; id <= 3; id++)
{
    data.Add(NewCompany(id));
    await data.SaveChangesAsync();
}

Recording.Start();

// the code under test
await Assert.ThrowsAsync<Exception>(async () =>
{
    for (var id = 11; id <= 13; id++)
    {
        data.Add(NewCompany(id));
        await data.SaveChangesAsync();
    }
});

Recording.Stop();

^{snippet source | anchor}

To check everything a context does, set OnlyWhileRecording = false.

For example, with ThrowOnRepeatedQueries and a RepeatedQueryThreshold of 2:

await ThrowsTask(async () =>
    {
        foreach (var id in new[] { 1, 4, 6 })
        {
            await data.Companies
                .Where(_ => _.Id == id)
                .ToListAsync();
        }
    })
    .IgnoreStackTrace();

^{snippet source | anchor}

Throws:

{
  Type: Exception,
  Message:
The same query executed 3 times in one context, which usually means a query in a loop (N+1).
Load the data in one query, for example with Include, a projection, or Contains.
Query:
SELECT [c].[Id], [c].[Name]
FROM [Companies] AS [c]
WHERE [c].[Id] = @id
}

^{snippet source | anchor}

ScrubInlineEfDateTimes

In some scenarios EntityFrmaeowrk does not parameterise DateTimes. For example when querying temporal tables.

ScrubInlineEfDateTimes() is a convenience method that calls .ScrubInlineDateTimes("yyyy-MM-ddTHH:mm:ss.fffffffZ").

Static usage

VerifyEntityFramework.ScrubInlineEfDateTimes();

Instance usage

var settings = new VerifySettings();
settings.ScrubInlineEfDateTimes();
await Verify(target, settings);

^{snippet source | anchor}

Fluent usage

await Verify(target)
    .ScrubInlineEfDateTimes();

^{snippet source | anchor}

DisableSqlFormatting

By default SQL captured against SQL Server is reformatted via SqlFormatter before being written to the snapshot. This applies to both Recording output and Queryable .sql files.

Reformatting can be disabled globally:

VerifyEntityFramework.DisableSqlFormatting = true;

^{snippet source | anchor}

When disabled, the SQL is written verbatim as produced by EntityFramework.

Replaying recent migrations

Migrations are usually only ever tested against a database built by migrating an empty one. Deployed databases are not like that: a deployment applies state after migrating, such as enabling change tracking, rebuilding views, or re-granting permissions. That state can make DDL that is valid against an empty database fail against a real one. SQL Server, for example, refuses to drop a primary key while change tracking is enabled on the table.

ReplayRecentMigrations applies the most recent migrations one at a time, running a callback after each, so migrations meet the conditions a deployment gives them.

The database has to start with no migrations applied, so build it from an empty template.

// the template is left empty, so each test migrates forward from nothing
static SqlInstance<MyDbContext> sqlInstance = new(
    constructInstance: builder => new(builder.Options),
    buildTemplate: _ => Task.CompletedTask);

^{snippet source | anchor}

await using var database = await sqlInstance.Build();

await database.Context.ReplayRecentMigrations(
    count: 5,
    afterEachMigration: ApplyDeploymentState);

^{snippet source | anchor}

The callback applies whatever the deployment applies after migrating.

// whatever the deployment does after migrating: enabling
// change tracking, rebuilding views, re-granting permissions
static Task ApplyDeploymentState(MyDbContext data) =>
    Task.CompletedTask;

^{snippet source | anchor}

Everything before the window is applied in a single hop, since those migrations are not under test. From there each migration is applied on its own, with the callback in between.

The one at a time part is the point. Applying the window in a single hop and running the callback once at the end is not equivalent: a table created by a migration inside the window would never have the state applied to it before a later migration alters it, and that is exactly the case that tends to break.

Migrating a database that is already up to date would revert migrations by running their Down, so this throws rather than doing that.

Icon

Database designed by Creative Stall from The Noun Project.

Name		Name	Last commit message	Last commit date
.github		.github
docs		docs
src		src
.editorconfig		.editorconfig
.gitattributes		.gitattributes
.gitignore		.gitignore
claude.md		claude.md
code_of_conduct.md		code_of_conduct.md
global.json		global.json
license.txt		license.txt
readme.md		readme.md
todo.md		todo.md

Uh oh!

Latest commit

History

Folders and files

Repository files navigation

Verify.EntityFramework

Sponsors

Entity Framework Extensions

Developed using JetBrains IDEs

NuGet

Enable

EF Core

EF Classic

Recording

Enable

Usage

DbContext spanning

Disabling Recording for an instance

Disabling Recording globally

InMemory

ChangeTracking

Added entity

Deleted entity

Modified entity

Queryable

EF Core

CoreTests.Queryable.verified.txt

CoreTests.Queryable.verified.sql

EF Classic

ClassicTests.Queryable.verified.txt

AllData

IgnoreNavigationProperties

Ignore globally

WebApplicationFactory

Descriptive Table Aliases

Enable

Result

Descriptive Parameter Names

Enable

Result

Duplicate column names

Missing OrderBy

Query complexity

Anti-patterns

Opting out

Ignored Include and tracking options

Ignored query splitting

Discarded OrderBy

Count compared to zero

Redundant Distinct

GroupBy that only uses the Key

Collection filter outside the Include

Case conversion of a column

Redundant null check

EF warnings

Runtime checks

Only while recording

ScrubInlineEfDateTimes

Static usage

Instance usage

Fluent usage

DisableSqlFormatting

Replaying recent migrations

Icon

About

Resources

Code of conduct

Stars

Watchers

Forks

Releases

Sponsor this project

Used by

Contributors

Languages