Upgrade to EFCore 7 Results in Unexpected Index Changes

[Mike-E-angelo]

File a bug

Remember:

• Please check that the documentation does not explain the behavior you are seeing.
• Please search in both open and closed issues to check that your bug has not already been filed.

I did a search for the following: All containing indexes must be removed or redefined before the property can be removed. and this did not return anything so I am filing a new issue here to investigate.

Additionally, I also did a search for "index" on this document:
https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-7.0/breaking-changes

And it does not surface anything obvious to the issue that I am experiencing.

I have attempted to upgrade my model to EfCore 7, using the following command:

dotnet ef dbcontext optimize --output-dir Generated --project Starbeam.Entities.Integration --startup-project Starbeam.Entities.Design

This results in the following error:

The property 'Subject' cannot be removed from entity type 'ResaleOrder' because it is being used in the index {'Subject'} on 'ResaleOrder'. All containing indexes must be removed or redefined before the property can be removed.

Include your code

Ok, so it seems like I need to create a migration to do this? I attempt to do so.

My class looks like this:

[Index(nameof(Subject), IsUnique = true)]
public class ResaleOrder : PurchaseOrder
{
	public ResaleListing Subject { get; set; } = default!;

	public float Royalties { get; set; }
}

So I comment out the [Index(nameof(Subject), IsUnique = true)]. This occurs for other 3 classes throwing this error and after commenting out the indexes I finally get a migration created.

(Note that zero model changes with the exception of the above have been done for my model and worked fantastic in EFCore6)

Within the migration, several indexes + FKs are dropped and recreated -- none of which are related to the classes with indexes I commented above. Here is an example

    /// <inheritdoc />
    public partial class RemoveIndexes : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
 // ... omitted for brevity
            migrationBuilder.DropIndex(
                name: "IX_Transaction_SystemDebit_SourceId",
                table: "Transaction");
migrationBuilder.CreateIndex(
                name: "IX_Transaction_SystemDebit_SourceId",
                table: "Transaction",
                column: "SystemDebit_SourceId",
                unique: true,
                filter: "[SourceId] IS NOT NULL");
 // ... omitted for brevity
        }       
    }

Note that filter: "[SourceId] IS NOT NULL" ... shouldn't this be filter: "[SystemDebit_SourceId] IS NOT NULL" ?

Include stack traces

NA this is upgrading from EFCore6 -> 7.

Include verbose output

As this output exposes a good deal of my model I am not comfortable posting this here, but can do so by request to a secure location if necessary. 👍

Include provider and version information

EF Core version: 7
Database provider: . Microsoft.EntityFrameworkCore.SqlServer
Target framework: (e.g. .NET 7.0)
Operating system: Windows 10
IDE: Microsoft Visual Studio Community 2022 (64-bit) - Preview Version 17.5.0 Preview 1.0

[ajcvickers]

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[Mike-E-angelo]

Thank you for your reply @ajcvickers unfortunately I am not sure that is viable due to the size of my model. That is, I am not even sure where I would begin with such an effort. This same model worked fine in EFCore6 and I am attempting to upgrade to EFCore7 but running into this issue.

[ajcvickers]

@Mike-E-angelo The problem is that if you can't reproduce it in a way we can investigate with all the code available to you and good knowledge of how it works, then the chances we can do so without these things is practically nil.

[Trapulo]

I don't know if this it same issue, but I have a project running EF Core 6, that after upgrade to EF Core 7.0 fails with:

System.InvalidOperationException: 'The property 'IncomingInvoice' cannot be removed from entity type 'IncomingInvoiceResent' because it is being used in the index {'IncomingInvoice'} on 'IncomingInvoiceResent'. All containing indexes must be removed or redefined before the property can be removed.'

running this line:
builder.Entity().HasOne(t => t.IncomingInvoiceResent).WithOne(t => t.IncomingInvoice).IsRequired().HasForeignKey(i => i.IncomingInvoiceID);

Basically the model has a 1:1 relationship that seems EF Core 7.0 is trying to remove.
No code changes from the project that is running in EF Core 6.0

Any idea?

[Mike-E-angelo]

After some thought @ajcvickers I do have a previous older version of my solution that I have provided for developercommunity issues in the past. I upgraded it to net7.0 along with the latest package references and was able to reproduce this issue.

You can find this here:
https://developercommunity.visualstudio.com/t/Upgrade-to-EFCore-7-Results-in-Unexpecte/10231883

It's not exactly simple, but it allows you to see the issue that is preventing me from upgrading to EfCore7. I am hoping you are able to work with it. 🙏

[ajcvickers]

@Mike-E-angelo So now I have your code. What are the exact steps I should follow to reproduce the issue?

