Dynamic library (System.Linq.Dynamic) SQL LIKE Operator

c# dynamic-linq linq sql

Question

Someone has posted a similar question here How Dynamic library (System.Linq.Dynamic) support LIKE Operator?

But it's not exactly what I want. The Contains operator mentioned in that post only do this in SQL "%SOMETHING%". But the LIKE operator in SQL can do this "SOME%THING". Is there a similar operator for Dynamic LINQ? If not, is there a solution for this? Maybe with Regex? Also is there a single character wildcard? E.g. "SOM$THING" returns "SOMETHING" or "SOM3THING"

Edit: I am developing a WPF application which should read log files in XML format. Each element contains 34 fields. So instead of writing a very long WHERE, I have used System.Reflection.PropertyInfo to iterate each field to write the query and then use System.Linq.Dynamic to execute it.

Edit2: I have edited the code so it's more readable for the viewers.

Here is some code:

Example 1: prop.Name = "FirstName", paramterString = "Ke%in", returns "Kevin", "Kelvin"... Example 2: prop.Name = "FirstName", paramterString = "Ke$in", returns "Kevin", "Kelin"...

var query = "";
StringBuilder sb = new StringBuilder();
foreach (var prop in stringProps)
{
    sb.Append($"({prop.Name} != null And {prop.Name}.Contains({parameterString})");
}
query = sb.ToString().Substring(0, sb.Length - 4);
filteredData = filteredData.Where(query);

One of the requirement is to implement a SQL LIKE operator, so the users can use something like this to get the result they want: FirstName LIKE 'SOME%THING'

1
2
11/16/2018 11:52:39 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

Since there is no LIKE operator in Dynamic Linq library, I have created it my own version. The code is not pretty, but it does work. Hopefully someone can optimize it or suggest a better way to do this.

public string ParseWildcardInParameterString(string parameter, string propertyName)
{
    string stringWithWildcard = parameter;
    if (parameter.Contains("%") || parameter.Contains("$"))
    {
        stringWithWildcard = parameter;
        string[] substrings = parameter.Split(new Char[] { '%', '$' }, StringSplitOptions.RemoveEmptyEntries);
        string[] wildcards = ParseWildcards(parameter);
        if (substrings.Any())
        {
            StringBuilder sb = new StringBuilder();
            int substringsCount = substrings.Length;
            for (int i = 0; i < substringsCount; i++)
            {
                if (!substrings[i].EndsWith("\\"))
                {
                    int index = parameter.IndexOf(substrings[i]);
                    if (i < substringsCount - 1)
                    {
                        index = parameter.IndexOf(substrings[i + 1], index + 1);
                        if (index > -1)
                        {
                            string secondPart = wildcards[i].Equals("%") ?
                                $"{propertyName}.IndexOf(\"{substrings[i + 1]}\", {propertyName}.IndexOf(\"{substrings[i]}\") + \"{substrings[i]}\".Length) > -1" :
                                $"{propertyName}.IndexOf(\"{substrings[i + 1]}\", {propertyName}.IndexOf(\"{substrings[i]}\") + \"{substrings[i]}\".Length + 1) == {propertyName}.IndexOf(\"{substrings[i]}\") + \"{substrings[i]}\".Length + 1";
                            sb.Append($"({propertyName}.IndexOf(\"{substrings[i]}\") > -1 And {secondPart}) And ");
                        }
                    }
                }
            }
            stringWithWildcard = sb.Remove(sb.Length - 5, 5).Append(") Or ").ToString();
        }
    }
    return stringWithWildcard;
}

private string[] ParseWildcards(string parameter)
{
    IList<string> wildcards = new List<string>();
    foreach (var chararcter in parameter.ToCharArray())
    {
        if (chararcter.Equals('%') || chararcter.Equals('$'))
        {
            wildcards.Add(chararcter.ToString());
        }
    }
    return wildcards.ToArray();
}
1
11/19/2018 2:50:16 PM

Related Questions

Using the System.Linq.Dynamic namespace, how to perform the following in Dynamic Linq

