cannot add view to the edmx

Question

When trying to add a view to the edmx file, nothing happens.
I opened the edmx file using wxl editor and I noticed the following error:

warning 6013: The table/view 'CellularOrders.dbo.V_LINK' 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.

(importent thing - I didn't and don't need to add the table which the view based on to the edmx. Moreover, the view is only for doing select statements on the data)

So in the db, I updated the T_LINK table and made one of the fields that reflects on the view as primary key. And then, when I tryed again to add the view to the edmx nothing happens again.

How can I solve this?? Is there an option to fix this without doing anything to the table? Can I add another view that will somehow wrap the old view but with fixed properties?

Inadvertency

EF is for "Enterprisey Fail". This problem still affects EF6.1 - the #6002 warnings are bad, but #6013 "has been excluded" errors and not even generating the a view of the database model is fail. Rubbish. – Ofris

Answer 1

Each table or view added to entity model must have some key. It actually doesn't have to be primary key. If the table doesn't have the primary key defined EF will try to infer a key using simple rule: It will take all non-nullable non-computed non-binary columns and marks them as an entity key. If none such column exist the entity cannot be automatically added and the designer will throw the mentioned warning. Workaround is adding the view manually and selecting the key yourselves but once you do it you cannot use Update from database because it will always overwrite your changes.

Your defined key should be unique otherwise you can have some other problems related to identity map used internally.

Basilio

I marked one of the table's field as primary key but the EF still doesn't load the view and say the same problem. View doesn't have primary keys but tables has. How does the EF know which field is primary key of a view? – Inadvertency

Also I had the same problem. I changed the Table marking a column as primary key, Updated Model from Database, but nothing happened. So I deleted and recreated the View in the Database. Doing so, EF has been able to import the View, but now there is this error: '<!--Errors Found During Generation: warning 6002: The table/view 'ControlloSchedeLocal.dbo.VProdotti' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view. -->' – Jemie

Answer 2

Just add a column to your view I added a Row_Number to create a key like this

SELECT ISNULL(CAST((row_number() OVER (ORDER BY tab.ENTRYDATE)) AS int), 0) 
AS EDMXID,...other columns go on

the tab expression is table alias and the entrydate is just a field needed for row_number built in sql-server func.

you may choose diffrent ways, e.g.

select newid() as MYEDMXID,....so on

Hope Helps

Leucoma

This way you have to add ORDER BY since results can be in different order and therefore the id will not be unique. – Inadvertency

Yes you are right, this ID's are not real IDs they are just virtual.You manage indexes and ordering by hand. They are not for real use.Just a hint.Entity model just look for real IDs but when you know indexes are ok.But you can't add them to edmx. – Northnortheast

My solution is just valid when you don't work with default generated domain service classs. If you manage with your custom filed these query and invoke ops. it helps.Otherwise as you said entities will be diffrent.And your last sentence is aslo ok as shown on my second example. wrapper view. – Northnortheast

Davut..I would love to get this working as I want to add a select newid() as myedmxid, but i get the error message "multi-part identifier "tab.ENTRYDATE" could not be found... any chance you could expand on your answer as I would love to get this working in edmx – Naked

@julianguppy ENTRYDATE is my tables field. Use your table's field. thats what I'm doing there just getting oracle's rowid like identifier. – Northnortheast

@julianguppy you may use the second approach also. – Northnortheast

@DavutGürbüz i have Select newid() as Id as my view, how do I get a primary key on this, i don't quite understand how I can use the cast((row_number().... I just want to be able to have in my entity framework select x from _ef.TeamUsers orderby _ef.RandomView() so that I can get the random order done by sql and then .Take(50) etc... so that I only marshal or retrieve 50 records rather than the entire lot... – Naked

