Entity framework single query insert child entity

Question

I am learning about Entity Framework (EF) using code first on version 6+ with C# POCOs. Consider the following example:

public class DatabaseContext : DbContext
{
    public virtual DbSet<TextElement> Textuals { get; set; }
}

public class TextElement
{
    [Key]
    public int                              Number   { get; set; }
    public string                           Content  { get; set; }
    public virtual ICollection<TextElement> Children { get; set; }
}

What I want to do is to add a new child to an existing parent. in pseudo SQL I could do something like:

INSERT INTO Textuals (Content, ParentId) VALUES ("Lorem Ipsum", 42)

Where 42 is the ID of the parent record.

I've been searching the web for the past day to get EF to do the same for me in C# code. The best I could find is:

using (var context = new  DatabaseContext())
{
    var parent = new TextElement { Number = 42, Children = new List<TextElement>() };
    var child  = new TextElement { Content = "I iz kiddo" };
    context.Textuals.Attach(parent);
    parent.Children.Add(child);
    context.SaveChanges();
}

However, the Attach call seems to run a query too. The same issue applies to deleting an entity by id, that needs an Attach as well, running an unnecessary query.

Is there a better performing alternative to do what I want: add a child entity to an existing parent?

Update

Based on cverb's answer I got the following code doing what I want:

public class TextElement
{
    [Key]
    public int                              Number   { get; set; }
    public string                           Content  { get; set; }
    public virtual ICollection<TextElement> Children { get; set; }
    // Added custom parent id reference, MUST be nullable to allow 
    // for entries without parent
    public int?                             ParentId { get; set; }
}

Now, because this is a name that breaks convention (convention dictates TextElementId which is as vague as can be) I needed to change the Context with a little "fluent api" code. This explicitly defines the relations between the properties in the class.

public class DatabaseContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TextElement>()
            .HasMany((e) => e.Children)        // Indicate the many relation
            .WithOptional()                    // Indicate that the many
                                               // relation is optional.
                                               // Don't require a nav prop
            .HasForeignKey((e) => e.ParentId); // The FK to use with the
                                               // relation.
    }

    public virtual DbSet<TextElement> Textuals { get; set; }
}

Which enables me to successfully apply Migrations as well.

Do you have a ParentIdproperty in your TextElement entity? — cverb
– cverb, Commented Aug 18, 2015 at 12:02
Only indirectly with the Children property. I obviously like EF to handle the implementation details =). But without performance overhead if possible. — Nebula
– Nebula, Commented Aug 18, 2015 at 12:20

cverb · Accepted Answer · 2015-08-18 12:27:12Z

2

You should add the ParentId explicitly on your TextElement entity.

public int? ParentId { get; set; }

If you don't know how to do that, there is some good information on this page under section "Understanding Convention for One to Many Relationships".

And then you can add a new entity by just using the following code.

using (var context = new  DatabaseContext())
{
    var child  = new TextElement { Content = "I iz kiddo", ParentId = 42 };
    context.SaveChanges();
}

No need for attaching or adding the child to the parent's Children-list, because Entity Framework will pick up the change itself.

answered Aug 18, 2015 at 12:27

cverb

6436 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nebula Over a year ago

Thanks for the hint and reference. I'll have a go at that! It's unfortunate that the implementation details need to be in the models as well but I guess I'll have to make do. I'll accept if it fits the bill!

lem2802 · Accepted Answer · 2015-08-18 11:59:21Z

0

the way that you are using is the correct way... here, the EF context do all the job for you: start a transaction do the inserts edits and deletes needed, and finally commit the changes.

answered Aug 18, 2015 at 11:59

lem2802

1,1627 silver badges18 bronze badges

1 Comment

Nebula Over a year ago

So you're basically saying that I should just accept 100% overhead on my queries in this case? I only need one query to insert a new record but I can't make the association to the parent node without running an additional query?

Yasser Shaikh · Accepted Answer · 2015-08-18 11:59:49Z

0

Going by this

What I want to do is to add a new child to an existing parent

and this sql script you used

INSERT INTO Textuals (Content, ParentId) VALUES ("Lorem Ipsum", 42)

here is what should work for you.

using (var context = new  DatabaseContext())
{
    var newTextual  = new Textuals { Number = 42, Content = "I iz kiddo" };
    context.Textuals.Attach(newTextual);
    context.SaveChanges();
}

answered Aug 18, 2015 at 11:59

Yasser Shaikh

47.9k50 gold badges208 silver badges290 bronze badges

1 Comment

Nebula Over a year ago

Please read my question. This would just overwrite the existing parent's content with the child's content. Note that there are (at least) three columns: Number, Content and 'ParentID'. The latter being the column that EF generates for that association. In the database it actually looks like: TextElement_Number. That would make you solution: var newTextual = new Textuals { TextElement_Number= 42, Content = "I iz kiddo" }; where it not that I can't directly access TextElement_Number.

Collectives™ on Stack Overflow

Entity framework single query insert child entity

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related