Can't Add A View to EF Data Model

Question

I have a view that I am trying to add to my ADO.NET Entity Data Model. Every time I try to Update From Database, and check the view, it refreshes everything else, but does not add the view.
I get no error message or output, so I have no idea what is wrong with the view. Other views are no problem. Am I missing something, is there a way to turn error messages on? Visual Studio 2008 sp1

Update: I found this link but the problem didn't solve with these solutions. MSDN Forum

Update: The view that i can't add it will query from another view.

Update: Help

WITH cte AS (SELECT     dbo.TBL_Gharardad.PK_Shenase, dbo.TBL_Gharardad.FK_NoeKhedmat AS NoeKhedmatId, 
                                                    dbo.TBL_NoeKhedmat.NoeKhedmat AS [نوع خدمت], dbo.TBL_Gharardad.OnvaneKhedmat AS [عنوان خدمت], 
                                                    dbo.TBL_Gharardad.MahaleEraeieKhedmat AS [محل ارائه خدمت], 
                                                    dbo.TBL_Gharardad.FK_NahveieTaieeneBarande AS NahveieTaeeneBararndeId, 
                                                    dbo.TBL_NahveieTaieeneBarande.NahveieTaieeneBarande AS [نحوه تعيين برنده], 
                                                    dbo.TBL_Gharardad.TarikheShorooeGharardad_Jalali AS [تاريخ شروع قرارداد], 
                                                    dbo.TBL_Gharardad.TarikhePayaneGharardad_Jalali AS [تاريخ پايان قرارداد], dbo.TBL_Gharardad.FK_VahedeArz AS VahedeArzId, 
                                                    dbo.TBL_VahedeArz.VahedeArz AS [واحد ارز], dbo.TBL_Gharardad.MablagheDariaftiKol AS [مبلغ دريافتي کل], 
                                                    dbo.TBL_Gharardad.MablaghePardakhtieKol AS [مبلغ پرداختي کل], dbo.TBL_Gharardad.SahmeKarfarma AS [درصد مشارکت کارفرما], 
                                                    100 - dbo.TBL_Gharardad.SahmeKarfarma AS [درصد مشارکت پيمانکار], dbo.TBL_Gharardad.TedadNirooyeMard AS [تعداد نيروي مرد], 
                                                    dbo.TBL_Gharardad.TedadNirooyeZan AS [تعداد نيروي زن], 
                                                    dbo.TBL_Gharardad.TedadNirooyeMard + dbo.TBL_Gharardad.TedadNirooyeZan AS [تعداد کل نيروها], 
                                                    dbo.TBL_Gharardad.FK_TarafeGharardad AS TarafeGharardadId, 
                                                    CASE TBL_TarafeGharardad.Hoghooghi WHEN 0 THEN ISNULL(TBL_TarafeGharardad.Naam, ' ') 
                                                    + ' ' + ISNULL(TBL_TarafeGharardad.NaameKhanevadegi, ' ') ELSE TBL_TarafeGharardad.NameSherkat END AS [طرف قرارداد], 
                                                    dbo.TBL_Gharardad.FK_VahedeVagozarKonande AS VahedeVagozarKonandeId, 
                                                    dbo.TBL_VahedeVagozarKonande.VahedeVagozarKonande AS [واحد واگذار کننده], dbo.TBL_Gharardad.ShomareGharardad AS [شماره قرارداد], 
                                                    dbo.TBL_Gharardad.TarikheGharardad_Jalali AS [تاريخ قرارداد], 
                                                    CASE VaziateGharardad WHEN 0 THEN N'لغو شده' WHEN 1 THEN N'ثبت اوليه' WHEN 2 THEN N'فسخ' WHEN 3 THEN N'ثبت نهايي ' WHEN 4 THEN
                                                     N' جاري ' WHEN 5 THEN N'تمام شده ' WHEN 6 THEN N' متمم ' END AS [وضعيت قرارداد], dbo.TBL_NoeMoamele.NoeMoamele AS [نوع معامله]
                             FROM          dbo.TBL_Gharardad INNER JOIN
                                                    dbo.TBL_NoeKhedmat ON dbo.TBL_Gharardad.FK_NoeKhedmat = dbo.TBL_NoeKhedmat.PK_Id INNER JOIN
                                                    dbo.TBL_NahveieTaieeneBarande ON 
                                                    dbo.TBL_Gharardad.FK_NahveieTaieeneBarande = dbo.TBL_NahveieTaieeneBarande.PK_Id INNER JOIN
                                                    dbo.TBL_VahedeArz ON dbo.TBL_Gharardad.FK_VahedeArz = dbo.TBL_VahedeArz.PK_Id INNER JOIN
                                                    dbo.TBL_TarafeGharardad ON dbo.TBL_Gharardad.FK_TarafeGharardad = dbo.TBL_TarafeGharardad.PK_Id INNER JOIN
                                                    dbo.TBL_VahedeVagozarKonande ON 
                                                    dbo.TBL_Gharardad.FK_VahedeVagozarKonande = dbo.TBL_VahedeVagozarKonande.PK_Id INNER JOIN
                                                    dbo.TBL_NoeMoamele ON dbo.TBL_Gharardad.FK_NoeMoamele = dbo.TBL_NoeMoamele.PK_Id)
    SELECT     v_Gharardad.شناسه, v_Gharardad.NoeKhedmatId, v_Gharardad.[نوع خدمت], v_Gharardad.[عنوان خدمت], v_Gharardad.[محل ارائه خدمت], 
                            v_Gharardad.NahveieTaeeneBararndeId, v_Gharardad.[نحوه تعيين برنده], v_Gharardad.[تاريخ شروع قرارداد], v_Gharardad.[تاريخ پايان قرارداد], 
                            v_Gharardad.VahedeArzId, v_Gharardad.[واحد ارز], v_Gharardad.[مبلغ دريافتي کل], v_Gharardad.[مبلغ پرداختي کل], v_Gharardad.[درصد مشارکت کارفرما], 
                            v_Gharardad.[درصد مشارکت پيمانکار], v_Gharardad.[تعداد نيروي مرد], v_Gharardad.[تعداد نيروي زن], v_Gharardad.[تعداد کل نيروها], 
                            v_Gharardad.TarafeGharardadId, v_Gharardad.[طرف قرارداد], v_Gharardad.VahedeVagozarKonandeId, v_Gharardad.[واحد واگذار کننده], 
                            v_Gharardad.[شماره قرارداد], v_Gharardad.[تاريخ قرارداد], v_Gharardad.[وضعيت قرارداد], v_Gharardad.[نوع معامله]
     FROM         dbo.TBL_Gharardad AS TBL_Gharardad_3 INNER JOIN
                            dbo.v_GharardadRecords AS v_Gharardad ON v_Gharardad.شناسه = TBL_Gharardad_3.PK_Shenase
     WHERE     (TBL_Gharardad_3.FK_GharardadeAsli IS NULL) AND (TBL_Gharardad_3.PK_Shenase NOT IN
                                (SELECT     FK_GharardadeAsli
                                   FROM         dbo.TBL_Gharardad AS TBL_Gharardad_2
                                   WHERE     (FK_GharardadeAsli IS NOT NULL)))
