Entity Framework Core: how to get table B which references with table A with 2 composite keys

Question

I'm using EF Core 6 to map my database table to entity classes using DB first.

I have table Conversation_Users has a composite primary key made up of 2 columns, which reference 2 other entities (Conversation, User) described in C# shown here:

public class Conversation_Users
{
    [Key]
    [Column(Order = 0)]
    public long ConversationId { get; set; }

    [Key]
    [Column(Order = 1)]
    public long UserId { get; set; }

    public string Name { get; set; }

    // other properties

    public virtual Conversation Conversation { get; set; }  // entity C, nevermind on it

    public virtual User User { get; set; }  // entity D, nevermind on it
}

I have other table Message which has a reference with table Conversation_Users with 2 foreign keys

[Table("Message")]
public class Message
{
    public long Id { get; set; }

    [ForeignKey("ConversationId")]
    public long ConversationId { get; set; }

    [ForeignKey("UserId")]
    public long SenderId { get; set; }

    [ForeignKey("ReferenceMessageId")]
    public long ReferenceMessageId { get; set; }

    // other properties

    public virtual Message ReferenceMessage { get; set; }  // refer to itself

    public virtual Conversation Conversation { get; set; }  // entity C, nevermind on it

    public virtual User Sender { get; set; }  // entity D, nevermind on it
}

From an instance of entity Message (message), how can I get the value Name property in entity Conversation_Users?

Answer 1

It is possible via LINQ query.

// input parameters
var messageId = ...

var query = 
    from m in context.Messages
    where m.Id == messageId
    join cu in context.Conversation_Users on 
       new { m.ConversationId, UserId = m.SenderId } equals 
       new { cu.ConversationId, cu.UserId }
    select cu.Name;

var conversationName = query.FirstOrDefault();

Anyway, it is suspicious Model design, which should contain approperiate navigation properties. Looks like it is many-to-many relationship.