Operator '&&' cannot be applied to operands of type 'string'

where myMPI2.EntitySystemAcctNum ==  myECPatient2.pid.ToString() &&
 myMPI2 ==  myECdoctor2.NPI

need the double = or .Equals

Yes as above you need the double == as you're wanting to say that A should be equal to B, whereas your code currently just says that A is B which can't be properly used when comparing and doing a check like you have.

When I use the double = I then get this error message
Operator '&&' cannot be applied to operands of type 'bool' and 'eClinicalReadData.MPI'
Any ideas?

is the mpi property a string -- might have to do a tostring()

I beleive in Linq, you use the keyword "equals" as opposed to "==".

var demographlookup = from CRpatient in dcChartRelay2.PATIENTs
                                                       where CRpatient.MPI equals getMPI
                                                       select (CRpatient);

Select allOpen in new window

you can use and equals qualifier including == and .equals

Hi Kenny;

It should be something line this. But this also has a problem because on the third line  myMPI2 is of type MPI and you are trying to compare a row of MPI with an int.

     var patientlookup = from MPIREC in dcChartRelay.MPI
                         where myMPI2.EntitySystemAcctNum == myMPI4.DLnum &&
                         myMPI2 == myMPI4.NPI
                         select new { MPIREC };

The third row should be something like this:

                         myMPI2.XXX == myMPI4.NPI

where XXX is a property from the MPI table of type integer.

Fernando

Fernando,
When I try to access che dcChartRelay context, it tells me that it does not exist in the current context.  That is why i created a new dcChartRelay2 context.  Is there something I did to not allow access to the originial context?
Thanks
Kenny
 

Can you post the complete file?

Fernando,
Here is the whole thing.  I just do not understand why I do not have access to the datacontext that was setup in the beginning of the program.  That is why I thought I needed to create a second datacontext.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Reflection;
 
 
 
