Monojit Sarkar Monojit Sarkar - 2 months ago 7
C# Question

Issue to compose a nested relation query with Entity Framework

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

Answer

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();
Comments