c# - One to many linq query -

i trying fetch list of customers , theirs orders not deleted state.

i have customer entity

public class customer {   public int id {get;set;}   public string name {get;set;}   public collection<order> orders {get;set;} } 

and have order entity like,

public class order {   public int id{get;set;}   public string name {get;set;}   public decimal cost {get;set;}   public int customerid {get;set;}    public customer customer {get;set;}   public bool isdeleted {get;set;} } 

here struggling fetch list of customers , theirs orders not deleted.

what have done achieve above requirement is,

var customers = (from customer in customers                      join order in orders.where(x=>!x.isdeleted)                          on customer.id equals order.customerid                   !order.isdeleted).tolist() 

i getting list of customers orders(including deleted = 1) when run query.

please me out ignore deleted orders.

thanks in advance.

you need differently if want filter orders of customer include orders not deleted.

a new (detached) customer instance, populated query result should created, or alternatively anonymous object or specific "dto" instance:

public class customerview {     public readonly int id;     public readonly string name;     public readonly list<orders> orders; }   var customers =      customer in customers     select new customerview() {         id = customer.id,         name = customer.name,         orders = customer.orders.where(x=>!x.isdeleted).tolist()     }; 

note since there no where clause limit number of returned customers dealing with, there unbounded result set output of query.

if loading specific customer id, can use following approach "lazy load" orders using filter. note: need replace mydbcontext own dbcontext type.

public customer loadbyid(int customerid) {     using (var context = new mydbcontext())      {         var mycustomer = context.customers.find(customerid);         if (mycustomer != null)         {             // filter orders.             context.entry(mycustomer)                 .collection(c => c.orders)                 .query()                  .where(o => !o.isdeleted)                 .load();         }         return mycustomer;     } }