?
Solved

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

Posted on 2009-04-26
18
Medium Priority
?
884 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:kwh3856
  • 10
  • 8
18 Comments
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24237923
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
 

Author Comment

by:kwh3856
ID: 24237985
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
 

Author Comment

by:kwh3856
ID: 24238008
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24238030
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
 

Author Comment

by:kwh3856
ID: 24238097
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
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24238103
Add these to you CS

using System.Collections.Generic;
using System.Linq;
0
 

Author Comment

by:kwh3856
ID: 24238130
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
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24238146
Can you show me your code
0
 

Author Comment

by:kwh3856
ID: 24238159
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
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24238178
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
 

Author Comment

by:kwh3856
ID: 24238197
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
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24238302
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
 

Author Comment

by:kwh3856
ID: 24238417
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
 
LVL 9

Expert Comment

by:Sreedhar Vengala
ID: 24238456
Sorry should be:

foreach(var p in patientResult)
PatientResult_DS.Add(PatAttndByDocResult.patattndbydocdata);
0
 

Author Comment

by:kwh3856
ID: 24238474
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
 

Author Comment

by:kwh3856
ID: 24238492
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
 
LVL 9

Accepted Solution

by:
Sreedhar Vengala earned 2000 total points
ID: 24238499
Sorry was looking at wrong lines:
foreach (var p in PatientResult)
                    PatientResult_DS.Add(p.patientdata);
0
 

Author Closing Comment

by:kwh3856
ID: 31574742
That did it.  Your AWESOME!!!!!
Thanks
Kenny
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question