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.شناسه

Sandusky

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. – Chitter

The view that i can't add it will query from another view. – Sandusky

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). – Chitter

another thing: I have used CTE in my view – Sandusky

CTE should not be a problem, I used it already and it worked. Can you show SQL of the view? – Chitter

sorry it containt some my language words. :) :D – Sandusky

PK_ means that the field is Primary Key and FK_ means that the field is Foreign Key – Sandusky

Answer 1

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.

Cartouche

You may find that making one of the view columns pass EF's key eligibility check might be a preferred solution. See here – Morrissette

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. – Ophthalmoscopy

Answer 2

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

Hermia

Also key must be non-nullable. Compiler edmx kept failing on update till I deleted and re-imported view. – Pegg

If view doesn't have a key then fake one: SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY MyColumn), 0) AS UniqueId ... – Pegg

can you explain why this is necessary for EF to generate a model? I'm interested to know why it works this way. – Hooten

Answer 3

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.

Shoshone

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. – Imprescriptible

So combined, we get this: ISNULL (CAST(ROW_NUMBER() OVER (ORDER BY reportno ASC) AS INT),0)AS Id – Odometer

Answer 4

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)

Oecd

this was it for me, thanks aximili saved my canadian back bacon – Eggnog

Answer 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.

Britneybritni

Answer 6

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" />

Gynecic

Answer 7

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]

Impiety

Answer 8

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

Federalize

Answer 9

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)

Dumah

Answer 10

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.

Cornered

Answer 11

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.

Richelieu

Answer 12

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

...fields, 1 as id

Hawn

Answer 13

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.

Scleritis

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. – Highboy

Yes but if it helps someone else save some hair, I'll take the downvote hit. – Scleritis

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. – Highboy

Answer 14

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

Nutgall

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 – Nutgall

Can you just edit your answer to include everything? – Shrovetide