Get multiple data from tables Using Entity Framework data model

Question

I am using Entity Framework model data to manipulate data from database (CRUD operations). I want to get all data from tables (not just one).

Here is database model:

I want to get multiple data from all tables.

Currently I am using query displayed bellow but problem with this query is that I got multiple values from Contact tables and other tables displays only one result. Does someone knows why my query is not working and how to get all multiple data from tables.

Here is Query/Function to get all data from database:

ContactsEntities db = new ContactsEntities();
        //get all contacts
        public JsonResult GetAll()
        {
            var data = (from c in db.Contacts
                        from e in db.Emails.Where(x => x.id == c.id).DefaultIfEmpty()
                        from p in db.Phones.Where(x => x.id == c.id).DefaultIfEmpty()
                        from t in db.Tags.Where(x => x.id == c.id).DefaultIfEmpty()
                        select new
                        {
                            id = c.id,
                            phones = p.number,
                            emails = e.email1,
                            tags = t.tag1,
                            firstname = c.firstname,
                            lastname = c.lastname,
                            address = c.address,
                            city = c.city,
                            bookmarked = c.bookmarked,
                            notes = c.notes
                        }).ToList();
            return Json(data, JsonRequestBehavior.AllowGet);
        } 

Answer 1

I have tested this on your model it works:

var test1 = (from c in db.Contacts
                   join e in db.Emails
                       on c.id equals e.id_contact
                   join t in db.Tags
                   on c.id equals t.id_contact
                   join p in db.Phones on c.id equals p.id_contact
                   select new
                   {
                       id = c.id,
                       phones = p.number,
                       emails = e.email1,
                       tags = t.tag1,
                       firstname = c.firstname,
                       lastname = c.lastname,
                       address = c.address,
                       city = c.city,
                       bookmarked = c.bookmarked,
                       notes = c.notes
                   }).ToList();

I was trying to resolve this in one step, otherwise add this after test1 it works properly:

 var result = (from contact in test1
                    group contact by contact.id into grp
                    select new
                    {
                        id = grp.Key,
                        firstname = grp.First().firstname,
                        lastname = grp.First().lastname,
                        address = grp.First().address,
                        city = grp.First().city,
                        bookmarked = grp.First().bookmarked,
                        notes = grp.First().notes,
                        phones = grp.Where(x => x.phones != null).Select(x => x.phones).Distinct().ToArray(),
                        emails = grp.Where(x => x.emails != null).Select(x => x.emails).Distinct().ToArray(),
                        tags = grp.Where(x => x.tags != null).Select(x => x.tags).Distinct().ToArray()
                    }).ToList();

If you establish the relation between them it will be resolved and this code will return all contacts as you want:

1- Create New Diagram

2- Add these tables and then Drag Contact's id on 'id_contact' of each Email,Tag and phone

3- Save the diagram on Sql Server

4- Recreate your Model in Visual Studio

var contacts = (from c in db.Contacts
                       select c).ToList();

for each contact it will get all related emails,phones and tags just by the relation.