Entity Framework and SQL Server View

Question

For several reasons that I don't have the liberty to talk about, we are defining a view on our Sql Server 2005 database like so:

CREATE VIEW [dbo].[MeterProvingStatisticsPoint]
AS
SELECT
    CAST(0 AS BIGINT) AS 'RowNumber',
    CAST(0 AS BIGINT) AS 'ProverTicketId',
    CAST(0 AS INT) AS 'ReportNumber',
    GETDATE() AS 'CompletedDateTime',
    CAST(1.1 AS float) AS 'MeterFactor',
    CAST(1.1 AS float) AS 'Density',
    CAST(1.1 AS float) AS 'FlowRate',
    CAST(1.1 AS float) AS 'Average',
    CAST(1.1 AS float) AS 'StandardDeviation',
    CAST(1.1 AS float) AS 'MeanPlus2XStandardDeviation',
    CAST(1.1 AS float) AS 'MeanMinus2XStandardDeviation'
WHERE 0 = 1

The idea is that the Entity Framework will create an entity based on this query, which it does, but it generates it with an error that states the following:

Warning 6002: The table/view 'Keystone_Local.dbo.MeterProvingStatisticsPoint' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view.

And it decides that the CompletedDateTime field will be this entity primary key.

We are using EdmGen to generate the model. Is there a way not to have the entity framework include any field of this view as a primary key?

Scherzando

Answer 1

We had the same problem and this is the solution:

To force entity framework to use a column as a primary key, use ISNULL.

To force entity framework not to use a column as a primary key, use NULLIF.

An easy way to apply this is to wrap the select statement of your view in another select.

Example:

SELECT
  ISNULL(MyPrimaryID,-999) MyPrimaryID,
  NULLIF(AnotherProperty,'') AnotherProperty
  FROM ( ... ) AS temp

Armipotent

I think this is the best to be hoped for. Bottom line it works. – Petrapetracca

I've tried this and it doesn't work. Does EF designer parses the view definition or just infers the columns from the data results? – Interruption

Thank You! It worked perfectly. @Interruption I think it parses the definition. that is why you need to specifically wrap the key properties in IsNull(). I have a view that does not return any nulls (and can not return any nulls) but because of the way the logic was written, EF could not determine that that was the case until I wrapped the keys in IsNull(). – Stylolite

The only issue i see here is that view might legitimately need to return an empty string ''. What I did, was simply cast the column back to its own data type. for example if AnotherProperty had a datatype of varchar(50) I would cast it as such 'CONVERT(VARCHAR(50), AnotherProperty) AS [AnotherProperty]'. this masked the nullability from EF and also allowed empty strings. – Dynamic

yes this works for instance to make EF use the column as a primary key isnull(CONVERT(VARCHAR(50), newid()),'') AS [PK] – Rueful

Aside from there just being an annoying message in the solution, is there any harm in not fixing this? I agree with your solution, but frankly I don't feel that I should have to do this - I think we can all agree this is a bug right? – Laniferous

Is this still relevant ? I still get the warning after updating the Entity model, even using ISNULL in my views. Visual Studio 2012, .NET Framework 4.5, MSSQL Server 2012. – Secretive

It doesn't work for me either, i.e I still get the error, with EF 6, VS 2013, .NET 4.5, SQL Server 2012 – Nisse

I suspect that the above solution will still produce the warning, but it may be a valid workaround for EF being too greedy and including a number of non-key columns as the Primary Key, which is the behaviour we're seeing (and having to correct manually). – Ilailaire

We've got this working now; you need to restart VS2013 for the error to go away though.. very annoying – Pentobarbital

Indeed, I've been searching for a solution for hours, and in the end I just had to restart VS2012. I'll edit the answer to include this. – Calcariferous

@Armipotent Why not to set all the view entity fields properties to "Entity Key = True" in the edmx designer instead of adding ISNULL/NULLIF to SQL code? It looks like a better performance workaround approach. I couldn't find drawbacks. – Sprage

The other option is to define a unique primary key using FluentAPI - using fields in the object; ` builder.Entity<DailyTick>() .ToTable("vwDailyTick") .HasKey("CurrencyPairId", "OpenTickId");` – Skylark

Answer 2

I was able to resolve this using the designer.

1. Open the Model Browser.
2. Find the view in the diagram.
3. Right click on the primary key, and make sure "Entity Key" is checked.
4. Multi-select all the non-primary keys. Use Ctrl or Shift keys.
5. In the Properties window (press F4 if needed to see it), change the "Entity Key" drop-down to False.
6. Save changes.
7. Close Visual Studio and re-open it. I am using Visual Studio 2013 with EF 6 and I had to do this to get the warnings to go away.

I did not have to change my view to use the ISNULL, NULLIF, or COALESCE workarounds. If you update your model from the database, the warnings will re-appear, but will go away if you close and re-open VS. The changes you made in the designer will be preserved and not affected by the refresh.

Crossfertilization

Confirmed. Have to restart VS2013 to make the warning go away. – Bronco

"Have you tried turning it off and on again?" ;-) Thanks, works like a charm! – Kvass

When I'm creating views, they don't even get to be in the model diagram. They're commented in the xml file – Revulsion

Simple and easy solution and doesnt seem as much of a nono hacky fix as manipulating the view! Thank you. – Hurd

Confirmed VS2017 needs to be restarted also for the warning to go away. – Nonattendance

It worked for me but I combined with ISNULL(MyPrimaryID,-1) as MyPrimaryID. – Dorkas

