EntityFramework load / update Entities

Question

i am struggeling for a while now to understand how EF loads / updates entities. First of all i wanna explain what my app (WPF) is about. I am developing an application where users can store Todo Items in Categories, these categories are predefined by the application. Each user can read all items but can only delete / update his own items. It's a multiuser system, means the application is running multiple times in the network accessing the same sql server database. When a user is adding/deleting/updating items the UI on all the other running apps has to update.

My model looks like this:

    public class Category
    {
        public int Id            { get; set; }
        public string Name       { get; set; }
        public List<Todo> Todos  { get; set; }
    }

    public class Todo
    {
        public int Id               { get; set; }
        public string Content       { get; set; }
        public DateTime LastUpdate  { get; set; }
        public string Owner         { get; set; }
        public Category Category    { get; set; }
        public List<Info> Infos     { get; set; }
    }

    public class Info
    {
        public int Id        { get; set; }
        public string Value  { get; set; }
        public Todo Todo     { get; set; }
    }

I am making the inital load like this, which works fine:

Context.dbsCategories.Where(c => c.Id == id).Include(c => c.Todos.Select(t => t.Infos)).FirstOrDefault();

Now i was trying to load only the Todos which are from the current user therefore i tried this:

Context.dbsCategories.Where(c => c.Id == id).Include(c => c.Todos.Where(t => t.Owner == Settings.User).Select(t => t.Infos)).FirstOrDefault();

This does not work because it's not possible to filter within include, so I tried this:

var cat = Context.dbsCategories.Where(c => c.Id == id).FirstOrDefault();
Context.dbsTodos.Where(t => t.Category.Id == cat.Id && t.Owner == Settings.User).Include(t=>t.Infos);

After executing the second line where i look for the Todo Items, these Items were automatically added to cat's Todos collection. Why? I would have expected that i have to add them manually to cat's Todos collection. Just for my understanding what is EF doing here exactly?

Now to my main problem -> the synchronization of the data between database and client. I am using a long running Context which lives as long as the application is running to save changes to the database which are made on owned items. The user does not have the possibility to manipulate / delete data from other users this is guarantee by the user interface. To synchronize the data i build this Synch Method which will run every 10 second, right now it's triggere manually.

Thats my synchronization Code, which only synchronizes Items to the client that do not belong to it.

private async Task Synchronize()
{
    using (var ctx = new Context())
    {
        var database = ctx.dbsTodos().Where(x =>  x.Owner != Settings.User).Select(t => t.Infos).AsNoTracking();
        var loaded = Context.dbsTodos.Local.Where(x => x.Owner != Settings.User);

        //In local context but not in database anymore -> Detachen
        foreach (var detach in loaded.Except(database, new TodoIdComparer()).ToList())
        {
            Context.ObjectContext.Detach(detach);
            Log.Debug(this, $"Item {detach} detached");
        }

        //In database and local context -> Check Timestamp -> Update
        foreach (var update in loaded.Intersect(database, new TodoIdTimeStampComparer()))
        {
            await Context.Entry(update).ReloadAsync();
            Log.Debug(this, $"Item {update} updated");
        }

        //In database but not in local context -> Attach
        foreach (var attach in database.ToList().Except(loaded, new TodoIdComparer()))
        {
            Context.dbsTodos().Attach(attach);
            Log.Debug(this, $"Item {attach} attached");
        }
    }
}

I am having following problems / issues of unknow origin with it: Detaching deleted Items seems to work, right now i am not sure if only the Todo Items are detached or also the Infos.

Updating Items works only for the TodoItem itsself, its not reloading the Infos within? How can i reload the whole entity with all it's relations? I am thankful for every help on this, even if you are saying it's all wrong what i am doing here!

Attaching new Items and Infos does not work so far? What am i doing wrong here?

Is this the right approach to synchronize data between client and database? What am i doing wrong here? Is there any "How to Sync" Tutorial? I have not found anything helpful so far?

Thanks!

Answer 1

My, you do like to deviate from entity framework code-first conventions, do you?

(1) Incorrect class definitions

The relations between your tables are Lists, instead of ICollections, they are not declared virtual and you forgot to declare the foreign key

There is a one-to-many relation between Todo and Category: every Todo belongs to exactly one Category (using a foreign key), every Category has zero or more Todos.

