2

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

1 Answer 1

3

You get this error message because EF Core thinks that SqlXml is one of your entities and tries to map it to a DB table, and obviously it can't find any Id on the SqlXml class.

I don't think that that current version of EF Core has native support XML. You have to read and write it as string, and you can create a wrapper like they do it in these examples:

XML columns in a Code-First application

Does Entity Framework 6.1 support an XML data type natively?

https://social.msdn.microsoft.com/Forums/en-US/a2e6aa49-f573-4dca-a1e7-505841c3c668/entity-framework-treats-xml-type-in-sql-server-as-a-string?forum=adodotnetentityframework

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

5 Comments

EF core has value conversion for this purpose.
@hujtomi Apologies, I might need more guidance. If I just set the type to string within the model, I still get exactly the same error (The entity type 'SqlXml' requires a primary key to be defined.). How can I construct a wrapper if I can't even read the value in as a string?
you changed public SqlXml workflowConfig { get; set; } to public string workflowConfig { get; set; } in your ShWorkflowRunsModel class and you still get the exception?
@hujtomi Exactly right, it even mentions SqlXml in the message which is surprising!
I think you should clean and build your project, I made a new sample project where I used your model class with string workflowConfig property and your controller and DBcontext, and I don't get the error. If it still does not work for you, then I think you should create a minimal sample project and share the code.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.