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?

Do you actually have a schema named mEconomyUser in your database and are these tables members of it? If you have a FROM mEconomyUser.category but your table is actually dbo.category, you'll have a problem. — JamieSee
– JamieSee, Commented Jul 10, 2012 at 15:28
The schema is named mEconomy. This is due to shared hosting environment where my ISP sets up a schema for each user. — Kristofer Källsbo
– Kristofer Källsbo, Commented Jul 11, 2012 at 6:05

Community · Accepted Answer · 2021-01-18 12:30:08Z

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!

Gert Arnold · Accepted Answer · 2012-07-14 20:54:26Z

0

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; }
}

edited Jul 14, 2012 at 20:54

answered Jul 10, 2012 at 20:31

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

4 Comments

Kristofer Källsbo Over a year ago

Thank you for your answer! I really have no reason to keep the context separated, I just went one with the "old" db approach, two tables = two contexts. How do I make one context of them both?

Gert Arnold Over a year ago

Just transport your dbset Categorys (Categories) to the first context. That is the regular approach: one context for all, unless there is a good reason to split contexts.

Kristofer Källsbo Over a year ago

I'm not sure what mean with "transport to the first context". Should I define them in the same Model?

Kristofer Källsbo Over a year ago

When I do that I get a new error I'm unable to figure out: Multiple object sets per type are not supported. The object sets 'Transactions' and 'Categorys' can both contain instances of type 'mEconomy.Models.Transaction'.

Collectives™ on Stack Overflow

Nested Models in .Net Entity Framework

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related