Querying Views Without Primary Keys in Entity Framework

Entity Framework is a nice way of accessing databases without having to write any data access code. Because Entity Framework is able to modify data in your database it needs a primary key to be defined in order for it to be able to uniquely identify rows.

In my case I wanted to query a view containing the latest available product message details that didn’t have a primary key on the product SKU which was set as a nullable column and there was no way for me to change this.

However, as I knew that the product SKU was always going to be distinct the solution was to manually specify that this column was the key in the model context when the model was created.

[Table("v__product_messages")]
public class Message
{
	[Column("product_sku")]
	public string ProductSku { get; set; }

	[Column("image_url")]
	public string ImageUrl { get; set; }

	[Column("target_url")]
	public string TargetUrl { get; set; }

	[Column("hierarchy")]
	public Int16 Hierarchy { get; set; }

	[Column("start_date")]
	public DateTime StartDateTime { get; set; }

	[Column("end_date")]
	public DateTime EndDateTime { get; set; }
}

public class MessageContext : DbContext
{
	private readonly string _dataConnectionString;

	public DbSet<Message> Messages { get; set; }

	public MessageContext(string dataConnectionString)
	{
		_dataConnectionString = dataConnectionString;
	}

	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer(_dataConnectionString);
	}

	protected override void OnModelCreating(ModelBuilder modelBuilder)
	{
		modelBuilder.Entity<Message>().HasKey(x => new { x.ProductSku });
	}
}

It’s also possible to create the primary key as a composite key if there’s no single unique column or even to add a row ID to the view as it is being read in and to set that as the primary key, though in this case data transformation operations are unlikely to work.