Error 6013 when adding view

[sjai013]

I get Error 6013 when I try to add a postgres view using the EF Designer (EF Designer from database). The view itself is just a SELECT * of another table which does have a PK defined.

Error 6013: The table/view 'xxxx' does not have a primary key defined and no valid primary key could be inferred. This table/view has been excluded. To use the entity, you will need to review your schema, add the correct keys, and uncomment it.

I'm using npgsql v3.0.5 with EF6, and VS2015 Community Update 2. Does anyone know of a solution to this problem?

[danieltulp]

same problem here, the problem is that views in PostgreSQL cannot have primary keys devArt's ADO.NET provider for PostgreSql allows the setting of primary keys after adding the views to your context, this could also be a good thing for this project

[titoine54]

@roji any updates on this ? Is there a workaround ?

[AshleyAS]

I am also interested if there is a solution to this issue. I have tried for WEEKS to get my views and stored procedures into the Entity Data Model with no luck. Please advise. Thank you.

[titoine54]

@AshleyAS, After a while, I was able to import a view into the Entity Data Model, however, it has to be done "by hand" in the .edmx file. You need to add the proper definition of your view in the 3 sections of the file (SSDL, CSDL and C-S mapping).

My advice would be to import a table that is similar to the result of your view into your Data Model. Edit your .edmx file with the XML Text Editor and than use the definition of the table that's already mapped to insert the same fields for your view.

Don't forget to specify the primary key for your view in the SSDL and CSDL sections and to declare the properties used for the key as Nullable="false". It should somehow look like this :

        <EntityType Name="viewName">
          <Key>
            <PropertyRef Name="Id" />
            <PropertyRef Name="OtherId" />
          </Key>
          <Property Name="Id" Type="int2" Nullable="false" />
          <Property Name="OtherId" Type="int2" Nullable="false" />
          <Property Name="Description" Type="varchar" MaxLength="100" />
        </EntityType>

The downside of this method is that all your modifications to the .edmx file will be erased whenever you add/remove a table through the function "Update Model from Database". So make sure you keep backups of your modifications. This solution was not reliable enough for us, so we moved to the commercial option provided by DevArt. https://www.devart.com/dotconnect/postgresql/

Cheers.

[AshleyAS]

Thank you @titoine54. Are you still able to see the diagram view after doing this addition? Mine keeps telling me I can only view in XML after I add a few. Maybe I'm missing something.

Do you happen to also have a solution for stored procedures? I have many in my database and cannot get them into my model.

[AshleyAS]

More reading and I don't think using Entity Framework 6 is the best method for Npgsql. I can only get the tables to come through. If I cannot use the full benefit of EF with all objects in my database then it's not worth it for me to convert to EF.

I have started on the journey of Entity Framework Core (EF7 prior to Jan 2016). So far what I've read it should work better with Npgsql. I'll report back with my results.

[titoine54]

@AshleyAS I was able to see the diagram after doing a lot of corrections. I was using an existing Entity Model mapped to a SQL Server database with the same schema as a reference. I also haven't found a solution for functions.