I get error

Invalid column name 'WorkID_Work'

not sure why, can someone please help? Thanks

var employeeTable = _db.FT_Employee
                       .AsEnumerable()
                       .Where(x => x.StartDate >= selectedStartDate 
                                   && x.StartDate <= selectedEndDate
                                   && (x.Work.WorkType == "Remote"))
                       .Select(x => new { x.FirstName, x.LastName });

public class FT_Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [ForeignKey("DM_Work")]
    [Column("ID_Work")]
    public int? ID_Work { get; set; }
    
    public DM_Work Work{ get; set; }
}

public class DM_Work
{
    public int ID_Work { get; set; }
    public string WorkType { get; set; }
}

CodePudding user response：

The problem is that you are using the ForeignKey Attribute but not specifying the relationship between the two entities FT_Employee and DM_Work. Your code don't tell the system which relationship one-to-one or one-to-many.

inside DM_Work class, you have to add references to FT_Employee class

public class FT_Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [ForeignKey("DM_Work")]
    [Column("ID_Work")]
    public int? ID_Work { get; set; }
    
    public DM_Work Work{ get; set; }
}

public class DM_Work
{
    public int ID_Work { get; set; }
    public string WorkType { get; set; }

    public ICollection<FT_Employee> Employees { get; set; }
}

Some more information

Actually, there are 3 ways you can use the ForeignKey Attribute to configure the relationship. You can choose one of the below to suite your need

// 1. [ForeignKey(NavigationPropertyName)] on the foreign key scalar property
// in the dependent entity. as same as the example above

public class FT_Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [ForeignKey("DM_Work")]
    [Column("ID_Work")]
    public int? ID_Work { get; set; }
    
    public DM_Work Work{ get; set; }
}

public class DM_Work
{
    public int ID_Work { get; set; }
    public string WorkType { get; set; }

    public ICollection<FT_Employee> Employees { get; set; }
}

// 2. [ForeignKey(ForeignKeyPropertyName)] on the related reference navigation
// property in the dependent entity

public class FT_Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [Column("ID_Work")]
    public int? ID_Work { get; set; }
    
    [ForeignKey("ID_Work")]
    public DM_Work Work{ get; set; }
}

public class DM_Work
{
    public int ID_Work { get; set; }
    public string WorkType { get; set; }

    public ICollection<FT_Employee> Employees { get; set; }
}

// 3. [ForeignKey(ForeignKeyPropertyName)] on the navigation property in the
// principal entity

public class FT_Employee
{
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [Column("ID_Work")]
    public int? ID_Work { get; set; }
    
    public DM_Work Work{ get; set; }
}

public class DM_Work
{
    public int ID_Work { get; set; }
    public string WorkType { get; set; }

    [ForeignKey("ID_Work")]
    public ICollection<FT_Employee> Employees { get; set; }
}

• More information Data Annotation - ForeignKey Attributte

CodePudding user response：

You need to setup primary keys properly for your entities and add the missing the StartDate property. I have implemented a working example for you below so hopefully it should help you with your issue.

public class FT_Employee
{
    [Key]
    public int FT_EmployeeId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime StartDate { get; set; }

    public int DM_WorkId { get; set; }
    public DM_Work Work { get; set; }
}

public class DM_Work
{
    [Key]
    public int DM_WorkId { get; set; }
    public string WorkType { get; set; }
}

Update your DbContext class including seeding data:

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }
    
    public DbSet<FT_Employee> FT_Employee { get; set; }
    public DbSet<DM_Work> DM_Works { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.EnableSensitiveDataLogging();
        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<DM_Work>()
            .HasData(new DM_Work()
            {
                DM_WorkId = 1,
                WorkType = "Remote"
            },
            new DM_Work()
            {
                DM_WorkId = 2,
                WorkType = "Local"
            });
        
        builder.Entity<FT_Employee>()
            .HasData(new FT_Employee()
            {
                FT_EmployeeId = 1,
                FirstName = "Barry",
                LastName = "Allen",
                StartDate = DateTime.Now.AddDays(-2),
                DM_WorkId = 1
            },
            new FT_Employee()
            {
                FT_EmployeeId = 2,
                FirstName = "Wally",
                LastName = "West",
                StartDate = DateTime.Now.AddDays(-2),
                DM_WorkId = 2
            });
    }
}

Run Migration and Update using dotnet ef commands

Querying the employee for selected dates and worktype:

var selectedStartDate = DateTime.Now.AddDays(-5);
var selectedEndDate = DateTime.Now;

var employeeTable = _dbContext.FT_Employee
    .Where(x => x.StartDate >= selectedStartDate 
                && x.StartDate <= selectedEndDate
                && x.Work.WorkType == "Remote")
    .Select(x => new { x.FirstName, x.LastName })
    .ToList();

foreach (var table in employeeTable)
{
    Console.WriteLine($"{table.FirstName} {table.LastName}");
}

Query Output:

Barry Allen