0

I have been looking into this, and I haven't been able to find an answer.

We are using EF 6.0 in C#, I was hoping to use the EF Compiled query to avoid some Dynamic SQL.

We have a set of Constraint defined to find recommendable data. and Those conditions can change between networks.

For instance AZNEtwork Network Id is AZNETWORK Speciality Code is PEDS Age is less than 21

For CANETWORK Network Id is AZNETWORK Speciality Code is PEDS Age is less than 18

We currently deal with this in Dynamic SQL, I was hoping to handle this in EF Queries. I know how to add constraints to an IQueryable with extensions. Is it possible to compile a EF Query with the configured constraints.

public static IQueryable<ProvData> azFunction(preprodContext ctx, string networkId, 
string specCode, int age)
{
   var azquery = from    load in ctx.ProvTable
                 select  new ProvData
                             {
                               Id        =   load.Id,
                               NetworkId =   load.NetworkId,
                               SpecCode  =   load.SpecCode,
                               Age       =   load.Age
                             });
  azQuery = azQuery.Where(x = x.NetworkId == networkId);
  azQuery = azQuery.Where(x = x.SpecCode  == specCode);
  azQuery = azQuery.Where(x = x.Age       == age);
  return(azQuery);
}

And then one for CA

private static Func<preprodContext, string, string, int, IAsyncEnumerable<Prov>> azCompiled =
   EF.CompileAsyncQuery((preprodContext ctx, string networkId, string specCode, int age ) =>
      azFunction(ctx, networkId, specCode, age);

From every example tat I have found defines the query in the same statement that is compiling it.

Is this Possible

Thanks

M. Daly

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
Mark Daly
  • 1
  • 1
  • I am trying to understanding your goals here. Do you want to define and pre-compile a query that includes NetworkId, SpecCode, and Age filters without tying the query to specific networkId, specCode, and age values? Something equivalent to a parameterized query where \@networkId, \@specCode, and \@age can be specified when the query is later executed? Can you give an example of your expected usage and perhaps explain why you need this to be a compiled query? (I'm not an expert here, but I suspect I'm not the only one who doesn't understand the question.) – T N Jun 20 '23 at 01:25
  • Not sure if it makes a difference, but you might need to arrange the order of operations so that the filters are applied before the selected data is mapped to the ProvData class. I'm not sure if the query translation can pierce the ProvData mapping so that the conditions are applied on the SQL Server side of he connection. – T N Jun 20 '23 at 01:31
  • No, you have to write query directly in `EF.CompileAsyncQuery` call. EF Core use Expression information to extract parameters. And actually it think you are doing preemptive optimisation. – Svyatoslav Danyliv Jun 20 '23 at 04:04
  • Thanks, I was afraid of that, so compiled queries are out, thank you – Mark Daly Jun 20 '23 at 15:05

0 Answers0