We help IT Professionals succeed at work.

Entity Framework - Select from multiple tables/objects

Kevin Robinson
on
1,480 Views
Last Modified: 2013-11-11
I have an entity model with 2 tables Organisations and Contacts.  I want to do a search that returns the organisations AND contacts.  I want to pass in the parameters OrganisationName, Firstname, Surname.  

OrganisationName being in Organisations
FirstName, Surname in Contacts

Comment
Watch Question

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
What is the relationship between the 2 tables?

Typically, if it is a 1->M, such as shown below, it could be done as follows:

using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Collections;
namespace WindowsFormsApplicationC1
{
    public class Organization
    {
        public int id;
        public string OrganisationName;
        public IList<Contacts> contacts;
    }

    public class Contacts
    {
        public int id;
        public int OrganizationID;
        public string FirstName;
        public string LastName;
    }

    public partial class Form1 : Form
    {
        public Form1()
        {
            List<Organization> filteredList = Sample();

        }

        private List<Organization> Sample() {

            IList<Contacts> contactList = new List<Contacts>();
            Contacts selectedContact = (from c in contactList 
                                        where c.FirstName == "John" && c.LastName == "Smith" 
                                        select c).FirstOrDefault();
            IList<Organization> orgList = new List<Organization>();
            orgList.Add(new Organization() { id = 1, OrganisationName = "test", contacts = new List<Contacts>() });
            return (from o in orgList
                     where o.contacts.Contains(selectedContact)
                     select o).ToList<Organization>();
        }
    }
}

Open in new window

Anil GolamariFull Stack Developer
CERTIFIED EXPERT

Commented:
What do you want to pull from those tables. You can use below query to pull any information from table where you have a PK & FK relationship between two tables.

var result =
(
 from t1 in con.Organizations
 join t2 in con.Contacts on t1.ID equals t2.ID  // PK and FK
 where t2.C3 == XXX  // Where statement to filter those tables to retrieve requried information.
 select t1.C3  // select statement to get required information.
);
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
This seems to work but is returning an anonomous type.

Dim results1 = From o In Me.ObjectContext.Organisations.Include("TBL_Contacts")Join c In Me.ObjectContext.Contacts On o Equals c.Tbl_Organisation
Where (o.OrganisationName.StartsWith(Organisation)) And c.Forename.StartsWith(Firstname) Select o, c



 
Anil GolamariFull Stack Developer
CERTIFIED EXPERT

Commented:
dim results = from o in me.objectcontext.organisations
                     join c in me.objectcontect.contacts on o equals c.tbl_organisation
Where (o.OrganisationName.StartsWith(Organisation) And c.Forename.StartsWith(Firstname) )
Select o, c;

Can you try this with our including INCLUDE METHOD in the query.
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
This
-------------------------------
 Dim results1 = From o In Me.ObjectContext.Organisations
                  Join c In Me.ObjectContext.Contacts On o Equals c.Organisation
               Where (o.OrganisationName.StartsWith(Organisation)) And c.Forename.StartsWith(Firstname)
               Select o, c

Returns This
-------------------------------
Unable to cast object of type 'System.Data.Objects.ObjectQuery`1[VB$AnonymousType_0`2[ContactDatabaseSilverlight.Organisation,ContactDatabaseSilverlight.Contact]]' to type 'System.Linq.IQueryable`1[ContactDatabaseSilverlight.Organisation]'.
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
What exactly do you wish to return from the query?
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:

I want to search for Organisations and Contacts.  
Using the Criteria (Organisation Name, FirstName)  Then return a list of organisations (with contact) that match this.

So I search for  Organisation "V"  Forename = "K"   it should return all organisations starting with V which also have contacts with the K as their first letter.  


(Note:  I actuall want to expand on the criteria and search options but first things first.)
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
This Code Produces this
Unable to cast object of type 'System.Data.Objects.ObjectQuery`1[VB$AnonymousType_0`2[ContactDatabaseSilverlight.Organisation,ContactDatabaseSilverlight.Contact]]' to type 'System.Linq.IQueryable`1[ContactDatabaseSilverlight.Organisation]'.
 Public Function SeachQuery(ByVal Organisation As String, ByVal Firstname As String, ByVal Surnane As String) As IQueryable(Of Organisation)

        Dim results1 = From o In Me.ObjectContext.Organisations.Include("Contacts")
                 Join c In Me.ObjectContext.Contacts On o Equals c.Organisation
              Where (o.OrganisationName.StartsWith(Organisation)) And c.Forename.StartsWith(Firstname)
              Select o, c

        Return CType(results1, IQueryable(Of Organisation))

    End Function

