Tuesday, December 13, 2011

how to return entities that have children with specific criteria in entity framework code first


This is how we can return users that have orderes with specific product name. the Orders collection will be eagerly filtered and can be loaded.


    public class User
    {
        public int UserId { get; set; }
        public string UserName { get; set; }
        public List<Order> Orders { get; set; }
    }

    public class Order
    {
        public int OrderId { get; set; }
        public int UserId { get; set; }
        public decimal Amount { get; set; }
        public string ProductName { get; set; }
    }


    //Assuming these two have been disabled
    ((DbContext)_context).Configuration.ProxyCreationEnabled = false;
    ((DbContext)_context).Configuration.LazyLoadingEnabled = false;

     //No change tracking and caching 
     var set1 = _context.Set<User>().AsNoTracking()
              .Select(u => new 
              { User = u, 
                Orders = u.Orders.Where(o => o.ProductName == "blahblah") 
              })
              .Where(f => f.Orders.Count() > 0).ToList();

     List<User> list1 = set1.Select(s => s.User).ToList();
     //result has the users that their orders children for each user has been loaded.



     var set2 = _context.Set<User>().AsNoTracking()
              .Select(u => new 
              { User = u, 
                Orders = u.Orders.Count(o => o.ProductName == "blahblah") 
              })
              .Where(f => f.Orders > 0).ToList();

     List<User> list2 = set2.Select(s => s.User).ToList();
     //result has the users that their orders children for each user has not been loaded.

0 comments:

Post a Comment