LINQ subexpression doesn't take variable values

Question

I have 3 contexts for 3 different servers. The main application data uses context1 connecting to SQL1. In a Web API service I get people records asynchronously from context2 connecting to SQL2. It returns 4800+ records. The records are further filtered against the userFacility which is a result of a stored procedure from context3 connecting to SQL3.

All approaches work on my development machine with Visual Studio 2019 and IIS Express. When published to the IIS Server on our network only the Approach 2 work. It seems the EF only likes a constant value in the inner expression but not a variable value. Does anyone know what the problem might be?

IEnumerable people= await aHelper.GetPeopleAsync(aRepository, defaultQuarter, criteria);
/* sample Facility property of People values are
(1V01) (402) Togus, ME
(1V01) (518) Bedford, MA
(1V01) (523) Boston, MA
(1V01) (608) Manchester, NH
(1V01) (631) Central Western Massachusetts
(3V12) (587) Eastern Colorado, CO
*/

List userFacilities = new List(){“608”,”518”,”674”, "587","648"};

/* approach 1 works in VS but not in IIS */
       pepole= pepole.Where(x => userFacilities.Any(y=> x.Facility.Contains(y)));

/* approach 2 works in both in VS and IIS */
       pepole= pepole.Where(x => userFacilities.Any(y=> x.Facility.Contains("587")));


/* Approach 3 works in VS but not in IIS */
List viewablePeople = new List();

foreach (PeopleDTO p in people)
{
    foreach (string y in userFacilities)
    {
        /* doesn't work in IIS but works in VS IIS  Express if y is a constant*/
        if (p.Facility.IndexOf(y) >= 0)
        {
            viewablePeople.Add(p); 
            break;
        }
    }
}

return Ok(viewablePeople);

      public partial class PeopleDTO
      {
        [DisplayName("Facility")]
        public string Facility { get; set; } 

        [DisplayName("District")]
        public string District { get; set; }

        [DisplayName("Name")]
        public string Name { get; set; }

        [DisplayName("SSN")]
        public string PTFSSN { get; set; }

        [DisplayName("FSOD SSN")]
        public string FSODSSN { get; set; }

        [DisplayName("Fiscal Period")]
        public string FiscalPeriod { get; set; }

        [DisplayName("Numeric Fiscal Period")]
        public int FiscalPeriodInt { get; set; }

        [DisplayName("Episode")]
        public IEnumerable Episodes { get; set; }

        public PeopleDTO() {
          Episodes = new List();
        }

user_11392290 · Answer

EF has nothing to do with your data at all, since you just have 2 lists in the computer memory and you don't use db context any more. So you don't need to use staff like "EF.Functions.Like" that was created to convert linq to t-sql and very inefficient when it is used for something else.

I created a special function GetIt that makes checking and I am sure it will work everywhere since it is using only c# System.Collections.Generic namespace. I think almost every c# application uses this namespace and I never heard about any problem with it.

Did you check a people list before starting each test? I think your code problem is in testing. Since you are using peope=people... construction the list is mutates after each testing. And the next approuch uses different data then the previous one had.

Or aHelper.GetPeopleAsync... can also bring the different data when it is using VS and after deployment at IIS.

I don't see any sense to use first 4 approaches since this is not EF. So only makes sense to test approach number 5 one more time, excluding the previous approaches.

This is a demo that was tested in VS. You can try it at IIS. If it is working there, than the problem is aHelper.GetPeopleAsync function

void Main()
{

 people = people.Where(x => GetIt(x.Facility)).ToList();

bool GetIt(string f)
{
    foreach (var uf in userFacilities)
    {
        if ( f.IndexOf(uf) >= 0 ) return true;
    }
    return false;
}


var people  = new List {
new People{ Name="name",Facility="(1V01) (402) Togus, ME"},
new People{ Name="name",Facility="(1V01)(518) Bedford, MA"},
new People{ Name="name",Facility="(1V01)(523) Boston, MA"},
new People{ Name="name",Facility="(1V01)(608) Manchester, NH"},
new People{ Name="name",Facility="(1V01)(631) Central Western Massachusetts"},
new People{ Name="name",Facility="(3V12)(587) Eastern Colorado, CO"}
};

List userFacilities = new List() {"608","518","674", "587","648"};

}

public partial class People
{
    public string Facility { get; set; }
    public string Name { get; set; }
}

user_7327744 · Answer

I think that renaming the variable fac could resolve the issue assuming that is a global variable because I dont see it in the code, the interpolation in the first approach doesnt seem to reference anything

LINQ subexpression doesn't take variable values

2 Answers