Entity Framework 5.0b2 Code First: One-To-Many and One-To-One for the same table, WITH Cascade Delete

Question

After several hours of trial and error, I reached to this thread which explains how to establish a One-To-Many relationship and a One-To-One relationship with the same two types.

However, I cannot get this to work with Cascade Delete:

Thrown: "Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values." (System.Data.UpdateException) Exception Message = "Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values.", Exception Type = "System.Data.UpdateException"

This only happens if I don't unset the 1:1 relationship (see code below), which I guess makes sense given that it would create an invalid reference. I'm just wondering if there is a better way to represent this.

Sample code:

class Program
{
    static void Main(string[] args)
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<Context>());

        using (var ctx = new Context())
        {
            var user = new User();

            ctx.Users.Add(user);
            ctx.SaveChanges();

            var source = new PaymentSource();
            user.PaymentSources = new Collection<PaymentSource>();
            user.PaymentSources.Add(source);
            user.DefaultPaymentSource = source;
            ctx.SaveChanges();

            // if I don't do this, I get ordering exception
            user.DefaultPaymentSource = null;
            ctx.SaveChanges();

            ctx.Users.Remove(user);
            ctx.SaveChanges();

            Assert.Equal(0, ctx.Users.Count());
            Assert.Equal(0, ctx.PaymentSources.Count());
        }
    }
}

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

    public virtual ICollection<PaymentSource> PaymentSources { get; set; }
    public virtual PaymentSource DefaultPaymentSource { get; set; }
    public int? DefaultPaymentSourceId { get; set; }
}

public class PaymentSource
{
    public int Id { get; set; }
    public virtual User User { get; set; }
    public int UserId { get; set; }
}

public class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<PaymentSource> PaymentSources { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>()
            .HasOptional(u => u.DefaultPaymentSource)
            .WithMany()
            .HasForeignKey(u => u.DefaultPaymentSourceId)
            .WillCascadeOnDelete(false);

        modelBuilder.Entity<PaymentSource>()
            .HasRequired(p => p.User)
            .WithMany(p => p.PaymentSources)
            .HasForeignKey(p => p.UserId)
            .WillCascadeOnDelete();
    }
}

Can you please explain the two tables which need both 1-1 and 1-* relationship? more specific: is it suppose to give the abstraction of a tree structure? — G.Y, May 14 '12 at 13:52
I thought the model was self-explanatory but I guess not. Well, a `User` has N `PaymentSource`s (1:N) and also keeps a default one (1:1 well, 0:1 to be strict.) — georgiosd, May 14 '12 at 15:29

score 2 · Answer 1 · edited May 14 '12 at 18:49

2

I listed other options to describe your abstraction:

A.

How about using 3 tables like that:

user 1-* paymentSource
user 1-0..1 DefaultPaymentSource
DefaultPaymentSource 0..1-1 PaymentSource

or this:

B.

user 1-* paymentSource
user 1-0..1 DefaultPaymentSource
DefaultPaymentSource --derive from--> PaymentSource

or this:

C.

user 1-* paymentSource
PaymentSource has addtional boolean field for "IsDefault"

I vote for choice B as best one.

I am certain that having two relations comming from the same source table to the same destination table is not going to be a good idea.. it is probably breaking some rule or pattern regarding databases best practice.

edited May 14 '12 at 18:49

Lucas Reis

743
11
21

answered May 14 '12 at 16:11

G.Y

6,042
2
37
54

I am against B and C because they can easily create problems if you fail to maintain only *one* `DefaultPaymentSource`in code. In other words, a perfect model would ensure that a) only one `PaymentSource` could be default at any time, without involving logic and b) does not create problems if the default is deleted (my model doesn't do that). How would A) translate in code? Not sure I understand it fully. – georgiosd May 14 '12 at 16:27
hmm.. yup - C would have that problem you described.. But A or B can be used without a problem of multiple defaults. However in both you will need to make sure that when you delete a source it must not be used as default. regarding Code, you just create a method for deleting and fill it with the conditions you need before you delete.. but I understand - you looking for somthing which is more cascadable on operations - are you sure EF allow you to set things up that way? (imho) I don't think so.. but if i'm wrong please let me know. – G.Y May 14 '12 at 16:42
You may be right and it doesn't allow it, I don't know either :) - hence the question! Can you elaborate on A? B would have a lot of boxing/unboxing in code. – georgiosd May 14 '12 at 17:56
You can do it by using POCO, this will give you the ability to describe exactly how you want stuff to be deleted if you choose B or C. another option is to use partial class on the entity you want to specialize. – G.Y May 14 '12 at 21:17

Entity Framework 5.0b2 Code First: One-To-Many and One-To-One for the same table, WITH Cascade Delete

1 Answers1

Linked