Open in new window

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi VolunteerDevelopmentAgency;

results1 is not of type Organisations but is a complex type made up of Organisations and Contacts and therefore can't be converted to type IQueryable(Of Organisations)).

If you want to return a concrete type then create a type that contains both Organisations and Contacts and return that as a List(Of ComplexType) see code snippet.

Fernando

Public Function SeachQuery(ByVal Organisation As String, ByVal Firstname As String, ByVal Surnane As String) As List(Of OrgContactJoined)

    Dim results1 As List(Of OrgContactJoined) = _
        (From o In Me.ObjectContext.Organisations.Include("Contacts") _
         Join c In Me.ObjectContext.Contacts On o Equals c.Organisation _
         Where (o.OrganisationName.StartsWith(Organisation)) And c.Forename.StartsWith(Firstname) _
         Select New With { _
             .Organisation = o, _
             .Contact = c _
         }).ToList()

    Return results1

End Function

Public Class OrgContactJoined

    Private Org As Organisations
    Public Property Organisation() As Organisations
        Get
            Return Org
        End Get
        Set(ByVal value As Organisations)
            Org = value
        End Set
    End Property

    Private Cont As Contacts 
    Public Property Contact() As Contacts 
        Get
            Return Cont
        End Get
        Set(ByVal value As Contacts)
            Cont = value
        End Set
    End Property

End Class

Open in new window

Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:

This Code returns both the Organisations AND the Contacts fine (i.e each of the organisations have a list of contacts, accessed through the contacts property)
but I am only performing the filter on the organisation.


Am I mssing something? is Contacts not already a property of Organisation?
Public Property Contacts As EntityCollection(Of Contact)




 Public Function SeachQuery(ByVal Organisation As String, ByVal Firstname As String, ByVal Surnane As String) As IQueryable(Of Organisation)

        Dim results1 = From o In Me.ObjectContext.Organisations.Include("Contacts")
              Where (o.OrganisationName.StartsWith(Organisation))
              Select o

        Return CType(results1, IQueryable(Of Organisation))

    End Function

Open in new window

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The previous query you posted was not the same as your current and my statement hold true for that query.

To your question, "Am I mssing something? is Contacts not already a property of Organisation? ", The way the query is set up yes it should be., Is not the current query returning what you want?
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Sorry I was not clear :))  I was just posting this to say I can get the collection of organisation and contact i want.  

The code below is return all the results (I.e Organisations with their contacts) but I am ONLY quering the "Organisation name".   I need to extend this to include the firstname etc.  

This is were I start falling down.
Public Function SeachQuery(ByVal Organisation As String, ByVal Firstname As String, ByVal Surnane As String) As IQueryable(Of Organisation)

        Dim results1 = From o In Me.ObjectContext.Organisations.Include("Contacts")
              Where (o.OrganisationName.StartsWith(Organisation))
              Select o

        Return CType(results1, IQueryable(Of Organisation))

    End Function

Open in new window

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Are you using Visual Studio 2008?
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
2010
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi VolunteerDevelopmentAgency;

Try this code snippet out. Make sure to change ContactsNavigationPropertyName in the code snippet to the name of the navigation property that posts to the Contacts collection.

Fernando
Public Function SeachQuery(ByVal Organisation As String, ByVal Firstname As String, ByVal Surnane As String) As IQueryable(Of Organisation)

    Dim results1 = From o In Me.ObjectContext.Organisations.Include("Contacts")
                   Where o.OrganisationName.StartsWith(Organisation) And 
                         o.ContactsNavigationPropertyName.Any(Function(c) c.Forename.StartsWith(Firstname)) And
                         o.ContactsNavigationPropertyName.Any(Function(c) c.Surnane.StartsWith(Surnane)) 
                   Select o

        Return CType(results1, IQueryable(Of Organisation))

End Function

Open in new window

Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Almost :))

If I Search for Organisation = "S" Firstname="R" Surname = "V"
I get 2 organisations starting with "V" and both of which have contacts with forename's starting with "S"  and surnames starting with "R" but it is returning ALL contacts from these organisations.





Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi VolunteerDevelopmentAgency;

