Lookup in db or in code (enum)?

Maybe this is a solved problem already, but I couldn’t figure it out until today. With massive usage of NHibernate, lookup in db or in code always appear as a puzzle to me. For example, given a lookup dictionary like mime type: text/plain, text/html, image/gif and other stuff. It make sense to save all those data into a table then enable foreign key on all referenced tables. But, in code, those mime type id itself will appear like magic number, I don’t likes this.

So, creating an enum contains all items solve the magic number issue, partly, and introduce another sync concern, what if data in db and enum in code out of sync?

This post demos a brilliant idea of creating enum on the fly, but mime value has slash in it, this solution doesn’t work. Also, wrapper class around enum seems unnecessary.

Based on the suggestion from this post. here comes my solution,


   public enum MimeType
    {
        [Description("application/octet-stream")] ApplicationOctetStream,
        [Description("text/plain")] TextPlain = 1,
        [Description("text/html")] TextHtml,
        [Description("application/pdf")] ApplicationPdf,
        [Description("application/vnd.ms-excel")] ApplicationVndMsExcel,
        [Description("image/gif")] ImageGif,
        [Description("image/jpeg")] ImageJpeg,
        [Description("application/rtf")] ApplicationRtf,
        [Description("application/zip")] ApplicationZip,
        [Description("application/msword")] ApplicationMsword,
        [Description("application/mspowerpoint")] ApplicationMspowerpoint
    }

    /// <summary>
    /// Extension methods container for enum used to check synchronization between enum in code and data in db.
    /// see usage in should_fetch_all_mime_types() of EmailQueueRepositoryTest class
    /// </summary>
    public static class MimeTypeEx
    {
        public static MimeType ToMimeTypeValue(this string value)
        {
            foreach (FieldInfo fi in typeof (MimeType).GetFields())
            {
                if (!fi.IsStatic)
                {
                    continue;
                }

                object[] attrs = fi.GetCustomAttributes(typeof (DescriptionAttribute), false);
                if (attrs == null || attrs.Length <= 0)
                {
                    continue;
                }

                var descr = (DescriptionAttribute) attrs[0];
                if (0 == string.Compare(value.Trim(), descr.Description))
                    // in case varchar type. trim it before compare.
                {
                    return (MimeType) fi.GetValue(null);
                }
            }

            throw new InvalidDataException(value);
            //return MimeType.Unknown;
        }

        public static string ToMimeTypeString(this MimeType value)
        {
            foreach (FieldInfo fi in typeof (MimeType).GetFields())
            {
                if (!fi.IsStatic || (MimeType) fi.GetValue(null) != value)
                {
                    continue;
                }

                object[] attrs = fi.GetCustomAttributes(typeof (DescriptionAttribute), false);
                if (attrs == null || attrs.Length <= 0)
                {
                    continue;
                }

                var descr = (DescriptionAttribute) attrs[0];
                return descr.Description;
            }

            return string.Empty;
        }
    }

    /// <summary>
    /// Used to read mime type from db, then we can check synchronization with defined enum mime type.
    /// </summary>
    public class MimeTypeInDb
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual bool BinaryInd { get; set; }
    }

Test code, similar functionality should be done in app start or somewhere static ctor to ensure the synchronization.

       [Test]
        public void should_fetch_all_mime_types()
        {
            var results = _repository.FetchAll<MimeTypeInDb>();

            Assert.That(results.Count(), Is.GreaterThan(0));

            foreach (MimeTypeInDb mimeTypeInDb in results)
            {
                Console.WriteLine(mimeTypeInDb.Name.ToMimeTypeValue()); // will throw invalid data exception if enum not found.
            }

            foreach (MimeType mimeType in Enum.GetValues(typeof(MimeType)))
            {
                Console.WriteLine(mimeType.ToMimeTypeString());
                if (! results.Any(x => x.Name.Trim() == mimeType.ToMimeTypeString()))
                {
                    Assert.Fail(mimeType.ToMimeTypeString() +" not found in db.");
                }
            }
        }

About mapping the enum to int in FluentNHibernate:

  Map(x => x.MimeType).Column("mime_type_id").CustomType<MimeType>();

Note: using CustomType<int>() also works, but it will cause extra update problem for reading query. NH is trying to set column to enum string value.

About these ads

One thought on “Lookup in db or in code (enum)?

  1. I think this is one of the most important information for me.
    And i am glad reading your article. But want to remark on few general things, The
    website style is great, the articles is really nice : D.
    Good job, cheers

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