How do you create a Join statement using LINQ

I am trying to create a join statement using LINQ.  I tried using the example from Microsoft but get an error message.  Can someone point out where I am going wrong.  The demonstration I tried to use from MS was

http://msdn.microsoft.com/en-us/vbasic/bb688085.aspx

Maybe you can only do this in Visual Basic and not C#.  I am not sure

Here is my code that I tried to setup.


Basically, I am trying to create a join between the Patient table and the MPI table using MPI and the key field between the join.



The error messages I get are

1) A query body must end with a select clause or a group clause

2) Invalid expression term 'in'      

3) ; expected      

Here is my code
      

protected void UltraWebGridPatSrch_InitializeDataSource(object sender, UltraGridEventArgs e)
        {
            bool authenticated;
            authenticated = Convert.ToBoolean(Session["authenticated"]);
 
            if (authenticated)
            {
 
 
                RadRelayDataClassDataContext dcRadRelay = new RadRelayDataClassDataContext();
 
                // Read  Table "Providers"
 
                int RefDocPracticeTIN;
                RefDocPracticeTIN = Convert.ToInt32(Session.Contents["RefDocPracticeTIN"].ToString());
 
                var PatAttndByDocResult = from patattndbydocdata in dcRadRelay.PATIENTS_ATTENDED_BY_DOCTORs
                                          where patattndbydocdata.PracticeTIN == RefDocPracticeTIN
 
                                          select new { patattndbydocdata };
 
                int patattndbydocCount = PatAttndByDocResult.Count();    // <===== Added this line to get total count here
 
                if (patattndbydocCount == 0)                    // <===== Changed this line so that the query does not get executed again
                {
                    // No user record found
                }
                else
                {
                    foreach (var PatAttndByDocResultRecord in PatAttndByDocResult)
                    {
ERRORS Occur on the NEXT LINE BELOW------------------------
                        var PatientResult = from patientdata in dcRadRelay.PATIENTs, mpidata in dcRadRelay.MPIs
                                            where patientdata.MPI == PatAttndByDocResultRecord.patattndbydocdata.MPI &&
                                            (patientdata.FIRST_NAME == tbDocFname.Text ||
                                            patientdata.LAST_NAME == tbDocLname.Text ||
                                            patientdata.ADDRESS1 == tbDocLname.Text ||
                                            patientdata.CITY == tbDocCity.Text ||
                                            patientdata.PHONE == tbDocState.Text ||
                                            patientdata.SSN == tbDocZip.Text )

Select allOpen in new window