How to Capture a 0..1 to 0..1 Relationship in Entity Framework

Is it possible to capture a 0..1 to 0..1 relationship in Entity Framework?

In EF6 and earlier it wasn't all that easy to implement such an association correctly. Fortunately, EF-core has greatly improved in supported associations. Now it's a piece of cake to implement the only model that enforces this kind of association by database constraints. That is: a junction class between Car and Driver in which the foreign keys have unique indexes (option 4 below). And it even almost entirely works with default mapping conventions.

The model:

class Car
{
    public int ID { get; set; }
    public string Brand { get; set; }
    public CarDriver CarDriver { get; set; }
}

class Driver
{
    public int ID { get; set; }
    public string Name { get; set; }
    public CarDriver CarDriver { get; set; }
}

class CarDriver
{
    public int CarId { get; set; }
    public int DriverId { get; set; }
    public Car Car { get; set; }
    public Driver Driver { get; set; }
}

The only required explicit mapping:

class CarDriverConfig : IEntityTypeConfiguration<CarDriver>
{
    public void Configure(EntityTypeBuilder<CarDriver> builder)
    {
        builder.HasKey(cd => new { cd.CarId, cd.DriverId });
    }
}

That's all EF needs to create the correct database model:

CREATE TABLE [Car] (
  [ID] int NOT NULL IDENTITY,
  [Brand] nvarchar(max) NULL,
  CONSTRAINT [PK_Car] PRIMARY KEY ([ID])
);
CREATE TABLE [Driver] (
  [ID] int NOT NULL IDENTITY,
  [Name] nvarchar(max) NULL,
  CONSTRAINT [PK_Driver] PRIMARY KEY ([ID])
);
CREATE TABLE [CarDriver] (
  [CarId] int NOT NULL,
  [DriverId] int NOT NULL,
  CONSTRAINT [PK_CarDriver] PRIMARY KEY ([CarId], [DriverId]),
  CONSTRAINT [FK_CarDriver_Car_CarId] FOREIGN KEY ([CarId]) REFERENCES [Car] ([ID]) ON DELETE CASCADE,
  CONSTRAINT [FK_CarDriver_Driver_DriverId] FOREIGN KEY ([DriverId]) REFERENCES [Driver] ([ID]) ON DELETE CASCADE
);
CREATE UNIQUE INDEX [IX_CarDriver_CarId] ON [CarDriver] ([CarId]);
CREATE UNIQUE INDEX [IX_CarDriver_DriverId] ON [CarDriver] ([DriverId]);

These two indexes at the end are the icing on the piece of cake. They show that EF exactly understands what's going on here.

Original, but updated, answer

"This can't be hard" is what I though when I read your question. But again I found that one-to-one associations are full of pitfalls. Here we go.

I assume that by 0..1 – 0..1 you mean that two objects can exist independent of each other, but may also be exclusively associated to one another.

Lets make it concrete. Car and Driver. Imagine a pool of many cars and drivers, among them CarA and a DriverA. Now suppose you want CarA to get associated to DriverA, and your implementation is that DriverA links himself to CarA. But as soon as DriverA does this, you want CarA to be for DriverA only, CarA's association is not optional any more, so it should be set as well, immediately.

How to implement that?

Option 1:

If this is the working model:

public class Car
{
    public int CarId { get; set; }
    public string Name { get; set; }
    public int? DriverId { get; set; }
    public virtual Driver Driver { get; set; }
}

public class Driver
{
    public int DriverId { get; set; }
    public string Name { get; set; }
    public int? CarId { get; set; }
    public virtual Car Car { get; set; }
}

technically, DriverA can have a foreign key to CarA and CarA a foreign key to DriverB.

Sample Image

Therefore, when the foreign key DriverA-CarA is established you should "simulaneously" establish the reverse foreign key CarA-DriverA. That is something you should do in code, meaning that it's a business rule. And in reality, it's not an atomic operation, so you must make sure that it's done in one database transaction.

The class model at least supports the use case, but it's too permissive. It needs to be constrained. More importantly, it won't work with EF. EF complaints about having to set a principal end. And if you do that, EF will not create a bidirectional association.

An alternative mapping was proposed here. I tried that but with two optional associations:

In the Driver's mapping configuration:

this.HasOptional(t => t.Car).WithMany().HasForeignKey(d => d.CarId);

In the Car's mapping configuration:

this.HasOptional(t => t.Driver).WithMany().HasForeignKey(c => c.DriverId);

(There is no data annotation alternative)

I found that EF only sets one foreign key value in the database when creating a new driver and car. You have to set and save both associations separately, managing your own transaction. With existing objects you still have to set both foreign keys, although this can be saved in one SaveChanges call.

Better options? Let's see...

Option 2:

This is the one-to-many association as mentioned in the link you refer to. This model needs external constraints, but creating the association is atomic. And you've still got a reference on one end and a collection on the other end. And it maps easily with EF.

Option 3:

You could create a junction table CarDriver that has two foreign keys, to Car and Driver, both of which comprise its unique primary key:

Sample Image

This is a regular many-to-many association. By default, EF would map this as a class model in which Car and Driver have collection properties pointing to each other, and the junction table is not mapped directly:

public class Car
{
    public int CarId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Driver> Drivers { get; set; }
}

public class Driver
{
    public int DriverId { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Car> Cars { get; set; }
}

Now the creation of association is an atomic operation. It's perfectly possible to map this model with EF. The mutual references are gone, but you still can get the FirstOrDefault() of the collection properties as a surrogate reference.

But there's an important gotcha. Now each object can have any number of associated counterparts. If you create an association, you need a coded business rule which checks if the the involved objects don't have any associations yet. Maybe this option is even worse than option 2. But I mentioned it because of the next option:

Option 4

Option 3 is atomic, but it also needs external constraints. To make an association exclusive, both columns in CarDriver should have unique keys, so each car or driver can only occur once in the table. By these indexes the model implements a bidirectionally optional 1:1 association all by itself. Any code working on it has to obey the rules. Safe and sound...

In EF6, since the introduction of HasIndex, this can be achieved by this mapping:

modelBuilder.Entity<Car>().HasOptional(c => c.CarDriver).WithRequired();
modelBuilder.Entity<Driver>().HasOptional(c => c.CarDriver).WithRequired();
modelBuilder.Entity<CarDriver>().HasKey(cd => new { cd.CarId, cd.DriverId });
modelBuilder.Entity<CarDriver>().HasIndex(cd => cd.CarId).IsUnique();
modelBuilder.Entity<CarDriver>().HasIndex(cd => cd.DriverId).IsUnique();

However, since EF6 adds indexes on FK fields by default, the unique indexes are added on top of the default non-unique indexes. So it still requires manual intervention in the migration code to remove the latter.

Conclusions

Option 1 is closest to what you want. But I don't like the obligation to set both foreign keys, it's easily forgotten or ignored by future developers.

But Option 2 and 3 have even heavier requirements in terms of coded business rules that can be forgotten. And the collections are unnatural as surrogate "1" ends. Option 3 has some appeal to me because Car and Driver are completely independent in the database and the association is a record with non-nullable foreign keys (DBAs tend to like that too).

Option 4 has the same appeal, and it's the best option when multiple applications would have to implement the external constraints that need to be imposed on option 2 and 3. Also, even if coded rules are forgotten, the database constraints are a final catch. But it can't easily be implemented by EF6.

How do I establish a one to one relationship with Entity Framework Code First

With EF, one-to-one relationships are only supported on tables that share a primary key. Your AccountConfig table has its own primary key, and a foreign key to Account. EF only supports a one-to-many relationship with this configuration.

This article has a nice explanation of 1:1 and 1:0..1 relationships in EF. The restriction here is a by-product of the fact that EF doesn't yet support unique constraints.

how to get data from a one to one and one to many relationship in EF Core 5

You will probably need to post code, but this issue commonly appears when projects disable lazy loading and then try to query against materialized entities where requested related data has not been loaded, or they have loaded that data eagerly and the related entity is optional.

For example, something like this:

var results = context.Ofertas.AsNoTracking().ToList();

var test = results.Where(x => x.Concurso.Organismo.Id == 1).ToList();

The issue here is that we have not fetched the Concursos or Oransimos of the Ofertas in the original query, and we have materialized the results with the first ToList call. The solution is either to ensure we put our Where condition somewhere where it can be integrated into the query, or we ensure we eager load all related data and we also ensure that optional/null references are accounted for:

a) Within the Query:

