Specified Cast is not valid

I am running a LINQ query against a database.  The program had been working in the past but recenctly it started to give me this error when the LINQ query is run.  Can someone point me in the right direction where I need to look to resolve this problem.  Here is the code it is hanging on.


 var provtbl = from prov in dcABC.PROVIDERs
                                      where prov.PRACTICE_TIN == userrec.u.PRACTICE_TIN
                                      select new { prov };


                        if (provtbl.Count() == 0)
                        {
                            // No Record found in Practice Table
                           
                        }
                        else
                        {
                            foreach (var provrec in provtbl)-----------<<<<<---------  Error Occurs Here
                            {
                                // Found Practice Record
                                //string LoginUser;
                               
                                physicianNPI = provrec.prov.PHYSICIAN_NPI;
kwh3856Asked:
Who is Participating?
 
vultreConnect With a Mentor Commented:
Hello kwh3856,

As far as I know, there is no enumarable object for loop, therefore you should use it as :

var provtbl = from prov in dcABC.PROVIDERs
                                      where prov.PRACTICE_TIN == userrec.u.PRACTICE_TIN
                                      select prov;

   if (provtbl.Count() == 0)
                        {
                            // No Record found in Practice Table
                           
                        }
                        else
                        {
                            foreach (var provrec in provtbl)
                            {
                                // your logic codes bla bla...
                               
                           }
                      }

As that, it will work, I think.

Thank you.
0
 
BardobraveConnect With a Mentor Commented:
Have you tried to assign types to provtbl and provrec instead of using undeterminate variables?
0
 
Todd GerbertIT ConsultantCommented:
Hmm, at first glance that looks like it should work - does the exception say specifically what type it's trying to cast from and to?  Is the identifier "provrec" used earlier in the code?  Can you post the rest of the foreach loop?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Fernando SotoConnect With a Mentor RetiredCommented:
Hi Kenny;

From the looks of the code provided that should work. It may be that one of the code files which have not been updated could have a problem or is corrupted. Please try this, from the main menu in the IDE click on Build -> Clean Solution then recompile the code and see if the error is still there.

On another point this part of the query, select new { prov }, creates an anonymous type. When you use the keyword new as you did it will create a anonymous type. In this case you could return a concrete type of PROVIDERs by dropping  the new keyword and the { }. changing the code as follows:

var provtbl = from prov in dcABC.PROVIDERs
              where prov.PRACTICE_TIN == userrec.u.PRACTICE_TIN
              select prov;


if (provtbl.Count() == 0)
{
    // No Record found in Practice Table
   
}
else
{
    foreach (var provrec in provtbl)-----------<<<<<---------  Error Occurs Here
    {
        // Found Practice Record
        //string LoginUser;
       
        physicianNPI = provrec.PHYSICIAN_NPI;

Open in new window


Please not the changes in the foreach loop as well.

Fernando
0
 
Todd GerbertConnect With a Mentor IT ConsultantCommented:
I think what vultre's getting at is that in your LINQ query there's no need to use "select new { some value }" (that's useful for returning a completely new type, but all it seems you want to do is return a provider).

So, instead of
var provtbl = from prov in dcABC.PROVIDERs
                     where prov.PRACTICE_TIN == userrec.u.PRACTICE_TIN
                     select new { prov };

foreach (var provrec in provtbl)-----------<<<<<---------  Error Occurs Here
{
    // Found Practice Record
    //string LoginUser;
                                
    physicianNPI = provrec.prov.PHYSICIAN_NPI; 

Open in new window


You can use
var provtbl = from prov in dcABC.PROVIDERs
                     where prov.PRACTICE_TIN == userrec.u.PRACTICE_TIN
                     select prov;

foreach (var provrec in provtbl)-----------<<<<<---------  Error Occurs Here
{
    // Found Practice Record
    //string LoginUser;
                                
    physicianNPI = provrec.PHYSICIAN_NPI; 

Open in new window


Although I think this is unrelated to your problem.
0
 
kwh3856Author Commented:
Ok...I did not quite understand what the new keyword meant so I have taken it out as you have suggested.  I also did a clean new.  Still no luck.  I just set a breakpoint at the

var provtbl = from prov in dcABC.PROVIDERs
                                      where prov.PRACTICE_TIN == userrec.u.PRACTICE_TIN
                                      select prov;

I then hovered over the provtble and told it to display results.  It gave me the invalid cast error.  I waited about 20 seconds and then did the same thing.  It then displayed the record for the table.....but....some fields showed as NULL when there is actually data in the SQL table.  Any thoughts?


0
 
kwh3856Author Commented:
now as I step through the code one line at a time, it let me by and continured executing.  It seems to be some kind of delay problem with the SQL server not responding in time.  Does that make sense?
0
 
kwh3856Author Commented:
additionally, I am now seeing the same error in a completely different area of the program in a different table.


Here is the code where I get the same error message.


var EncounterMasterResult = from encountermasterdata in dcABC.EncounterMasters
                                        where encountermasterdata.MPI == ConvertedMPI
                                        orderby encountermasterdata.EncDate

                                        select new
                                        {
                                            encountermasterdata.ChiefComplaint,
                                            encountermasterdata.CRencounterId,
                                            encountermasterdata.EncDate,
                                            encountermasterdata.HPINotes,
                                            encountermasterdata.NPI,


                                        };

            int clinicalencdataCount = EncounterMasterResult.Count();    // <===== Added this line to get total count here

            if (clinicalencdataCount == 0)                    // <===== Changed this line so that the query does not get executed again
            {
                // No user record found
            }
            else
            {

                // Set the grid to the data source
                this.UltraWebGrid1.DataSource = EncounterMasterResult;
                this.UltraWebGrid1.DisplayLayout.Bands.Clear();
0
 
Fernando SotoRetiredCommented:
Hi Kenny;

What happens if you change the query to this:

var EncounterMasterResult = (from encountermasterdata in dcABC.EncounterMasters
                             where encountermasterdata.MPI == ConvertedMPI
                             orderby encountermasterdata.EncDate
                             select new
                             {
                                 encountermasterdata.ChiefComplaint,
                                 encountermasterdata.CRencounterId,
                                 encountermasterdata.EncDate,
                                 encountermasterdata.HPINotes,
                                 encountermasterdata.NPI,
                             }).ToList();

Open in new window


Fernando
0
 
John ClaesConnect With a Mentor Senior .Net Consultant & Technical AnalistCommented:
It looks like you're right .
Linq only executes the real select when starting the Foreach-Loop
If the call is timing-out the IQuerable<T> null cant cast the null part to a T-Object.

You might look at creating a Index upon the Field : prov.PRACTICE_TIN
This might help you alot.

regards

poor beggar
0
 
kwh3856Author Commented:
Thank you each for your expert advice.  I finally found the problem.  I had the same field in two different tables that was not the same type.  I awarded each of you points because I learned from each of your comments and now have a better understanding of LINQ.
0
All Courses

From novice to tech pro — start learning today.