question

GuillermoPerez-9757 avatar image
0 Votes"
GuillermoPerez-9757 asked DanielZhang-MSFT edited

Help with relationships without using Id...

Please help on how to form a relationship correctly. I have 2 tables with many fields, one contains users, the other a list of messages. Both are not related by the user Id nor the messages Id, instead these are formed like this: (example only)

 User {
 Id
 ...
 MessagesId (string by the way)
 ...
 }
    
 Messages{
 id
 ...
 MessageGroupId (String)
 }

I would like to be able to create a relationship between these 2, that the user can get the list of messages from the other table using the MessagesId and viceversa, that the table Messages could get the user the message belongs to by using its MessageGroupId...

I have tried but received a bunch of errors, maybe because in the list of users, a user could or not have the MessagesId (as not all users send or receive messages)

Any help will be appreciated, thank you in advance!

dotnet-entity-framework-core
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered DanielZhang-MSFT edited

Hi GuillermoPerez-9757,
>>that the user can get the list of messages from the other table using the MessagesId and viceversa, that the table Messages could get the user the message belongs to by using its MessageGroupId...
According to my understanding, you need to configure a one-to-many relationship (a user has multiple messages, and multiple messages correspond to one user).
And you can configure a relationship via the Fluent API.
Code looks like below:

 Class MyContext : DbContext
 {
     public DbSet<User> users { get; set; }
     public DbSet<Messages> messages { get; set; }
    
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
         modelBuilder.Entity<Messages>()
             .HasOne(m => m.User)
             .WithMany(u => u.messages);
     }
 }
 public class User
 {
     public int Id { get; set; }
     public string MessagesId { get; set; }
     ...
     public List<Messages> messages { get; set; }
 }
    
 public class Messages
 {
     public int id { get; set; }
     public string MessageGroupId{ get; set; }
     ...
     public User User { get; set; }
 }

More details you can refer to this document.
Best Regards,
Daniel Zhang


If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

how do we specify what's the key in both tables? My real structure is like this:

 public class Pre1User
 {
         [Key]
         public int id { get; set; }
         ...        
         public string TwilioPhoneId { get; set; }
         public virtual ICollection<TwilioSMSMMS> Messages { get; set; }
 }
    
 public class TwilioSMSMMS
 {
         [Key]
         public int Id { get; set; }
         ...        
         public string PhoneSID { get; set; }
         public virtual Pre1User Pre1User { get; set; }
 }

The real fields are below. I tried:

 modelBuilder.Entity<Pre1User>()
                 .HasMany<TwilioSMSMMS>(pre1User => pre1User.Messages)
                 .WithOne(twilioSMSMMS => twilioSMSMMS.Pre1User)
                 .HasForeignKey(fk => fk.PhoneSID)
                 .IsRequired(false);
    
             modelBuilder.Entity<TwilioSMSMMS>()
                 .HasOne<Pre1User>(twilioSMSMMS => twilioSMSMMS.Pre1User)
                 .WithMany(pre1User => pre1User.Messages)
                 .HasForeignKey(fk => fk.PhoneSID)
                 .IsRequired(false);

throws the error: The relationship from 'TwilioSMSMMS.Pre1User' to 'Pre1User.Messages' with foreign key properties {'PhoneSID' : string} cannot target the primary key {'id' : int} because it is not compatible. Configure a principal key or a set of compatible foreign key properties for this relationship.

Thanks for any help.

0 Votes 0 ·

Hi @GuillermoPerez-9757,
Your Pre1User entity does not contain an attribute (PhoneSID) whose name matches one of these patterns.
So you need to add PhoneSID attribute to your Pre1User.

  public class Pre1User
  {
         ...
          public String  PhoneSID { get; set; }
             
  }

Or if you want the foreign key to reference a property other than the primary key, you can use the Fluent API to configure the principal key property for the relationship.

 modelBuilder.Entity<Pre1User>()
                  .HasMany<TwilioSMSMMS>(pre1User => pre1User.Messages)
                  .WithOne(twilioSMSMMS => twilioSMSMMS.Pre1User)
                  .HasForeignKey(fk => fk.TwilioPhoneId )
                  .HasPrincipalKey(tw => tw.PhoneSID );
                  .IsRequired(false);

Please refer to this document for details.
Best Regards,
Daniel Zhang





0 Votes 0 ·

Pre1Users have TwilioPhoneId
TwilioSMSMMS have PhoneSID

I tried to resolve this but I can't.

0 Votes 0 ·
Show more comments