var results = context.Ofertas.AsNoTracking()
    .Where(x => x.Concurso.Organismo.Id == 1).ToList();

This will pass because our query will look for and return Ofertas that have the desired Organismo ID, even if the relationship in the data is optional. However, it will not eager load the Concurso or Organismo with the Ofertas returned. Those references can still be null.

b) Eager load related data and check for #Null:

var results = context.Ofertas
    .Include(x => x.Concurso)
    .ThenInclude(x => x.Organismo)
    .AsNoTracking()
    .ToList();

var test = results.Where(x => x.Concurso?.Organismo?.Id == 1).ToList();
// or
var test = results.Where(x => x.Concuso != null 
    && x.Concurso.Organismo != null 
    && x.Concurso.Organismo.Id == 1).ToList();

Like the original query this pre-fetches all Ofertas but eager loads all related data. The null checks would only be needed if/when a relationship can be NULL. If the relationships are required and eager loaded then the null checks are not needed, but it requires the data to either be eager loaded, or capable of being lazy loaded.

Where this can get interesting is that EF will populate related entities if/where those entities are already tracked. So for instance if anything had previously loaded one or more of the Concurso's that the first Oferta result references, as well as the Organismo ID #1, then those references would exist in the returned query data In this case I will eager load the Concursos with the Oferta, but pre-fetch just Organismo #1:

var organismo = context.Organismos.Single(x => x.Id == 1); 
// The DbContext is now tracking this entity, we do nothing with this instance.

var results = context.Ofertas.Include(x => x.Concurso).AsNoTracking.ToList();

var test = results[0].Concurso.Organismo.Id == 1; // Passes in our case.

var test2 = results.Where(x => x.Concurso.Organismo.Id == 1).ToList(); // can still fail!

The first test will pass because when we loaded the Ofertas from the DbContext, that context was already tracking Organismo #1 so it pre-populated that even when we don't eager load the organismos. However, unless all references happen to be #1, the rest of the Organismo references will be null and the second test will continue to fail.

Entity Framework 1 to 1 Relationship?

To configure 1-1 relation ship, you have to configure it this way

// Configure FirstClassID as PK for SecondClass
    modelBuilder.Entity<SecondClass>()
        .HasKey(m => m.FirstClassId);

    // Configure FirstClassId as FK for SecondClass
    modelBuilder.Entity<FirstClass>()
                .HasRequired(m => m.SecondClass) 
                .WithRequiredPrincipal(m => m.FirstClass);

this will lead to FirstClassId is a primaryKey in both classes and a foreign key in the second class, i.e , SecondClass is a weak entity

you might read more about one to one relation here

hope this will help you

Updating an Entity in EF with a 1:1 relationship

I think I see the problem now - when you do product.Ticketing = ticketing;, EF treats this as a new insert.

To avoid this, you can do one of these things:

Continue using the workaround (which is not a wokaround actually but just the way EF expects you to tell when to insert vs. when to update).
Now this depends on rest of your code and design, but instead of fetching the product, you can fetch the ticket and update its properties. Of course, this means that if the ticketing is not found, you need to insert it which then kinda looks like what you're already doing with UpdateProductTicketing.

Use the InsertOrUpdate pattern (I made some assumptions about your code but hopefully it gives you the idea - the main thing here is the InsertOrUpdate method):

public class ProductRepository : IRepository 
{    
    private SomeContext context;

    public void InsertOrUpdate(ProductTicketing ticketing) 
    { 
        context.Entry(ticketing).State = ticketing.ProductId == 0 ? 
                                       EntityState.Added : 
                                       EntityState.Modified; 
        context.SaveChanges(); 
    } 
}

// And a generic version
public void InsertOrUpdate<T>(T entity) where T : class
{
    if (context.Entry(entity).State == EntityState.Detached)
        context.Set<T>().Add(entity);

    context.SaveChanges(); 
}

1:1 relationship in Entity framework

you want a 1 to 1 relation between user and post ? A user can only post one, and only, post ?

Anyway, in EF (at least 6) a 1 to 1 relation can be established between two entities sharing the same PK. That is the PK is the FK. So you must set the PK of posts as a string.

Otherwise you are in a 1 to * relation.

Entity Framework 1 to 0 or 1 relationship configuration

Let add the related entities to the desired relationship along with their cardinality:

Parent 1 <--> 0..1 Child

You can read it this way:

(1) --> 0..1 Child means that each Parent can have 0 or 1 Child, or in other words, the Child property of the Parent entity is optional

(2) Parent 1 <-- means that each Child always have 1 Parent, or in other words, the Parent property of the Child entity is required.

The fluent configuration which corresponds to the above from Child side is:

HasRequired(c => c.Parent).WithOptional(p => p.Child);

or alternatively from Parent side:

HasOptional(p => p.Child).WithRequired(c => c.Parent);

These two are the correct ones and fully equivalent - you can use one or the another depending of whether you start the configuration from Child (as in your sample) or from Parent. To avoid discrepancies, never do both.

Why both your current solutions are incorrect?

Because:

(solution 1)

HasRequired(t => t.Parent).WithRequiredDependent(t => t.Child);

represents Parent 1 <--> 1 Child relationship (parent must have always 1 child).

(solution 2)

HasOptional(c => c.Parent).WithRequired(c => c.Child);

represents Parent 0..1 <--> 1 Child relationship (child can have 0 or 1 parent, parent must have always 1 child), i.e. the opposite of what you want.

Get value in 1-to-many relationship (EF Core)

Reason

This error occurs because you're trying to ask EF Core not to track a list of GUID. However, the list of Guid is a list of value types.

As you know, EF Core can only track a series of reference type, so the method signature of AsNoTracking<TEntity>() is :

public static IQueryable<TEntity> AsNoTracking<TEntity> (this IQueryable<TEntity> source) 
    where TEntity : class;

Note the constraints of where TEntity : class.

In other words, you can never invoke AsNoTracking<Guid>():

Events = await _context.Events
    .SelectMany(e => e.EventAdmins.Where(x => x.EventId == e.Id).Select(x => x.AdminRoleId))
    .AsNoTracking()    // Actually, it will invoke `AsNoTracking<Guid>()`
    .ToListAsync();

How to Fix

Your SQL doesn't seem valid. I guess you want to return a {Id, Name, EventRoleId}.

If you would like to do that with SelectMany, you could simply query as below:

var Events = await this._context.Events
    .SelectMany( 
        e => e.EventAdmins.Where(x => x.EventId == e.Id).Select(x => x.AdminRoleId),
        (p,g) => new {
            Id = p.Id,
            Name = p.Name,
            EventRoleId = g
        }
    )
    // .AsNoTracking()   
    .ToListAsync();

There's no need to call .AsNoTracking() at all. Because no tracking is performed if the result set does not contain any entity types.

As a side note, you shouldn't decorate the Event.EventAdmins with a [ForeignKey("Id")] attribute :


    public class Event {

        public Guid Id { get; set; }
        public string Name { get; set; }

        [ForeignKey("Id")]
        public IList EventAdmins { get; set; }

    }

Because the Event is the principal entity and the EventAdmin is the dependent entity. Only the EventAdmin has a foreign key that references the Event.