Where method doesn’t find object when it is clearly there

Question

Given a class

[Table ( Name = "AllPlayerInfo" ) ]
public class AllPlayerInfo
{
   [Column (IsPrimaryKey = true)]
   public decimal Classes_ID { get; set; }
   [Column (IsPrimaryKey = true)]
   public decimal Member_ID { get; set; }
   //...
}

I call

DataContext db2 = new DataContext ( sqlconnectstring );
Table<AllPlayerInfo> api = db2.GetTable<AllPlayerInfo> ();

which returns all the records from the database table. I look at the contents of api and confirm that the record I want is there and has Member_ID == 4617.

So I issue following command:

AllPlayerInfo attempt1 = api.Where ( r => r.Member_ID == 4617).FirstOrDefault<AllPlayerInfo> ();

that (incorrectly) returns null.

So then I transfer the object to a list:

List<AllPlayerInfo> listapi = api.ToList<AllPlayerInfo> ();

and then use the Exists method and it does find the record I wanted:

bool recordexists = listapi.Exists ( r => r.Member_ID == 4617 );

recordexists is true!

So the question is: Why does the Where method not find the record when it is clearly there? I did try the Where method with other fields and values and they worked. Just not this particular field. I tested other decimal fields such as the class_id and it worked.

Answer 1

One guess (like Timwi's) is that the decimal type is confusing things. Try this:

AllPlayerInfo attempt1 = api.Where(r => r.Member_ID == 4617m)
                            .FirstOrDefault();

In other words, make the constant a decimal too. That may well stop LINQ to SQL from applying conversions where it shouldn't.

Also, when in doubt, check the generated SQL.

Answer 2

I would check the generated SQL in SQL profiler. Sometimes when LINQ to SQL determines you are comparing incorrect types, e.g. Where(x => x.somestring == null) and x.somestring is not set as nullable in the DBML file then it generates SQL with a where clause that says WHERE 1=0 and then goes off and runs that in SQL server which will boviously return no records. Member ID is a decimal and it is probably inerpretting your literal as an int so it has decided that Where( r => r.Member_ID == 4617) cannot ever be true so it creates SQL that will never return any records.

Answer 3

Thanks Ben. You got me thinking...

I decided to change the Column attribute to "[Column ( IsPrimaryKey = true, CanBeNull = false, DbType = "decimal" )]" and all of a sudden the code worked.

I then tried removing the "DbType=" part and again the WHERE method did not work. So apparently if you don't specify the datatype in the attribute, then it will sometimes not match without giving any clue that it is not working.

So I guess I better always include the DbType attribute EVEN IF the variable is already the same time as the database.