UNION
SELECT     sub.FK_GharardadeAsli AS شناسه, cte_2.NoeKhedmatId, cte_2.[نوع خدمت], cte_2.[عنوان خدمت], cte_2.[محل ارائه خدمت], cte_2.NahveieTaeeneBararndeId, 
                      cte_2.[نحوه تعيين برنده], cte_2.[تاريخ شروع قرارداد], cte_2.[تاريخ پايان قرارداد], cte_2.VahedeArzId, cte_2.[واحد ارز], cte_2.[مبلغ دريافتي کل], cte_2.[مبلغ پرداختي کل], 
                      cte_2.[درصد مشارکت کارفرما], cte_2.[درصد مشارکت پيمانکار], cte_2.[تعداد نيروي مرد], cte_2.[تعداد نيروي زن], cte_2.[تعداد کل نيروها], cte_2.TarafeGharardadId, 
                      cte_2.[طرف قرارداد], cte_2.VahedeVagozarKonandeId, cte_2.[واحد واگذار کننده], cte_2.[شماره قرارداد], cte_2.[تاريخ قرارداد], cte_2.[وضعيت قرارداد], 
                      cte_2.[نوع معامله]
FROM         dbo.v_GharardadRecords AS cte_2 INNER JOIN
                          (SELECT     FK_GharardadeAsli, MAX(PK_Shenase) AS PK_Shenase, MAX(TarikheSabt) AS TarikheSabt
                             FROM         dbo.TBL_Gharardad AS TBL_Gharardad_1
                             WHERE     (FK_GharardadeAsli IS NOT NULL)
                             GROUP BY FK_GharardadeAsli) AS sub ON sub.PK_Shenase = cte_2.شناسه

