Exposing database field to 2 properties

Question

Is there a way to expose 1 column from the database to 2 properties in a model class?

Example use case [NOT WORKING, HYPOTHETICAL EXAMPLE]:

public interface ISearcheable
{
    public string SearcheableField { get; set; }
}

public class User : ISearcheable
{
    public string Username { get; set; }
    [DisplayName("Username")]
    public string SearcheableField { get; set; }
}

public class Book : ISearcheable
{
    public string Title { get; set; }
    [DisplayName("Title")]
    public string SearcheableField { get; set; }
}

Now if I wanted to search on each, I could just use:

db.entity<T>.Where(w => w.SearcheableField == "Username to check")

Answer 1

I would use some code similar to the following:

public interface ISearcheable
{
    // This is a read-only property.
    public string SearcheableField{get;}
}

public class User:ISearcheable{
    public string Username {get;set;}

    // Let this return the property that you want to search by and do not map it to the database.
    [NotMapped]
    public string SearcheableField 
    {
        get { return Username; }
    }
}

public class Book:ISearcheable{
    public string Title {get;set;}
    
    // Let this return the property that you want to search by and do not map it to the database.
    [NotMapped]
    public string SearcheableField 
    {
        get { return Title; }
    }
}

This would avoid duplicate of data and fields in the database.

However, just be mindful of this approach. For Example, a SQL Select statement that said SELECT author FROM book WHERE book.Title LIKE '%TEST%' would work, in Entity Framework LINQ-to-Sql this could produce unexpected results if using the SearchableField but the Username (for example) is not included in the LINQ. This should only be a concern if using Dynamic Linq and selecting only specific properties.

Also note I would personally call this field SearchPropertyValue.

Answer 2

I don't know if you can map the same table column to two properties on your model but I'm quite sure it's not the right way to do it.

For example, suppose the column "user_name" of the database is mapped in both User properties. Now look at this code:

var user = db.Entity<User>().First(); // Username = "Niko", SearcheableField = "Niko"
user.Username = "Joe";
var list = db.Entity<User>().Where(w=> w.SearcheableField == "Joe");

Now, what do you expect to have in your database? Joe or Niko? And in the list array, do you want the user you just changed the name?

From my point of view, is better if you use a different approach to the problem.

For example, if you can query the data in memory, you can use this approach:

public interface ISearcheable {
   string SearcheableField { get; }
}

public class User : ISearcheable {

   public string Username { get; set; }

   public string SearcheableField
   {
      get
      {
         return Username;
      }
   }
}

If you can't and won't change your DB for save a new column, you can use an Extension Method to it:

public static IQueryable<User> Search(this IQueryable<Usery> query, string value)
{
   return query.Where(x => x.Username == value);
}

And then you can use it in this way:

db.Entity<User>().AsQueryable().Search("username to check");

Pay attention: I wrote it by hand and whith method names I remember. There can be few errors in synstax and code can't compile.

Answer 3

With a good bit of coding, some help from @AaronV over in How to specify dynamic field names in a Linq where clause? I was able to get this put together.

You will need to create a custom SearchableProperty attribute and apply it to the correct property in your model. Then extend Linq's IQueryable by adding a Search method. This Search method will take as parameters the type of search you want to do (being Equal to, Contains, Greater than, etc.) and the value you are searching for. The Search method will need to use reflection to look over the properties in your model and find the property that has the SearchableProperty attribute. The Search method will then dynamically build a .Where clause using that property, the operator you passed and the search value you passed.

First, create a custom SearchableProperty Attribute.

[System.AttributeUsage(AttributeTargets.Property, Inherited = false, AllowMultiple = false)]
sealed class SearchableProperty : Attribute
{
    public SearchableProperty() { }
}

Second, apply this attribute to your Model classes:

class Book
{
    [Key]
    public int Id { get; set; }

    [SearchableProperty]
    public string Author { get; set; }
}

Third, Add an enum of type Operator to your project:

public enum Operator
{
    Gt,
    Lt,
    Eq,
    Le,
    Ge,
    Ne,
    Contains,
    StartsWith,
    EndsWith
}

Fourth , Use the following Search Linq Extension:

public static class LinqExtensions
{

