Nested Models in .Net Entity Framework

Question

Trying to get my head around Entity Framework. I have a database with two tables containing information on transactions. One contains transaction category information and is linked 1-to-1 into the transaction table (transactions.Category <=> Category.CategoryID). I can create new transactions in memory with the category entity filled but when I try to flush to DB I get "{"Invalid object name 'mEconomyUser.category'."}"

Transactions table:

TransactionID   uniqueidentifier
UserID      uniqueidentifier
Date        date
Text        nvarchar(250)
Category    uniqueidentifier
Amount      decimal(18, 0)

Category Table:

CategoryID  uniqueidentifier
UserID      uniqueidentifier
Text        nvarchar(50)

Here are my models:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;

namespace mEconomy.Models
{
    [Table("transactions", Schema = "mEconomyUser")]
    public class Transaction
    {
        [Key]
        public Guid TransactionID { get; set; }
        public Guid UserID { get; set; }
        public DateTime Date { get; set; }
        public string Text { get; set; }
        public virtual Category Category { get; set; }
        public decimal Amount { get; set; }
    }


    public class TransactionDBContext : DbContext
    {

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;

namespace mEconomy.Models
{
    [Table("category", Schema = "mEconomyUser")]
    public class Category
    {
        [Key]
        [ForeignKey("Transaction")]
        public Guid CategoryID { get; set; }
        public Guid UserID { get; set; }
        public String Text { get; set; }

        public virtual Transaction Transaction { get; set; }
    }

    public class CategoryDBContext : DbContext
    {

        public DbSet<Transaction> Categorys { get; set; }
    }
}

Any suggestions?

Answer 1

Thank you everybody for pushing me in the right direction! I now sorted all the issues with my code. Almost a little embarrassing but this was my very first encounter with EF. So this is what I ended up with:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;

namespace mEconomy.Models
{
    [Table("Transactions", Schema = "mEconomyUser")]
    public class Transaction
    {
        [Key] // Define primary-key
        public Guid TransactionID { get; set; }
        public Guid UserID { get; set; }
        public DateTime Date { get; set; }
        public string Text { get; set; }
        public decimal Amount { get; set; }

        [Column("Category")] // Column name of the database foreign key is named Category, want to use that name for the Model
        public Guid? CategoryID { get; set; } // Property to store the foreign key for the Category
        [ForeignKey("CategoryID")] // Define property that holds the foreign key for the category model/object
        public virtual Category Category { get; set; }
    }

    [Table("Categorys", Schema = "mEconomyUser")]
    public class Category
    {
        [Key] // Define primay-key
        public Guid CategoryID { get; set; }
        public Guid UserID { get; set; }
        public String Text { get; set; }

        public virtual ICollection<Transaction> Transactions { get; set; }
    }

    public class TransactionDBContext : DbContext
    {

        public DbSet<Transaction> Transactions { get; set; }
        public DbSet<Category> Categorys { get; set; }
    }
}

Okej a quick rundown of the problems I had.

As GertArnold pointed out, copy & past error! I had two DbSets of type Transaction instead of one each as above.

Two make sure that the code understands the one-to-many, or one-category-to-many-transactions, connection I had to add a ICollection of Transactions on the Category model.

On the transaction option there where nowhere to store the foreign key for the categoy model. I added a Guid (CategoryID) to store it in.

The EF tried to find a Column named Category_CategoryID in the Transactions table to store the foreign key for the corresponding Category entry. Since I used a database already in existence I had to use data annotations to set the Column for the CategoryID field.

I had to do the CategoryID Guid? or nullable. Otherwise all transactions that didn't have a Category would try to add a category into the database with GUID = 000-000.... Witch gave me a primary key constraint error.

And yes I know that Categories are miss spelled... done the same mistake since high-school.

The information you guys provided me with gave me a better understanding of EF so I was able to Google the right words. Found an blog series from a user here that really helped me get into it! - Thanks Morteza Manavi for the great input!

Answer 2

Each context only knows of one entity. So if you do

transaction.Category = category;

and try to commit this through the TransactionDBContext it is evident that it should complain.

The context (well, actually the query provider) tries to convert your code into SQL and therefore it needs mapping info of the Category, but that is in the other context.

I assume you have a reason to use two contexts, but it would be easier to map both entities in one context. If you need to hang on to two contexts, you can get an categoryId from CategoryDBContext and set Transaction.CategoryDBContext by that value. This can be committed by TransactionDBContext because now you're dealing with primitive properties.

---

EDIT
Defined in one context would be:

public class TransactionDBContext : DbContext
{
    public DbSet<Transaction> Transactions { get; set; }
    public DbSet<Category> Categorys { get; set; }
}