How to model a collection of items where one of them is the active item?

Question

I have a situation where an entity has a list of children that are inactive. Additionally, they have another "current/active" sub entity of the same type.

The following would be the ideal modeling, but I cannot figure out how to this with Entity Framework Core:

public class Customer 
{
    public Application CurrentApplication { get; set; }
    public List<Application> PastApplications { get; set; }
}

public class Application
{
    public bool IsCurrent { get; set; }
    public Customer Customer { get; set; }
}

In the past, I've typically modeled it as so:

public class Customer
{
    public Application CurrentApplication => AllApplications.Single(a => a.IsCurrent);
    public List<Application> PastApplications => AllApplications.Where(a => !a.IsCurrent);  
    public List<Application> AllApplications { get; set; }   
}

public class Application
{
    public bool IsCurrent { get; set; }
    public Customer Customer { get; set; }
}

However, I feel that this could lead to the possibility of another Application incorrectly being set as IsCurrent, thus breaking the .Single().

What's the suggested way to accomplish this from a DDD perspective? If that doesn't match up with what EF Core can do, what is a good practical suggestion?

Answer 1

I don't think that this is a DDD problem, rather a how to design a relational DB model and how to use EF Core question.

First you need to decide what is the relationship between Customers and Applications:

• One-to-Many, that is, a Customer may have zero or more Applications, but an Application belongs to exactly one Customer. This scenerio is also called a master-detail relationship. The entity on the Many side (Application in this case) stores a reference (called a foreign key) to its owner (Customer).
• Many-to-Many, that is, a Customer may have zero or more Applications, but an Application may belong to zero or more Customers, as well. This scenario is modelled using an extra "join" table (usually named something like CustomerApplication) so the problem is resolved to two One-to-May relationships in the end.

If there is only one active Application at a given time (per customer), the active application can be modelled using a One-to-One (Zero-to-One, to be precise) relationship between Customer and Application (with a foreign key on Customer's side). It can also be modelled using a flag field on Application as you tried but that's not as error-proof as a foreign key (but may have better performance, though).

The code you posted resembles rather a One-to-Many scenario, so I show an example for that case. Understanding the following, you can easily change it Many-to-Many if desired.

First let's define the entities:

public class Customer
{
    public int Id { get; set; }

    public int? CurrentApplicationId { get; set; }
    public Application CurrentApplication { get; set; }

    public ICollection<Application> Applications { get; set; }
}

public class Application
{
    public int Id { get; set; }

    public Customer Customer { get; set; }
}

The single interesting part is int? CurrentApplicationId. We need to explicitly define the foreign key for our Zero-to-Many relationship (more on this later). The nullable int (int?) tells EF that this field can be NULL.

EF is usually able to figure out the relationship mappings but in this case we need to explain them to it explicitly:

class DataContext : DbContext
{
    // ctor omitted for brevity

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>(customer =>
        {
            customer.HasMany(entity => entity.Applications)
                .WithOne(relatedEntity => relatedEntity.Customer)
                .OnDelete(DeleteBehavior.Cascade);

            customer.HasOne(entity => entity.CurrentApplication)
                .WithOne()
                .HasForeignKey<Customer>(entity => entity.CurrentApplicationId);
        });
    }

    public DbSet<Application> Applications { get; set; }
    public DbSet<Customer> Customers { get; set; }
}

What's going on in the OnModelCreating method is called fluent API configuration. This topic and conventions is a must to understand and control how EF maps the entities to DB tables.

Based on the mapping EF is able to generate (see code-first migrations) the following DB schema:

CREATE TABLE [Customers] (
  [Id] INTEGER  NOT NULL
, [CurrentApplicationId] bigint  NULL
, CONSTRAINT [sqlite_master_PK_Customers] PRIMARY KEY ([Id])
, FOREIGN KEY ([CurrentApplicationId]) REFERENCES [Applications] ([Id]) ON DELETE RESTRICT ON UPDATE NO ACTION
);
CREATE UNIQUE INDEX [IX_Customers_CurrentApplicationId] ON [Customers] ([CurrentApplicationId] ASC);

CREATE TABLE [Applications] (
  [Id] INTEGER  NOT NULL
, [CustomerId] bigint  NULL
, CONSTRAINT [sqlite_master_PK_Applications] PRIMARY KEY ([Id])
, FOREIGN KEY ([CustomerId]) REFERENCES [Customers] ([Id]) ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX [IX_Applications_CustomerId] ON [Applications] ([CustomerId] ASC);

Exactly what we wanted.

Now, how you query the active and inactive applications in this configuration? Something like this:

var customerId = 1;

using (var ctx = new DataContext())
{
    var currentApplication = (
        from customer in ctx.Customers
        where customer.Id == customerId
        select customer.CurrentApplication
    ).FirstOrDefault();

    var pastApplications =
    (
        from customer in ctx.Customers
        from application in customer.Applications
        where customer.Id == customerId && customer.CurrentApplication != application
        select application
    ).ToArray();
}

I suggest you to read through the acticles to be found here to get familiar with EF Core.

As for relational DB modelling this site seems a useful resource.

Answer 2

Using EFCore, the following would work:

public class Customer 
{
    [Key]
    public int ID {get; set;}
    //other properties
    //Navigation Property
    public virtual ICollection<Application> Applications{ get; set; }
}

public class Application
{
    [Key]
    public int ID {get; set;}
    [ForeignKey("Customer")]
    public int CustomerID{get; set;}
    public DateTime ApplicationDate{get; set}
    //other properties
    public bool IsCurrent { get; set; }
}

I am assuming your are using Code First, so this will be the correct way to do your mappings.

After migrating and updating the context, you could use some backend code to always ensure that your most recent application returns as IsCurrent.

You would can then select your current application as follows:

 private yourContext _context;

var yourContext = _context.Customers
                .Include(m=>m.Application)
                .Where(m=> m.isCurrent==false)
                .Single(a=>a.);

            return yourContext;

You would of course have to set up your constructor with the context etc