Entity Framework how to make two foreign keys refer to same table

Question

I need to create a table, that consists of 2 foreign keys that refer to the same model/table. I have tried many approaches, like making a own ICollection for each of the foreign keys of the same table or like using Inverseproperty and fluentapi but it never worked out (always error).

What I actually need (I also tried above options) would be: My table1:

using Messanger_Backend.SideClasses;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace Messanger_Backend.Models
{
    [Index(nameof(User.UserPhoneNumber), IsUnique = true)]
    public class User : BaseEntity
    {
        public User()
        {
            UserMessages = new HashSet<UserMessage>();
        }
        [Key]
        public int UserID { get; set; }

        [Column(TypeName="nvarchar(50)")]
        public string UserFirstname { get; set; }
        [Column(TypeName = "nvarchar(50)")]
        public string UserLastname { get; set; }
        [Column(TypeName = "nvarchar(20)")]
        [Required]
        public string UserPhoneNumber { get; set; }


        public ICollection<UserMessage> UserMessages { get; set; }

    }
}

My table2:

using Messanger_Backend.SideClasses;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace Messanger_Backend.Models
{
    public class UserMessage : BaseEntity
    {

        [Key]
        public int UserMessageID { get; set; }


        public int UserMessageSenderID { get; set; }
        public User UserMessageSender { get; set; }

        public int UserMessageReceiverID { get; set; }
        public User UserMessageReceiver { get; set; }

        [Column(TypeName="nvarchar(500)")]
        public string UserMessageText { get; set; }
        
    }
}

UPDATE I tried @David Browne - Microsoft suggestion, but I didnt work as well. Heres how I have tried it:

User:

using Messanger_Backend.SideClasses;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace Messanger_Backend.Models
{
    [Index(nameof(User.UserPhoneNumber), IsUnique = true)]
    public class User : BaseEntity
    {
        public User()
        {
            UserMessagesSender = new HashSet<UserMessage>();
            UserMessagesReceiver = new HashSet<UserMessage>();
        }
        [Key]
        public int UserID { get; set; }

        [Column(TypeName="nvarchar(50)")]
        public string UserFirstname { get; set; }
        [Column(TypeName = "nvarchar(50)")]
        public string UserLastname { get; set; }
        [Column(TypeName = "nvarchar(20)")]
        [Required]
        public string UserPhoneNumber { get; set; }
        [Column(TypeName = "nvarchar(250)")]
        public string UserStatus { get; set; }


        [InverseProperty("UserMessageSenderID")]
        public virtual ICollection<UserMessage> UserMessagesSender { get; set; }
        [InverseProperty("UserMessagesReceiverID")]
        public virtual ICollection<UserMessage> UserMessagesReceiver { get; set; }

    }
}

```



UserMessage:
```
using Messanger_Backend.SideClasses;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace Messanger_Backend.Models
{
    public class UserMessage : BaseEntity
    {


        [Key]
        public int UserMessageID { get; set; }

        public int UserMessageSenderID { get; set; }
        [ForeignKey("UserMessageSenderID")]
        public User UserMessageSender { get; set; }
        public int UserMessageReceiverID { get; set; }
        [ForeignKey("UserMessageReceiverID")]
        public User UserMessageReceiver { get; set; }

        [Column(TypeName="nvarchar(500)")]
        public string UserMessageText { get; set; }


        [Column(TypeName = "DateTime")]
        public DateTime UserMessageReceived { get; set; }
        [Column(TypeName = "DateTime")]
        public DateTime UserMessageRead { get; set; }
    }
}

