Issue to compose a nested relation query with Entity Framework

Question

my relation is hierarchical like customer -> address -> contacts

a single customer may have multiple address and a single address may have multiple contacts. see my class structures.

   public class CustomerBase
    {
        public int CustomerID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class Customer : CustomerBase
    {
        public virtual List<Addresses> Addresses { get; set; }
    }

    public class Addresses
    {
        [Key]
        public int AddressID { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public bool IsDefault { get; set; }
        public virtual List<Contacts> Contacts { get; set; }

        public int CustomerID { get; set; }
        public virtual Customer Customer { get; set; } 

    }

    public class Contacts
    {
        [Key]
        public int ContactID { get; set; }

        public string Phone { get; set; }
        public string Fax { get; set; }

        public int AddressID { get; set; }
        public virtual Addresses Customer { get; set; } 

    }

    public class TestDBContext : DbContext
    {
        public TestDBContext()
            : base("name=TestDBContext")
        {
        }

        public DbSet<Customer> Customer { get; set; }
        public DbSet<Addresses> Addresses { get; set; }
        public DbSet<Contacts> Contacts { get; set; }
    }

this way i am population data in db table with EF code first.

    using (var db = new TestDBContext())
    {
        var customer = new Customer
        {
            FirstName = "Test Customer2",
            LastName = "Test Customer2",
            Addresses = new List<Addresses>
            {
                new Addresses
                {
                    Address1 = "foo1",
                    Address2 = "foo2",
                    IsDefault=true,
                    Contacts =  new List<Contacts>
                    {
                       new Contacts {  Phone = "22222222", Fax = "1-999999999" }
                    }
                }
            }
        };

        db.Customer.Add(customer);
        db.SaveChanges();

now i want to query data. suppose i want to fetch customer whose customerid is 1 and want to load address related to customer id is 1 and address Isdefault is true and default address related contacts details.

i try to compose it this way hence could not complete because i am in EF and LINQ query.

var bsCustomer = db.Customer.Where(c => c.CustomerID == 2).Include(a=> 
a.Addresses.Where(a=> a.IsDefault==true)).Include(c=> c.)

so please tell me what will be the query as a result customer id 1 related address will load whose isdefault would be true and address related contact will be loaded. thanks

Sampath · Accepted Answer · 2016-09-07T03:54:16.670

1

You can try as shown below.

Query based syntax :

var dbquery =  from cu in db.Customers 
               where (cu.CustomerID  == 1) 
               select new { 
                           cu,  
                           Addresses= from ad in cu.Addresses
                                      where (ad.IsDefault == true) 
                                      from ct in ad.Contacts  
                                      select ad,

             };

You can iterate it as you wish :

var customers = dbquery.AsEnumerable() 
                       .Select(c => c.cu);

foreach(var customer in customers ) 
{ 
    foreach(var address in customer.Addresses) 
     {
       //your code; 
     }
}

Method based syntax :

var dbquery =  db.Customers.Where(cu=>cu.CustomerID  == 1) 
               .Select(cus=> new { 
                   cus,  
                   Addresses= cus.Addresses.Where(ad=>ad.IsDefault == true).Include(c=>c.Contacts)
                   }).AsEnumerable()
                   .Select(f => f.cus).ToList();

edited Sep 07 '16 at 03:54

answered Sep 06 '16 at 15:04

Sampath

63,341
64
307
441

you cannot do it hence `Include` doesn't support filtering. – Sampath Sep 06 '16 at 15:11
how to achieve the same query with include options ? – Monojit Sarkar Sep 06 '16 at 15:11
you cannot do that using `include`. – Sampath Sep 06 '16 at 15:12
give me relevant link which speak "Include doesn't support filtering" what u said. – Monojit Sarkar Sep 06 '16 at 15:12
here it is :http://stackoverflow.com/questions/8333934/entity-framework-selective-condition-on-included-navigation-property – Sampath Sep 06 '16 at 15:17
how to show customer name and address and contact details from dbquery variable ? add more code to do this. thanks – Monojit Sarkar Sep 06 '16 at 15:18
I have expanded it further.please see that too. – Sampath Sep 06 '16 at 15:36
Thank u very much. Can we fetch data with lambda only? – Monojit Sarkar Sep 06 '16 at 18:04
no problem :) yes,we can.if you need any help for that please let me know. – Sampath Sep 06 '16 at 18:42
Please add one sample to do the same with lambda. – Monojit Sarkar Sep 06 '16 at 20:17

Issue to compose a nested relation query with Entity Framework

this way i am population data in db table with EF code first.

1 Answers1