Check value of same column in all tables [Entity Framework]

Question

I have more than 100 tables in data base in which 60+ table's contain column called ShortCode nvarchar(12) which represent globally unique code of that record.

Now is there any way to find that the ShortCode value eg. AST_SHIP_FIRE present in any of the table in database.

Note:ShortCode is user define.

currently I am try below code,it works but I have to code for all table.

if (entities.Table1.Any(x =>  x.ShortCode.Trim().ToLower() == a.ShortCode.Trim().ToLower()) 
{return false;}
else if(entities.Table2.Any(x => x.ShortCode.Trim().ToLower() == a.ShortCode.Trim().ToLower()))
{return false;}
else if( entities.Talble3.Any(x => x.ShortCode.Trim().ToLower() == a.ShortCode.Trim().ToLower()))
{return false;}
.
.
.
else
{
//insert code
}

I think there may be more efficient way.

Answer 1

Ok, maybe not very straightforward but lets do it!

First of all define an interface for ShortCode property and implement it by any entity that has it:

public interface ITableWithShortCode
{
    public string ShortCode { get; set; }
}

public class Table1 : ITableWithShortCode
{
    public long Id { get; set; }
    public string ShortCode { get; set; }
}

public class Table2 : ITableWithShortCode
{
    public long Id { get; set; }
    public string ShortCode { get; set; }
}

Now using power of Reflection you can write a method like this:

public bool IsExistShortCode(string shortCode)
{
    using (var context = new AppDbContext())
    {
        /* 
           find all tables that are defined in your DbContext and are implemented ITableWithShortCode like:
           public DbSet<Table1> Table1 { get; set; }
           public DbSet<Table2> Table2 { get; set; }
           ...
        */
        var properties = typeof(AppDbContext).GetProperties()
            .Where(p => p.PropertyType.IsGenericType
                && typeof(ITableWithShortCode).IsAssignableFrom(p.PropertyType.GenericTypeArguments[0]));

        foreach (var property in properties)
        {
            var contextProp = (IQueryable<ITableWithShortCode>)property.GetValue(context);
            bool isExist = contextProp.Any(p => p.ShortCode == shortCode);
            if (isExist)
                return true;
        }

        return false;
    }
}

Note: You can do some optimization on this code, I prefered to keep it in its simplest state to show the idea. But in production, for example you can easily cache DbContext properties on startup and use it afterward