'FromSql'操作的结果中不存在必需的列'CustomerId' [英] The required column 'CustomerId' was not present in the results of a 'FromSql' operation

问题描述

错误消息很明确:

"FromSql"操作

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

但是我不怎么希望有CustomerId?

But somehow I didn't really expect a CustomerId?

错误发生在这里:

contacts = db.Contacts.FromSql("SIP_API_MONDIA_Contacts_sel").ToList();
addresses = db.Addresses.FromSql("SIP_API_MONDIA_Address_sel").ToList();

控制器:

  public IList<Customer> GetAllCustomers()
        {
            //Initialize the objects
            IList<Customer> customers = null;
            IList<Contacts> contacts = null;
            IList<Addresses> addresses = null;

            //Fetch the data from stored procedures
            customers = db.Customers.FromSql("SomeProcName").ToList();
            contacts = db.Contacts.FromSql("SomeProcName").ToList();
            addresses = db.Addresses.FromSql("SomeProcName").ToList();

            //Loop through customers and add the contact and addresses when required
            foreach(var item in customers)
            {
                item.Contacts = contacts.Where(x => x.Customer == item.Id).ToList();
                item.Addresses = addresses.Where(x => x.Customer == item.Id).ToList();
            }
            return customers;
        }

模型:

public class Customer
    {
        public Guid Id { get; set; }
        public string Code { get; set; }
        public string Name { get; set; }
        public string VatCode { get; set; }
        public string ChamberOfCommerceCode { get; set; }
        public DateTime Modified { get; set; }
        public DateTime Created { get; set; }
        public string LanguageCode { get; set; }
        public decimal Discount { get; set; }
        public string CustomerManager { get; set; }
        public Guid PriceList { get; set; }
        public Guid PaymentCondition { get; set; }
       // public bool VatLiable { get; set; }
        public bool IsBlocked { get; set; }
        public bool IsProspect { get; set; }
        public bool IsSuspect { get; set; }
        public string Website { get; set; }
        public string DashboardUrl { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public string Fax { get; set; }
        //     public ICollection<FreeFields> FreeFields { get; set; }
        //      public Dictionary<string, string> UknownElements { get; set; }
        public ICollection<Contacts> Contacts { get; set; }
        public ICollection<Addresses> Addresses { get; set; }
    }

    public class FreeFields
    {
        public string Key { get; set; }
        public string Value { get; set; }
    }

    public class Contacts
    {
        public Guid Id { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public string Initials { get; set; }
        public string Function { get; set; }    
        public Guid Customer { get; set; }
        public string Email { get; set; }
        public string Phone { get; set; }
        public string Mobile { get; set; }
        public string LanguageCode { get; set; }
        public bool IsMainContact { get; set; }
        public string Gender { get; set; }
        public string Username { get; set; }
    }
    public class Addresses
    {
        public Guid Id { get; set; }
        public string AddressLine1 { get; set; }
        public string AddressLine2 { get; set; }
        public string AddressLine3 { get; set; }
        public string Postcode { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public string CountryCode { get; set; }
        public string Type { get; set; }
        public Guid Customer { get; set; }// This Property should be GUID instead of String..
        public bool IsMainAddress { get; set; }
        public string Route { get; set; }
        public string State { get; set; }
    }

我不能完全确定"CustomerId"错误代表什么意思存储过程返回模型的100％精确值.

I am not entirely sure what the error means with 'CustomerId' The stored procedures return the 100% exact value of the models.

编辑以添加sql结果集的打印scrn&&DbContext:

public class IsahContext : DbContext
    {
        public  IsahContext()
        {

        }

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

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(Setting.ConnectionString);
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }

        //Entities will come here 
        public DbSet<Customer> Customers { get; set; }
        public DbSet<Addresses> Addresses { get; set; }
        public DbSet<Contacts> Contacts { get; set; }

    }

推荐答案

CustomerId 是

public ICollection<Contacts> Contacts { get; set; }
public ICollection<Addresses> Addresses { get; set; }

Customer 类的

集合导航属性.

collection navigation properties of the Customer class.

尽管相关类 Contacts 和 Addresses 包含属性 Guid Customer ，但由于其名称，它不能被识别为外键，因此它属于没有外键属性类别.EF Core假定一个名为 CustomerId 的阴影属性(和列).影子属性约定的解释是:

Although the related classes Contacts and Addresses contain a property Guid Customer, due to its name it's not recognized as a Foreign Key, so it falls into No Foreign Key Property category. And EF Core assumes a shadow property (and column) named CustomerId. Shadow property convention explanation is:

可以通过约定在发现关系但在从属实体类中未找到任何外键属性的情况下创建阴影属性.在这种情况下，将引入影子外键属性.影子外键属性将被命名为<导航属性名称><主键属性名称> (在依赖实体上的导航，它指向主体实体，用于命名).如果主键属性名称包括导航属性的名称，则该名称将只是<主键属性名称> .如果从属实体上没有导航属性，那么将在其位置使用主体类型名称.

Shadow properties can be created by convention when a relationship is discovered but no foreign key property is found in the dependent entity class. In this case, a shadow foreign key property will be introduced. The shadow foreign key property will be named <navigation property name><principal key property name> (the navigation on the dependent entity, which points to the principal entity, is used for the naming). If the principal key property name includes the name of the navigation property, then the name will just be <principal key property name>. If there is no navigation property on the dependent entity, then the principal type name is used in its place.

为了将 Customer 属性映射为FK，您应该使用

In order to map the Customer property as a FK, you should use either ForeignKey attribute:

您可以使用数据注释来配置应将哪个属性用作给定关系的外键属性.通常，当约定没有发现外键属性时，就会执行此操作.

You can use the Data Annotations to configure which property should be used as the foreign key property for a given relationship. This is typically done when the foreign key property is not discovered by convention.

提示
可以将 [ForeignKey] 批注放置在关系中的任一导航属性上.不需要在从属实体类中继续导航属性.

Tip
The [ForeignKey] annotation can be placed on either navigation property in the relationship. It does not need to go on the navigation property in the dependent entity class.

例如(因为您在从属实体中没有导航属性):

e.g. (since you have no navigation property in the dependent entities):

[ForeignKey(nameof(Contacts.Customer))]
public ICollection<Contacts> Contacts { get; set; }

[ForeignKey(nameof(Addresses.Customer))]
public ICollection<Addresses> Addresses { get; set; }

或 Fluent API :

modelBuilder.Entity<Customer>()
    .HasMany(customer => customer.Contacts)
    .WithOne() // no nav property
    .HasForeignKey(contact => contact.Customer); // the FK property

modelBuilder.Entity<Customer>()
    .HasMany(customer => customer.Addresses)
    .WithOne() // no nav property
    .HasForeignKey(address => address.Customer); // the FK property

这篇关于'FromSql'操作的结果中不存在必需的列'CustomerId'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！