I've followed Adam's answer here and the Entity Framework now works and the Seed()
method also works.
But when I try to access the database like this:
public User FindUserByID(int id)
{
return (from item in this.Users
where item.ID == id
select item).SingleOrDefault();
}
.............................................................................
// GET: /Main/
public ActionResult Index(int? id)
{
var db = UserDataBaseDB.Create();
if (!id.HasValue)
id = 0;
return View(db.FindUserByID(id.Value));
}
It throws an exception at return (from item in this.Users
stating:
Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'dbo.BaseCs'.
I've tried replacing it with:
return this.Users.ElementAt(id);
but then it throws this exception.
LINQ to Entities does not recognize the method 'MySiteCreator.Models.User ElementAt[User](System.Linq.IQueryable
1[MySiteCreator.Models.User], Int32)' method, and this method cannot be translated into a store expression.`
Can anyone help me?
Thank you!
This question is related to
c#
sql
asp.net-mvc-3
entity-framework
If everything is fine with your ConnectionString
check your DbSet collection name in you db context file. If that and database table names aren't matching you will also get this error.
So, for example, Categories, Products
public class ProductContext : DbContext
{
public DbSet<Category> Categories { get; set; }
public DbSet<Product> Products { get; set; }
}
should match with actual database table names:
It might me an issue about pluralizing of table names. You can turn off this convention using the snippet below.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}
For what it is worth, I wanted to mention that in my case, the problem was coming from an AFTER INSERT Trigger!
These are not super visible so you might be searching for a while!
My fix was as simple as making sure the correct connection string was in ALL appsettings.json files, not just the default one.
Instead of
modelBuilder.Entity<BaseCs>().ToTable("dbo.BaseCs");
Try:
modelBuilder.Entity<BaseCs>().ToTable("BaseCs");
even if your table name is dbo.BaseCs
In the context definition, define only two DbSet contexts per context class.
EF is looking for a table named dbo.BaseCs. Might be an entity name pluralizing issue. Check out this link.
EDIT: Updated link.
If you are providing mappings like this:
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Configurations.Add(new ClassificationMap());
modelBuilder.Configurations.Add(new CompanyMap());
modelBuilder.Configurations.Add(new GroupMap());
....
}
Remember to add the map for BaseCs.
You won't get a compile error if it is missing. But you will get a runtime error when you use the entity.
It is most likely a mismatch between the model class name and the table name as mentioned by 'adrift'. Make these the same or use the example below for when you want to keep the model class name different from the table name (that I did for OAuthMembership). Note that the model class name is OAuthMembership whereas the table name is webpages_OAuthMembership.
Either provide a table attribute to the Model:
[Table("webpages_OAuthMembership")]
public class OAuthMembership
OR provide the mapping by overriding DBContext OnModelCreating:
class webpages_OAuthMembershipEntities : DbContext
{
protected override void OnModelCreating( DbModelBuilder modelBuilder )
{
var config = modelBuilder.Entity<OAuthMembership>();
config.ToTable( "webpages_OAuthMembership" );
}
public DbSet<OAuthMembership> OAuthMemberships { get; set; }
}
I don't know if is the case,
If you create a migration before adding a DbSet your sql table will have a name of your model, generally in singular form or by convention we name DbSet using plural form.
So try to verifiy if your DbSet name have a same name as your Table. If not try to alter configuration.
You have to define both the schema and the table in two different places.
the context defines the schema
public class BContext : DbContext
{
public BContext(DbContextOptions<BContext> options) : base(options)
{
}
public DbSet<PriorityOverride> PriorityOverrides { get; set; }
protected override void OnModelCreating(ModelBuilder builder)
{
builder.HasDefaultSchema("My.Schema");
builder.ApplyConfiguration(new OverrideConfiguration());
}
}
and for each table
class PriorityOverrideConfiguration : IEntityTypeConfiguration<PriorityOverride>
{
public void Configure(EntityTypeBuilder<PriorityOverride> builder)
{
builder.ToTable("PriorityOverrides");
...
}
}
Source: Stackoverflow.com