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

Question

I am new to LINQ. I have one year of experience with C#.

I have a List<Detail> with 3 properties: JobNumber, PieceID, & ClaimNbr

I have a DB table with the same 3 fields. I need to compare PieceID to make sure the are the same using LINQ. How do I get the table into a List or something so I can use it. I tried datareader and datatable and it does not seem to be able to use them.

I know that I have to use a Join, but I cannot even start to figure out the query until I can use the DB fields.

Please help.

Thanks for your help ahead of time.

Arep

A Repasky · Answer

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

Vulpes · Answer

Incidentally it's dawned on me now why the following code wasn't working in the light of what you said about the names on both sides not being equal: 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) }; As we weren't specifying property names for this line: on new { list.ClaimNbr list.JobNumber } the compiler was inferring them to be ClainNbr and JobNumber. However these weren't the same as the explicit property names in this line - hence the error: equals new { DbClaimNbr = db[ClaimNbr].ToString() DbJobNumber = db[JobNumber].ToString() } Errors such as this illustrate just how easy it can be to run aground with LINQ :)

A Repasky · Answer

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

Vulpes · Answer

I'd try instead: where Convert.ToInt32(list.PieceID) != (int)db[PieceID]

A Repasky · Answer

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

Vulpes · Answer

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 ?

A Repasky · Answer

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

Vulpes · Answer

The first problem is caused (apparently) by list.PieceId being a numeric string which fills out the ID to 6 digits using leading zeros whereas db[PieceID] is a simple integer with no leading zeros when converted to a string. So in other words the strings are not equal and so get included in the results. To cope with this you either need to convert list.PieceId to an integer and compare it with (int)db[PieceID] or alternatively you could do this: where list.PieceID != db[PieceID].ToString( D6) With regard to the second problem you're on the right lines creating anonymous types including both the PieceID and JobNumber and then comparing them for equality but in order for them to be equal they need to have exactly the same types as well as the same formats. I'd therefore try this instead: 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) };

A Repasky · Answer

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

A Repasky · Answer

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?

A Repasky · Answer

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); }

A Repasky · Answer

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

Vulpes · Answer

You can use an anonymous type to capture all three values. Assuming PieceID is an int the following should work: var query = from list in fileList join DataRow db in dt.Rows on list.ClaimNbr equals db[ClaimNbr].ToString() where list.PieceID != (int)db[PieceID] 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 {0} where the list gives {1} and the database gives {2} result.ClaimNbr result.ListPieceID result.DbPieceID); }

A Repasky · Answer

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()); }

A Repasky · Answer

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

Vulpes · Answer

Hmm type inference might be failing because the DataRowCollection object which is returned by DataTable.Rows only implements IEnumerable and not IEnumerable . To deal with that try declaring the type of 'db' explicitly: var query = from list in fileList join DataRow db in dt.Rows on list.ClaimNbr equals db[ClaimNbr].ToString() select (int)db[PieceID];

A Repasky · Answer

I get the same error on Join. ClaimNbr will not be null.

Vulpes · Answer

OK with the usual caveat about there being no nulls in the ClaimNbr column I'd try: var query = from list in fileList join db in dt.Rows on list.ClaimNbr equals db[ClaimNbr].ToString() select (int)db[PieceID];

A Repasky · Answer

It is string.

Vulpes · Answer

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

A Repasky · Answer

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 ]; }

Vulpes · Answer

Suppose that your List is called 'details' and that PieceID is an int column in the DataTable (table1) and an int property in the Detail class. Then you could obtain a list of all Detail objects for which there is a row in the DataTable with the same PieceID column as follows: List result = (from row in table1.Rows join detail in details on (int)row[PieceID] equals detail.PieceId select detail).ToList(); This query will need modifciation if there's a possiblity that some columns in the DataTable could be null.