```


Error I receive on second option:

PM> Add-Migration "Init Migration"
Build started...
Build succeeded.
System.InvalidOperationException: The [InverseProperty] attribute on property 'User.UserMessagesReceiver' is not valid. The property 'UserMessagesReceiverID' is not a valid navigation on the related type 'UserMessage'. Ensure that the property exists and is a valid reference or collection navigation.
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.InversePropertyAttributeConvention.ConfigureInverseNavigation(IConventionEntityTypeBuilder entityTypeBuilder, MemberInfo navigationMemberInfo, IConventionEntityTypeBuilder targetEntityTypeBuilder, InversePropertyAttribute attribute)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.InversePropertyAttributeConvention.Process(IConventionEntityTypeBuilder entityTypeBuilder, MemberInfo navigationMemberInfo, Type targetClrType, InversePropertyAttribute attribute)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.InversePropertyAttributeConvention.ProcessEntityTypeAdded(IConventionEntityTypeBuilder entityTypeBuilder, MemberInfo navigationMemberInfo, Type targetClrType, InversePropertyAttribute attribute, IConventionContext`1 context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.NavigationAttributeConventionBase`1.ProcessEntityTypeAdded(IConventionEntityTypeBuilder entityTypeBuilder, IConventionContext`1 context)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnEntityTypeAdded(IConventionEntityTypeBuilder entityTypeBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnEntityTypeAddedNode.Run(ConventionDispatcher dispatcher)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.DelayedConventionScope.Run(ConventionDispatcher dispatcher)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ConventionBatch.Run()
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ConventionBatch.Dispose()
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelInitialized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.OnModelInitialized(IConventionModelBuilder modelBuilder)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.Model..ctor(ConventionSet conventions, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.ModelBuilder..ctor(ConventionSet conventions, ModelDependencies modelDependencies, Boolean _)
   at Microsoft.EntityFrameworkCore.ModelBuilder..ctor(ConventionSet conventions, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.GetModel(DbContext context, IConventionSetBuilder conventionSetBuilder, ModelDependencies modelDependencies)
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel()
   at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model()
   at Microsoft.EntityFrameworkCore.Infrastructure.EntityFrameworkServicesBuilder.<>c.<TryAddCoreServices>b__7_3(IServiceProvider p)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitFactory(FactoryCallSite factoryCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSiteMain(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitCache(ServiceCallSite callSite, RuntimeResolverContext context, ServiceProviderEngineScope serviceProviderEngine, RuntimeResolverLock lockType)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScopeCache(ServiceCallSite singletonCallSite, RuntimeResolverContext context)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteVisitor`2.VisitCallSite(ServiceCallSite callSite, TArgument argument)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.Resolve(ServiceCallSite callSite, ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.DynamicServiceProviderEngine.<>c__DisplayClass1_0.<RealizeService>b__0(ServiceProviderEngineScope scope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngine.GetService(Type serviceType, ServiceProviderEngineScope serviceProviderEngineScope)
   at Microsoft.Extensions.DependencyInjection.ServiceLookup.ServiceProviderEngineScope.GetService(Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType)
   at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider)
   at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure<System.IServiceProvider>.get_Instance()
   at Microsoft.EntityFrameworkCore.Infrastructure.Internal.InfrastructureExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Infrastructure.AccessorExtensions.GetService[TService](IInfrastructure`1 accessor)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(Func`1 factory)
   at Microsoft.EntityFrameworkCore.Design.Internal.DbContextOperations.CreateContext(String contextType)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType, String namespace)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The [InverseProperty] attribute on property 'User.UserMessagesReceiver' is not valid. The property 'UserMessagesReceiverID' is not a valid navigation on the related type 'UserMessage'. Ensure that the property exists and is a valid reference or collection navigation.

Show why the version with two `ICollection`s didn't work. That's the way to go. — Gert Arnold, Jun 12 '21 at 21:31
Does this answer your question? [Entity Framework multiple references to same table](https://stackoverflow.com/questions/30717484/entity-framework-multiple-references-to-same-table) — David Browne - Microsoft, Jun 12 '21 at 21:32
@GertArnold I have edited my question. Now you can see the other way I tried to resolve the issue — vazun, Jun 13 '21 at 08:22
The exception message is pretty clear. Nevertheless, I'd recommend you solve this by fluent mapping. https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#manual-configuration — Gert Arnold, Jun 13 '21 at 11:25
I have also tried it with fluentapi but it didn’t work as well — vazun, Jun 13 '21 at 11:26
But that didn’t answer my question as well. It would be nice if you would write me, why I got the deception and what I did wrong, because you were the person how told me that this works like that. — vazun, Jun 13 '21 at 11:31

David Browne - Microsoft · Answer 1 · 2021-06-13T16:54:45.407

InverseProperty refers to the Navigation Property, not the Foreign Key property, and shorten your navigation property names eg Sender instead of UserMessageSender. So something like

[InverseProperty("Sender")]
public virtual ICollection<UserMessage> MessagesSent { get; set; }

[InverseProperty("Receiver")]
public virtual ICollection<UserMessage> MessagesReceived { get; set; }

Prepending the type name in a property name is unusual in .NET, whereas prepending a column name with the name of its table is more common.

Entity Framework how to make two foreign keys refer to same table

1 Answers1