How do I use EF6 with Database First and existing views?


I'm an EF noob (any version) and my Google-foo has failed me on finding out how to do this. Which makes me think I must be doing this wrong, but here is the situation:

I'm definitely in an environment that is database first and the schema won't be updated by us coders. I'm also not a fan of 'automatic' code generation, so I've stayed away from the designer or the EF powertools (though I did run through them just to see them work).

To learn I imported the Northwind DB into my LocalDB to have something to play with while creating some simple Web API 2 endpoints. This all went well as I created slimmed down models of the Employees, Shippers, & Region tables in Northwind. Region was particularly interesting as it wasn't plural and EF had issues with that. Anyway, I got by that.

My trouble now is; I want to use a view instead of a table as my source and whatever I'm doing just doesn't seem to work. What I tried was setting it up just like I did the tables. But that produces a ModelValidationException error. I tried looking at the auto-generated code from the designer, but got no insight.

My models:

//-- employee, shipper, & region work as expected
public class employee {
    public int EmployeeID { get; set; }
    public string LastName { get; set; }
    public string FirstName { get; set; }

public class shipper {
    public int ShipperID { get; set; }
    public string CompanyName { get; set; }
    public string Phone { get; set; }

public class region {
    public int RegionID { get; set; }
    public string RegionDescription { get; set; }

//-- invoice is a view (actual viewname is 'Invoices')
//-- so i followed the same rules as i did for employee & shipper
//-- i have tried uppercase 'I' as well as a plural version of the model
public class invoice {
    public string CustomerID { get; set; }
    public string CustomerName { get; set; }
    public string Salesperson { get; set; }
    public int OrderID { get; set; }
    public int ProductID { get; set; }
    public string ProductName { get; set; }

My Context looks like this:

public class NorthwindDBContext : DbContext {
    public DbSet<Employee> Employees { get; set; }
    public DbSet<shipper> Shippers { get; set; }
    public DbSet<region> Regions { get; set; }
    public DbSet<Invoice> Invoices { get; set; } //-- offending line of code

    protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        //--- fix for Region being singular instead of plural

If I comment out the public DbSet<Invoice> Invoices { get; set; } line in the context everything works. Just by having the line present (even if i don't reference the Invoices property) I receive the ModelValidationException error when using the context in anyway.

Can anybody tell me what I'm doing wrong here? Thanks.

Update: I tried this in one of my controllers, but I am too noob'ish to know if this is the right path either, though it worked as far as getting records.

using (var dbContext = new NorthwindDBContext()) {
    return dbContext.Database.SqlQuery<Invoice>("select * from invoices").ToList();

Accepted Answer

Code-first conventions will look for an ID or InvoiceID property to use as a key. Your Invoice model has neither, while the others do. This is the specific reason your code is failing.

The less-specific one is that you can't have entities in EF which lack a unique key. If you can, have the view define a key. Otherwise, you may still be able to work around the issue.

Licensed under: CC-BY-SA
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why