How do I compare a List against a DB Table using LINQ?

Here is the LINQ statement when using an integer:

         var query = from list in fileList
                     join DataRow db in dt.Rows
                     on new { list.ClaimNbr, list.JobNumber }
                        equals new { ClaimNbr = db["ClaimNbr"].ToString(), JobNumber = db["JobNumber"].ToString() }
                     where Convert.ToInt32(list.PieceID) != (int)db["PieceID"]
                     select new
                     {
                        ClaimNbr = list.ClaimNbr,
                        ListPieceID = Convert.ToInt32(list.PieceID),
                        DbPieceID = (int)db["PieceID"]
                     };

 

Vulpes, thanks for your help.

 

Arep

Here this works.

         var query = from list in fileList
                     join DataRow db in dt.Rows
                     on new { list.ClaimNbr, list.JobNumber }
                        equals new { ClaimNbr = db["ClaimNbr"].ToString(), JobNumber = db["JobNumber"].ToString() }
                     where list.PieceID.ToString() != db["PieceID"].ToString().PadLeft(6, '0')
                     select new { ClaimNbr = list.ClaimNbr, ListPieceID = list.PieceID.ToString(),
                        DbPieceID = db["PieceID"].ToString().PadLeft(6, '0') };

 

Now I going to try to do the same but check for an int instead.

Arep

I solved that problem.  The names on both sides of the equals has to be the same (I found something online about this). 

         var query = from list in fileList
                     join DataRow db in dt.Rows
                     on new { list.ClaimNbr, list.JobNumber }
                        equals new { ClaimNbr = db["ClaimNbr"].ToString(), JobNumber = db["JobNumber"].ToString() }
                     where list.PieceID.ToString() != db["PieceID"].ToString()
                     select new { ClaimNbr = list.ClaimNbr, ListPieceID = list.PieceID.ToString(), DbPieceID = db["PieceID"].ToString() };


 

Now it compiles and runs.  But the where clause is not working correctly, because it sees list.PieceID as a string of 6 characters
and db["PieceID"] it sees as 1 character.  In the ToString it gives an error if I use "D6".  Maybe I will try a substring to make it 6 characters?


 

I get back on that.

Arep

OK, let's just go back to the beginning again for a moment.

So, we know that this works:

var query = from list in fileList
            join DataRow db in dt.Rows
            on list.ClaimNbr equals db["ClaimNbr"].ToString()
            select (int)db["PieceID"];

but this doesn't work:

var query = from list in fileList
            join DataRow db in dt.Rows
            on new { list.ClaimNbr, list.JobNumber }
            equals new { DbClaimNbr = db["ClaimNbr"].ToString(), DbJobNumber = db["JobNumber"].ToString() }
            where list.PieceID != db["PieceID"].ToString("D6")
            select new { ClaimNbr = list.ClaimNbr, ListPieceID = list.PieceID, DbPieceID = db["PieceID"].ToString("D6") };

So that tells us that:

1. It can't be anything to do with the first two lines as they're the same in both cases.

2. list.ClaimNbr is definitely a string otherwise it could never be equal to db["ClaimNbr"].ToString().

The only remaining possibility is that list.JobNumber is not a string. Could it be an int, perhaps ?

Vulpes,

I see what you are talking about that PieceID would never compare.  I also see what you mean about the types.  So I copied your code over into the program.  It gets the same type error.

Arep

Here is what I came up with but it will not compile.

         var query = from list in fileList
                     join DataRow db in dt.Rows
                     on new { list.ClaimNbr, list.JobNumber }
                         equals new { DbClaimNbr = db["ClaimNbr"], DbJobNumber = db["JobNumber"] }
                     where list.PieceID != db["PieceID"].ToString()
                     select new { ClaimNbr = list.ClaimNbr, ListPieceID = list.PieceID, DbPieceID = (int)db["PieceID"] };


But the join has an error:


 
The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'. 



 

Does anyone have an idea how to join on multiple columns?

 

I add the JobNumber is all.



 

Arep

I just realized that each ClaimNbr can have 2 different JobNumbers so then there will be 2 PieceID's.  So the code on this line has to also match JobNumber.

on list.ClaimNbr equals db["ClaimNbr"].ToString()

So I have to figure out how to do that.  I will start looking online.

How can I do that?

Vulpes,

I got interrupted and just got back to this.  I was doing some testing after you helped me get this code to compile.  The PieceID in the file (list) and that on the DB are the same, but I get my error message on every ClaimNbr.  I should never get this message. 

PieceID does not match for ClaimNbr 00013E9908104 where the file has 000001 and the database has  1

It is like it is doing the opposite that it is supposed to.

So here is the code.

         SqlCommand cmd = new SqlCommand(@"SELECT JobNumber, PieceID, ClaimNbr
            FROM DentalMemberEOB.dbo.HeaderHistory", cn);
         cmd.CommandTimeout = 60000;
         SqlDataReader dr = cmd.ExecuteReader();
         DataTable dt = new DataTable();
         dt.Load(dr);
         dr.Dispose();
         cmd.Dispose();
         var query = from list in fileList
                     join DataRow db in dt.Rows
                     on list.ClaimNbr equals db["ClaimNbr"].ToString()
                     where list.PieceID != db["PieceID"].ToString()
                     select new { ClaimNbr = list.ClaimNbr, ListPieceID = list.PieceID, DbPieceID = (int)db["PieceID"] };
         foreach (var result in query)
         { 
           Output_Files.PrintError("PieceID does not match for ClaimNbr " + result.ClaimNbr + " where the file has "
               + result.ListPieceID + " and the database has  " + result.DbPieceID);
         }

That did it.  I did have to change

 where list.PieceID != (int)db["PieceID"]

to

 where list.PieceID != db["PieceID"].ToString()

And I changed the write to console.writeline.

Thanks,

Arep

Here is what I have.  How can I save both list.PieceID & db["PieceID" so that I can display them in the error message?

         var query = from list in fileList
                     join DataRow db in dt.Rows
                     on list.ClaimNbr equals db["ClaimNbr"].ToString()
                     where list.PieceID != db["PieceID"].ToString()
                     select db["ClaimNbr"];
         foreach (var result in query)
         {
            Output_Files.PrintError("PieceID does not match for ClaimNbr " + result.ToString());
         }

That worked.  Now I need to compare the PieceID from the List and from the DB to see if they are the same, if not write an error.  When I tried to add list.PieceID to the select if gave an error.  I guess I cannot do it that way.  How can I now compare the PieceID #'s?

Arep

What is the type of list.ClaimNbr and the ClaimNbr column in the datatable?

Here is what I have so far.

I get the error:
 Error 1 The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'.

      static void CheckDB(List<Detail> fileList, SqlConnection cn)
      {
         SqlCommand cmd = new SqlCommand(@"SELECT JobNumber, PieceID, ClaimNbr
            FROM DentalMemberEOB.dbo.HeaderHistory", cn);
         cmd.CommandTimeout = 60000;
         SqlDataReader dr = cmd.ExecuteReader();
         DataTable dt = new DataTable();
         dt.Load(dr);
         dr.Dispose();
         cmd.Dispose();







         var query = from list in fileList
                     join db in dt.Rows
                     on list.ClaimNbr equals row["ClaimNbr"]
                     select (int)Row["PieceID"];
       }