实体框架生成的查询包含not-exists列

Question

A有一张桌子

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))

Entity Framework引发以下错误:列名'AppUser_Id‘无效。列名'AppUser_Id1‘无效。AppUser_Id和AppUser_Id1列并没有真正消失。生成这些列的原因是什么？

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

Answer 1

无法使用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))