Generating like-JIRA tickets using Entity Framework (EF6) and MS SQL (TSQL)


The problem

We have a Master/Detail relationship with two entities, and we want to identify detail records by using a friendly ID. The friendly ID is based on a master code (which it is specified in the Master Table) and a Detail Number (which works like a counter on each given master code). For example

Master Table ( When IsAutoGen is 1, then Details should auto generate an friendly ID using the AutoGenPrefix)

Id          IsAutoGen AutoGenPrefix
----------- --------- -------------
1           1         GRPA
2           1         GRPB
5           0         NULL

Detail table AutoGenPreix-AutoGenNumber identify uniquely each ticket by providing a friendly-auto-Incremental Id.

Id          MasterId    AutoGenPrefix AutoGenNumber
----------- ----------- ------------- -------------
361         1           GRPA          1
362         2           GRPB          1
363         5           NULL          1
364         1           GRPA          2
365         2           GRPB          2
366         5           NULL          2
367         1           GRPA          3
368         2           GRPB          3

Remarks

Certainly this problem may have many different solutions. For example it is technically possible to do all the computations only using C# and Entity Framework, thread locks and database transactions to support concurrency. However by doing it so, the developer will be responsible for dealing with concurrency, it will lock the database for longer periods since changes happens at business logic layer. Thus, this approach attempts to delegate the concurrency handling task to the database engine and reduce the lock time when possible. This solution is very particular to Entity Framework and Microsoft SQL, although ORMs and Triggers are concepts available in most of the platforms nowadays.

The solution

Just in case, you have skipped the test before, I encourage you to read the remarks. That said, First lets create our sample tables by using the following T-SQL script:

CREATE TABLE [dbo].[Master] (
    [Id]            INT         IDENTITY (1, 1) NOT NULL,
    [IsAutoGen]     BIT         CONSTRAINT [DF_Master_IsAutoGen] DEFAULT ((0)) NOT NULL,
    [AutoGenPrefix] VARCHAR (5) NULL,
    CONSTRAINT [PK_Master] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE TABLE [dbo].[Detail] (
    [Id]            INT         IDENTITY (1, 1) NOT NULL,
    [MasterId]      INT         NOT NULL,
    [AutoGenPrefix] VARCHAR (5) NULL,
    [AutoGenNumber] INT         NULL,
    CONSTRAINT [PK_Detail] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Detail_Master] FOREIGN KEY ([MasterId]) REFERENCES [dbo].[Master] ([Id])
);
GO

As simple as two tables, in order to establish a master-detail relationship. Now with the same ease, lets see the suggested trigger:

CREATE TRIGGER  [dbo].[Insert_Detail_ReadableId]
ON [dbo].[Detail]
INSTEAD OF INSERT
AS
BEGIN
   SET NOCOUNT OFF;
	INSERT INTO [Detail] ([MasterId],[AutoGenPrefix], [AutoGenNumber])
	SELECT 
		I.[MasterId] AS [MasterId],
		M2.AutoGenPrefix AS [AutoGenPrefix],
		ISNULL(ROW_NUMBER() OVER(ORDER BY I.[Id]) + M.[AutoGenNumber],1) AS [AutoGenNumber]
	FROM INSERTED  I
	LEFT JOIN [Master] M2 ON I.[MasterId] = M2.[Id]
	LEFT JOIN (
		SELECT 
			[MasterId],
			MAX(ISNULL([AutoGenNumber],0)) AS [AutoGenNumber]
		FROM [AutoGenDB].[dbo].[Detail]
		GROUP BY [MasterId]
	) M ON I.[MasterId] = M.[MasterId]

	SELECT [Id] FROM [Detail] WHERE @@ROWCOUNT > 0 AND [Id] = SCOPE_IDENTITY();
END

Notice that the trigger is responsible of performing the INSERT since it has been configured to be INSTEAD OF INSERT. The triggers uses the INSERTED variable to hold a table with all the records that should have been inserted. Thus the application performs a INSERT INTO using as data source the INSERTED table, the inserted max AutoGenNumber per AutoGenPrefix and a row number to be added to the Max AutoGenNumber.
Pay attention to the last line, which returns the inserted ID, this is required by Entity Framework to detect as successful the INSERT operation (see StackOverflow | OptimisticConcurrencyException — SQL 2008 R2 Instead of Insert Trigger with Entity Framework or StackOverflow | error when inserting into table having instead of trigger from entity data framework )

This is all we need in the TSQL level. Every time we attempt to INSERT a new record, right after the INSERT request, our code will perform the inserted by computing some additional data.

In our C# code, I’ll assume we’re using an EDMX file and using DB first approach. Thus, we usually only update our DbContext by updating our models from Visual Studio (VS).

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AutoGenDB
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new AutoGenDB.AutoGenDBEntities())
            {
                // if master table is empty, then it inserts some master records.
                if (!context.Masters.Any())
                {
                    for (int masterIndex = 0; masterIndex < 5; masterIndex++)
                    {
                        var isAutoGenEnabled = masterIndex % 2 == 0;
                        var newMaster = new Master { IsAutoGen = isAutoGenEnabled, AutoGenPrefix = isAutoGenEnabled ? "GRP" + Convert.ToChar((masterIndex % 65) + 65) : default(string) };
                        context.Masters.Add(newMaster);
                    }
                    context.SaveChanges();
                }

                // Inserts some details on different groups
                List<Detail> details = new List<Detail>();
                List<Master> masters = context.Masters.AsNoTracking().ToList();
                for (int i = 0; i < 100; i++)
                {
                    var masterId = masters[i % masters.Count].Id;
                    details.Add(new Detail() { MasterId = masterId });
                }
                context.Details.AddRange(details);
                context.SaveChanges();

                // Prints 10 auto generated IDS
                foreach (var master in context.Masters.AsNoTracking().Take(3))
                    foreach (var detail in master.Details.Take(5))
                        Console.WriteLine(master.IsAutoGen ? "Ticket:{0}-{1}" : @"N/A", detail.AutoGenPrefix, detail.AutoGenNumber);
            }
            Console.ReadKey();
        }
    }
}

The previous C# code inserts some records and prints the information generated by the Trigger. At this point there is only one missed component.
If we run the code as it is. The application won’t load the auto generated values, this is because Entity Framework does not know that these columns were populated. An option is to instruct Entity Framework about when it should reload the entities. The following code, decorates the Detail class with an IDetail interface which can be used in the DbContext to identify the entity type that should be reloaded.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace AutoGenDB
{
    interface IDetail
    {
        int? AutoGenNumber { get; set; }
        string AutoGenPrefix { get; set; }
        int Id { get; set; }
        Master Master { get; set; }
        int MasterId { get; set; }
    }

    public partial class AutoGenDBEntities : DbContext
    {
        public override int SaveChanges()
        {
            var entriesToReload = ChangeTracker.Entries<IDetail>().Where(e => e.State == EntityState.Added).ToList();
            int rowCount = base.SaveChanges();
            if (rowCount > 0 && entriesToReload.Count > 0)
                entriesToReload.ForEach(e => e.Reload());
            return rowCount;
        }

    }

    public partial class Detail : AutoGenDB.IDetail
    {

    }
}

Please notice how the subclass of the DbContext is overriding the SaveChanges method in order to request to reload the instances of IDetail. Reloading entities may or may not be desirable in depending on the application.
With these overrides, the DbContext will know when a given entity should be reloaded.

As usual the code sample can be check at https://github.com/hmadrigal/playground-dotnet/tree/master/MsDotNet.EntityFrameworkAndTriggers

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s