Entity Framework - entity using a view giving duplicate data

Question

I have the following view (SQL Server 2012 if it matters):

SELECT
 EntityId
,EntityType
,StateId
FROM
 SomeTable 
 INNER JOIN SomeOtherTable

When I generate an entity for this view (EF 6 - database first) it looks like this in the EDMX file:

<EntityType Name="VW_MyView">
   <Key>
      <PropertyRef Name="EntityId" />
      <PropertyRef Name="EntityType" />
   </Key>
   <Property Name="EntityId" Type="Int32" Nullable="false" />
   <Property Name="EntityType" Type="String" Nullable="false" MaxLength="2" FixedLength="false" Unicode="false" />
   <Property Name="StateId" Type="Int32" />
</EntityType>

As you can see, the model generator created an entity key on the first two columns. The problem is, the first two columns do not guarantee uniqueness.

So for example I could have data like this in the view:

EntityId   EntityType   StateId
--------   ----------   -------
1234       CR           1
1234       CR           2
1234       CR           3

When I query the data using linq such as:

using (ContextA context = new ContextA())
{
    var zList = context.VW_MyView.Where(f => f.EntityId == 1234 
                                    && f.EntityType == "CR").ToList();
}

I get a list of three items, but like this (notice stateid duplicated):

EntityId   EntityType   StateId
--------   ----------   -------
1234       CR           1 <-- dupe
1234       CR           1 <-- dupe
1234       CR           1 <-- dupe

I migrated this exact same code from EF 4 (object context templates) to EF 6 (dbcontext templates), and before the migration it did not perform like this.

I know I can manually add an EntityKey to the StateId column, and it will work properly, but I have over 100 views in my model and I don't want to go through each one to check.

Why has this behavior changed, and is there a setting I can enable (globally) to correct this?

EDIT:

So based on the answers, I have been able to gather three ways to prevent this issue.

Add all primary key values from each consisting table into the view
Use nullif() tricks in the view to force columns to be non-nullable, and those be added by EF to the key
Manually add the Entity Key in the model myself

But this doesn't explain really why this happens, and how it could possibly be desired behavior? The EF linq query is simply returning entirely incorrect data, without any exceptions or warnings. I can't imagine this is correct.

Does running the same query in T-SQL provide the same 3 results? — mxmissile
– mxmissile, Commented Mar 17, 2014 at 22:36
Is the StateId ever null? The designer did not include StateId property as part of the key since it is marked as nullable. If it in fact is not nullable you could cheat in the edmx and mark it as non-nullable and include as a key. Also, if you include the StateId property - will it make the composite key unique always? — Pawel
– Pawel, Commented Mar 17, 2014 at 23:04
@mxmissile - running the query in SQL returns the correct results (with stateid 1, 2 and 3). — EkoostikMartin
– EkoostikMartin, Commented Mar 18, 2014 at 0:07
@Pawel - StateId is never null. I don't mind if it didn't mark it as a key, I just don't see how EF is correct to return 3 rows with the "wrong" data in this case. — EkoostikMartin
– EkoostikMartin, Commented Mar 18, 2014 at 0:08
How does the query created by EF looks like? What is the result when executing the query against SQL Server? Finally is the INNER JOIN in the view definition needed and how it affects the results? — Pawel
– Pawel, Commented Mar 18, 2014 at 0:59

marc_s · Accepted Answer · 2014-03-18 13:59:22Z

1

I have the same "issue" in EF4 (with an .edmx file using the ObjectContext database-first approach) - not sure why it worked for you.

For Entity Framework, if it doesn't have a specified primary key (like on a table), it will fall back to using all non-nullable columns of that object (here: your view) as its compound PK.

These non-nullable columns are now the key for the table/view, and thus, only one value of that key can exist.

In order to resolve this, you need to either include more columns in your view to make the auto-detected key really unique (by including e.g. the primary key of all underlying base tables), or you need to manually set the key properly to something that works for you.

answered Mar 18, 2014 at 13:59

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EkoostikMartin Over a year ago

So I assume there is no wholesale way to prevent this? I have to go to each entity/view and correct individually?

Aamol · Accepted Answer · 2017-05-25 04:48:04Z

0

Another solution I found is by setting entity's MergeOption to NoTracking.

 using (ContextA context = new ContextA())
{
  context.VW_MyView.MergeOption = System.Data.Objects.MergeOption.NoTracking;
  //Rest code goes here...
}

Solution found in this thread

answered May 25, 2017 at 4:48

Aamol

1,2091 gold badge16 silver badges24 bronze badges

Collectives™ on Stack Overflow

Entity Framework - entity using a view giving duplicate data

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related