So you have Employees and Qualifications, and a one-to-many relation: every Employee has zero or more Qualifications, every Qualification is the Qualification of exactly one Employee, namely the Employee that the foreign key EmployeeCode refers to.
If you have followed the entity framework conventions, you will have classes similar to:
class Employee
{
public int Id {get; set;}
public string Name {get; set;}
...
// Every Employee has zero or more Qualifications (one-to-many)
public virtual ICollection<Qualification> {get; set;}
}
class Qualification
{
public int Id {get; set;}
public string Description {get; set;}
...
// Every qualification is the qualification of exactly one Employee using foreign key
public int EmployeeId {get; set;}
public virtual Employee Employee {get; set;}
}
This would be enough for entity framework to detect the tables, the columns in the tables, and the relations between the tables using the primary keys and foreign keys.
In entity framework the columns of the tables are represented by the non-virtual properties. The virtual properties represent the relations between the tables (one-to-many, many-to-many)
The foreign key Qualification.EmployeeId is a real column, hence it it non-virtual. Property Qualfication.Employee represents the relation, hence it is virtual.
There is only need for attributes nor fluent API if you deviate from the conventions, like you did in your foreign key. In DbContext.OnModelCreating:
modelBuilder.Entity<Employee>().HasKey(employee => employee.EmployeeCode);
modelBuilder.Entity<Employee>().HasMany(employee => employee.Qualifications)
.WithRequired(qualification => qualification.Employee)
.HasForeignKey(qualification => qualification.EmployeeCode);
In words: every Employee has a primary key in property EmployeeCode. Every Employee has zero or more Qualifications in property Employee.Qualifications. Every Qualification has exactly one Employee in property Qualification.Employee, using foreign key Qualificatioin.EmployeeCode.
Back to your question
Requirement: count the number of qualifications with qualification_type != 4 for Employees that have Designation_Code == 3
Usig the virtual ICollection this is straightforward:
int qualificationTypeToIgnore == 4;
int employeeDesignationCode == 3;
var result = dbContext.Qualifications.Where(qualification =>
qualification.QualificationType != qualificationTypeToIgnore &&
qualification.Employee.DesignationCode == employeeDesignationCode)
.Count();
In words: from all Qualifications, keep only those Qualifications that have a QualificationType unequal to qualificationTypeToIgnore that also belongs to an Employee that has a DesingationCode equal to employeeDesignationCode. Count the number of remaining Qualifications.
Entity framework knows the one-to-many relation, and will do the property Join for you.
Some people don't like to use the virtual properties, they prefer to do the join themselves.
var eligibleEmployees = dbContext.Employees
.Where(employee => Employee.DesignationCode == employeeDesignationCode);
var eligibleQualifications = dbContext.Qualifications
.Where(qualification => qualification.QualificationType != qualificationTypeToIgnore);
var result = eligibleQualifications.Join(eligibleEmployees,
qualification => qualification.EmployeeCode, // from every Qualification take the foreign key
employee => employee.EmployeeCode, // from every Employee take the primary key
// parameter resultSelector: from every (qualification, employee) combination make one new
(qualification, employee) => new {qualification, employee})
.Count();
Since you will not be using the join result, you could simplify parameter resultSelector:
(qualification, employee) => 1
Or let entity framework do the join:
var result = eligibleQualifications.Where(qualification => eligibleEmployees
.Any(employee => employee.EmployeeCode == qualification.EmployeeCode))
.Count();
From all eligible qualifications, count the number of qualifications that have an EmployeeCode that is also an EmployeeCode in the eligible Employees.
IMHO the solution using the virtual properties is the most elegant one.
