实体框架未存在

问题描述：

柱

CREATE TABLE AppUserInRole 
(
    Id    BIGINT   NOT NULL IDENTITY PRIMARY KEY,      
    IdAppUser  BIGINT   NOT NULL FOREIGN KEY REFERENCES AppUser (Id),   
    IdAppUserRole BIGINT   NOT NULL FOREIGN KEY REFERENCES AppUserRole (Id), 
    ValidFrom  DATETIME2  NOT NULL DEFAULT GETDATE(),       
    ValidTo   DATETIME2  NULL,             
    CreatedDate  DATETIME2  NOT NULL DEFAULT GETDATE(),       
    CreatedBy  BIGINT NULL FOREIGN KEY REFERENCES AppUser (Id),            
)

为了产生我使用的EntityFramework反向POCO发生器（http://www.reversepoco.com/）。

这是我的课：

[Table("AppUserInRole", Schema = "dbo")] 
[System.CodeDom.Compiler.GeneratedCode("EF.Reverse.POCO.Generator", "2.31.1.0")] 
public partial class AppUserInRole 
{ 
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
    [Column(@"Id", Order = 1, TypeName = "bigint")] 
    [Required] 
    [Key] 
    [Display(Name = "Id")] 
    public long Id { get; set; } // Id (Primary key) 

    [Column(@"IdAppUser", Order = 2, TypeName = "bigint")] 
    [Required] 
    [Display(Name = "Id app user")] 
    public long IdAppUser { get; set; } // IdAppUser 

    [Column(@"IdAppUserRole", Order = 3, TypeName = "bigint")] 
    [Required] 
    [Display(Name = "Id app user role")] 
    public long IdAppUserRole { get; set; } // IdAppUserRole 

    [Column(@"ValidFrom", Order = 4, TypeName = "datetime2")] 
    [Required] 
    [Display(Name = "Valid from")] 
    public System.DateTime ValidFrom { get; set; } = System.DateTime.Now; // ValidFrom 

    [Column(@"ValidTo", Order = 5, TypeName = "datetime2")] 
    [Display(Name = "Valid to")] 
    public System.DateTime? ValidTo { get; set; } // ValidTo 

    [Column(@"CreatedDate", Order = 6, TypeName = "datetime2")] 
    [Required] 
    [Display(Name = "Created date")] 
    public System.DateTime CreatedDate { get; set; } = System.DateTime.Now; // CreatedDate 

    [Column(@"CreatedBy", Order = 7, TypeName = "bigint")] 
    [Required] 
    [Display(Name = "Created by")] 
    public long CreatedBy { get; set; } // CreatedBy 

    // Foreign keys 

    /// <summary> 
    /// Parent AppUser pointed by [AppUserInRole].([CreatedBy]) (FK__AppUserIn__Creat__1AD3FDA4) 
    /// </summary> 
    [ForeignKey("CreatedBy")] public virtual AppUser AppUser_CreatedBy { get; set; } // FK__AppUserIn__Creat__1AD3FDA4 
    /// <summary> 
    /// Parent AppUser pointed by [AppUserInRole].([IdAppUser]) (FK__AppUserIn__IdApp__30F848ED) 
    /// </summary> 
    [ForeignKey("IdAppUser")] public virtual AppUser AppUser_IdAppUser { get; set; } // FK__AppUserIn__IdApp__30F848ED 
    /// <summary> 
    /// Parent AppUserRole pointed by [AppUserInRole].([IdAppUserRole]) (FK__AppUserIn__IdApp__31EC6D26) 
    /// </summary> 
    [ForeignKey("IdAppUserRole")] public virtual AppUserRole AppUserRole { get; set; } // FK__AppUserIn__IdApp__31EC6D26 
} 

[System.CodeDom.Compiler.GeneratedCode("EF.Reverse.POCO.Generator", "2.31.1.0")] 
    public partial class AppUserInRoleConfiguration : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<AppUserInRole> 
    { 
     public AppUserInRoleConfiguration() 
      : this("dbo") 
     { 
     } 

     public AppUserInRoleConfiguration(string schema) 
     { 
      Property(x => x.ValidTo).IsOptional(); 

      InitializePartial(); 
     } 
     partial void InitializePartial(); 
    }

所以我需要从这个表法选择数据：