namespace eClinicalReadData
{
    class Program
    {
        static void Main(string[] args)
        {
 
 
            DataClasses1DataContext dcChartRelay = new DataClasses1DataContext();
 
            DataClasses2DataContext dceClinicalWorks = new DataClasses2DataContext();
            
 
            // Query to get all patients from the Patients table
            // that have not been flagged as imported
            var patients = from p in dceClinicalWorks.patients
                           where p.Imported == null
                           join d in dceClinicalWorks.doctors on p.doctorId equals d.doctorID
                           join u in dceClinicalWorks.users on p.pid equals u.uid
                           join i in dceClinicalWorks.patientinfos on p.pid equals i.pid
                           select new { d, u, i, p };
 
 
            // Process each row of the result set in the foreach loop
            foreach (var patient in patients)
            {
 
                // ------- eClinical Tables
                patientinfo myPatientInfo = new patientinfo();
                user myUser = new user();
 
 
                // ------- Chart Relay Tables
                MPI myMPI = new MPI();
                PATIENT myCRpatient = new PATIENT();
                PATIENT_PHONE_NUMBER myPatPhones = new PATIENT_PHONE_NUMBER();
                Patient_Address myPatAddresses = new Patient_Address();
                PATIENTS_ATTENDED_BY_DOCTOR myPatAttByDoc = new PATIENTS_ATTENDED_BY_DOCTOR();
                PATIENT_IN myPatIns = new PATIENT_IN();
                EMPLOYER myPatEmployers = new EMPLOYER();
                BillingAlert myPatBillAlerts = new BillingAlert();
                EligibilityStatus myPatElgStatus = new EligibilityStatus();
 
 
 
 
                // ----------------Write the MPI Record 
                Guid initGuid = Guid.NewGuid();
                myMPI.MPI1 = initGuid;
                myMPI.NPI = Convert.ToInt32(patient.d.NPI);
                myMPI.ModifiedDate = DateTime.Today;
                myMPI.EntitySystemAcctNum = patient.p.pid.ToString();
                myMPI.EntitySystemModifiedDate = DateTime.Today;
 
 
 
 
                // ----------------Write the Patient Demographic Record
 
                myCRpatient.MPI = initGuid;
                myCRpatient.SEX = patient.u.sex[0];
                string ssn = patient.u.ssn;
                ssn = patient.u.ssn.Sanitize();
                myCRpatient.SSN = patient.u.ssn;
                myCRpatient.LAST_NAME = patient.u.ulname;
                myCRpatient.FIRST_NAME = patient.u.ufname;
                myCRpatient.EMAIL = patient.u.uemail;
                myCRpatient.RACE = patient.p.race;
                myCRpatient.UPDATED_DATE_TIME = DateTime.Today;
                myCRpatient.PRIMARY_LANGUAGE = patient.p.language;
                myCRpatient.MARITAL_STATUS = patient.p.maritalstatus;
 
                string tmpDateTime = patient.p.deceasedDate.ToString();
                if (tmpDateTime.Length > 0)
                {
                    DateTime tmpDateTime2;
 
                    if (DateTime.TryParse(patient.p.deceasedDate.ToString(), out tmpDateTime2))
                    {
                        myCRpatient.DECEASED_DATE = tmpDateTime2;
                    }
                    else
                    {
                        // failed, invalid date format
                    }
                }
                else
                {
                    // failed, DateTime is null
                }
 
                string tmp2DateTime = patient.u.dob.ToString();
                if (tmp2DateTime.Length > 0)
                {
                    DateTime tmp2DateTime2;
 
                    if (DateTime.TryParse(patient.u.dob.ToString(), out tmp2DateTime2))
                    {
                        myCRpatient.DATE_OF_BIRTH = tmp2DateTime2;
                    }
                    else
                    {
                        // failed, invalid date format
                    }
                }
                else
                {
                    // failed, DateTime is null
                }
 
                //  ------------  Fields that would be nice to get
                //
                // myCRpatient.PRIMARY_CARE_PHYSICIAN_NPI = patient.i.------Add to physician table
                //myCRpatient.ORGAN_DONAR = patient.p.or
                // myCRpatient.DISABILITY_PERCENT = patient.p.disa
                // myCRpatient.PREFIX = patient.p.pre
                // myCRpatient.SUFFIX =
                // myCRpatient.COUNTRY = patient.p.cou
                // myCRpatient.RELIGION = patient.p.re
                // myCRpatient.FACILITY_TIN = patient.i.---------Will be hard coded for each program
                //myCRpatient.SELF_EMPLOYED = patient.p.em
                //myCRpatient.UPDATED_BY = patient.p.m
                //myCRpatient.HEAD_OF_HOUSEHOLD = patient.p.hea
                // myCRpatient.PATIENT_RELATIONSHIP_TO_INSURED = patient.p.rel
                //  myCRpatient.COUNTY = patient.u.ucoun ------  Do a lookup based on zip 
                //myCRpatient.DATE_OF_BIRTH = patient.u.dob;
 
 
 
 
 
 
 
                // ----------------Write the Patient_Phone_Numbers Record
                myPatPhones.MPI = initGuid;
                myPatPhones.SourceOfRecord = Convert.ToInt32(patient.d.NPI);
                myPatPhones.TypeOfNumber = "Home";
                myPatPhones.DateOfRecord = DateTime.Today;
 
                int myInt;
                if (Int32.TryParse(patient.u.upPhone, out myInt))
                {
                    // Success! myInt now contains the numeric value of myString
                    myPatPhones.PhoneNumber = myInt;
                }
                else
                {
                    // Failed! MyInt is not defined.
                }
 
 
 
 
                // ----------------Write the Patient_Addresses Record
                myPatAddresses.MPI = initGuid;
                myPatAddresses.Source = Convert.ToInt32(patient.d.NPI);
                myPatAddresses.Address = patient.p.straddress;
                myPatAddresses.City = patient.p.city;
                myPatAddresses.State = patient.p.state;
                myPatAddresses.Zip = patient.p.zip;
                myPatAddresses.DateOfRecord = DateTime.Today;
 
 
 
 
 
 
                // ----------------Write the Patient Attended By Doctor Record
                myPatAttByDoc.MPI = initGuid;
                myPatAttByDoc.Fname = patient.d.PrintName;
                myPatAttByDoc.DateOfRecord = DateTime.Today;
 
                int myPhysNPI;
                if (Int32.TryParse(patient.d.NPI.ToString(), out myPhysNPI))
                {
                    // Success! myInt now contains the numeric value of myString
                    myPatAttByDoc.PHYSICIAN_NPI = myPhysNPI.ToString();
                }
                else
                {
                    // Failed! MyInt is not defined.
                }
 
                //myPatAttByDoc.Phone = patient.d.??
 
                int myPhysPatAcctNum;
                if (Int32.TryParse(patient.p.pid.ToString(), out myPhysPatAcctNum))
                {
                    // Success! myInt now contains the numeric value of myString
                    myPatAttByDoc.PhysPatAcctNum = myPhysPatAcctNum.ToString();
                }
                else
                {
                    // Failed! MyInt is not defined.
                }
 
 
 
 
 
                // ----------------Write the Employers Record
                myPatEmployers.MPI = initGuid;
                myPatEmployers.SourceNPI = Convert.ToInt32(patient.d.NPI);
                myPatEmployers.CompanyName = patient.p.employername;
                myPatEmployers.ADDRESS_1 = patient.p.employeraddress;
                myPatEmployers.ADDRESS_2 = patient.p.employeraddress2;
                myPatEmployers.CITY = patient.p.employercity;
                myPatEmployers.STATE = patient.p.employerstate;
                myPatEmployers.ZIP = patient.p.employerzip;
                myPatEmployers.PHONE = patient.p.employerPhone;
                // myPatEmployers.OCCUPATION = patient.p.
                // myPatEmployers.CONTACT_FIRST_NAME = patient.p.
 
 
 
 
 
 
                // ----------------Write the Patient Insurance Records
 
                // setup foreach loop to lookup Insurance Comp NPI and adress info
                var ecinscomps = from ecins in dceClinicalWorks.insurances
                                 where ecins.insId == patient.p.insId
                                 select new { ecins };
 
                // Process each Insurance Record row of the result 
                foreach (var insurance in ecinscomps)
                {
                    myPatIns.MPI = initGuid;
                    myPatIns.ProviderNPI = Convert.ToInt32(patient.d.NPI);
 
                    int myInsId;
                    if (Int32.TryParse(patient.p.insId.ToString(), out myInsId))
                    {
                        // Success! myInt now contains the numeric value of myString
                        myPatIns.Id = myInsId.ToString();
                    }
                    else
                    {
                        // Failed! MyInt is not defined.
                    }
 
                    myPatIns.GroupId = patient.p.insgroupno;
                    myPatIns.ADDRESS_1 = insurance.ecins.insuranceaddress;
                    myPatIns.ADDRESS_2 = insurance.ecins.insuranceaddress2;
                    myPatIns.CITY = insurance.ecins.insurancecity;
                    myPatIns.STATE = insurance.ecins.insurancestate;
                    myPatIns.ZIP = insurance.ecins.insurancezip;
                    myPatIns.PHONE = insurance.ecins.insurancephone;
                    myPatIns.Phone2 = insurance.ecins.insurancephone2;
                    myPatIns.FAX_PHONE = insurance.ecins.insurancefax;
                    myPatIns.ClaimOfficePhone = insurance.ecins.ClaimOfficeNumber;
                    myPatIns.email = insurance.ecins.insuranceemail;
                    myPatIns.MediGapId = insurance.ecins.MedigapID;
                    myPatIns.website = insurance.ecins.website;
                    //myPatIns.CoPay = patient.p.inscopay;
                }
 
 
 
 
                // ----------------Write the Billing Alert Record
 
                short blalrt;
                blalrt = patient.p.BillingAlert;
                short myBillAlert;
                if (short.TryParse(patient.p.BillingAlert.ToString(), out myBillAlert))
                {
                    // Success! myInt now contains the numeric value of myString
                    myPatBillAlerts.BillingAlert1 = myBillAlert.ToString();
                }
                else
                {
                    // Failed! MyInt is not defined.
                }
 
 
                myPatBillAlerts.BillingAlertNotes = patient.p.BillingAlertNotes;
 
                short myCollection;
                if (short.TryParse(patient.p.BillingAlert.ToString(), out myCollection))
                {
                    // Success! myInt now contains the numeric value of myString
                    myPatBillAlerts.GivenToCollection = myCollection.ToString();
                }
                else
                {
                    // Failed! MyInt is not defined.
                }
 
                myPatBillAlerts.GivenDate = patient.p.givenDate;
                myPatBillAlerts.CollectionAgencyStatus = patient.p.CollectionAgencyStatus;
 
 
 
 
 
 
                // ----------------Write the Patient Insurance Elgibility Record
 
                myPatElgStatus.ElgibilityStatus = patient.u.EligibilityStatus;
                myPatElgStatus.EligibilityMessage = patient.u.EligibilityMessage;
                myPatElgStatus.DateEligibilityObtained = DateTime.Today;
 
 
                // PastHistory
                // Pharmacy Name
                // Surgical History
                // Hospitalization
                // Medications
                // Pharmacy Fax
                // Notes
                // Deceased Notes
                // Student Status
                // PicFileName
                // SelfPay
 
                // =======================================================
                // Call Updated Record Function for each Table 
                // =======================================================
                UpdateTable(dcChartRelay, myMPI);
                UpdateTable(dcChartRelay, myCRpatient);
                UpdateTable(dcChartRelay, myPatIns);
                UpdateTable(dcChartRelay, myPatBillAlerts);
                UpdateTable(dcChartRelay, myPatEmployers);
                UpdateTable(dcChartRelay, myPatPhones);
                UpdateTable(dcChartRelay, myPatAddresses);
                UpdateTable(dcChartRelay, myPatAttByDoc);
                UpdateTable(dcChartRelay, myPatElgStatus);
 
            }
        }
 
        // --- Check DB Field Lenght Info
 
 
 
        private static void PrintFields(Object pRecord)
        {
            // Print the header of the report
            Console.WriteLine("Length   Field Name");
            FieldInfo[] fInfos = pRecord.GetType().GetFields(BindingFlags.Public |
                                                             BindingFlags.NonPublic |
                                                             BindingFlags.Instance);
            // Iterate through all the fields and print the length of each string
            // or binary filed.
            foreach (FieldInfo fInfo in fInfos)
            {
                // String Fields
                if (fInfo.FieldType == typeof(string))
                {
                    // Get the value of the field
                    Object value = fInfo.GetValue(pRecord);
                    // If value has data print its field lentgth and name
                    if (value != null)
                    {
                        Console.WriteLine(String.Format("{0,-8} {1}",
                            value.ToString().Length, fInfo.Name));
                    }
                }
                // Binary Fields
                if (fInfo.FieldType == typeof(Binary))
                {
                    Binary value = (Binary)fInfo.GetValue(pRecord);
                    if (value != null)
                    {
                        Console.WriteLine(String.Format("{0,-8} {1}",
                            value.Length, fInfo.Name));
                    }
                }
            }
        }
 
 
 
 
        //-----------Update Table Code
 
        private static void UpdateTable(DataClasses1DataContext db, Object record)
        {
            // Get the type of the record to insert
            string type = record.GetType().Name;
            
 
            // Then one if statement for each table type
            if (type == "MPI")
                db.MPIs.InsertOnSubmit(record as MPI);
 
            if (type == "PATIENT")
                db.PATIENTs.InsertOnSubmit(record as PATIENT);
 
            if (type == "EMPLOYER")
                db.EMPLOYERs.InsertOnSubmit(record as EMPLOYER);
 
            if (type == "PATIENT_PHONE_NUMBER")
                db.PATIENT_PHONE_NUMBERs.InsertOnSubmit(record as PATIENT_PHONE_NUMBER);
 
            if (type == "Patient_Address")
                db.Patient_Addresses.InsertOnSubmit(record as Patient_Address);
 
            if (type == "PATIENTS_ATTENDED_BY_DOCTOR")
                db.PATIENTS_ATTENDED_BY_DOCTORs.InsertOnSubmit(record as PATIENTS_ATTENDED_BY_DOCTOR);
 
            if (type == "PATIENT_IN")
                db.PATIENT_INs.InsertOnSubmit(record as PATIENT_IN);
 
            if (type == "BillingAlert")
                db.BillingAlerts.InsertOnSubmit(record as BillingAlert);
 
            if (type == "EligibilityStatus")
                db.EligibilityStatus.InsertOnSubmit(record as EligibilityStatus);
 
 
 
            // ---------------  Write MPI Record -------------------------
 
            try
            {
                // Attempt to update the database
                db.SubmitChanges();
            }
            catch (DuplicateKeyException dke)
            {
                // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                // in the DataContect and not the database, reason why it was not catching it.
                // Remove record from DataContext and continue.
                switch (type)
                {
                    case "MPI":
                        db.MPIs.DeleteOnSubmit(record as MPI);
                        break;
                }
            }
            catch (SqlException sqle)
            {
                // This is a duplicate record found in the databas, remove record from DataContext.
                switch (type)
                {
                    case "MPI":
                        db.MPIs.DeleteOnSubmit(record as MPI);
                        break;
                }
 
 
 
                // ---------------  Write Phone Number Record -------------------------
 
 
                // Now Insert the Patient Phone Number Table Record
 
                // Get the type of the record to insert
 
                // Then one if ststement for each table type
                if (type == "PATIENT_PHONE_NUMBER")
                    db.PATIENT_PHONE_NUMBERs.InsertOnSubmit(record as PATIENT_PHONE_NUMBER);
 
 
                try
                {
                    // Attempt to update the database
                    db.SubmitChanges();
                }
                catch (DuplicateKeyException dke)
                {
                    // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                    // in the DataContect and not the database, reason why it was not catching it.
                    // Remove record from DataContext and continue.
                    switch (type)
                    {
                        case "PATIENT_PHONE_NUMBER":
                            db.PATIENT_PHONE_NUMBERs.DeleteOnSubmit(record as PATIENT_PHONE_NUMBER);
                            break;
                    }
                }
                catch (SqlException phonerecsExc)
                {
                    // This is a duplicate record found in the databas, remove record from DataContext.
                    switch (type)
                    {
                        case "PATIENT_PHONE_NUMBER":
                            db.PATIENT_PHONE_NUMBERs.DeleteOnSubmit(record as PATIENT_PHONE_NUMBER);
                            break;
                    }
 
 
 
 
                    // ---------------  Write Patient Addresse Record -------------------------
 
 
                    //-----------------------
                    // Now Insert the Patient Addresses Table Record
 
                    // Get the type of the record to insert
 
                    // Then one if ststement for each table type
                    if (type == "Patient_Address")
                        db.Patient_Addresses.InsertOnSubmit(record as Patient_Address);
 
 
 
                    try
                    {
                        // Attempt to update the database
                        db.SubmitChanges();
                    }
                    catch (DuplicateKeyException dke)
                    {
                        // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                        // in the DataContect and not the database, reason why it was not catching it.
                        // Remove record from DataContext and continue.
                        switch (type)
                        {
                            case "Patient_Address":
                                db.Patient_Addresses.DeleteOnSubmit(record as Patient_Address);
                                break;
                        }
                    }
                    catch (SqlException pataddrExc)
                    {
                        // This is a duplicate record found in the databas, remove record from DataContext.
                        switch (type)
                        {
                            case "Patient_Address":
                                db.Patient_Addresses.DeleteOnSubmit(record as Patient_Address);
                                break;
                        }
 
 
                        // ---------------  Write Patient Attended By Record -------------------------
 
 
                        //-------------------------------------------------------
                        //-----------------------
                        // Now Insert the Doctor Attended By Patient Table Record
 
                        // Get the type of the record to insert
 
                        // Then one if ststement for each table type
                        if (type == "PATIENTS_ATTENDED_BY_DOCTOR")
                            db.PATIENTS_ATTENDED_BY_DOCTORs.InsertOnSubmit(record as PATIENTS_ATTENDED_BY_DOCTOR);
 
                        try
                        {
                            // Attempt to update the database
                            db.SubmitChanges();
                        }
                        catch (DuplicateKeyException dke)
                        {
                            // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                            // in the DataContect and not the database, reason why it was not catching it.
                            // Remove record from DataContext and continue.
                            switch (type)
                            {
                                case "PATIENTS_ATTENDED_BY_DOCTOR":
                                    db.PATIENTS_ATTENDED_BY_DOCTORs.DeleteOnSubmit(record as PATIENTS_ATTENDED_BY_DOCTOR);
                                    break;
                            }
                        }
                        catch (SqlException docattndbyExc)
                        {
                            // This is a duplicate record found in the databas, remove record from DataContext.
                            switch (type)
                            {
                                case "PATIENTS_ATTENDED_BY_DOCTOR":
                                    db.PATIENTS_ATTENDED_BY_DOCTORs.DeleteOnSubmit(record as PATIENTS_ATTENDED_BY_DOCTOR);
                                    break;
                            }
 
 
 
                            // ---------------  Write Patient Demographics Record -------------------------
 
 
 
                            //----------Code to check field length
                            //==================================
                            //PrintFields(record);    // Will report the length of all String and Binary fields that have values
                            //Console.ReadLine();   // Will pause the program now verify that non of the file are longer then define in DB
                            //===================================
                            // Now Insert the Patient Demographic By Patient Table Record
 
                            // Lookup the patient in the MPI table by the Provider NPI and Entity System
                            // acct number.  If exist, obtain MPI from record.
                            // If the MPI was obtained, update the record in the Chart Relay patient table
                            //  If no record was found in the MPI table, insert a new record into the 
                            // Chart Relay patient table.
                            // If it exist in the MPI table
 
                            // Then one if statement for each table type
 
                            DataClasses1DataContext dcChartRelay2;
                            if (type == "PATIENT")
 
 
                                dcChartRelay2 = new DataClasses1DataContext();
 
                                 MPI myMPI2 = new MPI();
                                 patient myECPatient2 = new patient();
                                 doctor myECdoctor2 = new doctor();
 
                                 
                                
                                 var patientlookup = from MPIREC in dcChartRelay.MPI
                                 where myMPI2.EntitySystemAcctNum == myECPatient2.pid.ToString() &&
                                 myMPI2 = myECdoctor2.NPI
                                 select new { MPIREC };
 
                                 Guid getMPI;
                                 getMPI = myMPI2.MPI1;
 
 
                                 var demographlookup = from CRpatient in dcChartRelay2.PATIENTs
                                                       where CRpatient.MPI == getMPI
                                                       select (CRpatient);
        
                            
                            if(demographlookup.Count() ==0)
                            {
                               // No Record was found, Insert new record
                               db.PATIENTs.InsertOnSubmit(record as PATIENT); 
                            }
                            else
                            {
                                // Record was found based on MPI search, Upate current record
                                db.SubmitChanges();
                            }
 
 
    
                            // ------------- Old Code Below for writing patient record
                                 
                                 db.SubmitChanges();
 
 
                                db.PATIENTs.InsertOnSubmit(record as PATIENT);
        
                            try
                            {
                                // Attempt to update the database
                                db.SubmitChanges();
                            }
                            catch (DuplicateKeyException duppatexc)
                            {
                                // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                                // in the DataContect and not the database, reason why it was not catching it.
                                // Remove record from DataContext and continue.
                                switch (type)
                                {
                                    case "PATIENT":
                                        db.PATIENTs.DeleteOnSubmit(record as PATIENT);
                                        break;
                                }
                            }
                            catch (SqlException patientExc)
                            {
                                // This is a duplicate record found in the databas, remove record from DataContext.
                                switch (type)
                                {
                                    case "PATIENT":
                                        db.PATIENTs.DeleteOnSubmit(record as PATIENT);
                                        break;
                                }
 
 
 
                                // ---------------  Write Insurance Company Record -------------------------
 
 
                                // ---------------- PATIENT INSURANCE COMPANIES
                                //-------------------------------------------------------
                                //-----------------------
                                // Now Insert the Patient Demographic By Patient Table Record
 
                                // Get the type of the record to insert
 
                                // Then one if ststement for each table type
                                if (type == "PATIENT_IN")
                                    db.PATIENT_INs.InsertOnSubmit(record as PATIENT_IN);
 
 
                                try
                                {
                                    // Attempt to update the database
                                    db.SubmitChanges();
                                }
                                catch (DuplicateKeyException duppatexc)
                                {
                                    // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                                    // in the DataContect and not the database, reason why it was not catching it.
                                    // Remove record from DataContext and continue.
                                    switch (type)
                                    {
                                        case "PATIENT_IN":
                                            db.PATIENT_INs.DeleteOnSubmit(record as PATIENT_IN);
                                            break;
                                    }
                                }
                                catch (SqlException patientInsExc)
                                {
                                    // This is a duplicate record found in the databas, remove record from DataContext.
                                    switch (type)
                                    {
                                        case "PATIENT_IN":
                                            db.PATIENT_INs.DeleteOnSubmit(record as PATIENT_IN);
                                            break;
                                    }
 
 
 
                                    // ---------------  Write Patient Employer Record -------------------------
 
                                    // ---------------- PATIENT EMPLOYER
                                    //-------------------------------------------------------
                                    //-----------------------
                                    // Now Insert the Patient Demographic By Patient Table Record
 
                                    // Get the type of the record to insert
 
                                    // Then one if ststement for each table type
                                    if (type == "EMPLOYER")
                                        db.EMPLOYERs.InsertOnSubmit(record as EMPLOYER);
 
 
                                    try
                                    {
                                        // Attempt to update the database
                                        db.SubmitChanges();
                                    }
                                    catch (DuplicateKeyException duppatexc)
                                    {
                                        // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                                        // in the DataContect and not the database, reason why it was not catching it.
                                        // Remove record from DataContext and continue.
                                        switch (type)
                                        {
                                            case "EMPLOYER":
                                                db.EMPLOYERs.DeleteOnSubmit(record as EMPLOYER);
                                                break;
                                        }
                                    }
                                    catch (SqlException patientEmployerExc)
                                    {
                                        // This is a duplicate record found in the databas, remove record from DataContext.
                                        switch (type)
                                        {
                                            case "EMPLOYER":
                                                db.EMPLOYERs.DeleteOnSubmit(record as EMPLOYER);
                                                break;
                                        }
 
                                        // ---------------  Write Patient Billing Alert Record -------------------------
 
                                        // ---------------- PATIENT Billing Alert
                                        //-------------------------------------------------------
                                        //-----------------------
                                        // Now Insert the Patient Demographic By Patient Table Record
 
                                        // Get the type of the record to insert
 
                                        // Then one if ststement for each table type
                                        if (type == "BillingAlert")
                                            db.BillingAlerts.InsertOnSubmit(record as BillingAlert);
 
 
                                        try
                                        {
                                            // Attempt to update the database
                                            db.SubmitChanges();
                                        }
                                        catch (DuplicateKeyException duppatexc)
                                        {
                                            // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                                            // in the DataContect and not the database, reason why it was not catching it.
                                            // Remove record from DataContext and continue.
                                            switch (type)
                                            {
                                                case "BillingAlert":
                                                    db.BillingAlerts.DeleteOnSubmit(record as BillingAlert);
                                                    break;
                                            }
                                        }
                                        catch (SqlException patientBillAlertExc)
                                        {
                                            // This is a duplicate record found in the databas, remove record from DataContext.
                                            switch (type)
                                            {
                                                case "BillingAlert":
                                                    db.BillingAlerts.DeleteOnSubmit(record as BillingAlert);
                                                    break;
                                            }
                                        }
 
 
                                        // ---------------  Write Patient Eligibility  Record -------------------------                 
                                        // ---------------- PATIENT Eligibility Status
                                        //-------------------------------------------------------
                                        //-----------------------
                                        // Now Insert the Patient Demographic By Patient Table Record
 
                                        // Get the type of the record to insert
 
                                        // Then one if ststement for each table type
                                        if (type == "EligibilityStatus")
                                            db.EligibilityStatus.InsertOnSubmit(record as EligibilityStatus);
 
 
                                        try
                                        {
                                            // Attempt to update the database
                                            db.SubmitChanges();
                                        }
                                        catch (DuplicateKeyException duppatexc)
                                        {
                                            // This DuplicateKeyException is a Linq exception for duplicate keys that exist
                                            // in the DataContect and not the database, reason why it was not catching it.
                                            // Remove record from DataContext and continue.
                                            switch (type)
                                            {
                                                case "EligibilityStatus":
                                                    db.EligibilityStatus.DeleteOnSubmit(record as EligibilityStatus);
                                                    break;
                                            }
                                        }
                                        catch (SqlException patientElgibilityStatusExc)
                                        {
                                            // This is a duplicate record found in the databas, remove record from DataContext.
                                            switch (type)
                                            {
                                                case "EligibilityStatus":
                                                    db.EligibilityStatus.DeleteOnSubmit(record as EligibilityStatus);
                                                    break;
                                            }
 
 
                                        }
 
 
 
                                    }
                                }
                            }
                        }
 
                    }
                }
            }
        }
    }
 
 
}

Select allOpen in new window

Fernando,
I think this is what it needs to be but it still does not give me the fiels I need to access from the tables.  What am I doing wrong?

var patientlookup = from MPIREC in db.MPIs 
where db.MPIs.EntitySystemAcctNum == db.PATIENTs.pid.ToString() && 
db.MPIs = db.doctor.NPI 
select new { MPIREC }; 
Guid getMPI; 
getMPI = myMPI2.MPI1; 
  
var demographlookup = from CRpatient in db.PATIENTs 
where db.PATIENTs.MPI == getMPI 
select (CRpatient); 
 

Select allOpen in new window

Hi Kenny;

In the code snippet I cut out most of the code to show why you are not getting access to the DataContext defined in the Main() procedure. The last line in Main() calls the function UpdateTable and passes two parameters. The first is the DataContext defined in the Main function and the second the record to be updated in the database. The first is dcChartRelay which is the DataContext defined at the top of Main and the second is myMPI which is an instance of MPI. When control is passed to the function UpdateTable the two parameters are renamed. The DataContext dcChartRelay defined in Main is now called db and second parameter myMPI is now called record. So now anytime you need to access the DataContext dcChartRelay in UpdateTable function you use the variable db and anytime you need to access myMPI instance you use the variable record.

When you define a variable inside a block, a block of code is any statements between { and } those variables are not visible outside of that block and this is the reason why you were not getting access to the DataContext defined in the Main function/Block from the UpdateTable function/Block.

