Styjun

In order to replace the following query which generate IN clause with 100+ elements inside and take 8.4 seconds:

List<AnalysisModel> analyses = AppDbContext.Analysis.Where(m => Id.Contains(m.TestId) & phasesAll.Contains(m.PhaseId)).AsNoTracking().ToList();

I use manual query:

string analysisQuery = $"SELECT id, time, compound, reagent, product, phase, conc, test_id FROM public.analysis INNER JOIN (VALUES stringHelper.WrapGuidToString(Id, GuidWrapper) ) testid_val (v) ON (test_id = v) INNER JOIN (VALUES stringHelper.WrapIntToString(phasesAll, IntWrapper) ) phase_val (p)ON (phase = p)";
List<AnalysisModel> analyses = AppDbContext.Analysis.FromSql(analysisQuery).AsNoTracking().ToList();

If I execute query generated in analysisQuery in pgAdmin it executes and provides the same table as the first expression (in 0.9 s). However when I execute through FromSql I receive the following error:

System.FormatException: 'Index (zero based) must be greater than or
equal to zero and less than the size of the argument list.'

Any suggestions why FromSql is not working correctly here?

stringHelper.WrapGuidToString() and stringHelper.WrapIntToString() wraps Guid and string into the appropriate format, e.g. (Guid1), (Guid2) and (1), (2) for Guid and Int respectively. The query generated via this method can be executed in pgAdmin without problems:

public string WrapIntToString(List<int> input, WrapModel wrapper)

 List<string> prep = new List<string>();
 input.ForEach(m => prep.Add(wrapper.LeftWrapper + m.ToString() + wrapper.RightWrapper));
 return string.Join(wrapper.Separator, prep);

WrapGuidToString code:

public string WrapGuidToString(List<Guid> input, WrapModel wrapper)

 List<string> prep = new List<string>();
 input.ForEach(m => prep.Add(wrapper.LeftWrapper + m.ToString() + wrapper.RightWrapper));
 return string.Join(wrapper.Separator, prep);

Where wrapper is

private WrapModel GuidWrapper => new WrapModel()

 LeftWrapper = "('",
 RightWrapper = "'::uuid)",
 Separator = ","
;

I got your problem:

wrapper.LeftWrapper + m.ToString() + wrapper.RightWrapper

It seems like you have created a string format yourStringValueHere that is why your got this error Zero index

String.Format("012","Value1","Value2","Value3")

It must look like that.

This one m.ToString() with your wrapper is wrong instead of an integer then followed by the value

Take a look at the content of your WrapGuidToString or WrapIntToString if it is in a correct format. This is your main issue here.