There should be some information message if any item is not added to the model. Is there any non-nullable column in your view? If not EF will not be able to add it. — Ladislav Mrnka, May 17 '11 at 10:30
But still EF must infer primary key. If EF doesn't infer primary key it will skip the database object. Open your EDMX as XML and check if the view is in the first part of XML (SSDL). — Ladislav Mrnka, May 17 '11 at 12:01
CTE should not be a problem, I used it already and it worked. Can you show SQL of the view? — Ladislav Mrnka, May 17 '11 at 12:25
PK_ means that the field is Primary Key and FK_ means that the field is Foreign Key — Saleh, May 17 '11 at 12:39

score 35 · Answer 1 · answered Jan 21 '13 at 10:05

35

If your view does not contain the primary key column,then it wont add to edmx file.

answered Jan 21 '13 at 10:05

Vinay

351
1
3
3

1

Also key must be non-nullable. Compiler edmx kept failing on update till I deleted and re-imported view. – Brent Feb 17 '16 at 16:14
4

If view doesn't have a key then fake one: SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY MyColumn), 0) AS UniqueId ... – Brent Feb 17 '16 at 19:09
can you explain why this is necessary for EF to generate a model? I'm interested to know why it works this way. – jtate Jul 06 '17 at 14:49

score 22 · Accepted Answer · answered May 17 '11 at 13:44

22

I have experienced this same behaviour when I try to add a view that doesn't select a primary key from another table. (Like Ladislav Mrnka has commented)

My strategy for solving this is to reduce the view to as simple as possible (1 column) and try and to get it added. Once you have it added to the model, slowly bring in more columns and refresh the model to make sure the view is still there. You can usually identify what section of the view is giving EDM problems.

answered May 17 '11 at 13:44

Aducci

26,101
8
63
67

1

You may find that making one of the view columns pass EF's key eligibility check might be a preferred solution. See [here](http://stackoverflow.com/a/2715299/1219280) – Veverke Aug 23 '15 at 14:37
I've found grouping to be a problem sometimes (seems a little random). To get around it I first create a view selecting the PK and the other fields I want. Then I bring it in to the edmx model. Once I got that, I got back to my view and add the grouping. If you aren't grouping on the PK and it's strictly a uid, I select the MAX(PK) so that the ID requirement is fulfilled. – gregnnylf94 May 17 '19 at 13:27

score 17 · Answer 3 · answered Oct 28 '15 at 03:39

17

Same issue here, what I did was to add the PrimaryKey on the View, using :