[Mike-E-angelo]

Thank you for your reply and investigation @ajcvickers ... I did provide the steps in the reported issue. Here they are for your review:

[severity:It’s more difficult to complete my work]
This is in regards to this issue:
https://github.com/dotnet/efcore/issues/29847

I have managed to reproduce this on an older solution. Please find it attached as a zip.

load Starbeam.sln
Run dotnet ef dbcontext optimize --output-dir Generated --project Starbeam.Entities.Integration --startup-project Starbeam.Entities.Design

[ajcvickers]

The solution doesn't load for me. I get 154 NuGet errors.

[Mike-E-angelo]

To be certain @ajcvickers I am using Developer PowerShell in my Visual Studio instance, but that is not necessary. As denoted in the original ticket description above the problem occurs when running a dotnet ef command.

How do you usually run dotnet ef commands? Please kindly attempt this and see if you are able to reproduce it this way.

This is what I see when unzipping the provided zip file, and following the directions on my local machine:

[ajcvickers]

In order to investigate I need to be able to build the application. How do I do that?

[Mike-E-angelo]

I was under the impression dotnet ef builds the solution for you. Does it not do this? 🤔 Is there a setting on my machine that is making this occur for me but not for you?

[ajcvickers]

@Mike-E-angelo I need a solution that I can open in Visual Studio (or some other IDE) and work with. Otherwise how am I to debug, rip out code and re-build, and all the other things associated with root-causing an issue?

[Mike-E-angelo]

@ajcvickers pardon my confusion here but are you saying that the dotnet ef command is not building the solution for you? What errors are you seeing when you use the dotnet ef command?

[Mike-E-angelo]

Incidentally @ajcvickers this same solution was successfully loaded and built in another issue so whatever you are experiencing here seems to be localized to your environment. 🤔

[Mike-E-angelo]

Hi @ajcvickers I wanted to check in on this issue as I have not heard back from you, and I want to be sure that you are not blocked on anything from my side. It is still unclear to me what the issue is (if any) that is blocking you, as very few details have been provided to me.

Please note that based on this issue there have now been two other Microsoft employees that have managed to use the same .zip file, to unzip and successfully build the provided solution without any assistance or direction from me. As an option, we can ping them to see if they can assist you with your environment there.

Thank you for any update you can provide. 👍🙏

[ajcvickers]

@Mike-E-angelo My next steps on this are:

• Get the solution to build
• Attempt to reproduce the issue
• Start ripping code out until I can get to a root cause and a manageably small repro

These things are usually done by the person filing the issue, since, as noted above, they presumably already have a solution that builds and also know the code and hence do not have to spend time understanding what is happening in parts of the code unrelated to the issue or EF Core. However, since you are unable to do this, I will make an attempt. That being said, I am prioritizing other work ahead of it at the moment, since the amount of work is high compared to investigating other issues, and yet the resulting value is unchanged.

[Mike-E-angelo]

These things are usually done by the person filing the issue

There seems to be some continuing confusion here @ajcvickers, please do pardon it from my side. It is my impression and understanding that running dotnet ef both builds the SLN and also reproduces the exception that occurs in EfCore7 (but not in EfCore6) that prevents successful migration.

Here is what I see, noting the Build started... and Build succeeded lines:

For clarity and your convenience, this is the command as originally reported in this ticket from over a week ago as well as the developercommunity ticket where the zip was provided:

dotnet ef dbcontext optimize --output-dir Generated --project Starbeam.Entities.Integration --startup-project Starbeam.Entities.Design

However, since you are unable to do this, I will make an attempt

As I mentioned and demonstrated here, I am able to build the solution fine on my side, and as I have mentioned with the other developercommunity issue, it builds fine with two other machines there. Additionally, I asked here if you are able to run the command to build the solution but did not hear back from you.

since the amount of work is high compared to investigating other issues, and yet the resulting value is unchanged

For further clarity from my side as I understand it, the amount of work here is to run a dotnet ef command, and the value is to unblock myself and others running into this issue from being able to successfully migrate from EfCore6 to EfCore7. :)

[ajcvickers]

@Mike-E-angelo This was my original request, which is the same request made to anyone submitting an issue with insufficient information for us to move forward:

This issue is lacking enough information for us to be able to fully understand what is happening. Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

You have not done this. Providing access to your entire solution is not this. Even if I can reproduce the issue on the command line with the code you provided, that still doesn't get me close to a "small, runnable project." If you are able to do make such a project, then please do so. If you are not able, then I will attempt it, but this requires I have your code in a state that builds in an IDE, which it currently does not for me. Maybe that's due to my machine, maybe not. Either way, I have to take time to make it build, and only then can I start trimming it down to the small project that is needed to understand what is really going on and move forward.

