Get multiple data from tables Using Entity Framework data model

asp.net c# entity-framework sql

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:

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);
        } 
1
7
12/27/2014 10:53:03 AM

Accepted Answer

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

Relation

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.

10
12/27/2014 1:30:09 PM

Popular Answer

You're linking x.id to c.id. I think you need to link x.id_contact to c.id. (same problem for phone & tag)

        var data = (from c in db.Contacts
                    from e in db.Emails.Where(x => x.id_contact == c.id).DefaultIfEmpty()
                    from p in db.Phones.Where(x => x.id_contact == c.id).DefaultIfEmpty()
                    from t in db.Tags.Where(x => x.id_contact == 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);

Judging by the 'phones', 'emails' and 'tags' properties of the select, i'm thinking you expect 1 record per contact. You can achieve this with a group by:

       var data = (from c in db.Contacts
                from e in db.Emails.Where(x => x.id_contact == c.id).DefaultIfEmpty()
                from p in db.Phones.Where(x => x.id_contact == c.id).DefaultIfEmpty()
                from t in db.Tags.Where(x => x.id_contact == c.id).DefaultIfEmpty()
                select new
                {
                    id = c.id,
                    phone = (p != null ? p.number : null),
                    email = (e != null ? e.email1 : null),
                    tag = (t != null ? t.tag1 : null),
                    firstname = c.firstname,
                    lastname = c.lastname,
                    address = c.address,
                    city = c.city,
                    bookmarked = c.bookmarked,
                    notes = c.notes
                }).ToList();

        var data2 = (from i in data
                    group i by i.id into grp
                    select new
                    {
                        id = grp.Key,
                        phones = grp.Where(x => x.phone != null).Select(x => x.phone).ToArray(),
                        emails = grp.Where(x => x.email != null).Select(x => x.email).ToArray(),
                        tags = grp.Where(x => x.tag != null).Select(x => x.tag).ToArray(),
                        firstname = grp.First().firstname,
                        lastname = grp.First().lastname,
                        address = grp.First().address,
                        city = grp.First().city,
                        bookmarked = grp.First().bookmarked,
                        notes = grp.First().notes
                    }).ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow