It is not possible to generate a C # class from a PostgreSQL view using Npgsql + EF6 code first

Question

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

_{(source: linearbench.com )}

Can someone tell me where I did wrong? I am using Entity Framework 6.1.3, with Npgsql 2.2.5. The PosgreSQL database version 9.3.6 is installed on the Ubuntu server.

thanks

Answer 1

I know this question is a bit old right now, but there is a bad buzz for anyone who can look for solutions here. My answer may not be exactly what I was looking for this question, but for me it was enough to solve the problem.

The problem with representations is that the entity structure hardly determines the primary key column for them. On an Sql server, you can use the ISNULL () function to trick EF into believing that the column is the key column, but the equavilant coalesce () function in postgres is not good enough for EF. I also tried to create an auto-incrementing row column by joining other tables with primary keys, etc .; no luck with any of them.

However, something that just emulates the functionality that I need to be able to query my views in the objects of my view is simply to extend my context class with functions that call Database.SqlQuery and return them as Queryable

For instance:

Suppose your database has "foo", with id, bar, baz columns. You can write your own POCO to store view data, for example:

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

 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 like any other table

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

You won’t be able to do inserts or use any additional features that usually come with the standard DbSet, but views are usually used as read requests anyway (unless you use some special insert triggers) ...

But this gives you a basic call that the entire view will query, and it doesn’t end up in the database, because it remains as a query, so you can name any other LINQ extensions on it, such as “Where to filter” this to the desired results.

I switched from a sql server to postgres sql using npgsql lib, and this fix allowed my views to work without the need to make any changes to my codebase, as if nothing had changed at all and even though edmx wouldn't generate my view objects due to the lack of a (noticeable) primary key.

Hope this helps!