So, again, the bottom line is can you, like everyone else, "attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate?" If yes, then this issue comes into line with all the other bugs filed. If not, then I'll spend some time to try to get there, but it is considerable more work on our side, which changes the cost/value ratio.

[Mike-E-angelo]

Even if I can reproduce the issue on the command line with the code you provided

This was not clear to me until now, as this was never stated. Thank you for verifying. The only reference I have is this comment and this comment, both which imply that there is a problem with building the solution. It is my understanding that if dotnet ef can build it, then Visual Studio can build it as they both use the same building apparatus. Please do pardon the confusion if this is incorrect.

So, again, the bottom line is can you, like everyone else

As I mentioned here no this is not feasible with my current environment+situation, along with acknowledging that the provided solution is not simple, but does demonstrate how I and others are currently not able to migrate to EfCore7 due to this issue.

[Mike-E-angelo]

FWIW @ajcvickers I have attached a zip file with much fewer projects, down to only 8 from the 100+ as provided earlier. It can be found at this comment/reaction here:
https://developercommunity.visualstudio.com/t/Upgrade-to-EFCore-7-Results-in-Unexpecte/10231883#T-N10233184-N10240511

Please note that the original zip that I had provided earlier has been used to track down several issues in both Microsoft and JetBrains this year, so it was a go-to solution when I thought of it here. Based on that conditioning, it did not strike me that I could reduce the number of projects until today (and thinking of the discussion here).

Note that I was able to run the following commands in the unzipped directory and got the error to surface:

dotnet build
dotnet ef dbcontext optimize --output-dir Generated --project Starbeam.Entities.Integration --startup-project Starbeam.Entities.Design

Additionally, I was able to subsequently open the Starbeam.sln in Visual Studio RTM (Microsoft Visual Studio Community 2022 (64-bit) -Current Version 17.4.3) and perform a build there without issue, but it is unclear if you will have the same success due to your environment. I included the dotnet build in the commands above to hopefully remove any potential issues with regard to this.

I am hoping the smaller solution will assist you here. Please let me know if there is any further information I can provide to further assist you in this matter.

[ajcvickers]

@Mike-E-angelo The DepositOrder entity type specifies that Subject should be an index:

[Index(nameof(Subject), IsUnique = true)]
public class DepositOrder : ExternalProcess
{
	public Checkout Subject { get; set; } = default!;

	public Deposit? Result { get; set; }
}

But then Subject is mapped as an owned type, so it can't be an index.

@AndriySvyryd We might want to throw a better exception or do something in model validation.

Minimal repro:

[Index(nameof(Subject), IsUnique = true)]
public class DepositOrder
{
    public Guid Id { get; set; }

    public Checkout Subject { get; set; } = default!;
}

public class Checkout
{
    public DateTimeOffset Created { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow")
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<DepositOrder>().OwnsOne(e => e.Subject);
    }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new SomeDbContext())
        {
            await context.Database.EnsureDeletedAsync();
            await context.Database.EnsureCreatedAsync();
        }
    }
}

[Mike-E-angelo]

Thank you very much for your time and investigation @ajcvickers it is appreciated. 🙏

But then Subject is mapped as an owned type, so it can't be an index.

Can you please go into more detail about this? This is what I see for my configuration and it does not make use of the OwnsOne call:

Additionally, I am curious about why this works in EFCore6 but not EFCore7. Thank you for any continued time/insight you can provide.

[ajcvickers]

@Mike-E-angelo You're right, it's not OwnsOne in this case, but HasOne has the same problem. That is, [Index(nameof(Subject)... says that Subject is a regular property, but HasOne says that it is a navigation to an entity type. These are incompatible things. IndexAttribute cannot reference a navigation.

Additionally, I am curious about why this works in EFCore6 but not EFCore7.

The model is not valid in either case. The IndexAttribute was likely being ignored in EF Core 6.

[Mike-E-angelo]

OK sounds good to me, @ajcvickers. Thank you again for your time and patience.

That does solve one problem but there is another problem as mentioned in the original issue that an index gets re-created with what appears to be the wrong filter:

migrationBuilder.CreateIndex(
                name: "IX_Transaction_SystemDebit_SourceId",
                table: "Transaction",
                column: "SystemDebit_SourceId",
                unique: true,
                filter: "[SourceId] IS NOT NULL");

Shouldn't this be filter: "[SystemDebit_SourceId] IS NOT NULL" ? This is what is generated in EFCore6 at least.

Unfortunately, I tried to reproduce this in the solution we're using here but this column does not exist there. I will see about getting something for you to test now that I am unblocked with this first issue.

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.