We use manual code first Entity Framework for System.Data.Sqlite
So when creating a new entity, we manually create the table, the c# object, and add the DbSet to our context.
I have a very simple object created and I am getting this error when trying to query the context. I have checked the column names and data types many times but I don't see a mismatch here. Also there are no foreign key relationships defined even though the fields have id in the name. Just a standalone table.
The weirdest part is that I can add a new entity to the context, save changes, and it will be persisted to the db. However in the next line when trying to retrieve the entities I get The entity set is not defined in the entity container
error. I have also noticed that if I hover over an instantiated context, all the other db sets will have the EF SQL such as SELECT EXTENT1.myCol as myCol
, but the department_resources
set just says {System.Data.Entity.DbSet<department_resource>}
.
Any ideas on what the issue is here?
Below are excerpts of my files:
DDL
CREATE TABLE department_resources (
dep_res_key VARCHAR PRIMARY KEY,
department_id INT NOT NULL,
resource_id INT NOT NULL);
department_resource.cs
[Table("department_resources")]
public class department_resource
{
[Key]
public string dep_res_key { get; set; }
public int department_id { get; set; }
public int resource_id { get; set; }
}
MyContext.cs
public class MyContext : DbContext
{
public DbSet<department_resource> department_resources { get; set; }
}
Sample Usage
using (MyContext db = new MyContext())
{
db.department_resources.Add(new department_resource()
{ dep_res_key = "anID",
resource_id = 22,
department_id = 23 }); // Works
db.SaveChanges(); // Also works. Even persists to db
var foo = from r in db.department_resources
select r.resource_id; // Doesn't work. Will error as soon as I try to use foo. Like assigning to a combo box item source. Or even just enumerating the results
var bar = db.department_resources; // Also doesn't work.
}
The issue is with deferred execution. Although you're assigning foo
and bar
within your using
block, they're not getting evaluated until they're actually used, after MyContext
has been disposed.
You need to force them to evaluated within your using block. e.g. by converting the results to lists.
Also I notice you've declared them as vars
within your using
block. They'll need to be defined outside of it to be able to use them outside (perhaps you just did this in your sample to simplify?)
List<int> foo;
List<department_resource> bar;
using (MyContext db = new MyContext())
{
db.department_resources.Add(new department_resource()
{ dep_res_key = "anID",
resource_id = 22,
department_id = 23 }); // Works
db.SaveChanges(); // Also works. Even persists to db
foo = (from r in db.department_resources
select r.resource_id).ToList();
bar = db.department_resources.ToList();
}
From MSDN
the query variable itself only stores the query commands. The actual execution of the query is deferred until you iterate over the query variable in a foreach statement. This concept is referred to as deferred execution
Forcing Immediate Execution
Queries that perform aggregation functions over a range of source elements must first iterate over those elements. Examples of such queries are Count, Max, Average, and First. These execute without an explicit foreach statement because the query itself must use foreach in order to return a result. Note also that these types of queries return a single value, not an IEnumerable collection.
You can also force execution by putting the foreach loop immediately after the query expression. However, by calling ToList or ToArray you also cache all the data in a single collection object.