public async Task<List<AppUserInRole>> GetUserRoles(long userId, bool onlyValid) 
     { 
      var data = from d in DataContext.AppUserInRoles 
       where d.IdAppUser == userId 
       select d; 

      if (onlyValid) 
       data = data.Between(DateTime.Now, a => a.ValidFrom, a => a.ValidTo ?? DateTime.MaxValue); 

      return await data.ToListAsync(); 
     }

当我把这种方法，实体框架生成以下查询：

SELECT 
    1 AS [C1], 
    [Extent1].[Id] AS [Id], 
    [Extent1].[IdAppUser] AS [IdAppUser], 
    [Extent1].[IdAppUserRole] AS [IdAppUserRole], 
    [Extent1].[ValidFrom] AS [ValidFrom], 
    [Extent1].[ValidTo] AS [ValidTo], 
    [Extent1].[CreatedDate] AS [CreatedDate], 
    [Extent1].[CreatedBy] AS [CreatedBy], 
    [Extent1].[AppUser_Id] AS [AppUser_Id], 
    [Extent1].[AppUser_Id1] AS [AppUser_Id1] 
    FROM [dbo].[AppUserInRole] AS [Extent1] 
    WHERE ([Extent1].[IdAppUser] = @p__linq__0) AND ([Extent1].[ValidFrom] <= convert(datetime2, '2017-07-31 22:13:26.6862657', 121)) AND (convert(datetime2, '2017-07-31 22:13:26.6862657', 121) <= (CASE WHEN ([Extent1].[ValidTo] IS NULL) THEN @p__linq__1 ELSE [Extent1].[ValidTo] END))

实体框架抛出以下错误：无效的列名'AppUser_Id'。无效的列名称'AppUser_Id1'。 AppUser_Id和AppUser_Id1列不会真正灭绝。它是什么原因产生这些列？

非常感谢您的建议和帮助。

什么是你的'AppUser'模型？ –

既然你有数据库，为什么不在数据库中创建表之后创建一个模型呢？首先使用db代替代码 –

EBrown：AppUser是一个庞大的类。每个应用程序表（即使在AppUser表中）都有一个对AppUser表ID列（CreatedBy BIGINT NULL FOREIGN KEY REFERENCES AppUser（Id））的引用。并且应用程序有大约90个表格 – Davecz

答

无法使用瑞普EF 6.1.3

create database foo 
go 
use foo 
go 
create table AppUser 
(
Id bigint primary key 
) 

create table AppUserRole 
(
Id bigint primary key 
) 

CREATE TABLE AppUserInRole 
(
    Id    BIGINT   NOT NULL IDENTITY PRIMARY KEY,      
    IdAppUser  BIGINT   NOT NULL FOREIGN KEY REFERENCES AppUser (Id),   
    IdAppUserRole BIGINT   NOT NULL FOREIGN KEY REFERENCES AppUserRole (Id), 
    ValidFrom  DATETIME2  NOT NULL DEFAULT GETDATE(),       
    ValidTo   DATETIME2  NULL,             
    CreatedDate  DATETIME2  NOT NULL DEFAULT GETDATE(),       
    CreatedBy  BIGINT NULL FOREIGN KEY REFERENCES AppUser (Id),            
)

和：

using System; 
using System.Collections.Generic; 
using System.ComponentModel.DataAnnotations; 
using System.ComponentModel.DataAnnotations.Schema; 
using System.Data.Entity; 
using System.IO; 
using System.Linq; 
using System.Threading.Tasks; 

namespace ConsoleApp6 
{ 

    public class AppUser 
    { 
     public long Id { get; set; } // Id (Primary key) 
    } 

    public class AppUserRole 
    { 
     public long Id { get; set; } // Id (Primary key) 
    } 

    [Table("AppUserInRole", Schema = "dbo")] 
    [System.CodeDom.Compiler.GeneratedCode("EF.Reverse.POCO.Generator", "2.31.1.0")] 
    public partial class AppUserInRole 
    { 
     [DatabaseGenerated(DatabaseGeneratedOption.Identity)] 
     [Column(@"Id", Order = 1, TypeName = "bigint")] 
     [Required] 
     [Key] 
     [Display(Name = "Id")] 
     public long Id { get; set; } // Id (Primary key) 

     [Column(@"IdAppUser", Order = 2, TypeName = "bigint")] 
     [Required] 
     [Display(Name = "Id app user")] 
     public long IdAppUser { get; set; } // IdAppUser 

     [Column(@"IdAppUserRole", Order = 3, TypeName = "bigint")] 
     [Required] 
     [Display(Name = "Id app user role")] 
     public long IdAppUserRole { get; set; } // IdAppUserRole 

