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?

Answer 1

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

Answer 2

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.

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.

Answer 4

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

Answer 5

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

Answer 6

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.