You choose to give Category a property:

 List<Todo> Todos {get; set;}

• Are you sure that category.Todos[4] has a defined meaning?
• What would category.Todos.Insert(4, new Todo()) mean?

Better stick to an interface where you can't use functions that have no proper meaning in your database: use ICollection<Todo> Todos {get; set;}. This way you'll have only access to functions that Entity Framework can translate to SQL.

Besides, a query will probably be faster: you give entity framework the possibility to query the data in its most efficient way, instead of forcing it to put the result into a List.

In entity framework the columns of a table are represented by non-virtual properties; the virtual properties represent the relations between the tables (one-to-many, many-to-many)

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    ... // other properties

    // every Category has zero or more Todos (one-to-many)
    public virtual ICollection<Todo> Todos  { get; set; }
}

public class Todo
{
    public int Id { get; set; }
    public string Content { get; set; }
    ... // other properties

    // every Todo belongs to exactly one Category, using foreign key
    public int CategoryId { get; set }
    public virtual Category Category { get; set; }

    // every Todo has zero or more Infos:
    public virtual ICollection<Info> Infos { get; set; }
}

You'll probably guess Info by now:

public class Info
{
    public int Id { get; set; }
    public string Value { get; set; }
    ... // other properties

    // every info belongs to exactly one Todo, using foreign key
    public int TodoId {get; set;}
    public virtual Todo Todo { get; set; }
}

Three major improvements:

• ICollections instead of Lists
• ICollections are virtual, because it is not a real column in your table,
• foreign key definitions non-virtual: they are real columns in your tables.

(2) Use Select instead of Include

One of the slower parts of a database query is the transport of the selected data from the Database Management System to your local process. Hence it is wise to limit the amount of transported data.

Suppose Category with Id [4] has a thousand Todos. Every Todo of this Category will have a foreign key with a value 4. So this same value 4 will be transported 1001 times. What a waste of processing power!

In entity framework use Select instead of Include to query data and select only the properties you actually plan to use. Only use Include if you plan to update the Selected data.

Give me all Categories that ... with their Todos that ...

var results = dbContext.Categories
    .Where(category => ...)
    .Select(category => new
    {
         // only select properties that you plan to use
         Id = category.Id,
         Name = category.Name,
         ...

         Todos = category.Todos
             .Where(todo => ...)        // only if you don't want all Todos
             .Select(todo => new
             {
                 // again, select only the properties you'll plan to use
                 Id = todo.Id,
                 ...

                 // not needed, you know the value:
                 // CategoryId = todo.CategoryId,

                 // only if you also want some infos:
                 Infos = todo.Infos
                    .Select(info => ....) // you know the drill by now
                    .ToList(),
            })
            .ToList(),
        });

(3) Don't keep DbContext alive for such a long time!

Another problem is that you keep your DbContext open for quite some time. This is not how a dbContext was meant. If your database changes between your query and your update, you'll have troubles. I can hardly imagine that you query so much data that you need to optimize it by keeping your dbContext alive. Even if you query a lot of data, the display of this huge amount of data would be the bottle-neck, not the database query.

Better fetch the data once, dispose the DbContext, and when updating fetch the data again, update the changed properties and SaveChanges.

fetch data:

RepositoryCategory FetchCategory(int categoryId)
{
     using (var dbContext = new MyDbContext())
     {
         return dbContext.Categories.Where(category => category.Id == categoryId)
            .Select(category => new RepositoryCategory
            {
                 ... // see above
            })
            .FirstOrDefault();
     }
}

Yes, you'll need an extra class RepositoryCategory for this. The advantage is, that you hide that you fetched your data from a database. Your code would hardly change if you'd fetch your data from a CSV-file, or from the internet. This is way better testable, and also way better maintainable: if the Category table in your database changes, users of your RepositoryCategory won't notice it.

Consider creating a special namespace for the data you fetch from your database. This way you can name the fetched Category still Category, instead of RepositoryCategory. You even hide better where you fetched your data from.

Back to your question

You wrote:

Now i was trying to load only the Todos which are from the current user

After the previous improvements, this will be easy:

string owner = Settings.User; // or something similar
var result = dbContext.Todos.Where(todo => todo.Owner == owner)
    .Select(todo => new 
    {
         // properties you need
    })