LINQ and Entity Framework, get sum of related rows of a non mapped column

I want to get the sum of applicants that applied to a specific position, this should not be saved as a column.

My model is simple:

We have positions:

• net developer

• java developer

We have applicants:

• Luis
• John
• etc

We have applicants per position

With this column or property I want to know how many people have applied to each position, depending on the status. So in my mvc view I want to show something like:

Position Applied Accepted Rejected ... other status

.net developer 5 3 2

java developer 3 2 1

The real problem here is the linq query which I am not very expert.

EDIT: I think I needed to change where the linq query must be coded, I suppose it should be in the ApplicantPosition class instead of Position, I also changed the types of Position and Application to be ICollection.

Please see the modified code.

 public class Position
    {
        public int id { get; set; }

        [StringLength(20, MinimumLength=3)]
        public string name { get; set; }
      开发者_如何学Python  public int yearsExperienceRequired { get; set; }


    }

    public class Applicant
    {
        public int ApplicantId { get; set; }
        [StringLength(20, MinimumLength = 3)]
        public string name { get; set; }
        public string telephone { get; set; }
        public string skypeuser { get; set; }
        public ApplicantImage photo { get; set; }
    }

public class ApplicantPosition
{
    public virtual ICollection<Position> appliedPositions { get; set; }
    public virtual ICollection<Applicant> applicants { get; set; }
    public DateTime appliedDate { get; set; }
    public int StatusValue { get; set; }

    public Status Status
    {
        get { return (Status)StatusValue; }
        set { StatusValue = (int)value; }
    }

      [NotMapped]
    public int numberOfApplicantsApplied
    {
        get
        {
            var query = 
                from ap in appliedPositions 
                select new
                {
                    positionName = g.Key.name,
                    peopleApplied = g.Count(x => x.Status == Status.Applied),
                };
            return query.Count(); ---??
        }
    }
}

---

Use direct SQL with PIVOT operator. This is really not a case for Linq query.

---

You can paste this into LINQPad as C# Program and run.

public enum  Status
{
    Applied, 
    Accepted,
    Rejected
}

public class Position
{
    public int id { get; set; }
    public string name { get; set; }
}

public class Applicant
{
    public int ApplicantId { get; set; }
    public string name { get; set; }
}

public class ApplicantPosition
{
    public Position appliedPosition { get; set; }
    public Applicant applicant { get; set; }
    public DateTime appliedDate { get; set; }
    public Status Status { get; set; }
}


void Main()
{
    var p1 = new Position { id = 1, name = ".net developer" };
    var p2 = new Position { id = 2, name = "java developer" };

    var a1 = new Applicant { ApplicantId = 100, name = "Luis" };
    var a2 = new Applicant { ApplicantId = 200, name = "John" };

    var ap1 = new ApplicantPosition { appliedPosition = p1, applicant = a1, Status = Status.Applied };
    var ap2 = new ApplicantPosition { appliedPosition = p1, applicant = a2, Status = Status.Accepted };
    var ap3 = new ApplicantPosition { appliedPosition = p2, applicant = a2, Status = Status.Rejected };

    var db = new[] { ap1, ap2, ap3};

    var query = 
        from ap in db 
        group ap by ap.appliedPosition into g
        select new
        {
            positionName = g.Key.name,
            peopleApplied = g.Count(x => x.Status == Status.Applied),
            peopleAccepted = g.Count(x => x.Status == Status.Accepted),
            peopleRejected = g.Count(x => x.Status == Status.Rejected),
        };

    query.Dump();
}

The result will be:

positionName     peopleApplied peopleAccepted peopleRejected
.net developer   1             1              0
java developer   0             0              1

---

According to my experiences you can use LinQ or Entity Framework just by mapping your tables in to a DBML to a Entity Framework Model file. In other way Microsoft gives you a Dynamic LinQ class that you can use it.I think you map all your columns and user Dynamic LinQ class.Good luck