Share via

Tip 26 – How to avoid database queries using Stub Entities

  • Article

What are Stub Entities?

A stub entity is a partially populated entity that stands in for the real thing.

For example this:

Category c = new Category {ID = 5};

is a stub entity.

It has only the ID populated, which indicates this is a stub for Category 5.

When are Stub Entities Useful?

Stub Entities are useful whenever you don’t really need to know everything about an entity, primarily because by using them you can avoid superfluous queries, but also because they are a lot easier to use than EntityKey.

Here are some examples:

Scenario 1 - Building a relationship using a Stub Entity:

This is probably the most common use of Stub Entities, lets say you want to build a relationship between a new product and an existing category, if you know the existing category has ID = 5, by default you would do this:

Product p = new Product {
Name = “Bovril”,
Category = ctx.Categories.First(c => c.ID == 5)
};
ctx.AddToProducts(p);
ctx.SaveChanges();

But this does a database query for the category, which is superfluous if all you are doing is building a relationship. You don’t need the whole entity, you already know all you need (the ID), so you can rewrite this to use a Stub Entity:

Category category = new Category { ID = 5};
//see tips 13 and 16 if you don’t like the string here!
ctx.AttachTo(“Categories”,category);

Product product = new Product {
Name = “Bovril”,
Category = category
};
ctx.AddToProducts(product);
ctx.SaveChanges();

And as a result you save a database query.

Note: You can use this approach to build new relationships via collections too.

Scenario 2 – Delete using a Stub Entity:

The standard way to do a delete looks like this:

Category category = ctx.Categories.First(c => c.ID == 5);
ctx.DeleteObject(category);
ctx.SaveChanges();

The first line is a query, to get an ‘full’ entity, but you can do a delete with a simple stub if the entity has no references to other entities:

Category category = new Category { ID = 5 };
ctx.AttachTo(“Categories”,category);
ctx.DeleteObject(category);
ctx.SaveChanges();

Again using a stub saves a query.

Scenario 3 – Delete a Stub Entity with References

If however the entity you want to delete has a reference (i.e. a product has a Category) the Entity Framework needs to know* about the reference in order to delete. Now if you do a query to get the entity you want to delete the EF gets this extra information automatically using a feature called relationship span.

But again we want to save the query, by using a stub entity we can tell the EF about the relationship using, you guessed it, another stub entity:

Product product = new Product {
ID = 5,
Category = new Category { ID = 5 }
};
ctx.AttachTo(“Products”,product);
ctx.DeleteObject(product);
ctx.SaveChanges();

And here by using two stub entities, we’ve again saved a query

* In .NET 4.0 is you use FK associations, this no longer true. The Entity Framework will happily delete without knowing about the references. As Roger Jennings would say hoorah.

Scenario 4 – Delete a Entity with a Timestamp

If an entity has a column used in concurrency token, generally this is a timestamp, you need to provide that value too, when you create the stub:

Order order = new Order{
OrderNo = 3425,
Timestamp = timestamp,
Customer = new Customer { ID = 7}
};
ctx.AttachTo(“Orders”, order);
ctx.DeleteObject(order);

Used a stub, saved a query.

Scenario 5 – Update an Entity

If you want to update an entity, you just need to attach something that represents the original version of the entity, and again that is where stubs come in:

Person person = new Person{
ID = 65,
Firstname = “Jo”,
Surname = “Andrews”
};
ctx.AttachTo(“People”, person);
person.Surname = “James”; // Yes my wife took my surname!
ctx.SaveChanges();

Used a stub check, saved a query check!

Summary

5 scenarios and 5 saved queries so as you can see Stub Entities are super useful.

Not only do they save database queries, which will make your apps perform and scale better. They also make your code more readable compared to the EntityKey alternatives.

The general pattern for using them is pretty simple too:

  1. Construct a stub Entity with the fields you need.
  2. Attach the stub Entity
  3. Do what you need to (Build Relationship, Delete, Update etc)

Let me know if you have any questions.

This is 26th post in my ongoing series of Entity Framework Tips.

Comments

  • Anonymous
    June 19, 2009
    PingBack from http://blogs.msdn.com/alexj/archive/2009/03/26/index-of-tips.aspx

  • Anonymous
    June 19, 2009
    Thank you for submitting this cool story - Trackback from DotNetShoutout

  • Anonymous
    June 21, 2009
    Can you do this in an EF4 POCO scenario without running into fixup trouble?

  • Anonymous
    July 19, 2009
    So, how would you use Stub Entities with Many-to-Many relationships?

  • Anonymous
    July 20, 2009
    @Robert,Well building a Many to many relationship using Stubs is pretty easy.Blog b = new Blog{ID = 1};Post p = new Post{ID = 2};ctx.AttachTo("Blogs",b);ctx.AttachTo("Posts", p);b.Posts.Add(p);ctx.SaveChanges();Removing is a little more involved, you need to have the relationship in the unmodified state first, before you can delete it:Blog b = new Blog{ID = 1};b.Posts.Add(new Post{ID = 2});ctx.AttachTo("Blogs",b); //Attachs b, p and b-pb.Posts.Remove(p); //Deletes b-pctx.SaveChanges();Hope this helpsAlex

  • Anonymous
    July 23, 2009
    Alex,Does this work in any version of the the Entity Framework.  I have only go .NET 3.5 and when I try attach the stub entity it just gives me:"An object with the same key already exists in the ObjectStateManager. The ObjectStateManager cannot track multiple objects with the same key"This is what my code looks like:SewerPipeHistory cloned = new SewerPipeHistory();cloned.SewerPipe = new SewerPipe { AssetId = 115 };this.Context.AttachTo("SewerPipe",cloned.SewerPipe);Is there something wrong with this code?Thanks,Nathan

  • Anonymous
    July 23, 2009
    @NathanYes this code works with 3.5 and 4.0 Beta 1.The problem is that if you ctx is long running it is definitely possible that a copy of the Entity is already attached. You should first check that it is not there. Look at ObjectStateManager.GetObjectStateEntry(..) to first check that a version of the object isn't already attached.Hope this helpsAlex

  • Anonymous
    September 02, 2009
    Hello,In my entities, the method "First" does not exists...I'm using VS2008 SP1 and .NET 3.5 SP1.I have a PieceGroup entity, but the method "First"is not available. I'm using:using (OtimizeDAL.OtimizeEntities context = new OtimizeDAL.OtimizeEntities())           {               context.PieceGroup.First.....What might be wrong?

  • Anonymous
    September 02, 2009
    IgorFirst() is an extension method that lives on both System.Linq.Enumerable and System.Linq.Queryable. So the only thing I can think of is to make sure you reference the have System.Linq namespace?Alex

  • Anonymous
    September 24, 2009
    Hello,I am trying to update a Document entity that is related to other two entities: Level and Subject. The relation between Document and each of this entities is of 1 to 1.DocumentUI is a UI model that contains the data but has no relation to the context. I have the following:   public void Update(DocumentUI ui) {     Level level = new Level { Id = ui.LevelId };     _context.AttachTo("Levels", level);     Subject subject = new Subject { Id = ui.SubjectId };     _context.AttachTo("Subjects", subject);     Document entity = new Document {       Id = ui.Id,       File = ui.File,       Published = ui.Published,       Title = ui.Title,       Updated = DateTime.UtcNow     };           _context.AttachTo("Documents", entity);     entity.Level = level;     entity.Subject = subject;     _context.SaveChanges();   } // UpdateI get the error:A relationship is being added or deleted from an AssociationSet 'FK_Documents_Levels'. With cardinality constraints, a corresponding 'Documents' must also be added or deleted.      What am I doing wrong?On "Scenario 5 – Update an Entity" you don't mention updating entities with relationships, in this case a 1 to 1 relationship.And a side note: You delete Entities on EF. Wouldn't be easier to have cascade delete on the database?Thank you,Miguel

  • Anonymous
    October 07, 2009
    RE: Scenario 1.This works great when adding a new item. But when updating an existing item, this fails. I keep getting the error: Store update, insert, or delete statement affected an unexpected number of rows (0). I ran Sql profiler and noticed that the where clause is checking for a null foreign key. Using your example, the where looks like "Where productid=8 and categories=null". In my case, I am just changing the foreign key

  • Anonymous
    October 08, 2009
    @Miguel, I have similar issues. The fix for me is:Load the reference. This is unfortunate. The only way to bypass this is to know the old reference id. There is a concurrency check done when updating. By not initially setting before changing, the t-sql generated assumes null.Set the reference entity key to the new reference. Save @Alex, could you please explain why a concurrency check is done for foreign keys? Can I set it to none as the default for other parameters?

  • Anonymous
    October 08, 2009
    @Miguel and Osa,Yes Miguel is right there is a concurrency check, you need to know the original values.This is because of the 'Independent Association' model adopted in 3.5.The EF need to remove the old association because you can't update an independent association (i.e. change the FK value) because conceptually that would be the equivalent of trying to update a table where every column is part of the Key.As a result to do an update you have to actually do a Delete / Insert instead.But in order to delete the old association you need all the information that identifies the association, unfortunately that means the PK and the old FK value.Hence the appearance the old FK values are part of the concurrency token.This is a real pain, and is why we added 'FK associations' in 4.0.With FK Associations the Association is conceptually (not just structurally) co-located with the Entity, so the EF explicitly knows it can update it without needing to know the old FK value, because the PK alone identifies the Entity, and all you need to do is update the Entity.Hope you managed to follow that...Alex

  • Anonymous
    October 13, 2009
    I noticed that all I needed to make stub updates was the foreign keys and that most times I used the initial value earlier. So basically I now save that data and use in my updates. Excerpts from my code is below:public interface IDataSource<T>   {

    ...
           bool UpdateItem(T obj);       //Only primary and foreign keys(that changed) are needed in oldObj
    //Sample implementation is below
           bool UpdateItem(T newObj, T oldObj);   }   public interface IEditView<T> : IView<T>   {       ...
    //when DataToUpdate is set, save the foreign keys. This should be used in OriginalDataToUpdate
           T DataToUpdate { get; set; }
    //default implementation returns default(T)
           T OriginalDataToUpdate { get; }   }   public interface IEditPresenter<T> : IPresenter<T>   {       IEditView<T> View { get; }
    //See implementation below
           void UpdateData(bool isPageValid);   }   //Implementation of UpdateData   if (isPageValid)           {               if (View.OriginalDataToUpdate != null)               {                   DataSource.UpdateItem(View.DataToUpdate, View.OriginalDataToUpdate);               }               else               {                   DataSource.UpdateItem(View.DataToUpdate);               }           }    //Pseudocode implementation of bool UpdateItem(T newObj, T oldObj)    oldObj.EntityKey = context.CreateEntityKey("T", oldObj);    context.Attach(oldObj);    context.ApplyPropertyChanges("T", newObj);    return ctx.SaveChanges() > 0;

  • Anonymous
    February 06, 2010
    Thanks for the great article; but I am running into ALL sorts of troubles with EF+MySQL. I am adding dummy entities to satisfy the foreign keys; however, EF for MySQL for some reason try to save the dummy entities as well :(...

  • Anonymous
    March 06, 2010
    Is it possible to use stub entities for queries?If, for example, I have the following:CategoryId (PK)ParentId (FK)Category 1(ParentId:0)Category 2(ParentId:1)Category 6(ParentId:2)Which becomes, in entity framework:class Category{ int CategoryId; Category Parent;}Is it possible to do the following using stub entities?from c in db.Categories where c.Parent.CategoryId=2;(I would normally just do "where C.ParentId=2", but this would result in an error 3007)or -Category category = from c in db.Categories where c.CategoryId=0;category.ParentCategory.CategoryId;(Again, normally I would just do category.ParentId, but not possible in entity framework).Or is the only option here to do a full query for the parent as well?Thanks in advance.

  • Anonymous
    March 06, 2010
    @Marco,When you are dotting through a relationship in a query, you are creating an expression that access properties, you aren't using stub entities.So something like:var query = from c in db.Categories where    c.Parent.CategoryId=2 select c;is completely fine.But doing this:var category = query.First();category.ParentCategory.Id will fail in 3.5 because you need to either explicitly load the ParentCategory or include it using query.Include(...)Note: 4.0 this will work if you have LazyLoading enabled.Hope this helpsAlex

  • Anonymous
    April 15, 2010
    I tried using stubs to remove many-to-many association like this:var ab = new Addressbook {ID = addressBookId};var addresses = addressIds.Select(aId => new Address {ID = aId});ab.Addresses.AddRange(addresses);_context.AttachTo(_context.AddressbooksSetName, ab);foreach (var address in addresses){   ab.Addresses.Remove(address);}_context.SaveChanges();where AddressbooksSetName is my T4 extension's generation result. It passes fine but relation is not deleted and in debug ObjectStateManager's _deletedRelationshipStore does not contain record about relation deletion right before SaveChanges call. Actually I see the same story if I load addressbook from context and the only way to make it work is to call ab.Addresses.Load() before removing address records. Is it by design?..

  • Anonymous
    April 08, 2011
    Hi, While using the Stub entities to attach, Updates to boolean fields, that tend to change their value from true to false, usually fails. This is because, by default all the boolean fields in the stub entity are initialised to false. When i call the ApplyCurrentValues() method, it doesnt detect any change. Is there any way to overcome this ?

  • Anonymous
    November 14, 2011
    Hi Alex,I am using the stub entities to attach to exisitng object in database, but when i attach the object none of the navigational properties are loaded. Is it the expexted behaviour ?Something Like :Entity : EmployeeEmployeeID (EnitityKey )ManagerIDNameIf in database i have a EmployeeEmployeeID : 4ManagerID : 1Name : Alexand i use Code likeEmployee existingEmployee = new Employee {EmployeeID = 4}context.Employee.Attach(existingEmployee )it still shows existingEmployee.ManagerID = 0and existingEmployee.Manager is also null. i expected existingEmployee.ManagerID = 1How i can acheive this.Any help will be greatly appreciated.

  • Anonymous
    November 14, 2011
    Hi Alex,I am using the stub entities to attach to exisitng object in database, but when i attach the object none of the navigational properties are loaded. Is it the expexted behaviour ?Something Like :Entity : EmployeeEmployeeID (EnitityKey )ManagerIDNameIf in database i have a EmployeeEmployeeID : 4ManagerID : 1Name : Alexand i use Code likeEmployee existingEmployee = new Employee {EmployeeID = 4}context.Employee.Attach(existingEmployee )it still shows existingEmployee.ManagerID = 0and existingEmployee.Manager is also null. i expected existingEmployee.ManagerID = 1How i can acheive this.Any help will be greatly appreciated.