Efcore: Model produced in 3.0 is different from 2.2

[robalexclark]

Migrating a website from 2.2-> 3.0 (with no changes made other than those required by .net core 3.0) causes a previously working EF linq based query to fail.

This is the linq query:

 public async Task<IList<V_FloodWebUserDoc>> GetPolicyDocs(string policyRef, int brokerID)
        {
            string clientLeadAgentCode = context.Brokers.Single(x => x.BrokerID == brokerID).ClientLeadAgentCode;
            return await context.V_FloodWebUserDocs.Where(x => x.PolicyRef == policyRef && x.Policy.ClientLeadAgentCode == clientLeadAgentCode && !x.Archived).ToListAsync();
        }

Using the following entities (based on db views but this shouldn't matter right?).

V_FloodWebUserDoc:

 public partial class V_FloodWebUserDoc
    {
        [Key]
        public int UserDocID { get; set; }

        [Required]
        public string PolicyRef { get; set; }

      .....

        public bool Archived { get; set; }

        public V_FloodWebClientPolicy Policy { get; set; }
    }

V_FloodWebClientPolicy

 public partial class V_FloodWebClientPolicy
    {
        public V_FloodWebClientPolicy()
        {
            UserDocs = new HashSet<V_FloodWebUserDoc>();
        }

        [Key]
        public string PolicyRef { get; set; }

        public string ClientLeadAgentCode { get; set; }

....

        public ICollection<V_FloodWebUserDoc> UserDocs { get; set; }
    }

On EF 2.2 The following correct query is produced:

exec sp_executesql N'SELECT [x].[UserDocID], [x].[Archived], [x].[DocumentType], [x].[FileData], [x].[Filename], [x].[PolicyRef], [x].[ProductType], [x].[UploadedBy], [x].[UploadedOn]
FROM [V_FloodWebUserDocs] AS [x]
INNER JOIN [V_FloodWebClientsPolicies] AS [x.Policy] ON [x].[PolicyRef] = [x.Policy].[PolicyRef]
WHERE (([x].[PolicyRef] = @__policyRef_0) AND ([x.Policy].[ClientLeadAgentCode] = @__clientLeadAgentCode_1)) AND ([x].[Archived] = 0)',N'@__policyRef_0 nvarchar(40),@__clientLeadAgentCode_1 nvarchar(20)',@__policyRef_0=N'NP031727/06/19',@__clientLeadAgentCode_1=N'NFF'

But on 3.0rc1 the following query is created which fails

exec sp_executesql N'SELECT [v].[UserDocID], [v].[Archived], [v].[DocumentType], [v].[FileData], [v].[Filename], [v].[PolicyRef], [v].[PolicyRef1], [v].[ProductType], [v].[UploadedBy], [v].[UploadedOn]
FROM [V_FloodWebUserDocs] AS [v]
LEFT JOIN [V_FloodWebClientsPolicies] AS [v0] ON [v].[PolicyRef1] = [v0].[PolicyRef]
WHERE ((([v].[PolicyRef] = @__policyRef_0) AND @__policyRef_0 IS NOT NULL) AND ((([v0].[ClientLeadAgentCode] = @__clientLeadAgentCode_1) AND ([v0].[ClientLeadAgentCode] IS NOT NULL AND @__clientLeadAgentCode_1 IS NOT NULL)) OR ([v0].[ClientLeadAgentCode] IS NULL AND @__clientLeadAgentCode_1 IS NULL))) AND ([v].[Archived] <> CAST(1 AS bit))',N'@__policyRef_0 nvarchar(4000),@__clientLeadAgentCode_1 nvarchar(4000)',@__policyRef_0=N'NP031727/06/19',@__clientLeadAgentCode_1=N'NFF'

The differences between 2.2 and 3.0rc1 are
1) PolicyRef column has been generated as _PolicyRef1_ for V_FloodWebUsersDocs (which doesn't exist and is why the query fails)
2) INNER JOIN is now a LEFT JOIN (not sure this matters as issue 1 is more pressing)

I haven't set up any foreign key references as annotations in the entities or as fluent code in modelbuilder as these were not required in EF Core 2.2. I appreciate the above "error" might be due to a breaking change rather than a bug but I couldn't find it in the breaking changes list.

Further technical details

EF Core version:
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.0 rc1
Operating system: Windows 10
IDE: Visual Studio 2019 16.2.5

[smitpatel]

@robalexclark - Both of them are not query issue but for some reason the generated model is different from 2.2. We will investigate if it was intentional breaking change or a bug.

[joakimriedel]

Just adding in that I had a similar issue migrating to 3.0. A foreign key Key by convention was now duplicated in migration as Key1. Had to explicitly configure the relationship between the entities using fluent configuration to resolve.

[robalexclark]

Yes, I found an acceptable workaround by adding the following fluent configuration:

   //fix for EF Core 3
            modelBuilder.Entity<V_FloodWebUserDoc>(entity =>
            {
                entity.HasOne(d => d.Policy)
                    .WithMany(p => p.UserDocs)
                    .HasForeignKey(d => d.PolicyRef);
            });

Although I still can't find this different requirement in the list of breaking changes

[thepra]

I agree with @robalexclark and @jcemoller that after setting the relationships in the fluent configuration the generated fields are correct now.
Like suggested by @robalexclark I strongly suggest to update the migration documentation form 2.x to 3.x with such aknowledgment.

[AndriySvyryd]

@robalexclark @thepra The breaking change is documented here