Using a view with no primary key with Entity

Question

I just started on a project converting an application from raw ADO.NET and embedded SQL to Entity. I ran in to a problem with one of the views used by the application. The view has no primary key and no column (or combination of columns) that uniquely identifies a row. Here is the select the view is created with:

SELECT
    filingmonth,
    CEIL(filingmonth / 3),
    licnum,
    filingyear,
    DECODE(GROUPING(insurername), '1', '- All Insured -', insurername),
    insurername,
    policylinecode,
    linedescription,
    SUM(NVL(grosspremium, 0)),
    SUM(DECODE(taxexempt, 1, grosspremium, 0)),
    TRUNC(
      CASE
        WHEN
          (
            b.rsn IS NOT NULL
            OR A.zeroreport = 1
          )
          AND b.datereceived IS NULL
            THEN A.datereceived
        ELSE b.datereceived
      END),
    SUM(aip.iscompanyadmitted(b.naiccocode, b.naicalienid)),
    A.insuredid
  FROM
    aip.slbtransinsured A
  LEFT OUTER JOIN aip.slbtransinsurer b
  ON
    A.insuredid = b.insuredid
  LEFT OUTER JOIN aip.slblinecodes C
  ON
    b.policylinecode = C.linecode
  WHERE
    A.submitted = 1
  AND A.entryincomplete = 0
  GROUP BY
    licnum,
    filingmonth,
    filingyear,
    TRUNC(
      CASE
        WHEN
          (
            b.rsn IS NOT NULL
            OR A.zeroreport = 1
          )
          AND b.datereceived IS NULL
            THEN A.datereceived
        ELSE b.datereceived
      END),
    ROLLUP(insurername, aip.iscompanyadmitted(b.naiccocode, b.naicalienid),
    policylinecode, linedescription), A.insuredid;

And here is some sample data showing that there are some rows that are completely duplicated (rows 3 and 4):

FILINGMONTH CEIL(FILINGMONTH/3) LICNUM FILINGYEAR DECODE(GROUPING(INSURERNAME),'1','-ALLINSURED-',INSURERNAME)                                         INSURERNAME                                                                                          POLICYLINECODE LINEDESCRIPTION                                                                                                                                                                                          SUM(NVL(GROSSPREMIUM,0)) SUM(DECODE(TAXEXEMPT,1,GROSSPREMIUM,0)) TRUNC(CASEWHEN(B.RSNISNOTNULLORA.ZEROREPORT=1)ANDB.DATERECEIVEDISNULLTHENA.DATERECEIVEDELSEB.DATERECEIVEDEND) SUM(AIP.ISCOMPANYADMITTED(B.NAICCOCODE,B.NAICALIENID)) INSUREDID

      6                   2   8150       2007 SAVERS PROPERTY AND CASUALTY INSURANCE CO                                                            SAVERS PROPERTY AND CASUALTY INSURANCE CO                                                            17             OTHER LIABILITY                                                                                                                                                                                                            721.25                                       0 18-JUL-07                                                                                                                                                          0        81 
      6                   2   8150       2007 SAVERS PROPERTY AND CASUALTY INSURANCE CO                                                            SAVERS PROPERTY AND CASUALTY INSURANCE CO                                                            17                                                                                                                                                                                                                                        721.25                                       0 18-JUL-07                                                                                                                                                          0        81 
      6                   2   8150       2007 SAVERS PROPERTY AND CASUALTY INSURANCE CO                                                            SAVERS PROPERTY AND CASUALTY INSURANCE CO                                                                                                                                                                                                                                                                                                      721.25                                       0 18-JUL-07                                                                                                                                                          0        81 
      6                   2   8150       2007 SAVERS PROPERTY AND CASUALTY INSURANCE CO                                                            SAVERS PROPERTY AND CASUALTY INSURANCE CO                                                                                                                                                                                                                                                                                                      721.25                                       0 18-JUL-07                                                                                                                                                          0        81 

insuredid is the pk for the aip.slbtransinsured table, rsn is the pk for aip.slbtransinsurer and aip.slblinecodes.

Is it at all possible to add a view to the Entity model without a unique identifier? Or is there an easy way to add a unique row identifier to the view? The view is only read from, never written to.

Penetralia

Answer 1

Is it at all possible to add a view to the Entity model without a unique identifier?

If without a primary key, no. That will result to this kind of error:

One or more validation errors were detected during model generation:

System.Data.Edm.EdmEntityType: : EntityType 'SalesOnEachCountry' has no key defined. Define the key for this EntityType. System.Data.Edm.EdmEntitySet: EntityType: The EntitySet SalesOnEachCountryList is based on type SalesOnEachCountry that has no keys defined.

If without a unique identifier, yes, albeit it has a non-desirable output. Records with same identifier would reference the same object, this is called Identity Map Pattern

