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?

score 255 · Accepted Answer · answered Apr 26 '10 at 17:00

255

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

answered Apr 26 '10 at 17:00

Tillito

7,718
7
34
31

2

I think this is the best to be hoped for. Bottom line it works. – MvcCmsJon Nov 13 '10 at 19:41
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? – sabanito Nov 09 '11 at 18:37
1

Thank You! It worked perfectly. @sabanito 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(). – Rabbi Dec 28 '11 at 20:27
3

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. – Bart Apr 11 '12 at 18:00
2

yes this works for instance to make EF use the column as a primary key isnull(CONVERT(VARCHAR(50), newid()),'') AS [PK] – dc2009 Jul 10 '12 at 15:27
2

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? – dyslexicanaboko Feb 21 '13 at 17:01
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. – Yann39 Jan 21 '14 at 16:06
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 – user1546077 Jan 21 '14 at 16:30
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). – MCattle Jan 22 '14 at 20:09
1

We've got this working now; you need to restart VS2013 for the error to go away though.. very annoying – Gordon Thompson Feb 20 '14 at 10:57
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. – Schiavini Mar 10 '14 at 09:14
@Tillito 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. – natenho Jun 03 '14 at 21:05
The other option is to define a unique primary key using FluentAPI - using fields in the object; ` builder.Entity() .ToTable("vwDailyTick") .HasKey("CurrencyPairId", "OpenTickId");` – James Joyce Sep 11 '17 at 03:19

score 68 · Answer 2 · answered Jan 18 '14 at 19:04

68

I was able to resolve this using the designer.

Open the Model Browser.
Find the view in the diagram.
Right click on the primary key, and make sure "Entity Key" is checked.
Multi-select all the non-primary keys. Use Ctrl or Shift keys.
In the Properties window (press F4 if needed to see it), change the "Entity Key" drop-down to False.
Save changes.
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.

answered Jan 18 '14 at 19:04

Casey Plummer

2,629
23
20

9

Confirmed. Have to restart VS2013 to make the warning go away. – Michael Logutov Jan 22 '14 at 08:10
5

"Have you tried turning it off and on again?" ;-) Thanks, works like a charm! – Obl Tobl Aug 06 '14 at 07:10
5

When I'm creating views, they don't even get to be in the model diagram. They're commented in the xml file – Luis Deras Sep 27 '16 at 21:02
Simple and easy solution and doesnt seem as much of a nono hacky fix as manipulating the view! Thank you. – LuqJensen Nov 15 '16 at 20:14
2

Confirmed VS2017 needs to be restarted also for the warning to go away. – Marc Levesque Jun 12 '18 at 15:39
It worked for me but I combined with ISNULL(MyPrimaryID,-1) as MyPrimaryID. – Ishwor Khanal Jul 09 '18 at 09:45
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 – Vland Feb 14 '22 at 14:34

Val Bakhtin · Answer 3 · 2012-05-10T17:57:53.907

46

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

edited May 10 '12 at 17:57

answered May 04 '12 at 19:24

Val Bakhtin

1,434
9
11

2

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. – zimdanen May 04 '12 at 19:32
6

The guy has a rep of 1, he can't add a comment, yet. – jrcs3 May 05 '12 at 02:16
@zimdanen There is no way you could fit all this information into a comment, it makes more sense to have it in a separate answer. – Contango Mar 25 '14 at 15:35
2

@Contango: This answer was edited six days after it was posted and I posted my comment. See the revision history. – zimdanen Mar 25 '14 at 16:17

score 10 · Answer 4 · answered Jan 29 '13 at 23:07

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

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. — ruffin, Mar 22 '16 at 21:55

score 4 · Answer 5 · answered Oct 07 '09 at 18:54

4

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.

answered Oct 07 '09 at 18:54

Annagram

700
7
16

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). – David Oliván Feb 22 '15 at 08:45

score 3 · Answer 6 · answered Jun 18 '09 at 15:55

3

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

answered Jun 18 '09 at 15:55

RBarryYoung

55,398
14
96
137

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? – Sergio Romero Jun 18 '09 at 18:31
1

Sorry, I am already beyond my level of expertise in Entity Framework. :-) – RBarryYoung Jun 18 '09 at 20:07
1

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. – live-love Aug 11 '11 at 15:45

score 3 · Answer 7 · edited Sep 04 '19 at 06:15

3

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

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

edited Sep 04 '19 at 06:15

MarredCheese

17,541
8
92
91

answered Feb 25 '14 at 13:22

Santhos

3,348
5
30
48

score 3 · Answer 8 · answered Feb 22 '10 at 23:02

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.

score 1 · Answer 9 · edited Aug 03 '14 at 20:14

1

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.

edited Aug 03 '14 at 20:14

daniula

6,898
4
32
49

answered Aug 03 '14 at 20:05

Ray

11
1

2

How would you create associations among entities going with this approach? Is it possible? – Luis Deras Sep 19 '16 at 21:17

Entity Framework and SQL Server View

9 Answers9

Linked

Related