I am not able to have one-to-many relationship in Entity Framework

Question

I am following examples from the internet but it's not working. The database is getting created successfully, there is no error.

What I want to have is: one user can have multiple transactions, and a transaction can have references to two users. One of those is the user who did the transaction, the second is the user to whom transaction is done.

But what is happening is I am getting three foreign keys in the Users table, but none in the Transactions table.

See image below:

My classes

public class User
{
    public int userId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public string CardNumber { get; set; }
    public string Password { get; set; }
    public int Balance { get; set; }
    public string UserType { get; set; }
    public string ProfileUrl { get; set; }

    public IList<Transaction> Transactions { get; set; }
}

public class Transaction
{ 
    public Transaction()
    {
        this.TranscationDateTime = DateTime.UtcNow;
    }

    public int TransactionId { get; set; }
    public int Amount { get; set; }
    public User FromUser { get; set; }
    public User ToUser { get; set; }
    public DateTime TranscationDateTime { get; set; }
}

public class DB: DbContext
{
    public DB() : base("name=DBConnection")
    { }

    public DbSet<User> Users { get; set; }
    public DbSet<Transaction> Transactions { get; set; }
}

Answer 1

You need to make some modification to your code.

First of all, each navigation property needs to be marked as virtual, in order to allow Entity Framework to lazy loading, unless you want always eager load all your navigations (could be a choice, is up to you).

After that, each of your user has outgoing and incoming transactions, so for the User class:

public class User
{
    public int userId { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }

    public string CardNumber { get; set; }

    public string Password { get; set; }

    public int Balance { get; set; }

    public string UserType { get; set; }

    public string ProfileUrl { get; set; }

    public virtual IList<Transaction> IncomingTransactions { get; set; }

    public virtual IList<Transaction> OutgoingTransactions { get; set; }
}

Let's make virtual navigation properties of Transaction class

public class Transaction
{ 
    public Transaction()
    {
        this.TranscationDateTime = DateTime.UtcNow;
    }
    public int TransactionId { get; set; }
    public int Amount { get; set; }
    public virtual User FromUser { get; set; }
    public virtual User ToUser { get; set; }

    public DateTime TranscationDateTime { get; set; }

}

Last, but not least, let's inform your DbContext of how things are supposed to go:

public class MyContext : DbContext
{
   public MyContext(string connectionString) : base(connectionString) { }

   public DbSet<User> Users { get; set; }
   public DbSet<Transaction> Transactions { get; set; }

   protected override void OnModelCreating(DbModelBuilder builder)
   {
      base.OnModelCreating(builder);
      builder.Entity<Transaction>()
        .HasRequired<User>(t => t.FromUser)
        .WithMany(u => u.OutgoingTransactions).WillCascadeOnDelete(false);
      builder.Entity<Transaction>()
        .HasRequired<User>(t => t.ToUser)
        .WithMany(u => u.IncomingTransactions).WillCascadeOnDelete(false);
   }

}

This should be enough for EF autodiscovery to make the right assumptions and create right database structure, that would be two FKs in Transaction table each of them to the primary key of Users table.

And voilà:

Answer 2

What i want to have is one user can have multiple transaction but a transaction can have reference to two user.

Your current database model reflects this accuratly. I will explain why in the rest of my answer.

The User table can not hold the foreign keys to the Transactions table because one User can be associated with multiple Transactions. If the FK column was on the User table, it would need to hold more than one TransactionId.

Instead, the references to the Users are stored in the Transaction table. So every Transaction only has to store a single UserId per FK column.

Transaction.User_userId tells us that this Transaction is in the IList<Transaction> Transactions of the User with the stored User_userId.

To get this list of Transactions for a certain user, we query

SELECT *
FROM Transactions t
INNER JOIN Users u on t.User_userId = u.userId
WHERE u.userId = {theUserId}

The additional FKs ToUser_userId and FromUser_userId exists because they might reference different Users.

If the semantics of the IList<Transaction> Transactions is actually "all transactions that originated from this User", you could configure the ModelBuilder to use the FromUser_userId FK for this collection instead of creating the third FK User_userId. See the answer of Sergey.

Answer 3

This happens because EF doesn't know that one of the FromUser and ToUser fields is supposed to match the collection Transactions - since you are not following the naming conventions. You have several options on how to resolve this situation:

• If you only want to match Transactions collection with either FromUser or ToUser but not both, you can use [ForeignKey] and/or [InverseProperty] attributes to setup the database relation explicitly
• If you want to use BOTH of them, then you would need to specify two collections in the User class - e.g. TransactionsFromUser and TransactionsToUser. You might still need to setup the relationships explicitly through the attributes though