How to read from a table without a primary key in Entity Framework Core 2.0?

entity-framework entity-framework-core primary-key sql-server


I am having a problem reading from a table in my SQL Server without a primary key. I have defined an entity class such as:

public class PerfData 
    public Int64 ActivityId { get; set; }
    public Int64 Numbers{ get; set; }

And the DbContext class, e.g.

class MyDBContext
    public DbSet<PerfData> {get; set;}

The entity type records the numbers for ActivityId each person performed, so there is no primary key defined on the SQL Server table. However, when I do retrieve data with the following code, EF Core complains that the entity type PerfData requires a primary key to be defined:

dBContext.AdPerfData.FromSql(@"select [ActivityId], [Numbers]
                               from PerfTable").AsNoTracking().ToList();

How would one work around this limitation? The table contains data for reading, I don't ever need to do insert, update or delete from my code.


I added the [Key] Annotation on the ActivityId property, and that made EF Core happy and allow my query to go through. I did not need to add Primary Key attribute on the table in the Sql Server, which would be wrong in terms of business logic anyway. However, I still think EF Core should support table without primary keys. It's just such common place. Now all tables need to have primary keys.

Update(2/15/2018): I researched more into the issue. So as @Ivan correctly pointed out, EF Core team is working on QueryType and it's available in EF Core 2.1, and you can get it now from myget feed. However, I am going to opt for Dapper Micro ORM instead of waiting for the release to get some quick action. Based on what I read, Dapper is fast and easier to use.

2/15/2018 8:00:04 PM

Popular Answer

Based on my research in last few days, EF Core's DBSet only works with table with Identity or Primary key. The whole EF Core is based on change tracking and almost always has CRUD in mind, so a key is mandatory. For ad-hoc or read only scenarios, DBSet is not suitable. EF Core team is working on DBQuery type that is supposed to work with table/views that does not have identity.

For read only scenario, Dapper micro ORM might be better fit, because it's fast and easy to use.

2/15/2018 8:05:00 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow