LINQ Query will not hold more than one record at a time. How do you get it to hold multiple records

I have setup a primary loop and a secondary loop using LINQ and for next loops.  In the first loop, the result variable shows that it has 7 records in it.  I then use this result set to lookup another table based on the data in the first query.  When the application loops through the secondary table, it finds the first record that I am actually looking for, next it loops through 6 more times.  Each of those records are not a match.  The end result is a query result with no data.  Then I try to bind that query variable to a datagrid but obviosuly it displays no data.  Can someone point out what I am doing wrong in the secondary loop?
var PatAttndByDocResult = from patattndbydocdata in dcRadRelay.PATIENTS_ATTENDED_BY_DOCTORs
                                          where patattndbydocdata.PracticeTIN == MyGlobalVars.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 record found
                }
                else
                {
                    foreach (var PatAttndByDocResultRecord in PatAttndByDocResult)
                    {
 
 
                        var PatientResult = from patientdata in dcRadRelay.PATIENTs
                                            where PatAttndByDocResultRecord.patattndbydocdata.MPI == patientdata.MPI &&
                                            (patientdata.FIRST_NAME == tbPatFname.Text ||---<<-------First record matches here-----
                                            patientdata.LAST_NAME == tbPatLname.Text ||
                                            patientdata.ADDRESS1 == tbPatAddr.Text ||
                                            patientdata.CITY == tbPatCity.Text ||
                                            patientdata.PHONE == tbPatPhone.Text ||
                                            patientdata.SSN == tbDocZip.Text)
                                            
 
 
                                            select new { patientdata.FIRST_NAME, patientdata.LAST_NAME, patientdata.DATE_OF_BIRTH, patientdata.ADDRESS1, patientdata.SEX };
 
                        this.UltraWebGridPatSrch.DataSource = PatientResult;----<<<<<----First Record shows in PatientResult

Open in new window

kwh3856Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
If you query is always returning one  row:
do something as :
   else
                {
     PATIENT  patientResult_DS = new PATIENT();
                    foreach (var PatAttndByDocResultRecord in PatAttndByDocResult)
                    {  
                        var PatientResult = (from patientdata in dcRadRelay.PATIENTs
                                            where PatAttndByDocResultRecord.patattndbydocdata.MPI == patientdata.MPI &&
                                            (patientdata.FIRST_NAME == tbPatFname.Text ||
                                            patientdata.LAST_NAME == tbPatLname.Text ||
                                            patientdata.ADDRESS1 == tbPatAddr.Text ||
                                            patientdata.CITY == tbPatCity.Text ||
                                            patientdata.PHONE == tbPatPhone.Text ||
                                            patientdata.SSN == tbDocZip.Text)
                                            select new { patientdata }).Single();  // also check for .First() / FirstOrDefault() -- suits.
     
      patientResult_DS.FIRST_NAME = patientdata.FIRST_NAME;
      patientResult_DS.LAST_NAME = patientdata.LAST_NAME:
      patientResult_DS.DATE_OF_BIRTH = patientdata.DATE_OF_BIRTH;
      patientResult_DS.ADDRESS1 = patientdata.ADDRESS1;
      patientResult_DS.SEX = patientdata.SEX:

      this.UltraWebGridPatSrch.DataSource = patientResult_DS;
In case if result is Multiple Records:  you can use : List
eg:  LIst<PATIENT>  patientResult_DS = new List<PATIENT>();
                   

0
 
kwh3856Author Commented:
sree,
The actual result may be one record or multiple records.  I need to be able to handle both situations based on the search criteria the user keys in.
Will your logic support that need?  I will try the code and let you know.  Thank you very much for helping me.
0
 
kwh3856Author Commented:
sree,
I tried the code and got the follwing error message
An invalid data source is being used for UltraWebGridPatSrch. A valid data source must implement either IListSource or IEnumerable.
 
I also tried to do the List function but it wanted to make it listbox.  What is the exact syntax to create a list?
Thanks
Kenny
 
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
This should sort you out:
List<PATIENT>  patientResult_DS = new List<PATIENT>();
                   foreach (var PatAttndByDocResultRecord in PatAttndByDocResult)
                    {  
                        var PatientResult = (from patientdata in dcRadRelay.PATIENTs
                                            where PatAttndByDocResultRecord.patattndbydocdata.MPI == patientdata.MPI &&
                                            (patientdata.FIRST_NAME == tbPatFname.Text ||
                                            patientdata.LAST_NAME == tbPatLname.Text ||
                                            patientdata.ADDRESS1 == tbPatAddr.Text ||
                                            patientdata.CITY == tbPatCity.Text ||
                                            patientdata.PHONE == tbPatPhone.Text ||
                                            patientdata.SSN == tbDocZip.Text)
                                            select new { patientdata }).ToList();
     
      patientResult_DS.AddRange((IEnumerable<PATIENT>)PatientResult);

      this.UltraWebGridPatSrch.DataSource = patientResult_DS;
0
 
kwh3856Author Commented:
sree,
When I typed in that first line, I got this error message.
do I need an additional using ?????   statement?  If so, what should it be?
Error 2 The type or namespace name 'List' could not be found (are you missing a using directive or an assembly reference?)
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Add these to you CS

using System.Collections.Generic;
using System.Linq;
0
 
kwh3856Author Commented:
sree,
now i get
Error 2 'System.Collections.Generic.List<ChartRelay35sp1.PATIENT>' does not contain a definition for 'FIRST_NAME' and no extension method 'FIRST_NAME' accepting a first argument of type 'System.Collections.Generic.List<RadtRelay35sp1.PATIENT>' could be found (are you missing a using directive or an assembly reference?)  
 
That occurs for
first name
last name
address
city
phone
ssn
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Can you show me your code
0
 
kwh3856Author Commented:
Here it is
protected void UltraWebGridPatSrch_InitializeDataSource(object sender, UltraGridEventArgs e)
        {
 
            bool authenticated;
            authenticated = Convert.ToBoolean(Session["authenticated"]);
 
            if (authenticated)
            {
 
 
                RadRelayDataClassDataContext dcRadRelay = new RadRelayDataClassDataContext();
 
                // Read  Table "Providers"
 
 
 
 
 
 
                var PatAttndByDocResult = from patattndbydocdata in dcRadRelay.PATIENTS_ATTENDED_BY_DOCTORs
                                          where patattndbydocdata.PracticeTIN == MyGlobalVars.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
                {
                    List<PATIENT> patientResult_DS = new List<PATIENT>();
 
 
                    foreach (var PatAttndByDocResultRecord in PatAttndByDocResult)
                    {
 
                       
 
 
 
                        var PatientResult = from patientdata in dcRadRelay.PATIENTs
                                            where PatAttndByDocResultRecord.patattndbydocdata.MPI == patientdata.MPI &&
                                            (patientResult_DS. == tbPatFname.Text ||
                                            patientResult_DS.LAST_NAME == tbPatLname.Text ||
                                            patientResult_DS.ADDRESS1 == tbPatAddr.Text ||
                                            patientResult_DS.CITY == tbPatCity.Text ||
                                            patientResult_DS.PHONE == tbPatPhone.Text ||
                                            patientResult_DS.SSN == tbDocZip.Text)
 
 
 
                                            select new { patientResult_DS.FIRST_NAME, patientResult_DS.LAST_NAME, patientResult_DS.DATE_OF_BIRTH, patientResult_DS.ADDRESS1, patientResult_DS.SEX, patientResult_DS.PHONE };
 
                        this.UltraWebGridPatSrch.DataSource = patientResult_DS;
                        
                       

Open in new window

0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
As Stated about do this:
List<PATIENT>  patientResult_DS = new List<PATIENT>();
                   foreach (var PatAttndByDocResultRecord in PatAttndByDocResult)
                   {  
                       var PatientResult = (from patientdata in dcRadRelay.PATIENTs
                                           where PatAttndByDocResultRecord.patattndbydocdata.MPI == patientdata.MPI &&
                                           (patientdata.FIRST_NAME == tbPatFname.Text ||
                                           patientdata.LAST_NAME == tbPatLname.Text ||
                                           patientdata.ADDRESS1 == tbPatAddr.Text ||
                                           patientdata.CITY == tbPatCity.Text ||
                                           patientdata.PHONE == tbPatPhone.Text ||
                                           patientdata.SSN == tbDocZip.Text)
                                          select new { patientdata }).ToList();
     
     patientResult_DS.AddRange((IEnumerable<PATIENT>)PatientResult);

0
 
kwh3856Author Commented:
sree,
I have a new error now on this line.

patientResult_DS.AddRange((IEnumerable<PATIENT>)PatientResult);
 
Unable to cast object of type 'System.Collections.Generic.List`1[<>f__AnonymousType5`1[RadRelay35sp1.PATIENT]]' to type 'System.Collections.Generic.IEnumerable`1[RadRelay35sp1.PATIENT]'.
 
I have to run...I will be back shortly and check back.  Thanks for all your help you are giving me.
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
try:
List<PATIENT>  patientResult_DS = new List<PATIENT>();
                  foreach (var PatAttndByDocResultRecord in PatAttndByDocResult)
                  {  
                      var PatientResult = (from patientdata in dcRadRelay.PATIENTs
                                          where PatAttndByDocResultRecord.patattndbydocdata.MPI == patientdata.MPI &&
                                          (patientdata.FIRST_NAME == tbPatFname.Text ||
                                          patientdata.LAST_NAME == tbPatLname.Text ||
                                          patientdata.ADDRESS1 == tbPatAddr.Text ||
                                          patientdata.CITY == tbPatCity.Text ||
                                          patientdata.PHONE == tbPatPhone.Text ||
                                          patientdata.SSN == tbDocZip.Text)
                                          select new { patientdata }).ToList();
   
    foreach(var p in patientResult)
             PatientResult_DS.Add(patattndbydocdata);

   this.UltraWebGridPatSrch.DataSource = patientResult_DS;
     
0
 
kwh3856Author Commented:
sree,
I tried the Add(patattndbydocdata) but it told me it did not exist in the current context.  I then tried  PatAttndByDocResultRecord and also PatAttndByDocResult.  When I used those variables I got the error
Error 2 The best overloaded method match for 'System.Collections.Generic.List<RadtRelay35sp1.PATIENT>.Add(RadRelay35sp1.PATIENT)' has some invalid arguments  
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Sorry should be:

foreach(var p in patientResult)
PatientResult_DS.Add(PatAttndByDocResult.patattndbydocdata);
0
 
kwh3856Author Commented:
sree,
PatAttndByDocResult did not have a patattndbydocdata as a choice, It only had Provider as a choice.  I tried that and it gave me
 
Error 2 The best overloaded method match for 'System.Collections.Generic.List<ChartRelay35sp1.PATIENT>.Add(RadRelay35sp1.PATIENT)' has some invalid arguments  
0
 
kwh3856Author Commented:
sree,
Here is the code you asked me to use and then the exact error message

foreach (var p in PatientResult)
patientResult_DS.Add(PatAttndByDocResult.patattndbydocdata);
  Error 2 'System.Linq.IQueryable<AnonymousType#1>' does not contain a definition for 'patattndbydocdata' and no extension method 'patattndbydocdata' accepting a first argument of type 'System.Linq.IQueryable<AnonymousType#1>' could be found (are you missing a using directive or an assembly reference?)
 
0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Sorry was looking at wrong lines:
foreach (var p in PatientResult)
                    PatientResult_DS.Add(p.patientdata);
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
kwh3856Author Commented:
That did it.  Your AWESOME!!!!!
Thanks
Kenny
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.