Try this code snippet out. Make sure to change ContactsNavigationPropertyName in the code snippet to the name of the navigation property that posts to the Contacts collection.

Fernando
Public Function SeachQuery(ByVal Organisation As String, ByVal Firstname As String, ByVal Surnane As String) As IQueryable(Of Organisation)

    Dim FilterContacts = Function(ByRef org As Organisations)
                           Dim cont = (From c In org.Contacts
                                       Where c.Forename.StartsWith(Firstname) And 
                                             c.Surnane.StartsWith(Surnane) 
                                       Select c).ToList()
                           org.ContactsNavigationPropertyName.Clear()
                           For Each contRec In cont
                               org.ContactsNavigationPropertyName.Add(contRec)
                           Next
                           Return org
                       End Function
                       
    Dim results1 = From o In Me.ObjectContext.Organisations.Include("Contacts")
                   Where o.OrganisationName.StartsWith(Organisation)
                   Select o

    For Each org In results1
        FilterContacts(org)
    Next
    
    Return org

End Function

Open in new window

Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Does not seem to be any different. Will keep trying.

Just so I am clear

I am getting this

Org      First name Surname
----------------------------------------
Vol1    Kevin Robinson
            Yvonne Dolan
           Jane Gribben
Vol2   Yulli Doran
           Sean Murphy

instead of
Org         First name Surname
----------------------------------------
Vol1       Yvonne      Dolan
Vol2        Yulli          Doran

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
You implemented the changes I made to SeachQuery function as shown in my post ID: 33199848?
Can you post your code of SeachQuery function as you implemented my code.
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
 Public Function SeachQuery(ByVal Organisation As String, ByVal Firstname As String, ByVal Surnane As String) As IQueryable(Of Organisation)

        Dim FilterContacts = Function(ByRef org As Organisation)
                                 Dim cont = (From c In org.Contacts
                                             Where c.Forename.StartsWith(Firstname) And
                                                   c.Surname.StartsWith(Surnane)
                                             Select c).ToList()
                                 org.Contacts.Clear()
                                 For Each contRec In cont
                                     org.Contacts.Add(contRec)
                                 Next
                                 Return org
                             End Function

        Dim results1 = From o In Me.ObjectContext.Organisations.Include("Contacts")
                       Where o.OrganisationName.StartsWith(Organisation)
                       Select o

        For Each org In results1
            FilterContacts(org)
        Next

        Return results1

    End Function
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
The below code works on my system so something else must be going on.
This query return all Organisations that starts with Organisation

Dim results1 = From o In Me.ObjectContext.Organisations.Include("Contacts")
               Where o.OrganisationName.StartsWith(Organisation)
               Select o

This code iterates through the result set of the above query and executes
the FilterContacts function.

For Each org In results1
    FilterContacts(org)
Next               

This function finds all Contacts that match the where clause then removes
all contacts from the collection attached to the Organisation object and 
then adding back those that matched the where clause. Which means that the
collection oc Contacts should only have those that match the where clasuse or empty
but not all.

Dim FilterContacts = Function(ByRef org As Organisation)
                         Dim cont = (From c In org.Contacts
                                     Where c.Forename.StartsWith(Firstname) And
                                           c.Surname.StartsWith(Surnane)
                                     Select c).ToList()
                         org.Contacts.Clear()
                         For Each contRec In cont
                             org.Contacts.Add(contRec)
                         Next
                         Return org
                     End Function

Open in new window

Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:

This bit does not seem to be returning any results

  Dim cont = (From c In org.Contacts
                                     Where c.Forename.StartsWith(Firstname) And
                                           c.Surname.StartsWith(Surnane)
                                     Select c).ToList()
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Well the very next statement clears the Contacts collection so if that were the case then it would not display any contacts at all.

Are you using a test database that you can upload to the web? if so I would be happy to do some testing.
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Yeah sorry it is currently not returning any contacts just the organisations.   Yeah I will upload database.
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Could I PM or email it to you?
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Zip the two database files .mdf and the .ldf and upload it to the support web site for EE.

Site ==>  http://www.ee-stuff.com
Your login if asked is the same as here the main site. Once logged in click on the "Expert Area" tab at the top of the page. Then on the next page click on the "Upload a new file" link. Then follow the instructions on the page. After the file has been uploaded it will return to you two links, post them here.
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Just as a point of reference this is the part of my windows application i am trying to recreate.
ContactDatabase1.jpg
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Try that again
ContactDatabase1.jpg
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Cant really post it publically.  I will have to remove all presonal data first.   I will create a restriced version and post that.  
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
On my Profile page you will find my Gmail account, you can use that if that will save you some time.
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Ok
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi VolunteerDevelopmentAgency;

