Reverse engineering: Unique Index interpreted incorrectly to give one-to-one instead of one-to-many relationship

[jjklee121]

Hi,

When running database first migration using Scaffold-DbContext the some of the models generated are linked by one-to-one relationships, when in actual fact they are one-to-many and I'm not getting any errors or exceptions.

Basically I have a table of jobs and a table of appointments.
A job can have multiple appointments and each of these appointments can have difference statuses e.g.
Active
Cancelled

So in my crude example above, Job1 has 3 appointments linked to it.

The thing that seems to be causing the issue I'm experiencing is an index I have on one of the tables which was recently added.

Previously without the index when Scaffold-DbContext was run the Job model looked something like this

        public Job()
        {
            Appointment = new HashSet<Appointment>();
            // more code
        }

        [InverseProperty("Job")]
        public virtual ICollection<Appointment> Appointment { get; set; }

But now with the index I am getting.

        public Job()
        {
            // more code
        }

        [InverseProperty("Job")]
        public virtual Appointment Appointment { get; set; }

When I remove the index it generates the models and the relationships correctly between Job and Appointment.

The index below is there to prevent multiple Active Appointments on a single Job at any given time.

CREATE UNIQUE NONCLUSTERED INDEX [IX_Appointment_JobId_NotActiveStatus_Unique] ON [Appointment] ([JobId] ASC) WHERE[StatusId] != 'Cancelled'

I'm not sure if there is anything to tell the scaffolding to ignore the index in this particular case?

Any help would be greatly appreciated.

Many thanks,

Jeremy

[ajcvickers]

Notes for triage: support for filtered indexes in SQL Server was added in 5817 and #6866. I'm not sure off the top of my head how far this permeates into the stack. It feels like in this case we should at least try to interpret the index to not mean the relationship is 1:1, and if we can also add support to reverse engineer the filtered index correctly that would be great. Let's discuss in triage.

[ajcvickers]

EF Triage: Minimal fix is to not reverse engineer this to a unique index. Ideally, the index should be reverse-engineered into the model retaining the filter information. If that is a lot of work, then we can have a new bug for it outlining the work that is needed and we will triage that separately.

[lajones]

@jjklee121 What version of EF packages do you have? I do see the above behavior in 1.1.1, but this is already fixed in 2.0.0 (@ajcvickers this includes retaining the filter info on indexes). Closing as already fixed, but please feel free to re-open if you can see it using the 2.0.0 packages.

[jjklee121]

@lajones Thanks for getting back to me.
I have EntityFrameworkCore 1.1.0 and EntityFrameworkCore.DynamicLinq 1.0.3.3 installed. (I should've mentioned the versions in my initial post)
I will get these updated and see if that fixes my issue.

[bwalters3]

I've just hit what I think is the same issue as posted above but I'm using version 2.0.2 of the Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.SqlServer packages, which would suggest that the issue hasn't been entirely resolved in 2.x. Should I open a new issue or post details to this thread?

[ajcvickers]

@bwalters3 Yes, please open a new issue.