CODEWITHSUNDEEP
c#asp.netlinqentity-frameworkcode-first
jackncoke
jackncoke

Reputation: 2020

Linq query multiple tables with different attributes

I have a search that looks for two things. Items and Contacts. They each have their own table with their own unique attributes. I am able to successfully search each independent of eachother and return the results to two list views. But is it ugly and paging has become a issue so I have to convert these two tables into a like result that I can display as a search result. These results have no relationship directly with eachother.

The group t3 by new is throwing me off. Do I have to group them to have it become a like result? The results currently get displayed in a ListView using for example <%#Eval("ItemName") %>

ItemContext db = new ItemContext(); //DB connection (Item,Contact)
            var q = (from t1 in db.Item
                     join t2 in db.Categories on t1.CategoryID equals t2.CategoryID
                     join t7 in db.Divisions on t1.DivisionID equals t7.DivisionID
                     from t3 in db.Contacts
                     join t4 in db.Categories on t3.CategoryID equals t4.CategoryID
                     join t5 in db.Divisions on t3.DivisionID equals t5.DivisionID
                     join t6 in db.ContactTitle on t3.ContactTitlesID equals t6.ContactTitlesID


                     where


                    (DDLInt == 1 || t3.DivisionID == DDLInt) &&

                         //Contains

                    (
                     t3.ContactName.Contains(keyword) ||
                     t3.ContactEmail.Contains(keyword) ||
                     t3.ContactOPhone.Contains(keyword) ||
                     t3.ContactID.Equals(searchID)


                     )
                     group t3 by new
                     {
                         t3.ContactID,
                         t3.ContactName,
                         t3.ContactOPhone,
                         t3.ContactCell,
                         t3.ContactEmail,
                         t3.DivisionID,
                         t3.CategoryID,
                         t4.CategoryName,
                         t5.DivisionName,
                         t6.ContactTitlesName

                     }

                        into i

                     select new
                     {
                         i.Key.ContactID,
                         i.Key.ContactName,
                         i.Key.ContactOPhone,
                         i.Key.ContactEmail,
                         i.Key.ContactCell,
                         i.Key.CategoryName,
                         i.Key.DivisionName,
                         i.Key.CategoryID,
                         i.Key.DivisionID,
                         i.Key.ContactTitlesName
                     });


            return q.ToList<dynamic>();
        }

Upvotes: 1

Views: 175

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109089

Use Union():

var contacts = from c in db.Contacts
              select new {
                           Id = c.ContactID,
                           Name = c.ContactName,
                           Phone = c.ContactOPhone,
                           ...
                           CategoryName = c.Category.CategoryName,
                           DivisionName = c.Division.DivisionName,
                           ContactTitlesName = c.ContactTitle.ContactTitlesName
                         }
var items = from t1 in db.Item
            select new {
                         Id = t1.ItemID,
                         Name = t1.ItemName,
                         Phone = t1.??, // string.Empty?
                         ... // more properties corresponding
                             // with the ones above
                         CategoryName = t1.Category.CategoryName,
                         DivisionName = t1.Division.DivisionName,
                         ContactTitlesName = string.Empty
                       }
var all = contacts.Union(items);

Upvotes: 1

Related Questions