I just want to know how to do the following in Dynamic Linq using System.Linq.Dynamic namespace...var query2 = db.Customers.Select( cust => new Customer { FirstName = cust.FirstName, LastName = cust.LastName Tags="sampleTag,Tag" ...
dynamic-linq linq
asked by emem

Does dynamic GroupBy in dynamic LINQ work with dynamic Where?

I use this ...dynamic LINQ library... together with Linq-to-Entities.... I build query and after that iterate it with ...foreach(object e in query){}...query=db.Table1.Where("it.FieldA>10").Select("it.FieldB");... works....query=db.Table1.Where(e=>e.FieldA>10).GroupBy("it.FieldB", "it").Select("key")... works.... But ...query=db.Table1.Where("it.Fi...
.net dynamic dynamic-linq linq linq-to-entities
asked by alpav

LINQ-to-SQL dynamic table name LINQ-to-SQL

I want to construct my query dynamically based on the contents user has selected, and I want to do this in LINQ-t0-SQL. I have seen ...this..., but I don't want to use SQL statements with datacontext....On Googling a bit I have a feeling that this can be done using dynamic LINQ. Would someone provide an example of similar scenario with dynamic LINQ...
dynamic-linq linq-to-sql
asked by Muhammad Adeel Zahid

Multiple table dynamic, Entity Data Model, Dynamic Linq Strongly typed return types, when clause

When writing a method for an oData service I have the below linq, for which I need to have a dynamic "where" clause to filter the results (the "new"s in the joins are for composite PKs in the Entity Data Model):...var query = from pl in CurrentDataSource.ProductListing join pla in CurrentDataSource.ProductListingAttribute ...
anonymous-types c# dynamic-linq linq-to-entities
asked by rposbo

How can I utilize System.Linq.Dynamic for the LIKE operation?

Can any body tell me how can I use a LIKE operator using ...System.Linq.Dynamic...?...I need to add more than one ...LIKE... expression in my dynamic ...where query.../* var query = db.Customers. Where("CityName Like @0 or CityName Like @1", "London", "USA") */ var query = db.Customers. Where("CityName Like @0 or CityName Like @1%", "London", "USA"...
asp.net c# dynamic-linq linq
asked by Kiarash

Need assistance with a LINQ query created using the Dynamic LINQ Library?

Forgive my ignorance on this. I have this LINQ Query: Dim ngBikersDataContext As New CarBikeWalkDataContext...bikersList = (From c In ngBikersDataContext.Reg_Bikers _ Order By c.L_Name _ Select New Bikers() With { _ .BikerID = c.BikerID, _ .F_Name = c.F_...
dynamic-linq linq vb.net
asked by Matt Porterfield

OrderBy inside a GroupBy using dynamic LINQ?

I had the following query using normal linq and it was working great (using anonymous type),... var result = from s in Items group s by s.StartTime into groupedItems select new {groupedItems.Key, Items= groupedItems.OrderBy(x => x.Name) }; ...But using Dynamic Linq I cannot get it to order by within the groupby.... r...
dynamic-linq linq linq-to-objects
asked by Martin

How to GroupBy a part of DateTime using System.Dynamic.LINQ library

I have tables that contain a lot of rows, each record is excellent by whom and on what date was added. I want to group all records, based on AddedBy and AddedOn fields....The problem is that the dates are in full including the miliseconds and I only want to group by day, month or year....I hereby noted that at Compilation time I do not know which t...
dynamic-linq lambda linq sql-server vb.net
asked by Haim Tabak

System.Linq.Dynamic.Core vs System.Linq.Dynamic

What is the difference between System.Linq.Dynamic.Core and System.Linq.Dynamic? I am currently using System.Linq.Dynamic and it does not contain support for ...Select... and ...SelectMany... (among other extension methods). Does System.Linq.Dynamic.Core support these methods?
dynamic-linq
asked by jjf1978

Dynamic linq Binary and/or operator (Nhibernate linq)

I want to create an expression that as a result is a binary and or a binary or operation. Something like ...int a = 1; int b = 2; int c = 3; int d = a & b; // d = 0 int e = a & c; // e = 1 ...I hav not found anything in dynamic linq! But in linq this is a normal expression!...public class Customer{ [Flags] public enum Status{ None =...
dynamic-linq nhibernate
asked by Franki1986

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow