Cannot generate C# class from PostgreSQL view using Npgsql + EF6 code first

Question

I am trying to use entity framework code first method to connect to PostgreSQL database, and when I use entity data model wizard in visual studio to generate C# class from database, it can generate classes for each table in database successfully, but the views in database cannot be generated.
_{(source: linearbench.com)}

_{(source: linearbench.com)}

Can someone told me where I did wrong? I use Entity framework 6.1.3, with Npgsql 2.2.5. PosgreSQL database is version 9.3.6 installed on a Ubuntu server.

Thanks

Answer 1

I know this question is a little bit old now, but ill chime in here for anyone else who may be looking for solutions here. My answer may not be exactly what the question was looking for, however, it has sufficed as a work around solution for me.

The problem with views is that entity framework has a hard time determining the primary key column for them. In Sql Server, you can use ISNULL() function to trick EF into thinking that the column is a key column, but the equvilant coalesce() function in postgres isn't good enough for EF. I also tried generating auto-incrementing row id column, joining to other tables with primary keys, etc; no luck with any of these.

However, something that has just about emulated the functionality that I needed as far as being able to query my views into my view objects is to just extend your context class with functions that call Database.SqlQuery and return it as a Queryable

For example:

Suppose a view in your database, "foo", with columns id, bar, baz. You can write your own POCO to hold the view data like so

public class foo
{
    public int id { get; set; }
    public string bar { get; set; }
    public string baz { get; set; }
}

and then extend your context class with a partial class definition like this

public partial class FooContext : DbContext
{
    public IQueryable<foo> foo => 
        this.Database.SqlQuery<foo>( "select * from foo" ).AsQueryable();
}

And then you can query it from your context just the same as any other table

context.foo.where( x => id > 100 ).toList(); //etc,etc

You wont be able to do inserts or use any of those extra capabilities that usually come with the standard DbSet, but Views are typically used as read-only queries anyways (unless youre using some special insert triggers)...

But this gives you a base call that will query the entire view, and it doesn't hit the database because its left as a queryable, so you're free to call any other LINQ extensions on it such as Where to filter it to the results you want.

I migrated from sql server to postgres sql using npgsql lib, and this fix allowed my views to work without having to make any changes to my programs codebase, just as if nothing had changed at all, and despite the fact that the edmx would not generate my view objects due to lack of a (discernible) primary key.

Hope this helps!