Fernando

class Program
{
    static void Main(string[] args)
    {
        DataClasses1DataContext dcChartRelay = new DataClasses1DataContext();
        DataClasses2DataContext dceClinicalWorks = new DataClasses2DataContext();            
         
         // More Code Here
         
         // Call Updated Record Function for each Table 
         // =======================================================
         UpdateTable(dcChartRelay, myMPI);
    } 
 
    private static void UpdateTable(DataClasses1DataContext db, Object record)
    {
        // To get access to the DataContext use db while in this function
        var patientlookup = from MPIREC in db.MPI
                            select new { MPIREC };     
    }
}

Select allOpen in new window

Hi Kenny;

Can you explain in words what you wish to accomplish with these two queries so I can better understand.

Fernando

var patientlookup = from MPIREC in db.MPIs 
                    where db.MPIs.EntitySystemAcctNum == db.PATIENTs.pid.ToString() && 
                          db.MPIs == db.doctor.NPI 
                    select new { MPIREC }; 
 
var demographlookup = from CRpatient in db.PATIENTs 
                      where db.PATIENTs.MPI == getMPI 
                      select (CRpatient); 

Select allOpen in new window

Basically, I need to search the Chart Relay MPI table based on the EntityAccountNumber and Provider NPI.  If it finds a record, that means I probably have a demographic record on file in the Chart Relay Patient table.  In that case I need to obtain the patients MPI from the Chart Relay MPI table.  Then search the Chart Relay Patient table for this MPI.  If the record is found, I need to update those fields on that record with the new data obtained from the eClinical Patient table.  If there was no record found, then I need to create a new demographics record for that patient in the Chart Relay Patient table.
Thanks
Kenny
 

