Expression to Check for a String Value in All Properties of a Type

Agent__007

I am trying to write a small search functionality in LINQ-to-SQL (with Sharp Repository and Entity Framework) where given a string value, I should be able to search it through all properties and return the entities which have the input string value "Contained" (as in someString.Contains) in any of their properties (with "searchable" types like int, long, etc since search by ID should be possible). I have written a method which does this for a "single" string property (which was the requirement earlier :sigh: ) and returns an Expression predicate which I pass to my SharpRepository instance as the selector.

public static Expression> ContainsExpression(string propertyName, string propertyValue)
{
var parameterExpression = Expression.Parameter(typeof(T), typeof(T).Name); // type =>

        var propertyExpression = Expression.PropertyOrField(parameterExpression, propertyName); // type.Name
        var stringTypeArray = new\[\] { typeof(String) };
        var containsMethod = typeof(String).GetMethod("Contains", stringTypeArray); // Contains method
        var valueToBeChecked = Expression.Constant(propertyValue, typeof(String)); // property value as a constant

        var finalContainsExpression = Expression.Call(propertyExpression, containsMethod, valueToBeChecked); // type.Name.Contains(propertyValue)

        return Expression.Lambda\>(finalContainsExpression, parameterExpression);
    }

I have even tried overriding ToString() method in my entity (simple Employee type at the moment) as:

public override string ToString()
{
string instance = String.Concat(Id, ",", Name, ",", Department, ",", DateOfJoining.ToString("dd-MMM-yyyy"), ",", Salary);
return instance;
}

and then generate an Expression as:

Employee => Employee.ToString().Contains(ValueToBeSearched)

But needless to say, the first one won't work for the types other than String (since I will need to do someTypeOtherThanString.ToString().Contains(..) which is not supported in LINQ-to-SQL and the later one also won't work due to the similar reason! I do know that I can simply write an SP for this functionality, I want to know if there's a way to achieve this using LINQ-to-SQL. Also, I need to pass the Expression as the selector to my SharpRepository insta

Lost User

Agent__007 wrote:

I do know that I can simply write an SP for this functionality, I want to know if there's a way to achieve this using LINQ-to-SQL

Even if it is possible, the SP is still the recommended way. That way the server would search for the data, as opposed to having the server blow all tables over the network to each client and have each client search their own in-memory-copies. Look into reflection and recursion, that should do the trick. You'd iterate every property, and if it isn't an interface or a class, you'd convert it to a string and search the string-represenation. This way you should be able to search most items, as an enum, a long and a boolean would all convert nicely. If the property represents a class, interface or struct, then you call the method again, but now passing the value of that property and not the object you're looking at. That'd be the recursive part. Should work for POCO's, can't say with certainty that it will work nicely on entities.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Alaric_

Any time you have an application send SQL to a database that identifies the data tables, you are doing it *so* wrong§. You should treat the data model as an implementation detail and the stored procedures and Table Valued Functions (if MSSQL) as your data services. It would be much, much nicer if the SQL Server team had followed through on their rhetoric that table valued parameters would be able to be OUTPUT parameters "in the next release of SQL Server" (which should have been 2008R2...that would allow the DataTransferObject to be registered directly with the DBMS) but an ADO.NET bridging layer that performs your data transfer into the application domain is *ridiculously simple* §...and that's why Entity Framework is pure, unadulterated evil; It's the ADO.NET DataSet 2.0

"I need build Skynet. Plz send code"

Agent__007

Anyway, now that I have managed to get it working for string and numeric properties (which are going to be "most" of them), I have been "suggested" to go with the SP approach - which was my suggestion to them in the first place. :sigh: If anyone is interested how I implemented the property-search (for string and numeric types only), here are the methods (please don't point out the refactoring/coding conventions, etc needed, this was just a quick one):

// This one is for searching in string types
private static Expression> ContainsInStringTypeExpression(string propertyName, string propertyValue)
{
var parameterExpression = Expression.Parameter(typeof(T), typeof(T).Name); // type =>

        var propertyExpression = Expression.PropertyOrField(parameterExpression, propertyName); // type.Name
        var stringTypeArray = new\[\] { typeof(String) };
        var containsMethod = typeof(String).GetMethod("Contains", stringTypeArray); // Contains method
        var valueToBeChecked = Expression.Constant(propertyValue, typeof(String)); // property value as a constant

        var finalContainsExpression = Expression.Call(propertyExpression, containsMethod, valueToBeChecked); // type.Name.Contains(propertyValue)

        return Expression.Lambda\>(finalContainsExpression, parameterExpression);
    }

// This one is for searching in numeric types
private static Expression> ContainsInNumericTypeExpression(string propertyName, string propertyValue)
{
var parameterExpression = Expression.Parameter(typeof(T), typeof(T).Name); // type =>

        var propertyExpression = Expression.PropertyOrField(parameterExpression, propertyName); // type.Name
        var decimalPropertyExpression = Expression.Convert(propertyExpression, typeof(System.Nullable)); // (decimal?)type.Name

        // SqlFunctions.StringConvert((decimal?)type.propertyName).Contains(propertyValue),
        var nullableDecimalType = new\[\] { typeof(System.Nullable) };
        var stringConvertExp = Expression.Call(null, typeof(SqlFunctions).GetMethod("StringConvert", nullableDecimalType), decimalPropertyExpression);


        var stringTypeArray = new\[\] { typeof(String) };
        var containsMethod = typeof(String).GetMethod("Contains", stringTypeArray); // Contains method
        var valueToBeChecked = Expression.Constant(propertyVa

Agent__007

Thanks for your reply, Sir. I always like your answers. :thumbsup: Fortunately they are now okay with my initial suggestion of using an SP.

Your time will come, if you let it be right.

Agent__007

Thanks for your reply. An SP is the way now! :thumbsup:

Your time will come, if you let it be right.

Lost User

Ehr, thanks :)