An example, even if your view produces these two rows:

Country     Year TotalSales
Philippines 2010 20.000000
Philippines 2011 40.000000

If you will just map the primary key on Country field only, e.g.

public class SalesOnEachCountry
{        
    [Key]
    public int CountryId { get; set; }
    public string CountryName { get; set; }        
    public int OrYear { get; set; }
    public long SalesCount { get; set; }
    public decimal TotalSales { get; set; }
}

, even your view produces the above two rows on your Oracle query editor, Entity Framework produces this incorrect output:

Country     Year TotalSales
Philippines 2010 20.000000
Philippines 2010 20.000000

Entity Framework will take it that the second row is same object as first row.

To guarantee uniqueness, you must identify what columns that makes each row unique. In the above example, Year must be included so the primary key is unique. i.e.

public class SalesOnEachCountry
{        
    [Key, Column(Order=0)] public int CountryId { get; set; }
    public string CountryName { get; set; }
    [Key, Column(Order=1)] public int OrYear { get; set; }

    public long SalesCount { get; set; }      
    public decimal TotalSales { get; set; }
}

Making your primary key similar to the attributes above, Entity Framework can correctly map your each view's row to their own objects. Hence, Entity Framework can now display exactly the same rows your view have.

Country     Year TotalSales
Philippines 2010 20.000000
Philippines 2011 40.000000

Full details here: http://www.ienablemuch.com/2011/06/mapping-class-to-database-view-with.html

---

Then regarding your views which don't have any columns to make a row unique, the easiest way to guarantee Entity Framework can map each of your view's row to their own objects is to create a separate column for your view's primary key, a good candidate is to just create a row number column on each row. e.g.

create view RowNumberedView as

select 
    row_number() over(order by <columns of your view sorting>) as RN
    , *
from your_existing_view

Then assign the [Key] attribute on RN property of your class RowNumberedView

Missionary

Thanks for this - you really helped me out. I'm working with Oracle tables that have no visible unique ID column and was beginning to despair because LINQ was returning the same row. I followed your suggestion about creating my own key in the Entity class and it works perfectly. – Node

I think adding a guid to the projection is more efficient. In views with binary data fields I have found that over(order by <column>) slow the query. Is there any problem with this approach? For example: SELECT NEWID() AS PrimaryKey, <other_columns> FROM View – Lisettelisha

There is, however, a way to get a view added as a "read only entity" sans primary key if you use database-first Entity Framework. You even get a neat error when you add a view without an obvious key that says EF is forcing your entity to be a keyless read-only entity. Surely there's a way to duplicate this yourself? – Burney

Won't you run into a similar identity mapping problem if you specify a non-unique column as the row_number() function's ORDER BY parameter? Because the unique RN it generates may change each time you query the view, so it references a different row each time. So suppose you show an index page, then select one - the row it pulls from the database (assuming it accesses the database again and not the local store) might not necessarily be the same row you selected. – Mcmillon

Using row_number() will often prevent efficient use of predicates and may require the whole view to be scanned by the engine Alternative answer. – Gavel

make the alias of your ROW_NUMBER() column ROW_ID, see more medium.com/@243/… – Abradant

Answer 2

Expanding on the answer from Michael Buen: I found that adding the row number to the view with an ISNULL() will allow the entity framework to pull the view in and create the necessary EntitySet data automatically.

create view RowNumberedView as

select 
    ISNULL(ROW_NUMBER() OVER (ORDER BY <column>), 0) AS RN
    , *
from your_existing_view

Cyst

This no work for me, I already put a row_number but I am still without retrieve the view in my EntitySet – Prana

Using row_number() will often prevent efficient use of predicates and may require the whole view to be scanned by the engine Alternative answer. – Gavel

This answer, extended -- use ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) instead: https://mcmap.net/q/210825/-row_number-without-order-by This post + the linked post solved my EF6 View with duplicating primary keys – Restrainer

Answer 3

At work recently, i ran into this same issue. Based on my research, I could not find any answers on how to attach a view to EF6 CodeFirst without a PK. Most seem to involve migrations and were quite confusing. I believe DB first has better support for working SQL VIEWS.

I did try introducing a window function (RowNumber) with the idea being, to use the row identifier as the PK to keep EF6 Happy. But this made my query more expensive overall so i had to drop this idea.

In the end, I had to carefully analyse my data set to see if i could introduce a composite key - one that covers all of the scenarios my business application needed to ensure would work. Remember to use IsNull(ColumnName,0) too to ensure you can satisfy the .IsRequired() in CodeFirst fluent methods.

i.e

HasKey(x => new { x.KfiId, x.ApplicationNumber, x.CustomerId });

I hope this helps someone - the answer for me was analyse the dataset the view denormalises and look for a composite key.

