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..

If i don't remember wrong, this `.Contains(\":764\")` means that it will look for this `":764"` inside newValue . — Simo, Sep 25 '18 at 14:32
You probably need to double backslash to get one in the final `Contains` string. — NetMage, Sep 25 '18 at 17:38
@Simo Exactly, it looks for ":764" inside newValue. @NetMage I tried double backlash, which makes perfect sense, but surprisingly it doesn't find any matching item.. I tried `dbContext.Processes.Where("@newValue.Contains(@newValue.Contains(\"ProcessId\\\":764\")\")\"` but it causes System.Linq.Dynamic.ParseException with message "')' or ',' expected" — robs23, Sep 26 '18 at 12:10

OfirD · Accepted Answer · 2018-10-18T09:54:55.280

There are two things to note here:

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().
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();
}

score 0 · Answer 2 · answered Oct 07 '18 at 18:49

@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?

A few things: **1.** If you know at compile time the column that should be queried (i.e., `NewValue`), just use simple Linq: `var list = items.Where(i => i.NewValue.Contains("904")).ToList()`. **2.** If you do want to use dyanmic Linq, then as I wrote in my answer, it should be: `items.Where("NewValue.Contains(\"904\")")` — OfirD, Oct 16 '18 at 19:32
The reason I wanted it as a part of dynamic linq query was that I could send all query critierias in single parameter, To query it with standard linq, I've added second parameter for this value. But honestly speaking I can see now that sometimes I'm focused so much on some idea that I can't see simplier but working solution.. Thank you for the briliant idea, I finally got it working :) — robs23, Oct 18 '18 at 07:34
Btw, I'd like to mark your post as solution to my problem, but unfortunately it's comment and I can't. Maybe you could put it in separate answer? — robs23, Oct 18 '18 at 07:45

Dynamic linq backslash in where clause

2 Answers2