    /// <summary>
    /// Queries the database for the entity which matches the provided search parameters.
    /// </summary>
    /// <typeparam name="TEntity">The type of entity being searched for.</typeparam>
    /// <typeparam name="TDataType">The data type of the value being searched for.</typeparam>
    /// <param name="op">The <see cref="Operator"/> operation that qualifies a match.</param>
    /// <param name="query">The query that must be matched for the entity to be returned. </param>
    /// <returns>A filtered sequence of elements. </returns>
    internal static IQueryable<TEntity> Search<TEntity, TDataType>(this IQueryable<TEntity> @this, Operator op, TDataType query)
    {
        // Get the property that is supposed to be searchable.
        PropertyInfo[] pInfo =
            typeof(TEntity)
                .GetProperties()
                .Where(prop => prop.GetCustomAttribute<SearchableProperty>() != null)
                .ToArray();

        if (pInfo.Length != 1)
        {
            throw new InvalidOperationException($"Unable to search entity of type: {typeof(TEntity)} as the entity exposes none or more than one properties with SearchableProperty Attribute");
        }
        else
        {
            PropertyInfo searchableProperty = pInfo[0];

            var dynamicExpression = CreateDynamicExpression<TEntity, TDataType>(
                searchableProperty.Name,
                op,
                query);

            return @this.Where(dynamicExpression);
        }
    }
    
    /// <summary>
    ///     A method to create an expression dynamically given a generic entity, and a propertyName, operator and value.
    /// </summary>
    /// <typeparam name="TEntity">
    ///     The class to create the expression for. Most commonly an entity framework entity that is used
    ///     for a DbSet.
    /// </typeparam>
    /// <param name="propertyName">The string value of the property.</param>
    /// <param name="op">An enumeration type with all the possible operations we want to support.</param>
    /// <param name="value">A string representation of the value.</param>
    /// <returns>An expression that can be used for querying data sets</returns>
    private static Expression<Func<TEntity, bool>> CreateDynamicExpression<TEntity, TValueType>(string propertyName,
        Operator op, TValueType value)
    {
        Type type = typeof(TEntity);
        var p = Expression.Parameter(type, "x");
        var property = Expression.Property(p, propertyName);
        MethodInfo method;
        Expression q;

        switch (op)
        {
            case Operator.Gt:
                q = Expression.GreaterThan(property, Expression.Constant(value));
                break;
            case Operator.Lt:
                q = Expression.LessThan(property, Expression.Constant(value));
                break;
            case Operator.Eq:
                q = Expression.Equal(property, Expression.Constant(value));
                break;
            case Operator.Le:
                q = Expression.LessThanOrEqual(property, Expression.Constant(value));
                break;
            case Operator.Ge:
                q = Expression.GreaterThanOrEqual(property, Expression.Constant(value));
                break;
            case Operator.Ne:
                q = Expression.NotEqual(property, Expression.Constant(value));
                break;
            case Operator.Contains:
                method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
                q = Expression.Call(property, method ?? throw new InvalidOperationException(),
                    Expression.Constant(value, typeof(string)));
                break;
            case Operator.StartsWith:
                method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) });
                q = Expression.Call(property, method ?? throw new InvalidOperationException(),
                    Expression.Constant(value, typeof(string)));
                break;
            case Operator.EndsWith:
                method = typeof(string).GetMethod("EndsWith", new[] { typeof(string) });
                q = Expression.Call(property, method ?? throw new InvalidOperationException(),
                    Expression.Constant(value, typeof(string)));
                break;
            default:
                throw new ArgumentOutOfRangeException(nameof(op), op, null);
        }

        return Expression.Lambda<Func<TEntity, bool>>(q, p);
    }
}

Finally, use the magic:

using (DemoDbContext _context = new DemoDbContext())
{
    // Sample where query to show the base sql that EF generates.
    var whereQuery = _context.People.Where(x => x.Name == "John");

    // Sql using the new Search method.
    var searchQuery = _context.People.Search(Operator.Eq, "John");

    // Sql using the new search method.
    var bookSearch = _context.Books.Search(Operator.Contains, "John");

    // Log everything to the console for viewing. 
    Console.WriteLine(whereQuery.ToQueryString());
    Console.WriteLine("------------------------------------");
    Console.WriteLine(searchQuery.ToQueryString());
    Console.WriteLine("------------------------------------");
    Console.Write(bookSearch.ToQueryString());

    Console.ReadKey();
}