Another cool idea you can try suggested by Marc Cals.

Ocotillo

Answer 4

In case you are using Entity Framework with MVC in ASP.NET

As previously said, create your view with column that has auto-increment or ROW_NUMBER. Let's say you have that column and it's name is rowNumber.

an than go to context file (yourDatabaseNameContext) file in your Models directory of your MVC application, find definition for your view and instead of

modelBuilder.Entity<yourView>(entity =>
    {
        entity.HasNoKey();

change it to:

 modelBuilder.Entity<yourView>(entity =>
            {
                entity.HasKey(e => e.rowNumber);

Pb

Answer 5

Is it at all possible to add a view to the Entity model without a unique identifier?

It's possible to have a views where there isn't a single column or set of columns that create the primary key; thus, you end up with spurious relationships. Data warehouse tables sometimes follow that form. In short, normalization is sometimes not followed for either performance reasons or reporting reasons.

Now to your second point:

Or is there an easy way to add a unique row identifier to the view?

What i suggest you do is to select all the columns from slbtransinsured and see if you can find the one column that uniquely identifies each record. It seems to me that the data is there should be a code type in slblinecodes that you need to select, somewhat like a lookup.

For kicks, try running this and tell me what you get:

SELECT filingmonth,
       CEIL (filingmonth / 3),
       licnum,
       filingyear,
       DECODE (GROUPING (insurername), '1', '- All Insured -', insurername),
       insurername,
       policylinecode,
       linedescription,
       SUM (NVL (grosspremium, 0)),
       SUM (DECODE (taxexempt, 1, grosspremium, 0)),
       TRUNC (
           CASE
               WHEN (b.rsn IS NOT NULL OR a.zeroreport = 1)
                    AND b.datereceived IS NULL
               THEN
                   a.datereceived
               ELSE
                   b.datereceived
           END),
       SUM (aip.iscompanyadmitted (b.naiccocode, b.naicalienid)),
       a.insuredid
  FROM aip.slbtransinsured a
       LEFT OUTER JOIN aip.slbtransinsurer b
           ON a.insuredid = b.insuredid
       LEFT OUTER JOIN aip.slblinecodes c
           ON b.policylinecode = c.linecode
 WHERE a.submitted = 1 AND a.entryincomplete = 0
GROUP BY filingmonth,
         licnum,
         filingyear,
         DECODE (GROUPING (insurername), '1', '- All Insured -', insurername),
         insurername,
         policylinecode,
         linedescription,
         TRUNC (
             CASE
                 WHEN (b.rsn IS NOT NULL OR a.zeroreport = 1)
                      AND b.datereceived IS NULL
                 THEN
                     a.datereceived
                 ELSE
                     b.datereceived
             END),
         a.insuredid;

Abuse

Thanks, it doesn't like the GROUPING function in the GROUP BY clause though. The only unique column in slbtransinsured is its primary key, insuredid, which is duplicated in the view. There is no relationship between slbtransinsured and slblinecodes, only between slbtransinsurer and slblinecodes. – Penetralia

Answer 6

Both NEWID() and ROW_NUMBER() OVER(...) will generate unique values as mentioned in the other answers, but they will appear as nullable values in the view results (even though we know the values will never be null). This prevents them from being selected as keys in entity framework.

The fix is to wrap them in an `ISNULL()' as follows.

CREATE VIEW MyView
AS
SELECT
    ISNULL(NEWID(), 0x0) as RowId,
    ...
FROM ...

or

-- *** See performance note below ***
CREATE VIEW MyView
AS
SELECT
    ISNULL(ROW_NUMBER() OVER(ORDER BY (SELECT NULL)), 0) as RowNum,
    ...
FROM ...

The resulting RowId and RowNum columns will now be reported as non-nullable types in the view results. The assigned values are not repeatable, and can only be used for read-only use cases.

IMPORTANT NOTE: The ROW_NUMBER() approach may introduce undesirable performance issues. In order to calculate the result, it appears that SQL Server will retrieve and assign row number values to the entire view row set before applying any filters that may be defined by the referencing query. For example, the query SELECT * FROM MyView V WHERE V.ActivityDate >= @StartDate AND V.ActivityDate < @EndDate may cause data for all-time to be retrieved and assigned row numbers before the date range filter is applied. This is not an issue when NEWID() is used.

See this db<>fiddle showing the various forms.

Fritillary

Answer 7

Consider using AsNoTracking() when consuming the view. This disables the usage of any key fields for EF tracking. Then any non-null field can be defined manually as the EF key (even if it repeats).

It is advised not to create an additional row counter field because most row counters end up requiring that the engine scan the entire domain of the view to generate the proper counter values even if querying using a predicate (where clause).

Please see link.

Gavel