..... (SELECT      TOP (100) PERCENT ROW_NUMBER() OVER (ORDER BY R.Road DESC) AS RoadNumber......

And then I had to do a CAST of this :

ISNULL (CAST(RoadNumber AS INT),0)AS RoadNumber

The column must be (not null), that's why the CAST at the end.

answered Oct 28 '15 at 03:39

Ruben D. Lopez

379
2
9

Your answer helped me solve the issue. Basically all the fields in my view were showing as nullable so I just used your ISNULL suggestion so the Entity Framework would see some not nullable columns and choose those for the key. After that I was able to add the view no problem. – Yves A Martin Jan 21 '16 at 01:35
So combined, we get this: ISNULL (CAST(ROW_NUMBER() OVER (ORDER BY reportno ASC) AS INT),0)AS Id – rothschild86 Apr 13 '17 at 20:00

score 6 · Answer 4 · edited May 23 '17 at 12:25

6

In my case it was because of an OUTER JOIN.
That causes the columns to be nullable and can't be imported by EF.

When I changed it to INNER JOIN it worked.

Another way is to use ISNULL (see the answer to this post)

edited May 23 '17 at 12:25

Community

1
1

answered Nov 19 '12 at 06:20

Aximili

28,626
56
157
216

1

this was it for me, thanks aximili saved my canadian back bacon – J King Sep 15 '14 at 02:49

score 5 · Answer 5 · answered Jan 28 '16 at 12:03

5

To be able to add a view into a model, Entity Framework needs at least one column in database view to be not nullable.

answered Jan 28 '16 at 12:03

Tomas Chabada

2,869
1
17
18

score 1 · Answer 6 · answered May 15 '15 at 08:50

1

Check if your View is for sure View in *.edmx file.

Correct:

<EntitySet Name="SomeView" EntityType="Model.SomeView" store:Type="Views" Schema="dbo" />

Wrong:

<EntitySet Name="SomeView" EntityType="Model.SomeView" store:Type="Tables" Schema="dbo" />

answered May 15 '15 at 08:50

Mateusz Rostański

11
1

score 1 · Answer 7 · answered Jun 07 '18 at 11:10

You can add a rowcount to the view and make it look like a key "Id" field that is not null. An example:-

SELECT DISTINCT
     -- dumb key for EF in C#
     IsNull(cast(ROW_NUMBER() OVER(PARTITION BY [Notes] ORDER BY [Notes] ASC) as int), 0) as [Id]

     -- required fields
    ,[Notes]        as [Notes]
    ,Count([Notes]) as [NoteCount]
FROM
    [dbo].[Communication]
GROUP BY
    [Notes]

score 0 · Answer 8 · answered Oct 24 '14 at 19:59

0

You add the field than primary key in your view, entity is bad boy with it

answered Oct 24 '14 at 19:59

Héctor de León

11
1

score 0 · Answer 9 · edited Mar 27 '20 at 18:10

0

I was trying to load a view with a single nullable column. My solution for this was to wrap the nullable column in isnull and provide a default of zero:

isnull([column], 0)

edited Mar 27 '20 at 18:10

David Buck

3,752
35
31
35

answered Mar 27 '20 at 17:35

mrkstrgn

1

score 0 · Answer 10 · answered Apr 08 '20 at 18:19

This might be a combination of answers on this topic but all that I've read doesn't seem to mention this particular fix. My view joins three tables and I was getting errors as mentioned. I had SELECT'd the PK from two of the tables but not the third. After including the PK from the third table, entity framework was satisfied.

score 0 · Answer 11 · edited Apr 08 '21 at 12:33

0

Add id column in your view:

isnull(ROW_NUMBER() OVER (PARTITION BY e.CCT ORDER BY e.CCT), 0) AS Id

After that, you can add your view in the Entity Model.

edited Apr 08 '21 at 12:33

Robson

813
5
21
40

answered Apr 07 '21 at 22:15

Susacande López Girón

1

score 0 · Answer 12 · answered Jun 12 '21 at 01:02

0

Add a dummy field with an alias "id", which is not null

...fields, 1 as id

answered Jun 12 '21 at 01:02

Onur Bassist

1
1

score -1 · Answer 13 · answered Apr 26 '21 at 05:01

-1

Just had this and nothing else worked. I've had it a few times and the primary key ID trick did it. Not this time. In desperation I rewrote them as stored procs and STILL they didn't come up!

The cause ended up being onedrive synching the sln folder between two machines. I deleted the .vs, bin and obj folders and recompiled, and it finally worked.

answered Apr 26 '21 at 05:01

SteveCav

6,649
1
50
52

But that's not something that *specifically* causes and solves this problem. This may cause so many problems you can add it as an answer almost anywhere. – Gert Arnold Apr 26 '21 at 07:44
Yes but if it helps someone else save some hair, I'll take the downvote hit. – SteveCav Jun 08 '21 at 03:42
Oh come on, there's a 1-in-a-billion chance that someone has the same error by the same cause. That means that for practically *everyone* this is just noise, yet another answer to read in vain. This question already has enough noise. – Gert Arnold Jun 08 '21 at 06:38

score -2 · Answer 14 · answered Aug 31 '17 at 18:15

-2

Create a supporting table with a primary key field and insert only one record.
create view with with joining to the created column(key) of that table.
Now you are able to add the view to the Entity Model.

answered Aug 31 '17 at 18:15

user8544837

1

GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[CUSTOM_DUAL]( [ID] [int] NOT NULL, CONSTRAINT [PK_TBL_CUSTOM_DUAL] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO – user8544837 Aug 31 '17 at 18:16
Can you just edit your answer to include everything? – Giulio Caccin Aug 31 '17 at 18:39

Can't Add A View to EF Data Model

14 Answers14