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

问题描述

错误信息很明确:

'所需的列 'CustomerId' 不存在于'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结果集的print scrn &&数据库上下文:

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 类的集合导航属性.

虽然相关的类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，您应该使用 ForeignKey 属性:

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屋！