How to Search through all fields in a LINQ table?

Question

in LINQ how do i search all fields in a table, what do i put for ANYFIELD in the below?

Thanks

var tblequipments = from d in db.tblEquipments.Include(t => t.User).Include(t => t.ChangeLog).Include(t => t.AssetType)
                                where d."ANYFIELD" == "VALUE" select d;

Answer 1

You can't. You must compare each field individually. It doesn't make sense to compare all fields, given a field may not even be of the same type as the object you're comparing to.

Answer 2

You can, using reflection. Try this:

    static bool CheckAllFields<TInput, TValue>(TInput input, TValue value, bool alsoCheckProperties)
    {
        Type t = typeof(TInput);
        foreach (FieldInfo info in t.GetFields().Where(x => x.FieldType == typeof(TValue)))
        {
            if (!info.GetValue(input).Equals(value))
            {
                return false;
            }
        }
        if (alsoCheckProperties)
        {
            foreach (PropertyInfo info in t.GetProperties().Where(x => x.PropertyType == typeof(TValue)))
            {
                if (!info.GetValue(input, null).Equals(value))
                {
                    return false;
                }
            }
        }
        return true;
    }

And your LINQ query:

var tblequipments = from d in db.tblEquipments.Include(t => t.User).Include(t => t.ChangeLog).Include(t => t.AssetType)
where CheckAllFields(d, "VALUE", true) select d;

The third parameter should be true if you want to check all fields and all properties, and false if you want to check only all fields.

Answer 3

EDIT: Someone already built this...see here.

Not a full answer, but I don't agree with assertion that you simply can't...

You could come up with an extension method that dynamically filtered the IQueryable/IEnumerable (I'm guessing IQueryable by the db variable) based on properties of a similar type for you. Here's something whipped up in Linqpad. It references PredicateBuilder and is by no means complete/fully accurate, but I tested it out in Linq-to-SQL on some of my tables and it worked as described.

void Main()
{
    YourDbSet.WhereAllPropertiesOfSimilarTypeAreEqual("A String")
         .Count()
         .Dump();
}

public static class EntityHelperMethods
{
    public static IQueryable<TEntity> WhereAllPropertiesOfSimilarTypeAreEqual<TEntity, TProperty>(this IQueryable<TEntity> query, TProperty value)
    {
        var param = Expression.Parameter(typeof(TEntity));

        var predicate = PredicateBuilder.True<TEntity>();

        foreach (var fieldName in GetEntityFieldsToCompareTo<TEntity, TProperty>())
        {
            var predicateToAdd = Expression.Lambda<Func<TEntity, bool>>(
                Expression.Equal(
                    Expression.PropertyOrField(param, fieldName),
                    Expression.Constant(value)), param);

            predicate = predicate.And(predicateToAdd);
        }

        return query.Where(predicate);
    }

    // TODO: You'll need to find out what fields are actually ones you would want to compare on.
    //       This might involve stripping out properties marked with [NotMapped] attributes, for
    //       for example.
    private static IEnumerable<string> GetEntityFieldsToCompareTo<TEntity, TProperty>()
    {
        Type entityType = typeof(TEntity);
        Type propertyType = typeof(TProperty);

        var fields = entityType.GetFields()
                            .Where (f => f.FieldType == propertyType)
                            .Select (f => f.Name);

        var properties = entityType.GetProperties()
                                .Where (p => p.PropertyType == propertyType)
                                .Select (p => p.Name);

        return fields.Concat(properties);
    }
}

Useful resources for the unresolved part:

• Finding the relevant properties

Answer 4

if this help some one.

first find all properties within Customer class with same type as query:

var stringProperties = typeof(Customer).GetProperties().Where(prop =>
    prop.PropertyType == query.GetType());

then find all customers from context that has at least one property with value equal to query:

context.Customer.Where(customer => 
    stringProperties.Any(prop =>
        prop.GetValue(customer, null) == query));