CODEWITHSUNDEEP
c#linqentity-framework-5
Neil
Neil

Reputation: 177

C# linq query filter child Collection

Good day,

I have a model class like below

    public class EmployeeModel
    {
      [Key]
     public int employeeId{get;set;}
     public string Fullname {get;set;}
     public string Address{get;set;}
     public ICollection<PaymentModel> Payments {get;set;}
    }


    public class PaymentModel
    {

      [Key]
     public int PaymentId{get; set;}
     public int employeeId{get; set;}
     public decimal PaymentAmount{get; set;}
      public int IsPosted {get; set;}
      public virtual EmployeeModel Employee {get; set;}

    }

i just want to query the list of employee together with their list of payments using linq. so i code like this:

dbcontext db = new dbcontext();
var listing = from d in db.Employees
              .include("Payments")
               select d;

this listing show the all of the employee and all its payments. but i need to filter each employee Payments that IsPosted = 1

so as initial answer, ill do this code;

dbcontext db = new dbcontext();
List<EmployeeModel> FinalList = new List<EmployeeModel>();
var listingofEmp = db.employee.ToList();

foreach(EmployeeModel emp in listingofEmp){
emp.Payments= db.payments.where(x => x.IsPosted == 1).ToList();
FinalList.Add(emp);
}

my Question is, is ther any other way to code it much easier? something like this.

    dbcontext db = new dbcontext();
    var listing = from d in db.Employees
                  .include(d => x.Payments.IsPosted == 1)
                   select d;

im curently using entityframework 5

ive research regarding it not work for me Link

Hope someone will help me

Thanks In Advance Guys

Upvotes: 3

Views: 4169

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205889

What are you asking for is not natively supported, so there is no easier way, but for sure there is more efficient way because your current code is performing N + 1 database queries.

A better way could be to retrieve employees and related filtered payments with one database query using anonymous type projection, and then do something similar to your approach to create the final result in memory. For instance: 

var listing = 
    db.Employees.Select(employee => new
    {
        employee,
        payments = employee.Payments.Where(p => p.IsPosted == 1)
    })
    .AsEnumerable() // Switch to LINQ to Objects
    .Select(r =>
    {
        r.employee.Payments = r.payments.ToList();
        return r.employee;
    })
    .ToList();

Upvotes: 7

BLoB
BLoB

Reputation: 9725

Try something like this: It will give you a list of anonymous type that will hold the employee and it's payments.

using (dbcontext ctx = new dbcontext())
{
    ctx.Connection.Open();

    var result = (from e in ctx.Employees
                  join p in ctx.Payments on e.employeeId equals p.employeeId
                  where p.IsPosted == 1
                  select new
                  {
                      Employee = e,
                      Payments = p
                  }).ToList();

    ctx.Connection.Close();
}

Upvotes: 0

danilonet
danilonet

Reputation: 1795

it colud be a good alternative

var listing = from d in db.Payments
              .include("Employees")
              .where d.IsPosted == 1
               select d.Employees;

(not tested, then please fix mistakes)

start from pyaments, filter for is posted=1 , then select related emplyees

Upvotes: 1

Related Questions