@julianguppy the case is that you can't add a view without a suitable primary key column. Suppose that you have name and surname fields in your view. By this method you alter your view or create another wrapper view and select newid() ID,name ,surname from myoldview .BTW you can add your new view into Entity Framework and don't get this "cannot add view to the edmx" error again. They are not physical IDs . They are virtual. This is a workaround. For Row_Number have a look technet.microsoft.com/en-us/library/ms186734.aspx – Northnortheast

This is so horrendously bad that you need an ID for each view. Seems so foreign to me... Thanks for the help though, I had the row_number but Entity Framework was still playing hard ball until I added the ISNULL... +1. – Moist

CAST to INT is extra. – Arborescent

Answer 3

You can easily solve this problem by joining your view with any arbitrary table with a primary column. Just make sure that you only grab a single row from the table.

Here is an example:

CREATE VIEW dbo.myView
AS
SELECT
	-- This column enables EF-import via designer by enabling PK generation
	Id,
	-- These columns belong to the view
	[Count],
	[Sum]
FROM
(
SELECT
	COUNT(*) AS [Count]
	,SUM(1) AS [Sum]
FROM
	dbo.myTable
) TheViewItself
-- Grab a primary key of a single row from atable
INNER JOIN (SELECT TOP 1 Id FROM dbo.TableWithPrimaryKey) Id ON 1 = 1

"ON 1 = 1" join predicate looks odd. But I needed this to convince EF to import the view.

Pronounce

As strange as it is, if you don't care that you have an actual id, this does work indeed. Thx – Veliger

Answer 4

You can set one of your view columns as not Nullable by using "ISNULL" function as following

ALTER VIEW [dbo].[MyView] 
AS
  SELECT 
   ISNULL([StringID],'') AS [Id],
   [Name]
 FROM [Table]

GO

Antilog

This is what worked for me; use ISNULLs to cue SQL Server (and EF) that a view column will never be null, and EF will infer it. That still only gets you read-only behavior (which for most views is fine) but you can map to an update SP. – Melatonin

Answer 5

Use a new table only for link with your views, if your have more then 100k rows, EF6 not its better solution ;)

CREATE TABLE dbo.TablePrimate(Id int CONSTRAINT PK_TablePrimate PRIMARY KEY (Id))
go
set nocount on;
DECLARE @i int;
set @i=1
WHILE @i<10000
BEGIN
    INSERT dbo.TablePrimate(Id) values(@i)
    SET @i = @i + 1
END
--In fews seconds & 1 MB of storage
GO

Now joins with "MyView"

CREATE VIEW dbo.vwTickets
AS
SELECT TP.Id, MyPKView.* FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Ticket) Line, MyView.*
    FROM (
        select Grupo, App, Ticket, Titulo, FApertura, Estado, Tipo from dbo.vwEvolutivos 
        union 
        select Grupo, App, Ticket, Titulo, FApertura, Estado, Tipo from dbo.vwIncidencias
    ) MyView
) MyPKView
    JOIN dbo.TablePrimate TP ON TP.Id = Line

Variation

Answer 6

For those using Oracle, try to add a Pk constraint to a view

Example

alter view myView add constraint CONSTRAINT_NAME primary key (column) disable novalidate

Zena

I tried to add this kind of a constraint I made on a view in Oracle and it did not work. View does not show up in Port - cannot be placed in the diagram either. I am using recent version of Oracle and Visual Studio 2022 and Entity Framework 6. Is reporting this error: Errors Found: warning 6047: The Entity Framework provider in use reported the primary key column ID on table VW_CONTACTINFO to be nullable. Entity Framework does not allow nullable primary keys, therefore it will assume the column is not nullable and any null values stored in this column may cause errors at runtime. – Wilmerwilmette

Try to assign an new column on your view and use it as primary key, like an level or rank to create an pseudo Pk, then use your VW_CONTACTINFO to filter values. It will not work if you want to use an updatable or insertable view – Zena

Answer 7

You can create a view in your database and make queries like this in your code:

List<users> _users = _context.users.SqlQuery("SELECT * FROM users_v").ToList<users>();

I tried to add PostgreSQl views for a long time but without success.

Varhol