Violation of Command-Query Separation while creating new database entry

Question

I have a method that creates some entry in database and returnes it's Id. I would like to somehow separate these two because it violates Command-Query Separation.
e.g. method (simplified):

int CreatePost(database::Post newPost)
{
    using(var db = new database::MainModelContainer())
    {
        db.Posts.Add(newPost);
        db.SaveChanges();
        return newPost.Id;
    }
}

I know there is workaround using ref or out but I consider those solutions unclean. I would like to have command with following signature.

void CreatePost(database::Post newPost)

Is there a way I could accomplish this ?
Note: I need to know what is the Id of newly created entry immediately after it was created.

Answer 1

This could be solved with a memento pattern, except that you don't seem to need all of the undo-ability that comes with the memento. Something simple to capture the state of the Post object after it has been inserted would work:

public class PostCreator 
{
    public PostCreator()
    {
        CreatedId = null;
    }

    public void CreatePost(database::Post newPost)
    {
        if (CreatedPostId != null)
            throw new InvalidOperationException("Post Already Created!");

        using(var db = new database::MainModelContainer())
        {
            db.Posts.Add(newPost);
            db.SaveChanges();
            CreatedPostId = newPost.Id;
        }
    }

    public int? CreatedPostId { get; private set; }
}

Each time you create a new post, you will construct one of these creators to do it for you. You can query the CreatedPostId property at any time without concern of affecting state.

Answer 2

After saving the entity, Entity Framework populates the Id property with the appropriate value. So if you have an entity:

public class Post
{
    public int Id { get; set; } // primary key
    ...
}

And the method that saves it:

public void SavePost(Post post)
{
    using (var db = new YourDatabase())
    {
        db.Posts.Add(post);
        db.SaveChanges();
    }
}

You can do this from the client code to get the id:

var myPost = new Post();
Console.Writeline(myPost.Id); // displays 0
SavePost(myPost);
Console.Writeline(myPost.Id); // displays the new id

Answer 3

There's an excellent answer to this question by Mark Seemann at http://blog.ploeh.dk/2014/08/11/cqs-versus-server-generated-ids/.

The short version is to not depend on the database generated ID, rather create your own key and use that (he suggests GUIDs).

The database may still generate identity keys for it's internal use; these could be queried in adherence to CQS though. This actually implements several good practices: it separates the app from the database implementation (not all databases have automatic identity generation or implement it the same), and it inherently discourages exposing the internal sequential IDs out to users which can be a security vulnerability (OWASP Top 10 #4 Insecure Direct Object Reference).