Having problems with FirstofDefault returning wrong values.

Question

User120938120 posted

I have what looks to me to be a straitforward query:

VirtualWallet WalletItem = context.VirtualWallets.FirstOrDefault(w => w.TokenID == theItem.TokenId);

The table in the database has two rows.  the value of the TokenID field (which is a type of char(36)) in those two rows is 'blah' and 'blah2' respectively. 

If the value of theItem.TokenId = 'Blah', the EF query above will return the row with the TokenID of 'blah' - padded with spaces.  This is not what I'm expecting.  I need it to be a case-sensitive search.

But now if the value of theItem.TokenId is something far removed like 'Fred', then WalletItem will be null as expected.  

This is with a SqlServer database on the back end.  

Any ideas what I'm doing wrong?  Do I have something configured wrong somewhere else in the app?

Answer 1

User753101303 posted

Hi,

For just this column? It is translated to SQL and run on the SQL Server side. So case sensitivity will depend on the server side configuration.

See https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support and https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation in particular.

CI/CS and AI/AS in collation names tells if it is case/accent sensitive.

Answer 2

User120938120 posted

I'm not sure how the db is configured.  I'm new here and the dba's are on another team.  I'll look into those docs you sent.

In the meantime I tried the following:

VirtualWallet WalletItem = context.VirtualWallets.Where(w => string.Compare(w.TokenID, theItem.TokenId, false) != 0).FirstOrDefault();

Which gives me even stranger results.  If theItem.TokenId is 'Blah', I get back the row where TokenID is 'blah2'.  ???

Answer 3

User120938120 posted

I changed the column's collation to SQL_Latin1_General_CP1_CS_AS and that did the trick.

Thanks Patrice - you ARE the all-star!