Entity Framework is not able to pull specific record by its ID in C# back end

Question

Lets say I have a table called Books, I have few records in that as shown below

ID    BookName
1      Book1
2      Book2
3      Book3

when I query above table from my C# back end using records ID as below

var result = context.Books.where(b => b.ID == 1).FistOrDefault();

I get null for ID = 1 but for IDs 2,3 I get the whole records. When I directly query record ID = 1 on SSMS I get the record ID 1. It dose not make sense to me why and how that can happen. Any help or clue will be highly appreciated.

Answer 1

Alternatively, run a trace against the database to capture the SQL statements actually running against it, use a breakpoint in the code, start the trace and execute the read line. The trace will reveal the exact SQL statement(s) being run. You can then copy those statements into SSMS to inspect what they return. A simple trace tool I use for SQL Server is ExpressProfiler from back in the days of enabling tracing for SQL Server Express. Though I recommend building it from source (https://github.com/ststeiger/ExpressProfiler) rather than any installer such as Sourceforge. SSMS has profiler under Tools/SQL Server Profiler which captures a lot more noise by default. Either tool is invaluable for investigating EF weirdness as well as performance.

if context.Books.Where(b => b.ID == 1).SingleOrDefault() returns #null, I would look at what SQL gets captured for that statement on your database. Compare that to b => b.ID == 2.

If your trace does not capture anything for either 1 or 2, but you see resulting data for the ID = 2 scenario then the explanation would be that your DbContext is not pointed at the database/server you think it is, or there is something amiss with your DbContext setup.

If your trace does capture something for both 1 and 2, but you are only seeing data for #2, check that the query for #1 executes and returns data. If the query looks valid but your code is not seeing anything for ID #1 then somehow your DbContext is in a state where ID #1 has been removed. I would add code to check the DbContext's ChangeTracker to find out if there is an entry for #1 that is sitting in a entity state of Removed. It is possible that you have code being run which is unexpectedly cascading a delete but SaveChanges was not called or was not successful. Long running DbContext instances are prone to this kind of problem.

Test the following:

instead of

var result = context.Books.where(b => b.ID == 1).FirstOrDefault(); 

use this:

using(var testContext = new MyDbContext())
{
    var result = testContext.Books.where(b => b.ID == 1).SingleOrDefault(); 
}

Substituting MyDbContext with your application DbContext. This eliminates any funny business with a long running DbContext's state. The context can still execute SQL but return what is in cached state.

Note: When querying data, opt for SingleOrDefault rather than FirstOrDefault when you expect 0..1 results. Operations like the First flavours should only be used when you expect 0..many but only care about the first result, and they should always be used with an Order By condition to ensure predictable ordering.

Answer 2

you need to query table books. please change your query as below:

var result = context.Books.Where(b => b.ID == 1).FistOrDefault();

or

var result = context.Books.FistOrDefault(b => b.ID == 1);

Also, if its working with ID values 2 & 3 then next thing I would check is the database that your C# is actually pointed to. Also I would check the schema where Books table exists. By default EF, queries table which are with dbo schema unless you have defined otherwise.

Next thing, I would check is the query that is being sent to database. For this, extract context.Books.Where(b => b.ID == 1) to a variable and get the sql query and run it manually in SSMS.

var queryableBooks = context.Books.Where(b => b.ID == 1); var result = queryableBooks.FirstOrDefault(); // have a break point here

Then during debugging, verify queryableBooks SQL via Quick Watch. YOu will see something like below: