This project is read-only.

Logic of GetIdentity method

Jan 2, 2012 at 8:34 PM

I do not understand the logic of GetIdentity method.

I can use GetIdentity so (base repository method):

public virtual void Insert(T obj, IDataMapper db)
{
            object newId = db.Insert<T>().GetIdentity().Entity(obj).Execute();
            obj.Id = Convert.ToInt32(newId);
}

but GetIdentity method has strange comment:

Note: this does not run when a query has been manually passed in.

And I cannot use methods:

Insert<T>(T entity)

Insert<T>(string tableName, T entity)

Insert<T>(T entity, string sql)

and get identity,

Why I cannot  use identity by setting optionally parameter?

Jan 3, 2012 at 12:08 AM

vitidev:

First, GetIdentity() only works for generated queries.  That is by design, although the code certainly could be modified to append the identity query to the end of your manual query.  My reasoning for this was that the user can manually add the identity query if they want to.  Maybe it would be better to still allow the GetIdentity method for manual queries?

I did, however, modify the Insert<T>(T entity) and Insert<T>(string tableName, T entity) methods so that they will automatically call GetIdentity() if you have A) mapped a column with IsAutoIncrement = true, and B) the current dialect has an identity query implemented.  So now you can do this and get an identity column filled in automatically:

       [TestMethod]
        public void GeneratedQueryShouldBeAbleToGetIdentity_UsingSimpleOverload()
        {
            using (var db = CreateSqlServerDB())
            {
                try
                {
                    db.BeginTransaction();

                    Order order = new Order { OrderName = "Order1" };

                    db.Insert<Order>(order);

                    Assert.IsTrue(order.ID > 0);
                }
                finally
                {
                    db.RollBack();
                }
            }
        }

Currently, if you have a manual query and you manually call the identity query, you should be able get the identity like this:

       [TestMethod]
        public void ManualQueryShouldBeAbleToGetIdentity()
        {
            using (var db = CreateSqlServerDB())
            {
                try
                {
                    db.SqlMode = SqlModes.Text;

                    db.BeginTransaction();

                    Order order = new Order { OrderName = "Order1" };

                    var identity = db.Insert(order, "INSERT INTO [Order] (OrderName) VALUES (@OrderName);SELECT SCOPE_IDENTITY();");

                    Assert.IsTrue(int.Parse(identity.ToString()) > 0);
                }
                finally
                {
                    db.RollBack();
                }
            }
        }
Jan 3, 2012 at 12:33 AM

After looking at the code, I decided that you should be able to use GetIdentity() on manually generated queries.  All of the Insert overloads will now call GetIdentity() if you have specified an AutoIncrement column, and if it is supported by the current dialect. 

Here are the supporting integration tests:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Marr.Data.IntegrationTests.DB_SqlServerCe.Entities;

namespace Marr.Data.IntegrationTests.DB_SqlServer
{
    [TestClass]
    public class IdentityTest : TestBase
    {
        [TestInitialize]
        public void Setup()
        {
            MapRepository.Instance.EnableTraceLogging = true;
        }

        [TestMethod]
        public void GeneratedQueryShouldBeAbleToGetIdentity_UsingSimpleOverload()
        {
            using (var db = CreateSqlServerDB())
            {
                try
                {
                    db.BeginTransaction();

                    Order order = new Order { OrderName = "Order1" };

                    db.Insert<Order>(order);

                    Assert.IsTrue(order.ID > 0);
                }
                finally
                {
                    db.RollBack();
                }
            }
        }

        [TestMethod]
        public void GeneratedQueryShouldBeAbleToGetIdentity()
        {
            using (var db = CreateSqlServerDB())
            {
                try
                {
                    db.BeginTransaction();

                    Order order = new Order { OrderName = "Order1" };

                    db.Insert<Order>().Entity(order).GetIdentity().Execute();
                    
                    Assert.IsTrue(order.ID > 0);
                }
                finally
                {
                    db.RollBack();
                }
            }
        }

        [TestMethod]
        public void ManualQueryShouldAutomaticallyGetIdentity()
        {
            using (var db = CreateSqlServerDB())
            {
                try
                {
                    db.SqlMode = SqlModes.Text;

                    db.BeginTransaction();

                    Order order = new Order { OrderName = "Order1" };

                    var identity = db.Insert(order, "INSERT INTO [Order] (OrderName) VALUES (@OrderName);");

                    Assert.IsTrue(int.Parse(identity.ToString()) > 0);
                }
                finally
                {
                    db.RollBack();
                }
            }
        }

        [TestMethod]
        public void ManualQueryWithManualIdentityStatementShouldBeAbleToGetIdentity()
        {
            using (var db = CreateSqlServerDB())
            {
                try
                {
                    db.SqlMode = SqlModes.Text;

                    db.BeginTransaction();

                    Order order = new Order { OrderName = "Order1" };

                    var identity = db.Insert(order, "INSERT INTO [Order] (OrderName) VALUES (@OrderName);SELECT SCOPE_IDENTITY();");

                    Assert.IsTrue(int.Parse(identity.ToString()) > 0);
                }
                finally
                {
                    db.RollBack();
                }
            }
        }
    }
}

Jan 3, 2012 at 12:36 PM

I do not know whether to do GetIdentity automatically, and not by setting the optional parameter, but I do not have use cases where it had not needed the id immediately after insertion. So while this is an acceptable solution.

Jan 3, 2012 at 2:53 PM

If you need to do an insert without calling GetIdentity, you can still call this overload: Insert<T>().  It allows you to configure your insert from the ground up, and then omit the call to GetIdentity().  The other Insert overloads are all "shortcuts" that call this overload behind the scenes to build the query, and it does seem like the most common use case will be to get the identity when inserting.  Also keep in mind that if the entity being inserted does not have an IsAutoIncrement column specified, then the identity query will not be called anyway - so that does add another point of intelligence.  But if a user has specified an IsAutoIncrement field, then it makes sense to me that they will probably want it populated with the identity value on insert.

My thinking is that the simple builder overloads can be used anytime someone wants full control; otherwise, the shortcut overloads can be used when appropriate.