     [Column(@"ValidFrom", Order = 4, TypeName = "datetime2")] 
     [Required] 
     [Display(Name = "Valid from")] 
     public System.DateTime ValidFrom { get; set; } = System.DateTime.Now; // ValidFrom 

     [Column(@"ValidTo", Order = 5, TypeName = "datetime2")] 
     [Display(Name = "Valid to")] 
     public System.DateTime? ValidTo { get; set; } // ValidTo 

     [Column(@"CreatedDate", Order = 6, TypeName = "datetime2")] 
     [Required] 
     [Display(Name = "Created date")] 
     public System.DateTime CreatedDate { get; set; } = System.DateTime.Now; // CreatedDate 

     [Column(@"CreatedBy", Order = 7, TypeName = "bigint")] 
     [Required] 
     [Display(Name = "Created by")] 
     public long CreatedBy { get; set; } // CreatedBy 

     // Foreign keys 

     /// <summary> 
     /// Parent AppUser pointed by [AppUserInRole].([CreatedBy]) (FK__AppUserIn__Creat__1AD3FDA4) 
     /// </summary> 
     [ForeignKey("CreatedBy")] public virtual AppUser AppUser_CreatedBy { get; set; } // FK__AppUserIn__Creat__1AD3FDA4 
                         /// <summary> 
                         /// Parent AppUser pointed by [AppUserInRole].([IdAppUser]) (FK__AppUserIn__IdApp__30F848ED) 
                         /// </summary> 
     [ForeignKey("IdAppUser")] public virtual AppUser AppUser_IdAppUser { get; set; } // FK__AppUserIn__IdApp__30F848ED 
                         /// <summary> 
                         /// Parent AppUserRole pointed by [AppUserInRole].([IdAppUserRole]) (FK__AppUserIn__IdApp__31EC6D26) 
                         /// </summary> 
     [ForeignKey("IdAppUserRole")] public virtual AppUserRole AppUserRole { get; set; } // FK__AppUserIn__IdApp__31EC6D26 
    } 

    [System.CodeDom.Compiler.GeneratedCode("EF.Reverse.POCO.Generator", "2.31.1.0")] 
    public partial class AppUserInRoleConfiguration : System.Data.Entity.ModelConfiguration.EntityTypeConfiguration<AppUserInRole> 
    { 
     public AppUserInRoleConfiguration() 
      : this("dbo") 
     { 
     } 

     public AppUserInRoleConfiguration(string schema) 
     { 
      Property(x => x.ValidTo).IsOptional(); 

      InitializePartial(); 
     } 
     partial void InitializePartial(); 
    } 
    class Db: DbContext 
    { 
     public Db(string nameOrConnectionString) : base(nameOrConnectionString) 
     { 
     } 

     public DbSet<AppUserInRole> AppUserInRoles { get; set; } 

     public async Task<List<AppUserInRole>> GetUserRoles(long userId, bool onlyValid) 
     { 
      var data = from d in this.AppUserInRoles 
         where d.IdAppUser == userId 
         select d; 

      if (onlyValid) 
       data = data.Where(r => r.ValidFrom < DateTime.Now && (DateTime.Now < (r.ValidTo ?? DateTime.MaxValue))); 

      return await data.ToListAsync(); 
     } 

    } 
    class Program 
    { 

     static void Main(string[] args) 
     { 




      using (var db = new Db("Server=.;Database=foo;Integrated Security=true")) 
      { 
       db.Database.Log = m => Console.WriteLine(m); 


       var data = db.GetUserRoles(1, true).Result; 

      } 

      Console.ReadKey(); 

     } 
    } 
}

输出

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[IdAppUser] AS [IdAppUser], 
    [Extent1].[IdAppUserRole] AS [IdAppUserRole], 
    [Extent1].[ValidFrom] AS [ValidFrom], 
    [Extent1].[ValidTo] AS [ValidTo], 
    [Extent1].[CreatedDate] AS [CreatedDate], 
    [Extent1].[CreatedBy] AS [CreatedBy] 
    FROM [dbo].[AppUserInRole] AS [Extent1] 
    WHERE ([Extent1].[IdAppUser] = @p__linq__0) AND ([Extent1].[ValidFrom] < (SysDateTime())) AND ((SysDateTime()) < (CASE WHEN ([Extent1].[ValidTo] IS NULL) THEN @p__linq__1 ELSE [Extent1].[ValidTo] END))

与应用程序中的其他表格有同样的问题。 – Davecz

相关推荐