Fernando,
When I type in record. I expected to see the fields from the MPI table but intellisense did not show them.  Is there something that I am missing?
Thanks
Kenny
 

If you enter the variable name record in the function UpdateTable you should see all the fields from that row of the table. Does intellisense display at all or it displays but none of the fields are showing?

When I type
record.
I get these options
Equals
Gethash Code
GetType
ToString
 
That is all I see when this is done in the Update function.
Thanks
Kenny
 

Fernando,
I tried

MPI recordMPI = (MPI) record;
and got the following error message
Embedded statement cannot be a declaration or labeled statement  
Thanks
Kenny
 

Hi Kenny;

p_davis is correct about that error. Also remember what I said about code in blocks, here is an example, read the Console.WriteLine statements

// Beginning of a code block
{
    bool x = true;
    // This line of code will give an error "The name 'y' does not exist in the current context"
    Console.WriteLine("Variable y can NOT be seen from here = {0}", y);  
    // Beginning of a code block with in another code block
    {
        bool y = true;
        Console.WriteLine("Variable y can be seen from here = {0}", y);
        Console.WriteLine("Variable x can be seen from here = {0}", x);
    }
}

Fernando

Thank you both for sticking with me.  It is so nice to know that there are friends out there who know what you are going through.  Do either one of you have a C# book in general or a C# on LINQ book that just turned all the lights on for you when you were learning C# and LINQ?

Thanks
Kenny

**in response to the author comments:**

books have hardly ever done me any good. they are usually way to specific or way to general to be of any use, on their own. i only use them as a reference, and infrequently at that.  digging in, giving myself goals, and challenging myself to find different ways to achieve those goals has always served me well. websites like ee and msdn are tools that i use frequently.

thanks for the points, good luck.

Hi Kenny;

I do not know of any one book that as you say turn the lights on but I have read the following and they do a very good job.

Pro C# 2008 and the .Net 3.5 Platform
By Andrew Troelsen

LINQ Unleashed For C#
By Paul Kimmel

Fernando

Thank you.  I will take a look at them.
Thanks
Kenny