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

Question

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
        modelBuilder.Entity<region>().ToTable("Region");
    }
}

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

Is there a message in the ModelValidationException? Im not sure if EF can read from views in this way — Mike Norgate, Nov 12 '13 at 17:14
The only other message text is: `One or more validation errors were detected during model generation`, which I don't think helps any. So is the answer not to use EF for views, or is it use it in some other way? — huxley, Nov 12 '13 at 18:05

score 0 · Accepted Answer · edited May 23 '17 at 12:14

0

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.

edited May 23 '17 at 12:14

Community

1
1

answered Nov 12 '13 at 19:56

Cory Nelson

29,236
5
72
110

I see. I'll take that to mean that Entity isn't always a good fit for legacy DBs situations? Northwind is actually a good practice project for us stuck with 15 year old databases but want to modernize an app. Inconsistent naming conventions and bad design, we're often working with a mess of a database that can't be changed. – huxley Nov 13 '13 at 01:20
Yea, EF isn't great if you don't have keys. I've never understood why because it supports keyless projections already, and seems like it'd be trivial to add the feature. – Cory Nelson Nov 13 '13 at 16:06
I did get the invoice view to work this morning. Why I continued to try I'm not sure, but I was reading through generated code from the EF powertools and found something I thought I could modify to work. Anyway, I added this to my context and it all started to work: `modelBuilder.Entity().HasKey(t => new { t.OrderID, t.ProductID });` I'm on the fence about whether I should do this, but it works and doesn't seem to be too hack'ish. – huxley Nov 13 '13 at 18:08

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

1 Answers1