I'm trying to use ASP.NET Core to read from a SQL database. I'm using Microsoft.EntityFrameworkCore.DbContext and I have it working except for any tables that have an XML column
When try and read from one of these tables I get the following error:
System.InvalidOperationException
HResult=0x80131509
Message=The entity type 'SqlXml' requires a primary key to be defined. If you intended to use a keyless entity type call 'HasNoKey()'.
I know this isn't talking about my model as it does have a primary key. It seems to be talking about the SqlXml class.
How can I construct a data model to bind to a SQL table with an XML column?
The DBContext
public class HistoryContext : DbContext
{
public HistoryContext(DbContextOptions<HistoryContext> options) : base(options)
{
}
public DbSet<ShWorkflowRunsModel> shWorkflowRuns { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<ShWorkflowRunsModel>().ToTable("sh_workflowRuns");
}
}
The Model
public class ShWorkflowRunsModel
{
[Key]
public Int64 runId { get; set; }
public Guid serviceId { get; set; }
public string serviceName { get; set; }
public Guid workflowId { get; set; }
public SqlXml workflowConfig { get; set; }
public DateTime startTime { get; set; }
public DateTime endTime { get; set; }
public bool isScheduled { get; set; }
public string errorMessage { get; set; }
public bool hasErrorCounters { get; set; }
}
The Controller
public class WorkflowController : Controller
{
private HistoryContext _context;
public WorkflowController(HistoryContext context)
{
_context = context;
}
public IActionResult History(string workflowId)
{
List<ShWorkflowRunsModel> model = GetShWorkflowRuns(workflowId);
return View(model);
}
public List<ShWorkflowRunsModel> GetShWorkflowRuns(string workflowId)
{
return _context.shWorkflowRuns.Where(wr => wr.workflowId.ToString() == workflowId).ToList();
}
}
}
The SQL table definition
CREATE TABLE [dbo].[sh_workflowRuns](
[runId] [bigint] IDENTITY(1,1) NOT NULL,
[serviceId] [uniqueidentifier] NOT NULL,
[serviceName] [nvarchar](1024) NULL,
[workflowId] [uniqueidentifier] NOT NULL,
[workflowConfig] [xml] NOT NULL,
[startTime] [datetime] NULL,
[endTime] [datetime] NULL,
[isScheduled] [bit] NULL,
[errorMessage] [nvarchar](1024) NULL,
[hasErrorCounters] [bit] NULL,
CONSTRAINT [PK_sh_workflowRuns] PRIMARY KEY CLUSTERED
(
[runId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Please let me know if you need more information