0

We are using EF 6.2 for .NET. We are trying the next query:

dbContext.memberScales
.Where(s => members.Contains(s.idMember))
.OrderByDescending(s => s.dateScale)
.Select(s => new MemberScaleBasicFields
{
    id = s.idMember,
    dateScale = s.dateScale,
    weight = s.weight,
    massMuscle = s.massMuscle,
    massFat = s.massFat,
    massBone = s.massBone,
    imc = s.imc,
    water = s.water,
    dailyCalories = s.dailyCalories,
    tmd = s.tmd,
    physicalValuation = s.physicalValuation,
    adiposity = s.adiposity,
    assessment = s.assessment,
    ageMetabolica = s.ageMetabolica
})
.ToList();

In the where clause, we have to filter for a list of long.

members.Contains(s.idMember)

When we see the query text, we get a query with hardcore values:

SELECT 
[Extent1].[ValoracionFisica] AS [ValoracionFisica], 
[Extent1].[IdSocio] AS [IdSocio], 
[Extent1].[Fecha] AS [Fecha], 
[Extent1].[Peso] AS [Peso], 
[Extent1].[MasaMagra] AS [MasaMagra], 
[Extent1].[MasaGrasa] AS [MasaGrasa], 
[Extent1].[MasaOsea] AS [MasaOsea], 
[Extent1].[IMC] AS [IMC], 
[Extent1].[Agua] AS [Agua], 
[Extent1].[CaloriasDiarias] AS [CaloriasDiarias], 
[Extent1].[TMB] AS [TMB], 
[Extent1].[Adiposidad] AS [Adiposidad], 
[Extent1].[Valoracion] AS [Valoracion], 
[Extent1].[EdadMetabolica] AS [EdadMetabolica]
FROM [dbo].[Socios_Bascula] AS [Extent1]
WHERE [Extent1].[IdSocio] IN (cast(1225789 as bigint), cast(1228549 as bigint), cast(1228557 as bigint), cast(1230732 as bigint)....

We want to know how to make this query parametrized. Or if there is any alternative to make this query without dropping cache plan. For example,

[Extent1].[IdSocio] IN (@value1, @value2...)

If we try make the 

members.Any(x => x == s.idMember)

we get the next text

SELECT 
[Extent1].[ValoracionFisica] AS [ValoracionFisica], 
[Extent1].[IdSocio] AS [IdSocio], 
[Extent1].[Fecha] AS [Fecha], 
[Extent1].[Peso] AS [Peso], 
[Extent1].[MasaMagra] AS [MasaMagra], 
[Extent1].[MasaGrasa] AS [MasaGrasa], 
[Extent1].[MasaOsea] AS [MasaOsea], 
[Extent1].[IMC] AS [IMC], 
[Extent1].[Agua] AS [Agua], 
[Extent1].[CaloriasDiarias] AS [CaloriasDiarias], 
[Extent1].[TMB] AS [TMB], 
[Extent1].[Adiposidad] AS [Adiposidad], 
[Extent1].[Valoracion] AS [Valoracion], 
[Extent1].[EdadMetabolica] AS [EdadMetabolica]
FROM [dbo].[Socios_Bascula] AS [Extent1]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM  (SELECT 
        cast(1225789 as bigint) AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
    UNION ALL
        SELECT 
        cast(1228549 as bigint) AS [C1]
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]....
Serafín
  • 132
  • 1
  • 9
  • 1
    There is a maximum of 2100 parameters per query so a longer parameterized list will fail. – Dan Guzman Mar 15 '18 at 10:59
  • Why are you asking? This *is* a proper query. It's actually faster than passing eg a TVP and joining with it - a table parameter has no statistics and is treated as if it only had one value. It *doesn't* harm execution plans. If you get different execution plans it's because you *need* different execution plans - 1 value requires a different execution plan than 100 values. Why do you want to change it? – Panagiotis Kanavos Mar 15 '18 at 11:03
  • I think if the number of parameters is changed the cached plan is also dropped. LINQ-to-SQL used to parametrize IN queries. This was abandoned in the later Microsoft ORM, Entity Framework. Probably for a reason. – Gert Arnold Mar 15 '18 at 11:12
  • Hi, in other case, when we did a long list query, we get "query processor ran out of internal resources". So we were trying to workround this kind of problem with EF. We use TVP in that case, and we want to know if there is alternative to do that. – Serafín Mar 15 '18 at 11:12
  • 1
    What you need is a [scalable Contains method](https://stackoverflow.com/q/24534217/861716). – Gert Arnold Mar 15 '18 at 11:14

0 Answers0