Entity Framework does not see data in database until a DBSet is converted to a list

Question

I define the index API of a controller as the following:

[HttpGet]
public IEnumerable<Blog> GetDatas()
{
    return _context.Blogs;
}

This always returns empty even-thought the database contains many blogs. However, when I do the following for test reasons only, entity framework manages to see the data and can return all the blogs in the database:

[HttpGet]
public IEnumerable<Blog> GetDatas()
{
    var blogs = _context.blogs.ToList();
    return _context.Blogs;
}

Any thoughts?

(maybe related to my other unanswered question).

Update 1

To avoid confusion around deferred execution in LINQ; I've tried the following two methods and using neither of the methods the returned json object contains the information already in the database. In other words, the serialized objects do not reflect entities persisted in the database. I think these methods would trigger execution of LINQ query, correct?

// Method 1:
[HttpGet]
public async Task<ActionResult<IEnumerable<Blog>>> GetDatas()
{
    return await _context.Blogs.ToListAsync().ConfigureAwait(false);
}

// Method 2:
[HttpGet]
public IEnumerable<Blog> GetDatas()
{
    return _context.Blogs.ToList();
}

That's how it works. A Linq query will not execute until something is called that requests the data. — Daniel Schmid, Oct 19 '19 at 22:13
again, I don't understand; `_context.Blogs` should return otherwise it does not make any sense (at least to me) to have it while you need to perform another operation before it can be populated. — Dr. Strangelove, Oct 19 '19 at 22:19
It is also according to the documentation. Microsoft docs isn't that bad. The reasons for this are quite complex. For now it has to suffice that if you return just complex.Blogs, you return a Linq query and not the result of the query. — Daniel Schmid, Oct 19 '19 at 22:27
The statement "it doesn't work" is relatively useless on stackoverflow. Please state explicitly your expected outcome vs your observed outcome — Caius Jard, Oct 20 '19 at 05:54
@CaiusJard updated, see if it relatively useful on stackoverflow now. — Dr. Strangelove, Oct 20 '19 at 06:53
No, believe me it really works. You might not believe it, but there is someone already using it with success. Now you start to confuse deferred execution and async/await. What is the resulting json of method 2 that seems not to reflect the content of the db table? — Daniel Schmid, Oct 20 '19 at 08:22

Caius Jard · Answer 1 · 2019-10-19T22:25:11.803

1

As Daniel said, this is by design. See What are the benefits of a Deferred Execution in LINQ? for an extended discussion but essentially data is loaded when it is used, not when it is requested. The only way you can see that it's empty is in the debugger; your runtime code doesn't see it that way because as soon as you try and find out whether the first form is empty or not it will fill with data, at which point (the point of use) it doesn't matter that it was empty up to that point - nothing was using it to find out whether it was empty or full

Think of it a bit like Schrondinger's cat

It's quite helpful actually:

var w = worldPopulation.Where(e => e.Gender = Gender.Male)

if(name!=null)
  w=w.Where(e=>e.Name == name)

The first query, if it ran immediately, could see 3.5 billion results being downloaded from your db to your client (a low spec machine compared with the server), then the name filter would reduce it to a few million. Better to only download a few million into your slow, low spec machine over a very slow network, in the first place.. right?

One benefit of only running the query when you actually ask for the data is that at that point you finally actually KNOW you want the data. Up to that point you might never have needed it, so downloading it would be a waste of resources

edited Oct 19 '19 at 22:25

answered Oct 19 '19 at 22:19

Caius Jard

72,509
5
49
80

does it mean `_context.Blogs` is executed using LINQ? the return object from this api is always serialized as JSON, would not be access to data to trigger execution? – Dr. Strangelove Oct 19 '19 at 22:24
It will fill when it's enumerated by the thing that is serializing to JSON.. – Caius Jard Oct 19 '19 at 22:27
Is it anyhow related to [this](https://stackoverflow.com/q/58420910/947889) question? – Dr. Strangelove Oct 19 '19 at 22:31
At the point where Json would like to enumerate the query results you are outside of the DbContext, so that cannot force the query to execute – Daniel Schmid Oct 19 '19 at 22:32
It is how it works and it makes perfectly sense and its the way it works since decades. – Daniel Schmid Oct 19 '19 at 22:33
@DanielSchmid perfect is relative. – Dr. Strangelove Oct 20 '19 at 00:22
@CaiusJard my database service is scoped, hence at the time of serialization the context may not be accessible. – Dr. Strangelove Oct 20 '19 at 00:24
@DanielSchmid I think you did not understand the question, the point is not whether deferring execution to later in LINQ is a good decision or not. – Dr. Strangelove Oct 20 '19 at 00:38

score 0 · Accepted Answer · answered Oct 28 '19 at 19:03

Use a scoped or context pool for database instead of singleton or transient; i.e., use:

services.AddDbContextPool<BlogsContext>(options => {options.UseSqlServer();});

and avoid registrations such as (note ServiceLifetime.Singleton):

services.AddDbContext<BlogsContext>(options => {options.UseSqlServer();}, ServiceLifetime.Singleton);

Entity Framework does not see data in database until a DBSet is converted to a list

Update 1

2 Answers2