The required column was not present in the results of a 'FromSql' operation and [NotMapped] has not helped

Question

I am using EF Core 3.0 to fetch data from a SQL Server stored procedure into my object model named Recipient. The Recipient model has a couple of properties defined, EnvelopeId and Name, that are not returned from the stored procedure. When I invoke the stored procedure using FromSqlInterpolated, I get the error message

The required column 'EnvelopeId1' was not present in the results of a 'FromSql' operation

From what I have read in the EF Core documentation, I should be able to add a [NotMapped] attribute to those properties and EF Core should be able to ignore them while reading from or writing to the database, right? Unfortunately, that isn't working for me and I am getting the above error.

If I add the two columns in the stored procedure and remove the [NotMapped] attribute in my object model, everything works fine. This proves that my other column/property names match up correctly and there isn't a typo anywhere.

I have seen suggestions of using DbQuery instead of DbSet, but that is being deprecated so I don't want to use that. I have seen suggestions of using two different object models, one with the properties exactly matching the stored procedure result set, but that just leads to lots of extra models. What am I doing wrong here?

My object model:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace MyNamespace.Models
{
    public class Recipient
    {
        [Key]
        public long RecipientId { get; set; }
        [NotMapped]
        public Guid? EnvelopeId { get; set; }
        public string Type { get; set; }
        public string UserId { get; set; }
        public string Email { get; set; }
        [NotMapped]
        public string Name { get; set; }
        public string RoleName { get; set; }
        public int RoutingOrder { get; set; }
        public string Status { get; set; }
        public DateTime StatusDate { get; set; }

        public Recipient() { }
    }
}

My DB context:

using Microsoft.EntityFrameworkCore;
using MyNamespace.Models;

namespace MyNamespace.Data
{
    public class MyDbContext : DbContext
    {
        public virtual DbSet<Recipient> Recipient { get; set; }
        public virtual DbSet<Envelope> Envelope { get; set; }
        public virtual DbSet<Template> Template { get; set; }

        public MyDbContext (DbContextOptions<MyDbContext> options) : base(options)   
        { }
    }
}

My failing code:

FormattableString sql = $"EXEC dbo.MyStoredProcedure @Param1={param1}, @Param2={param2}";
var result = await MyDbContext.Recipient.FromSqlInterpolated(sql).ToListAsync();

Edit #1 ...

The Envelope object model:

public class Envelope
{
    [Key]
    public Guid EnvelopeId { get; set; }
    public string Status { get; set; }
    public DateTime StatusDate { get; set; }
    public DateTime StatusPollDate { get; set; }
    [NotMapped]
    public List<Recipient> Recipients { get; set; }

    public Envelope() {
        Recipients = new List<Recipient>();
        StatusPollDate = DateTime.Parse("1753-01-01");
    }
}

The stored procedure result set schema:

[RecipientId] bigint,
[Type] varchar(20),
[UserId] varchar(50),
[Email] varchar(100),
[RoleName] varchar(100),
[RoutingOrder] int,
[Status] varchar(13),
[StatusDate] datetime

After reading some of the answers and comments, I have realized that a shadow property is being created by EF because it recognizes that Recipient is a child of the Envelope object. To be honest, I really should include the EnvelopeId in the result set (and remove [NotMapped]) to appease EF Core. The only reason I wasn't returning EnvelopeId in the result set of the stored procedure was because I had passed it in as an input parameter to begin with, and thought it was wasting network resources to pass something in and then get it back in each result set record.

Now I don't know where EnvelopeId1 came from, but that is what the error message mentions. As you can see in my object models and the stored procedure result set schema, I only ever have references to EnvelopeId. My best guess is that when EF Core decided to make a shadow property, it couldn't use EnvelopeId since I already had it in my object model, so it created one called EnvelopeId1 and then expected it to be present in the result set of the stored procedure.

Answer 1

There are two reasons for this error message:

The required column 'EnvelopeId1' was not present in the results of a 'FromSql' operation

The obvious one, for every column in the SQL result set, there must be a corresponding property in the object graph that you are trying to hydrate, in this case your Recipient class does not have EnvelopeId1.

Because your query is a stored procedure, it would be wise to post the SQL result in questions like this as it will show the SQL structure that you are trying to map to your object graph.

The reason that EnvelopeId1 is in your SQL tables but not in your object graph is because the Entity Framework has created a Shadow Property for the foreign that you have not defined a property for.

You haven't listed the Schema for Envelope but my money says you have an equivalent ICollection<Recipient> property.

Creating a NotMapped property for EnvelopeId just makes this schema more confusing instead we should map it properly, or you should omit it from your Stored Procedure output.

If you are going to use FromSql variants in EF (.Net or Core) then you should always define navigation properties in the schema classes as Shadow or Auto foreign key columns in the database will cause trouble unless you explicitly omit the columns that exist in the database but not in your schema

This means no SELECT * you will have to explicitly define all columns.

Solution 1: Modify Stored Procedure
You could simply modify your stored procedure to not return EvelopeId1 from your recipient table.

Solution 2: Fully define properties for both ends of all relationships in your data classes
Here I have used EnvelopeId1 as the name so that no migrations a needed, I would however recommend you change this to EnvelopeId, I personally use the existence of any field in the DB that ends in a numeral as an indication that some of my relationships have been inadequately defined.

public class Recipient
{
    [Key]
    public long RecipientId { get; set; }
    public Guid? EnvelopeId1 { get; set; }
    [ForeignKey(nameof(EnvelopeId1))]
    public virtual Envelope Envelope { get; set; }
    public string Type { get; set; }
    public string UserId { get; set; }
    public string Email { get; set; }
    [NotMapped]
    public string Name { get; set; }
    public string RoleName { get; set; }
    public int RoutingOrder { get; set; }
    public string Status { get; set; }
    public DateTime StatusDate { get; set; }

    public Recipient() { }
}

This is based on the assumption that your Envelope class has a definition that is similar to this partial definition:

public class Envelope
{
    [Key]
    public Guid EnvelopeId { get; set; }
    public virtual ICollection<Recipient> Recipients { get; set; } = new Hashset<Recipient>();
    ...
}