Editor.Where() a field value is contained in a string array

rdm

Suppose I have a scenario where a function parameter contains a string array, like

["Quarter 1 Week 7: Sep 18 - Sep 21","Quarter 1 Week 8: Sep 25 - Sep 27"]

How can I write an Editor.Where() condition where a field value must be contained in that string? E.g., the field should be "Quarter 1 Week 7: Sep 18 - Sep 21" or "Quarter 1 Week 8: Sep 25 - Sep 27", but not anything else not contained in that array.

I figured the key would be in the use of lambda syntax, but I'm not sure how to do a contains filter. This is my latest "where" attempt, which causes a browser error.

.Where(q => q.Where("WeekList.WeekDescription",weeks,"IN",false))

I also tried .Where(q => q.Where("WeekList.WeekDescription", "(select WeekDescription from WeekList where "+weeks+ " like %WeekList.WeekDescription%)")) without success.

In this example, "WeekList" is a joined table, "weeks" is the function parameter, and "IN" is my best guess of "WeekList.WeekDescription" is "in" weeks.

As of now, I'm getting an error message from the browser:DataTables warning: table id=example - Incorrect syntax near '"Quarter 1 Week 8: Sep 25 - Sep 27"'.

If this were a T-SQL query, I would hope to do this type of where condition:

DECLARE @weeks NVARCHAR(max) = 'Quarter 1 Week 7: Sep 18 - Sep 21","Quarter 1 Week 8: Sep 25 - Sep 27';

SELECT *
FROM dbo.WeekList w
WHERE @weeks LIKE '%' + w.WeekDescription + '%'

To provide proper context, I pasted a trimmed down version of my draft code, showing only the essentials. How should I tweak my "where" statement?

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
public ActionResult JoinedTableTest(string weeks)
{
    var settings = Properties.Settings.Default;
    var formData = HttpContext.Request.Form;
    
    // Debug trace:   
    //       Week: ["Quarter 1 Week 7: Sep 18 - Sep 21","Quarter 1 Week 8: Sep 25 - Sep 27"]
    
    using (var db = new Database(settings.DbType, settings.DbConnection))
    {
        var response = new Editor(db, "FridayPlanning", "Id")                                                   
            .Field(new Field("WeekList.WeekDescription"))
            .LeftJoin("WeekList", "WeekList.Quarter", "=", "FridayPlanning.QuarterNumber and (WeekList.Week = FridayPlanning.WeekNumber)")  
            .Where(q => q.Where("WeekList.WeekDescription",weeks,"IN",false))
            .Process(formData)
            .Data();

        return Json(response, JsonRequestBehavior.AllowGet);
    }
}

rdm

I figured it out, after interpreting the accepted answer here.

What I had failed to consider is that I need to do some character replacements:
* Replace [ and ] with ( and )
* Replace double-quotes with single quotes.

var s = weeks.Replace('[', '(').Replace(']', ')').Replace('\"', '\'');

using (var db = new Database(settings.DbType, settings.DbConnection))
{
  [...code...]
  
   .Where(q => q.Where("WeekList.WeekDescription",s, "IN",false))

And now it works.

allan

Thanks for posting back. Great to hear that you got it working.

Allan