Sure, it works. The problem with this approach is that you will lose the custom PKs if you delete + add the entities again in the model. I suggest dealing with that in the DB like accepted answer – Expeller

In Visual Studio 2022 and using Entity Framework 6 (I am also using Oracle.ManagedDataAccess.EntityFramework) the table w/o Primary key does not show up under Portal, only Portal.Store. There is no way in this version to change "ENTITY KEY" in the Designer (without having to edit the file in an xml editor like Visual Studio Code. This fix does not work in this version. – Jerryjerrybuild

Answer 3

Agree with @Tillito, however in most cases it will foul SQL optimizer and it will not use right indexes.

It may be obvious for somebody, but I burned hours solving performance issues using Tillito solution. Lets say you have the table:

 Create table OrderDetail
    (  
       Id int primary key,
       CustomerId int references Customer(Id),
       Amount decimal default(0)
    );
 Create index ix_customer on OrderDetail(CustomerId);

and your view is something like this

 Create view CustomerView
    As
      Select 
          IsNull(CustomerId, -1) as CustomerId, -- forcing EF to use it as key
          Sum(Amount) as Amount
      From OrderDetail
      Group by CustomerId

Sql optimizer will not use index ix_customer and it will perform table scan on primary index, but if instead of:

Group by CustomerId

you use

Group by IsNull(CustomerId, -1)

it will make MS SQL (at least 2008) include right index into plan.

If

Antonia

This should be a comment on Tillito's answer, not an answer itself, as it does not provide a solution for the OP's question. – Ancheta

The guy has a rep of 1, he can't add a comment, yet. – Suellensuelo

@Ancheta There is no way you could fit all this information into a comment, it makes more sense to have it in a separate answer. – Abwatt

@Contango: This answer was edited six days after it was posted and I posted my comment. See the revision history. – Ancheta

Answer 4

This method works well for me. I use ISNULL() for the primary key field, and COALESCE() if the field should not be the primary key, but should also have a non-nullable value. This example yields ID field with a non-nullable primary key. The other fields are not keys, and have (None) as their Nullable attribute.

SELECT      
ISNULL(P.ID, - 1) AS ID,  
COALESCE (P.PurchaseAgent, U.[User Nickname]) AS PurchaseAgent,  
COALESCE (P.PurchaseAuthority, 0) AS PurchaseAuthority,  
COALESCE (P.AgencyCode, '') AS AgencyCode,  
COALESCE (P.UserID, U.ID) AS UserID,  
COALESCE (P.AssignPOs, 'false') AS AssignPOs,  
COALESCE (P.AuthString, '') AS AuthString,  
COALESCE (P.AssignVendors, 'false') AS AssignVendors 
FROM Users AS U  
INNER JOIN Users AS AU ON U.Login = AU.UserName  
LEFT OUTER JOIN PurchaseAgents AS P ON U.ID = P.UserID

if you really don't have a primary key, you can spoof one by using ROW_NUMBER to generate a pseudo-key that is ignored by your code. For example:

SELECT
ROW_NUMBER() OVER(ORDER BY A,B) AS Id,
A, B
FROM SOMETABLE

Sherylsheryle

Yeah, I ended up cheating with NEWID() as id, but it's the same idea. And there are legitimate use-cases -- if you've got a read-only view, for instance. Ugly, EF, ugly. – Michaelmichaela

Answer 5

The current Entity Framework EDM generator will create a composite key from all non-nullable fields in your view. In order to gain control over this, you will need to modify the view and underlying table columns setting the columns to nullable when you do not want them to be part of the primary key. The opposite is also true, as I encountered, the EDM generated key was causing data-duplication issues, so I had to define a nullable column as non-nullable to force the composite key in the EDM to include that column.

Accordant

We have the same problem with the inferred PK, the entity returns duplicated records and is completely annoying. If you execute Context.Entity.ToList() duplicates records, but if you execute the SQL Query generated by EF directly (obtained with LINQPad), no record duplication happens. Seems to be a problem mapping the database records to the entity objects (POCO) returned, as the PK is inferred using the explained logic (non nullable columns). – Offering

Answer 6

Looks like it is a known problem with EdmGen: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/12aaac4d-2be8-44f3-9448-d7c659585945/

Shroudlaid

That makes sense. So, is there a way to define a column as not null or null in a view the way we are defining it? – Scherzando

Sorry, I am already beyond my level of expertise in Entity Framework. :-) – Shroudlaid

Any one knows when this issue is gonna be fixed? Annoying to have to workaround this when you have non null columns that are not primary keys. – Capps

Answer 7

To get a view I had to only show one primary key column I created a second view that pointed to the first and used NULLIF to make the types nullable. This worked for me to make the EF think there was just a single primary key in the view.

Not sure if this will help you though since I don't believe the EF will accept an entity with NO primary key.

Khmer

Answer 8

If you do not want to mess with what should be the primary key, I recommend:

1. Incorporate ROW_NUMBER into your selection
2. Set it as primary key
3. Set all other columns/members as non-primary in the model

Wakefield

Answer 9

Due to the above mentioned problems, I prefer table value functions.

If you have this:

CREATE VIEW [dbo].[MyView] AS SELECT A, B FROM dbo.Something

create this:

CREATE FUNCTION MyFunction() RETURNS TABLE AS RETURN (SELECT * FROM [dbo].[MyView])

Then you simply import the function rather than the view.

Nourishing

How would you create associations among entities going with this approach? Is it possible? – Revulsion