0

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.

Update(2/11/2018):

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.

Samuel An
  • 11
  • 3
  • could you provide the ef version? i have noticed a big difference in the behavior or different versions – Neville Nazerane Feb 11 '18 at 06:46
  • 1
    Tell EF what the key is... – ErikEJ Feb 11 '18 at 07:11
  • i don't think his db has a pk – Neville Nazerane Feb 11 '18 at 07:26
  • 2
    *If it doesn't have a primary key, it's not a table* - how to work around this? By **defining** a primary key for your table! Every proper table **ought to have one** anyway. – marc_s Feb 11 '18 at 07:35
  • As far as i know, EF won't accept entity `DbSet` if it doesn't have a primary key. Solution would be defining primary key on your table or removing `DbSet` property from context class (which i am not sure would work). – Michał Turczyn Feb 11 '18 at 08:24
  • If you are adamant you don't want a primary key (you *should* have one), treat the table like a view https://stackoverflow.com/questions/36012616/working-with-sql-views-in-entity-framework-core – Balah Feb 11 '18 at 10:27
  • `FromSql` indicates EF Core. You have to wait for [Query Types: Model types that do not require identity #9290](https://github.com/aspnet/EntityFrameworkCore/issues/9290) – Ivan Stoev Feb 11 '18 at 13:01
  • @NevilleNazerane, this is EF Core 2.0. – Samuel An Feb 11 '18 at 19:19

1 Answers1

0

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.

Samuel An
  • 11
  • 3