1

C# newbie question here.

In my database, I have a Load table, filled with a lot of entries.
I have done the following in the past and it was working fine:

var list_Loads = database.GetData<Load>().AsQueryable();
Load load_1 = list_Loads.First();
Load load_2 = list_Loads.Skip(1).First();
Load load_3 = list_Loads.Skip(1).First();
Load load_4 = list_Loads.Skip(1).First();
...

By "working fine" I mean that load_x is different for x being 1, 2, 3, 4, ...

Now I'm trying to do the following:

var list_Loads = database.GetData<Load>().AsQueryable();
Load load_1 = list_Loads.First();
...
database.SaveChanges();
for (int i=1; i<=99 ; i++)
{
    log.Debug($"Test number [{i}]");
    load_1 = list_Loads.Skip(1).First();
    ...
    database.SaveChanges();
}

What I now see, is that the first time the value of load_1 changes indeed, but next times (when for-loop index i increases), the value of load_1 stays the same (I can confirm that there are plenty of entries in list_Loads).

Does anybody know why and how I can solve this?

For your information, I already tried:

var load_i = list_Loads.Skip(1).First();

This did not help.

P.s. I have use Extent as a tag for this question, as list_Loads is an extent, according to the watch-window. I'd like to use the kind of expressions, like .Skip(1).First() but I don't know how this is called.

Dominique
  • 16,450
  • 15
  • 56
  • 112
  • 2
    It works, but this code is very inefficient. For starters, why not use `ToList()`? Or `foreach` over the query results? `Skip(i).First()` is equivalent to `.ElementAt(i+1)` too. Finally, why all those `SaveChanges`? This breaks the Unit-of-Work semantics of DbContext and requires using an explicit long-lived database transactin. There should be only a single call to `SaveChanges` at the very end to commit all changes. – Panagiotis Kanavos Mar 16 '22 at 08:47
  • DbContext isn't a database connection or database model. It's a multi-entity Unit-of-Work/Repository combination that tracks changes to all the entities used in a specific scenario - what DDD would call a bounded context. All changes are tracked in memory and persisted atomically when `SaveChanges` is called. If you don't call that, the changes are discarded. – Panagiotis Kanavos Mar 16 '22 at 08:49
  • @PanagiotisKanavos: The `SaveChange()` is needed, because every time something changes in DB, the modification is caught and something is done (which is exactly the way my application works). However, your proposal about replacing `Skip(1).First()` by `ElementAt(i+1)` solves my issue. Do you have any idea why? – Dominique Mar 16 '22 at 08:54
  • That's not how DbContext works. Again, DbContext isn't a connection. It doesn't even keep a connection open until you call `SaveChanges`. A DbContext has no way of detecting database changes, it only detects in-memory object changes. – Panagiotis Kanavos Mar 16 '22 at 08:58
  • 2
    As for why the code fails, again, because that's not how EF Core works. A Queryable isn't a cursor, it's a query definition. When you enumerate it in any way (with `foreach`, `ToList()`, `Skip`) the query gets translated to SQL and executed. You get the results back as an `IEnumerable`. You're executing the same query over and over. `ElementAt()` doesn't solve the actual design problem, you're still executing the same query N times, but discard all results except one. Your code ends up executing `N+1` queries. That's why you should just use `ToList()` or `foreach` – Panagiotis Kanavos Mar 16 '22 at 09:02
  • `which is exactly the way my application works` then it has a serious design and *performance* problem. Unless `GetData<>` returns the fully loaded results, your code is executing multiple queries, keeping connections and locks far longer than necessary – Panagiotis Kanavos Mar 16 '22 at 09:05
  • @PanagiotisKanavos: ??? I thought that I launch the query only once: `var list_Loads = database.GetData().AsQueryable();`. All the other times (inside the for-loop) I thought I was browsing through the results of the query. Now you are saying that I'm executing the query multiple times? How does that work? – Dominique Mar 16 '22 at 09:05
  • What does `GetData<>` do? We can't guess that. If it *doesn't* return a DbSet, there's no reason to use `AsQueryable`, you could use `Enumerable.First<>` instead of `Queryable.First<>` – Panagiotis Kanavos Mar 16 '22 at 09:13
  • 3
    In any case, `IEnumerable` and `IQueryable` aren't cursors. When you call `.Skip(1)` you're starting a new iteration and skipping the first item, not moving to the next item in a cursor. Calling `Skip(1)` 5 times will always return the same item. When you use `ElementAt(5)` you start a new iteration and skip the first 4 items before returning the 5th. Again, why aren't you using `foreach`? Or `ToList()` ? – Panagiotis Kanavos Mar 16 '22 at 09:17
  • This isn't some C# quirk either - most of the languages that have sequences, iterators, iterables behave the same, including JavaScript and Python. – Panagiotis Kanavos Mar 16 '22 at 09:18
  • 1
    @PanagiotisKanavos: Oh! `Skip(1)` starts back at the first entry every time? That I didn't know: I thought it kept in memory where it was and skipped to the next entry. And what your say about `ElementAt(i)` makes perfectly sense now. The reason I'm not working with `foreach` is just basic newbie behaviour. Can you write your comment (especially the last one) in an answer? I'll accept it. – Dominique Mar 16 '22 at 09:22
  • 1
    @Dominique correct. Check out this simple sample which demonstrates it: https://dotnetfiddle.net/kwgfsN – Matthew Watson Mar 16 '22 at 09:31

0 Answers0