Entity framework: How to return a row from a table with composite keys?

Question

  public class UserBuilding
    {
        [Key, Column(Order = 0)]
        public int UserId { get; set; }
        [Key, Column(Order = 1)]
        public int BuildingId { get; set; }
        public int BuildingLevel { get; set; }
    }

If I wanted to return all the different buildings that belong to a user, I would do the following:

database.UserBuildings.Where(b => b.UserId == userId);

My question is what if I wanted to return a specific building from a specific user? What would be the most 'efficient' way of doing this? Is there a better way (such as a built-in function) than the following:

database.UserBuildings.Where(b => b.UserId == userId && b.BuildingId == buildingId);

Hennessy

Answer 1

I think you are lookding for DbSet.Find method. This method finds entity by primary key. If you have composite primary key, then pass key values in the order they defined in model:

var userBuilding = database.UserBuildings.Find(userId, buildingId);

Rancher

works like a charm thank you! Do you think this would be considered "more efficient" in terms of the speed of the algorithm? – Hennessy

@Hennessy Find first checks if entity already in context before querying database, so yes, its more efficient – Rancher

@Deniz: Find can actually be much slower if the entity is not loaded (#11686725). Rule of thumb, maybe: Use Find if it is likely that the object is already in the context. If you know that it isn't loaded yet or it is very unlikely, use the query in your question. – Inspire

Thanks for the input, in my case the entity will always definitely be there. I will bear that in mind. – Hennessy

@Inspire thanks, didn't know that Find is much slower if entity is not in context. BTW is there any tests of performance? – Rancher

@lazyberezovsky: Regarding Find I only know the measurement in the linked question. Generally automatic change detection in DbContext ( = the internal DetectChanges call that occurs in many EF methods) is notorious for being slow (Attach is another example: https://mcmap.net/q/29010/-what-causes-attach-to-be-slow-in-ef4. Or Add: https://mcmap.net/q/28995/-why-is-inserting-entities-in-ef-4-1-so-slow-compared-to-objectcontext). But in cases where only a few entities are attached to the context it doesn't matter. It's usually only to consider for "bulk operations". – Inspire