Try this code out. I was not removing those Organisation's that there Contacts had no match. This should work, let me know.

Fernando
Public Function SeachQuery(ByVal Organisation As String, ByVal Firstname As String, ByVal Surnane As String) As IQueryable(Of Organisations)

    Dim FilterContacts = Function(ByRef org As Organisations)
                             Dim cont = (From c In org.Contacts
                                         Where c.Forename.StartsWith(Firstname) And
                                               c.Surname.StartsWith(Surnane)
                                         Select c).ToList()
                             If cont.Count() > 0 Then
                                 org.Contacts.Clear()
                                 For Each contRec In cont
                                     org.Contacts.Add(contRec)
                                 Next
                                 Return True
                             Else
                                 Return False
                             End If
                         End Function

    Dim results1 = (From o In Me.ObjectContext.Organisations.Include("Contacts")
                    Where o.Organisation.StartsWith(Organisation)
                    Select o).ToList()

    For idx As Integer = results1.Count() - 1 To 0 Step -1
        If Not FilterContacts(results1(idx)) Then
            results1.RemoveAt(idx)
        End If
    Next

    Return results1.AsQueryable()

End Function

Open in new window

Retired
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
This bit is still not returning any results.  I steped through and org.contacts did have contacts matching.

  Dim cont = (From c In org.Contacts
                                             Where c.Forename.StartsWith(Firstname) And
                                                   c.Surname.StartsWith(Surnane)
                                             Select c).ToList()
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
ites because its case sensitive.  ignore :))
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
ok

cont now has a count of 1 but now the highlighed bit is not getting hit.

If cont.Count() > 0 Then
     org.Contacts.Clear()
     
      For Each contact In cont
               org.Contacts.Add(contact)
      Next
      Return True
Else
        Return False
End If
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
What values are you using for Organisation, Firstname, andl Surnane so I can test here?
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Well i was looking for my self  Kevin Robinson  Volunteer Development Agency.  So K R V

I mean i was using the first letter just as a test but the whole name should work?


Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi Kevin;

I just ran the code I had posted and tried V for Organisations, K for Forename and R for Surname and got one record returned and that was yours.

Can you post the SeachQuery function as it was implemented in your code and the code where you call and use the its return value.

Fernando
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:

Private Sub btnsearch_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnsearch.Click
        Dim ctx As New ContactsDomainContext
       LoadOperation = ctx.Load(ctx.SeachQueryQuery(Me.txtOrganisation.Text,                 Me.txtFirstname.Text, Me.txtlastname.Text))
End Sub


Private Sub LoadOperationCompleted(ByVal sender As Object, ByVal e As EventArgs) Handles LoadOperation.Completed

   Dim LoadOperationCompleted As System.ServiceModel.DomainServices.Client.LoadOperation
   LoadOperationCompleted = sender
   Me.dgContacts.ItemsSource = LoadOperationCompleted.Entities

End Sub

Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
I tried to load your project but it seems I must be missing something can't find the following dll's

System.ServiceModel.DomainServices.EntityFramework
System.ServiceModel.DomainServices.Hosting
System.ServiceModel.DomainServices.Server

Are they part of Silverlight or something else?
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Ria services
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi Kevin;

I finally got the RIA service dll's loaded but still could not get it to work on my Windows 7 box but tried it on my Windows Vista and got it to run but could not break on breakpoints. The code I posted on ID: 33219362 does work on Windows Form app. The interface you posted on post ID: 33215828 looks like a Windows Form or WPF if so can you send me that project. I am not a Web developer or Silverlight person and just can't  get around it without spending to much time.

Fernando
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Hey thanks for all your work.  Let me spend some more time on the stuff you already posted.  You have already gone beyond the call of duty and I thank you.  I may be monday however before I will be spending more time on it.  I will post back when I have i working.

Again thanks
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi VolunteerDevelopmentAgency;

Have you had any luck getting this to work?

Fernando
Kevin RobinsonPrivate VB.NET Contractor

Author

Commented:
Is it ok if i keep this open for a few more days.  I will try to get back to it this week.  Thanks
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
That's not a problem, I just want to check in an see how things are going.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.