Dynamic linq backslash in where clause

Question

I use System.Linq.Dynamic to query entities with dynamic 'where' expressions. I'm querying object that has property "newValue" of string type. Exemplary value would be : "{\"ProcessId\":764, \"ProcessLength\":1000}". I can't use == because I want to find all hits where the property contains "ProcessId:764", regardless on the rest of the string. The thing is, that stored string contains escape sign "\" and double quotes and I can't figure out what it should like exactly..

dbContext.Processes.Where("@newValue.Contains(\"ProcessId\":764\")") brings error, however dbContext.Processes.Where("@newValue.Contains(\":764\")") works correctly. I guess it must be something with backslashes or double quotes in my query but can't figure it out on my own..

Answer 1

There are two things to note here:

1. If you know at compile time the column that should be queried (i.e., newValue), just use standard Linq: var list = items.Where(i => i.NewValue.Contains("904")).ToList().

2. If you do want to use dyanmic Linq, What you'd usually want is to apply Where on some column, e.g. Where("SomeColumn.Contains("something")"), or Where("SomeColumn.Contains(@0)", new string[] {"something"}).

   So, in your case, this should work: items.Where("newValue.Contains(\"904\")").

   Doing Where("@newValue.Contains("something")") doesn't really make sense, since @newValue would be parsed as a string literal. See also this comment on a similiar question.

Here' a quick example:

public static void Main(string[] args)
{
    var items = new [] 
    { 
       new { Id = "1", Title = "ProcessId: 123"}, 
       new { Id = "4", Title = "ProcessId: 456"}, 
       new { Id = "7", Title = "ProcessId: 789"}, 
    }.ToList();

    // returns null, because the string "Title" doesn't contain the string "7"
    var res1 = items.Where("@0.Contains(\"7\")", new string[] {"Title"}).FirstOrDefault();     

    // works - returns the 3rd element of the array
    var res2a = items.Where("Title.Contains(@0)", new string[] {"ProcessId: 789"}).FirstOrDefault();                 
    var res2b = items.Where("Title.Contains(\"ProcessId: 789\")").FirstOrDefault();
}

Answer 2

@HeyJude Thanks for the effort, but I still can't get it to work. It has somehow gone wronger and now I can't even fetch correct rows giving only ProcessId number..

Let me give you more detailed description of my setup. In the database there's a table with column "NewValue", I use this column to store json string of current (for the time of creating row in the table) representation of some object e.g. object Process. So the column stores for example string of {"ProcessId":904,"ProcessLength":1000}. To fetch this data from db I create collection of table's records: var items = (from l in db.JDE_Logs join u in db.JDE_Users on l.UserId equals u.UserId join t in db.JDE_Tenants on l.TenantId equals t.TenantId where l.TenantId == tenants.FirstOrDefault().TenantId && l.Timestamp >= dFrom && l.Timestamp <= dTo orderby l.Timestamp descending select new //ExtLog { LogId = l.LogId, TimeStamp = l.Timestamp, TenantId = t.TenantId, TenantName = t.TenantName, UserId = l.UserId, UserName = u.Name + " " + u.Surname, Description = l.Description, OldValue = l.OldValue, NewValue = l.NewValue });. Then I query it to find matching rows for given ProcessId number e.g. query = "@NewValue.Contains(\"904,)\")"; items = items.Where(query); This should fetch back all records where NewValue column contains the query string, but this doesn't work. It compiles and 'works' but no data are fetched or fetched are only those records where 904 appears later in the string. Sounds stupid but this is what it is.

What should the query string look like to fetch all records containing "ProcessId":904?