Which .NET ORMs have good support for composite keys

Question

We have a large existing database that uses composite primary and foreign keys almost exclusively. We are tasked with writing a large-ish C# application that talks to this database (on SQL Server 2012).

We are considering using an ORM, but most ORMs that I know of seem to discourage (or plain out not support) the use of composite keys (e.g. NHibernate, Massive, PetaPoco, etc).

Performance and throughput is important for this application.

What ORMs (if any) have good support for composite keys, and good performance to boot? Which would you recommend?

nhibernate do support composite keys: http://stackoverflow.com/q/5709141/70386 — jgauffin, Jan 09 '13 at 07:59
What do you mean with "good support"? EF, e.g., can work with composite keys, but you should define criteria for "good". — Dennis, Jan 09 '13 at 07:59
@Dennis I mean that I would not have to go through hoops in order to work with this database. Since almost all the tables have composite keys, I would like something that minimizes pain dealing with them. To quote the NHibernate article that I linked: "there's some extra setup work, queries are more painful, and they tend to be less optimized in terms of lazy loading". ORMs for which there are such statements in abundance do not seem to have what I would call "good support", but maybe I'm mistaken? — Eyvind, Jan 09 '13 at 08:04
It will of course be more work since composite keys ARE more work. — jgauffin, Jan 09 '13 at 08:10
@Eyvind: well, your conclusions are very hasty. You've just read a word "painful". Have you tried to write a code? Maybe, this "painful" will be suitable for you - any criteria is a subjective thing... That's why I'm asking you to define, what things will be important for **you**. — Dennis, Jan 09 '13 at 08:12
@Dennis: I agree. However, this project is just starting, so I was hoping to get some pointers on where to start prototyping :) I will try to get back to the question with some more specific criteria in a while. — Eyvind, Jan 09 '13 at 09:13

Dennis · Answer 1 · 2013-01-09T08:36:10.753

Entity Framework supports composite keys. E.g., here's an entity with composite key:

public class MyEntity
{
    public int NodeId { get; set; } // this is a part of entity key
    public int ItemId { get; set; } // this is a part of entity key
    public string Name { get; set; }
}

...its configuration for Code First approach, using fluent API:

internal sealed class MyEntityConfiguration : EntityTypeConfiguration<MyEntity>
{
        public MyEntityConfiguration()
        {
            // this is a composite key configuration
            HasKey(_ => new { _.NodeId, _.ItemId });

            Property(_ => _.NodeId);
            Property(_ => _.ItemId);
            Property(_ => _.Name);
        }
}

... and query sample:

context.MyEntities.SingleOrDefault(myEntity => myEntity.NodeId == 1 && myEntity.ItemId == 1);

What is not so good with composite keys in EF:

you can't declare a separate type for key. Hence, if your key consists of three properties, you'll have three properties instead one key property;
you should write your own fixup code to achieve key consistency, if changes to the existing entity's key are allowed (you can change one key property, and forget about another).

I don't know about any performance issues with EF and composite keys. I believe, that this is database engine responsibility.

Which .NET ORMs have good support for composite keys

1 Answers1