# Migration EF CORE

## Breaking changes identified for PopleeTalent

### EF Core 5.0
https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/breaking-changes#defining-query
We use projection with queryable for example in class `AnswersReportRepository`

https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-5.0/breaking-changes#getcolumnname-obsolete
We use this method in class `DbContextExtensions`

### EF Core 6.0
/

## To do before migration

### 1 Delete `System.Data.SqlClient` dependency ?

#### Context
According to this [post](https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/), `Microsoft.Data.SqlClient` is the package that will receive new features for SqlClient. `System.Data.SqlClient` is still supported, but won't evolve.

Is it worth it to use `Microsoft.Data.SqlClient` only ?

:warning: The 2 packages are in different namespace, so they can live there life in a common project.

> What does this mean for System.Data.SqlClient?
> It means the development focus has changed. We have no intention of dropping support for System.Data.SqlClient any time soon. It will remain as-is and we will fix important bugs and security issues as they arise. If you have a typical application that doesn’t use any of the newest SQL features, then you will still be well served by a stable and reliable System.Data.SqlClient for many years.
>
> However, Microsoft.Data.SqlClient will be the only place we will be implementing new features going forward. We would encourage you to evaluate your needs and options and choose the right time for you to migrate your application or library from System.Data.SqlClient to Microsoft.Data.SqlClient.

#### Action
- Remove all reference to `System.Data.SqlClient` ans use `Microsoft.Data.SqlClient` instead.
The project compile and all tests are green after that ([pr](https://github.com/LuccaSA/Poplee.Talent/pull/3497))

**Then**

- Update `Microsoft.Data.SqlClient` to `4.1.0` (last)
We then need to update `Lucca.Core.Aspnetcore` to `>= v4.0.3 `(see [here](https://lucca.slack.com/archives/C0BASUM42/p1641492767004800) for more details)

:warning: To update `Lucca.Core.Aspnetcore` to `v4.0.3`, wee need to first update `Microsoft.EntityFrameworkCore.*` package to `>= 6.\*.\*` version.

## What packages do we update ?

`Microsoft.EntityFrameworkCore.Design` : `3.1.18` to `6.0.6`
`Microsoft.EntityFrameworkCore.SqlServer` : `3.1.18` to `6.0.6`
`Microsoft.EntityFrameworkCore.Tools` : `3.1.18` to `6.0.6`

`Microsoft.Data.SqlClient` : `2.1.3` to `4.1.0`

> because `Microsoft.EntityFrameworkCore.*` packages needs `Microsoft.Data.SqlClient` at `2.1.4`


`EntityFrameworkCore.SqlServer.HierarchyId` : `1.0.0` to `3.0.1`
`EntityFrameworkCore.SqlServer.HierarchyId.Abstractions` : `2.1.1` to `3.0.1`

`Lucca.Core.Api.Abstractions` : `0.3.5` to `1.2.1`
`Lucca.Core.Api.Queryable` : `0.3.5` to `1.2.1`
`Lucca.Core.Api.Queryable.EntityFrameworkCore` : `0.3.5` to `1.2.1`
`Lucca.Core.Api.Web` : `0.3.5` to `1.2.1`

## Useful links

[Breaking change for ef-core-5.0](https://github.com/dotnet/EntityFramework.Docs/blob/main/entity-framework/core/what-is-new/ef-core-5.0/breaking-changes.md)

[Breaking change for ef-core-6.0](https://github.com/dotnet/EntityFramework.Docs/blob/main/entity-framework/core/what-is-new/ef-core-6.0/breaking-changes.md)

# What we had to do on PopleeTalent migration EF CORE 3.1 to 6.0.6

## Migration HierarchyId failing

After updating HierarchyId packages to `3.0.1`, we had a fail when launching migration in integration tests :


Message: 
System.ArgumentNullException : Value cannot be null. (Parameter 'o')

  Stack Trace: 
MigrationCommandListBuilder.Append(String o)
SqlServerMigrationsSqlGenerator.ColumnDefinition(String schema, String table, String name, ColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
MigrationsSqlGenerator.ColumnDefinition(AddColumnOperation operation, IModel model, MigrationCommandListBuilder builder)
MigrationsSqlGenerator.Generate(AddColumnOperation operation, IModel model, MigrationCommandListBuilder builder, Boolean terminate)
SqlServerMigrationsSqlGenerator.Generate(AddColumnOperation operation, IModel model, MigrationCommandListBuilder builder, Boolean terminate)
MigrationsSqlGenerator.Generate(MigrationOperation operation, IModel model, MigrationCommandListBuilder builder)
MigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model, MigrationsSqlGenerationOptions options)
SqlServerMigrationsSqlGenerator.Generate(IReadOnlyList`1 operations, IModel model, MigrationsSqlGenerationOptions options)
Migrator.GenerateUpSql(Migration migration, MigrationsSqlGenerationOptions options)
Migrator.Migrate(String targetMigration)
RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
CustomWebApplicationFactory`1.InitializeDatabase(PopleeTalentContext dbContext, String dbName) line 193
CustomWebApplicationFactory`1.ReplaceDatabase(IServiceCollection services, PopleeTenant tenant) line 177
CustomWebApplicationFactory`1.<ConfigureWebHost>b__5_0(IServiceCollection services) line 72
<>c__DisplayClass10_0.<ConfigureServices>b__0(WebHostBuilderContext context, IServiceCollection services)
<>c__DisplayClass11_0.<ConfigureServices>b__0(HostBuilderContext context, IServiceCollection builder)
HostBuilder.CreateServiceProvider()
HostBuilder.Build()
WebApplicationFactory`1.CreateHost(IHostBuilder builder)
WebApplicationFactory`1.ConfigureHostBuilder(IHostBuilder hostBuilder)
WebApplicationFactory`1.EnsureServer()
WebApplicationFactory`1.CreateDefaultClient(DelegatingHandler[] handlers)
WebApplicationFactory`1.CreateDefaultClient(Uri baseAddress, DelegatingHandler[] handlers)
WebApplicationFactory`1.CreateClient(WebApplicationFactoryClientOptions options)
WebApplicationFactory`1.CreateClient()
CustomWebApplicationFactory`1.<CreateAuthenticatedClient>b__4_0() line 49
Lazy`1.ViaFactory(LazyThreadSafetyMode mode)
Lazy`1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor)
Lazy`1.CreateValue()
FeedbackFormElementsControllerTests.GetByCampaignIdAsync_ShouldReturn200_WhenUserHasPermission() line 46
--- End of stack trace from previous location ---

In ef core repo, we can see that this line `Check.NotNull(o, nameof(o));` throw our error.


This line wasn't present in earlier version and adding `type: "hierarchyid",` in the migration corrected the problem (type is an argument set to `null` by default)

## Configure new way for configuring sorting with Lucca.Core.Api

Old way :

---
New way :

## Cast problem

In the `AnswersReportRepository `, we calculate a rate by doing a division.

After packages update, this calculation now throw an exception :


Exception we got :

  Message: 
System.InvalidCastException : Unable to cast object of type 'System.Decimal' to type 'System.Double'.

  Stack Trace: 
SqlBuffer.get_Double()
SqlDataReader.GetDouble(Int32 i)
lambda_method3191(Closure , QueryContext , DbDataReader , ResultContext , SingleQueryResultCoordinator )
AsyncEnumerator.MoveNextAsync()
EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
AnswersReportRepository.ManageListFormElementStructure(AnswersReport answerReport, IQueryable`1 answersDbo, List`1 answerSummaries, ListFormElementStructure listFormElementStructure) line 218
AnswersReportRepository.AnswerSummariesReportAsync(AnswersReport answerReport, IQueryable`1 answersDbo, Dictionary`2 propertiesById) line 162
AnswersReportRepository.GetAnswersReportForCampaignIdAsync(Guid reviewsCampaignId, List`1 reviewerIds, Dictionary`2 propertiesById) line 90
AnswersReportRepositoryTests.GetAnswersReportForCampaignId_ReturnAnswersReportAsync(AnswerType answerType, Int32 answerSummariesCount) line 70
--- End of stack trace from previous location ---

We correct the problem by enumerating before doing the calculation :


## Can't use queryables in projection

We have to change the request by enumerating our queryable before using it in another projection

 System.InvalidOperationException : The query contains a projection 'fe => DbSet<Review>()
        .Where(r => (int)r.Status == 8)
        .Where(_ => False)
        .Where(r => __reviewsCampaignId_1
            .Contains(r.ReviewsCampaignId))
        .Select(r => r.ReviewContributions
            .FirstOrDefault(c => c.OwnerId == r.ReviewerId))
        .SelectMany(c => DbSet<AnswerDbo>()
            .Where(a => a.ContributionId == c.Id))
        .Where(a => a.FormElementId == fe.Id)' of type 'IQueryable<AnswerDbo>'. Collections in the final projection must be an 'IEnumerable<T>' type such as 'List<T>'. Consider using 'ToList' or some other mechanism to convert the 'IQueryable<T>' or 'IOrderedEnumerable<T>' into an 'IEnumerable<T>'.

We had a projection with a queryable here:

Here is the refactored code :

You can see diff in github [here](https://github.com/LuccaSA/Poplee.Talent/pull/3499/files?diff=split&w=0#diff-dedf77960bf543db11c8cdb07ce5f9a62eaf58ac652176f1a7436f964f9ccfe6)

## Delete config for Goal events

Exception :

  Message: 
System.ApplicationException : Error switching type goal : The association between entities 'Goal' and 'TeamGoalUnSetParentEvent' with the key value '{GoalId: f2872c11-5c33-44a2-8ab6-08da64d14e41}' has been severed, but the relationship is either marked as required or is implicitly required because the foreign key is not nullable. If the dependent/child entity should be deleted when a required relationship is severed, configure the relationship to use cascade deletes.

  Stack Trace: 
<<SetAsIndividualGoal>b__0>d.MoveNext() line 80
--- End of stack trace from previous location ---
<<ExecuteAsync>b__3_0>d.MoveNext()
--- End of stack trace from previous location ---
SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
PopleeTalentTransaction.LaunchTransaction(Func`1 lambda) line 35
TeamGoalSetAsIndividualApplication.SetAsIndividualGoal(Guid goalIdToConvert, IExpressionTree`1 fields) line 84
TeamGoalsController.SetAsIndividual(Guid teamGoalId) line 75
TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
<16 more frames...>
ErrorHandlingMiddleware.Invoke(HttpContext context) line 34
SwaggerUIMiddleware.Invoke(HttpContext httpContext)
SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
RequestLocalizationMiddleware.Invoke(HttpContext context)
<<SendAsync>g__RunRequestAsync|0>d.MoveNext()
--- End of stack trace from previous location ---
ClientHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
HttpClient.<SendAsync>g__Core|83_0(HttpRequestMessage request, HttpCompletionOption completionOption, CancellationTokenSource cts, Boolean disposeCts, CancellationTokenSource pendingRequestsCts, CancellationToken originalCancellationToken)
GoalSetAsIndividualTests.SetAsIndividualGoalShouldBeOkAsync() line 35
--- End of stack trace from previous location ---

Since when we switch a goal type we, in reality, delete the current goal and recreate a new one with the correct type, we shouldn't have to ever delete events of goal.

Delete behaviour is now [DeleteBehavior.ClientNoAction](https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.deletebehavior?view=efcore-6.0)


[github diff](https://github.com/LuccaSA/Poplee.Talent/pull/3499/commits/535a4e83a7575d1a755452942df2bf0f42f92450)

## Rights application for TeamGoal

In poplee talent, rights of team goals are a little differente than Individual goals since we have implicite rights with hierarchy. For example :
- I can see all the team goals that are in the hierarchy of team goals that belong to me

Or rights that add access to team goals by hierarchy. For example :
- I can see direct parent of team goals that belong to me

Since these rights give access to team goals that we are not normally allowed to see, the implementation is done by using a `Union` to merge two `Queryables`, example below:


- `entities` : queryable representing the goals filtered by classic lucca rights
- `Set().Where(g => allCanAccessTeamGoal.Contains(g.Id))` : queryable representing the team goals I can access implicitly with hierarchy rights.

This way of doing it now throw an exception and can not anymore be translated to SQL.

System.InvalidOperationException: Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
   at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.LoadAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Poplee.Talent.Infra.Repositories.Goals.GoalsRepository`1.BeforeEnumerateAsync(IQueryable`1 entities, Query`1 query) in C:\Sites\Poplee.Talent\back\Poplee.Talent.Infra\Repositories\Goals\GoalsRepository.cs:line 144
   at Poplee.Talent.Infra.Repositories.Goals.GoalsRepository`1.GetByIdAsync(Guid id, Query`1 query) in C:\Sites\Poplee.Talent\back\Poplee.Talent.Infra\Repositories\Goals\GoalsRepository.cs:line 209
   at Poplee.Talent.Application.IndividualGoals.IndividualGoalSetAsTeamApplication.SetAsTeamGoal(Guid individualGoalId, IExpressionTree`1 fields) in C:\Sites\Poplee.Talent\back\Poplee.Talent.Application\IndividualGoals\IndividualGoalSetAsTeamApplication.cs:line 71
   at Poplee.Talent.Web.Controllers.Goals.IndividualGoalsController.SetAsTeam(Guid individualGoalId) in C:\Sites\Poplee.Talent\back\Poplee.Talent.Web\Controllers\Goals\IndividualGoalsController.cs:line 41
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|20_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Poplee.Talent.Web.Shared.SetCurrentCultureMiddleware.Invoke(HttpContext httpContext, ICurrentCultureService currentCultureService, IPrincipalContainer principalContainer) in C:\Sites\Poplee.Talent\back\Poplee.Talent.Web\Shared\SetCurrentCultureMiddleware.cs:line 24
   at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
   at Poplee.Talent.Web.Aspects.Middlewares.ErrorHandlingMiddleware.Invoke(HttpContext context) in C:\Sites\Poplee.Talent\back\V4\Poplee.Talent.Web.Aspects\Middlewares\ErrorHandlingMiddleware.cs:line 28
   at Poplee.Talent.Web.Aspects.Middlewares.ErrorHandlingMiddleware.Invoke(HttpContext context) in C:\Sites\Poplee.Talent\back\V4\Poplee.Talent.Web.Aspects\Middlewares\ErrorHandlingMiddleware.cs:line 34
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Poplee.Talent.Web.Shared.Tenancy.SeedDataMiddleware.Invoke(HttpContext httpContext, PopleeTalentContext context) in C:\Sites\Poplee.Talent\back\Poplee.Talent.Web\Shared\Tenancy\SeedDataMiddleware.cs:line 44
   at Lucca.Core.AspNetCore.EfMigration.Middleware.DevelopmentMigrationMiddleware`1.InvokeAsync(HttpContext context, ITenantInfoAccessor tenantInfoAccessor)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

We have to rewrite the query. What we did was to still do the Union, but with `Guid` instead of `TGoal`.

Example of the application of rights for children's team goals:

#### Before
[repo file](https://github.com/LuccaSA/Poplee.Talent/blob/a0470fedfcdeb892481ab788c9b68aafacade278/back/Poplee.Talent.Infra/Repositories/Goals/GoalsRepository.cs#L675)

#### After
[repo file](https://github.com/LuccaSA/Poplee.Talent/blob/0f76eab1714ce8fca10f86b7b671e3230b3da46f/back/Poplee.Talent.Infra/Repositories/Goals/GoalsRepository.cs#L683)

---

The method that applied rights for team goals now uses the `Queryable<Guid>` to filter on `Guids` from the original `Queryable<TGoal>`

#### Before
[repo file](https://github.com/LuccaSA/Poplee.Talent/blob/a0470fedfcdeb892481ab788c9b68aafacade278/back/Poplee.Talent.Infra/Repositories/Goals/GoalsRepository.cs#L171)

#### After
[repo file](https://github.com/LuccaSA/Poplee.Talent/blob/0f76eab1714ce8fca10f86b7b671e3230b3da46f/back/Poplee.Talent.Infra/Repositories/Goals/GoalsRepository.cs#L162)

## Perfs

### Answers Report

#### Generated request before code update

BEFORE : ~10 sec
AFTER : ~23 sec

https://hessautomobile.ilucca.local/popleetalent/api/v4/reviews-campaigns/95576e9e-f4d4-4c1a-b059-08d9e6db7e46/answers-synthesis

exec sp_executesql N'SELECT [t0].[Id], [t0].[ContractEndDate], [t0].[ContractStartDate], [t0].[CultureId], [t0].[DepartmentId], [t0].[DisplayName], [t0].[EmployeeNumber], [t0].[EstablishmentId], [t0].[FirstName], [t0].[JobTitle], [t0].[LastName], [t0].[LegalEntityId], [t0].[Login], [t0].[Mail], [t0].[ManagerId], [t0].[Name], [t0].[PictureId], [t0].[SeniorityDate], [t0].[SocioprofessionalGroupId], [t0].[GuidToken], [p].[Id], [p].[ApiKeyId], [p].[EstablishmentId], [p].[ExternalEntityId], [p].[HasContextualLegalEntityAssociation], [p].[LegalEntityId], [p].[OperationId], [p].[ScopeId], [p].[SpecificDepartmentId], [p].[SpecificUserId], [p].[UserId]
FROM (
    SELECT TOP(2) [u].[Id], [u].[ContractEndDate], [u].[ContractStartDate], [u].[CultureId], [u].[DepartmentId], [u].[DisplayName], [u].[EmployeeNumber], [u].[EstablishmentId], [u].[FirstName], [u].[JobTitle], [u].[LastName], [u].[LegalEntityId], [u].[Login], [u].[Mail], [u].[ManagerId], [u].[Name], [u].[PictureId], [u].[SeniorityDate], [u].[SocioprofessionalGroupId], [t].[GuidToken]
    FROM [PopleeTalent].[Tokens] AS [t]
    INNER JOIN [PopleeTalent].[Users] AS [u] ON [t].[OwnerId] = [u].[Id]
    WHERE [t].[GuidToken] = @__token_0
) AS [t0]
LEFT JOIN [PopleeTalent].[Permissions] AS [p] ON [t0].[Id] = [p].[UserId]
ORDER BY [t0].[GuidToken], [t0].[Id], [p].[Id]',N'@__token_0 uniqueidentifier',@__token_0='AF672886-2BCF-458F-BD62-E4AB1AFA8200'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [f].[Id], [f].[Description], [f].[FormElementDiscriminator], [f].[Name], [f].[OwnerRight], [f].[ReviewerRight], [f].[ReviewsCampaignId], [f].[SortOrder], [f].[SectionId], [t0].[Id], [t0].[Discriminator], [t0].[AllowsComment], [t0].[AllowsMultiple], [t0].[HRFileKey], [t0].[MaxLabel], [t0].[MaxValue], [t0].[MinLabel], [t0].[MinValue], [t0].[AllowsAnswer], [l].[Id], [l].[Description], [l].[FormElementId], [l].[Name], [l].[SortOrder], [g].[Id], [g].[EndDate], [g].[FormElementId], [g].[PeriodType], [g].[StartDate], [t1].[Id], [t1].[BooleanComparisonValue], [t1].[DateComparisonValue], [t1].[Discriminator], [t1].[FilterPropertyType], [t1].[Name], [t1].[NumberComparisonValue], [t1].[OperandType], [t1].[PropertyId], [t1].[StringComparisonValue], [t1].[FormElementId], [t1].[Id0], [t1].[EntryId], [t1].[EntryName], [t1].[FormElementFilterId], [t1].[SectionFilterId]
FROM [PopleeTalent].[FormElementsLegacy] AS [f]
LEFT JOIN (
    SELECT [f0].[Id], [f0].[Discriminator], [f0].[AllowsComment], [f0].[AllowsMultiple], [f0].[HRFileKey], [f0].[MaxLabel], [f0].[MaxValue], [f0].[MinLabel], [f0].[MinValue], [f0].[AllowsAnswer], [t].[Id] AS [Id0]
    FROM [PopleeTalent].[FormElementsLegacy] AS [f0]
    INNER JOIN (
        SELECT [f1].[Id], [f1].[Description], [f1].[FormElementDiscriminator], [f1].[Name], [f1].[OwnerRight], [f1].[ReviewerRight], [f1].[ReviewsCampaignId], [f1].[SortOrder], [f1].[SectionId]
        FROM [PopleeTalent].[FormElementsLegacy] AS [f1]
        WHERE [f1].[FormElementDiscriminator] IN (N''FeedbackFormElement'', N''ReviewFormElement'')
    ) AS [t] ON [f0].[Id] = [t].[Id]
    WHERE [f0].[Discriminator] IN (N''GoalFormElement'', N''HRFileDataFormElement'', N''ListFormElement'', N''RangeFormElement'', N''StarFormElement'', N''TextFormElement'', N''SynthesisFormElement'')
) AS [t0] ON [f].[Id] = [t0].[Id]
LEFT JOIN [PopleeTalent].[ListFormElementEntries] AS [l] ON [t0].[Id] = [l].[FormElementId]
LEFT JOIN [PopleeTalent].[GoalFormElementPeriods] AS [g] ON [t0].[Id] = [g].[FormElementId]
LEFT JOIN (
    SELECT [r].[Id], [r].[BooleanComparisonValue], [r].[DateComparisonValue], [r].[Discriminator], [r].[FilterPropertyType], [r].[Name], [r].[NumberComparisonValue], [r].[OperandType], [r].[PropertyId], [r].[StringComparisonValue], [r].[FormElementId], [f2].[Id] AS [Id0], [f2].[EntryId], [f2].[EntryName], [f2].[FormElementFilterId], [f2].[SectionFilterId]
    FROM [PopleeTalent].[ReviewFormFilters] AS [r]
    LEFT JOIN [PopleeTalent].[FilterComparisonValues] AS [f2] ON [r].[Id] = [f2].[FormElementFilterId]
    WHERE [r].[Discriminator] = N''FormElementFilter''
) AS [t1] ON [f].[Id] = [t1].[FormElementId]
WHERE ([f].[FormElementDiscriminator] = N''ReviewFormElement'') AND ([f].[ReviewsCampaignId] = @__campaignId_0)
ORDER BY [f].[SortOrder], [f].[Id], [l].[Id], [g].[Id], [t1].[Id], [t1].[Id0]',N'@__campaignId_0 uniqueidentifier',@__campaignId_0='95576E9E-F4D4-4C1A-B059-08D9E6DB7E46'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
WHERE (([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND [r].[ReviewsCampaignId] IN (''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')',N'@__principal_Id_0 int',@__principal_Id_0=0
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [f].[Id], [f].[Description], [f].[FormElementDiscriminator], [f].[Name], [f].[OwnerRight], [f].[ReviewerRight], [f].[ReviewsCampaignId], [f].[SortOrder], [f].[SectionId], [t2].[Id], [t2].[Discriminator], [t2].[AllowsComment], [t2].[AllowsMultiple], [t2].[HRFileKey], [t2].[MaxLabel], [t2].[MaxValue], [t2].[MinLabel], [t2].[MinValue], [t2].[AllowsAnswer], [s].[Id], [s].[Description], [s].[Name], [s].[ReviewsCampaignId], [s].[SortOrder], (
    SELECT COUNT(*)
    FROM [PopleeTalent].[Reviews] AS [r]
    INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
    CROSS APPLY (
        SELECT [a].[Id], [a].[Comment], [a].[ContributionId], [a].[CreatedAt], [a].[Discriminator], [a].[FormElementId], [a].[GoalAnswer_Comment], [a].[LastModifiedAt], [a].[OwnerId]
        FROM [PopleeTalent].[Answers] AS [a]
        WHERE [a].[ContributionId] = (
            SELECT TOP(1) [c].[Id]
            FROM [PopleeTalent].[Contributions] AS [c]
            WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
    ) AS [t]
    WHERE (((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_1))) AND [r].[ReviewsCampaignId] IN (''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t].[FormElementId] = [f].[Id])) AND ((EXISTS (
        SELECT 1
        FROM [PopleeTalent].[AnswerValues] AS [a0]
        WHERE ([t].[Id] = [a0].[AnswerId]) AND ([a0].[Discriminator] <> N''TextAnswerValue'')) OR ([t].[Comment] IS NOT NULL AND ((LTRIM(RTRIM([t].[Comment])) <> N'''') OR LTRIM(RTRIM([t].[Comment])) IS NULL))) OR EXISTS (
        SELECT 1
        FROM [PopleeTalent].[AnswerValues] AS [a1]
        WHERE ([t].[Id] = [a1].[AnswerId]) AND (([a1].[Discriminator] = N''TextAnswerValue'') AND ([a1].[TextAnswerValue_Value] IS NOT NULL AND ((LTRIM(RTRIM([a1].[TextAnswerValue_Value])) <> N'''') OR LTRIM(RTRIM([a1].[TextAnswerValue_Value])) IS NULL)))))), (
    SELECT COUNT(*)
    FROM [PopleeTalent].[Reviews] AS [r1]
    INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r2] ON [r1].[ReviewsCampaignId] = [r2].[Id]
    CROSS APPLY (
        SELECT [a2].[Id], [a2].[Comment], [a2].[ContributionId], [a2].[CreatedAt], [a2].[Discriminator], [a2].[FormElementId], [a2].[GoalAnswer_Comment], [a2].[LastModifiedAt], [a2].[OwnerId]
        FROM [PopleeTalent].[Answers] AS [a2]
        WHERE [a2].[ContributionId] = (
            SELECT TOP(1) [c0].[Id]
            FROM [PopleeTalent].[Contributions] AS [c0]
            WHERE (([c0].[Discriminator] = N''ReviewContribution'') AND ([r1].[Id] = [c0].[ReviewId])) AND ([c0].[OwnerId] = [r1].[ReviewerId]))
    ) AS [t0]
    WHERE (((([r1].[Status] = 8) AND (([r2].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r1].[OwnerId] <> @__principal_Id_1))) AND [r1].[ReviewsCampaignId] IN (''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t0].[FormElementId] = [f].[Id])) AND ([t0].[Comment] IS NOT NULL AND ((LTRIM(RTRIM([t0].[Comment])) <> N'''') OR LTRIM(RTRIM([t0].[Comment])) IS NULL))), [l].[Id], [l].[Description], [l].[FormElementId], [l].[Name], [l].[SortOrder], [l0].[Id], [l0].[Description], [l0].[FormElementId], [l0].[Name], [l0].[SortOrder]
FROM [PopleeTalent].[FormElementsLegacy] AS [f]
LEFT JOIN (
    SELECT [f0].[Id], [f0].[Discriminator], [f0].[AllowsComment], [f0].[AllowsMultiple], [f0].[HRFileKey], [f0].[MaxLabel], [f0].[MaxValue], [f0].[MinLabel], [f0].[MinValue], [f0].[AllowsAnswer], [t1].[Id] AS [Id0]
    FROM [PopleeTalent].[FormElementsLegacy] AS [f0]
    INNER JOIN (
        SELECT [f1].[Id], [f1].[Description], [f1].[FormElementDiscriminator], [f1].[Name], [f1].[OwnerRight], [f1].[ReviewerRight], [f1].[ReviewsCampaignId], [f1].[SortOrder], [f1].[SectionId]
        FROM [PopleeTalent].[FormElementsLegacy] AS [f1]
        WHERE [f1].[FormElementDiscriminator] IN (N''FeedbackFormElement'', N''ReviewFormElement'')
    ) AS [t1] ON [f0].[Id] = [t1].[Id]
    WHERE [f0].[Discriminator] IN (N''GoalFormElement'', N''HRFileDataFormElement'', N''ListFormElement'', N''RangeFormElement'', N''StarFormElement'', N''TextFormElement'', N''SynthesisFormElement'')
) AS [t2] ON [f].[Id] = [t2].[Id]
LEFT JOIN [PopleeTalent].[Section] AS [s] ON [f].[SectionId] = [s].[Id]
LEFT JOIN [PopleeTalent].[ListFormElementEntries] AS [l] ON [t2].[Id] = [l].[FormElementId]
LEFT JOIN [PopleeTalent].[ListFormElementEntries] AS [l0] ON [t2].[Id] = [l0].[FormElementId]
WHERE (([f].[FormElementDiscriminator] = N''ReviewFormElement'') AND ([f].[ReviewsCampaignId] = @__reviewsCampaignId_0)) AND ((((([t2].[Discriminator] IN (N''ListFormElement'', N''SynthesisFormElement'') OR ([t2].[Discriminator] = N''SynthesisFormElement'')) OR ([t2].[Discriminator] = N''RangeFormElement'')) OR ([t2].[Discriminator] = N''StarFormElement'')) OR ([t2].[Discriminator] = N''TextFormElement'')) OR ([t2].[Discriminator] = N''HRFileDataFormElement''))
ORDER BY [f].[Id], [l].[Id], [l0].[Id]',N'@__principal_Id_1 int,@__reviewsCampaignId_0 uniqueidentifier',@__principal_Id_1=0,@__reviewsCampaignId_0='95576E9E-F4D4-4C1A-B059-08D9E6DB7E46'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [a0].[Value], COUNT(*), ROUND(CAST(COUNT(*) AS decimal(18,2)) / @__p_4, 2)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[Comment], [a].[ContributionId], [a].[CreatedAt], [a].[Discriminator], [a].[FormElementId], [a].[GoalAnswer_Comment], [a].[LastModifiedAt], [a].[OwnerId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
INNER JOIN [PopleeTalent].[AnswerValues] AS [a0] ON [t].[Id] = [a0].[AnswerId]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND [r].[ReviewsCampaignId] IN (''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t].[FormElementId] = @__8__locals1_answerReport_FormElement_Id_2)
GROUP BY [a0].[Value]',N'@__p_4 decimal(2,0),@__principal_Id_0 int,@__8__locals1_answerReport_FormElement_Id_2 uniqueidentifier',@__p_4=25,@__principal_Id_0=0,@__8__locals1_answerReport_FormElement_Id_2='744DE29E-C759-41C4-7C89-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[Comment], [a].[ContributionId], [a].[CreatedAt], [a].[Discriminator], [a].[FormElementId], [a].[GoalAnswer_Comment], [a].[LastModifiedAt], [a].[OwnerId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND [r].[ReviewsCampaignId] IN (''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ((([t].[FormElementId] = @__answerReport_FormElement_Id_2) AND NOT (EXISTS (
    SELECT 1
    FROM [PopleeTalent].[AnswerValues] AS [a0]
    WHERE [t].[Id] = [a0].[AnswerId]))) AND ([t].[Comment] IS NOT NULL AND ((LTRIM(RTRIM([t].[Comment])) <> N'''') OR LTRIM(RTRIM([t].[Comment])) IS NULL)))',N'@__principal_Id_0 int,@__answerReport_FormElement_Id_2 uniqueidentifier',@__principal_Id_0=0,@__answerReport_FormElement_Id_2='744DE29E-C759-41C4-7C89-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [a0].[Value], COUNT(*), ROUND(CAST(COUNT(*) AS decimal(18,2)) / @__p_4, 2)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[Comment], [a].[ContributionId], [a].[CreatedAt], [a].[Discriminator], [a].[FormElementId], [a].[GoalAnswer_Comment], [a].[LastModifiedAt], [a].[OwnerId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
INNER JOIN [PopleeTalent].[AnswerValues] AS [a0] ON [t].[Id] = [a0].[AnswerId]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND [r].[ReviewsCampaignId] IN (''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t].[FormElementId] = @__8__locals1_answerReport_FormElement_Id_2)
GROUP BY [a0].[Value]',N'@__p_4 decimal(2,0),@__principal_Id_0 int,@__8__locals1_answerReport_FormElement_Id_2 uniqueidentifier',@__p_4=25,@__principal_Id_0=0,@__8__locals1_answerReport_FormElement_Id_2='C341E06F-B208-4DCC-7C8B-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[Comment], [a].[ContributionId], [a].[CreatedAt], [a].[Discriminator], [a].[FormElementId], [a].[GoalAnswer_Comment], [a].[LastModifiedAt], [a].[OwnerId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND [r].[ReviewsCampaignId] IN (''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ((([t].[FormElementId] = @__answerReport_FormElement_Id_2) AND NOT (EXISTS (
    SELECT 1
    FROM [PopleeTalent].[AnswerValues] AS [a0]
    WHERE [t].[Id] = [a0].[AnswerId]))) AND ([t].[Comment] IS NOT NULL AND ((LTRIM(RTRIM([t].[Comment])) <> N'''') OR LTRIM(RTRIM([t].[Comment])) IS NULL)))',N'@__principal_Id_0 int,@__answerReport_FormElement_Id_2 uniqueidentifier',@__principal_Id_0=0,@__answerReport_FormElement_Id_2='C341E06F-B208-4DCC-7C8B-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [a0].[Value], COUNT(*), ROUND(CAST(COUNT(*) AS decimal(18,2)) / @__p_4, 2)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[Comment], [a].[ContributionId], [a].[CreatedAt], [a].[Discriminator], [a].[FormElementId], [a].[GoalAnswer_Comment], [a].[LastModifiedAt], [a].[OwnerId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
INNER JOIN [PopleeTalent].[AnswerValues] AS [a0] ON [t].[Id] = [a0].[AnswerId]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND [r].[ReviewsCampaignId] IN (''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t].[FormElementId] = @__8__locals1_answerReport_FormElement_Id_2)
GROUP BY [a0].[Value]',N'@__p_4 decimal(2,0),@__principal_Id_0 int,@__8__locals1_answerReport_FormElement_Id_2 uniqueidentifier',@__p_4=25,@__principal_Id_0=0,@__8__locals1_answerReport_FormElement_Id_2='FB009CD7-2279-4DF8-7C8C-08D9E6DB7E48'
go

---

#### Generated request with code update

exec sp_executesql N'SELECT [t0].[Id], [t0].[ContractEndDate], [t0].[ContractStartDate], [t0].[CultureId], [t0].[DepartmentId], [t0].[DisplayName], [t0].[EmployeeNumber], [t0].[EstablishmentId], [t0].[FirstName], [t0].[JobTitle], [t0].[LastName], [t0].[LegalEntityId], [t0].[Login], [t0].[Mail], [t0].[ManagerId], [t0].[Name], [t0].[PictureId], [t0].[SeniorityDate], [t0].[SocioprofessionalGroupId], [t0].[GuidToken], [p].[Id], [p].[ApiKeyId], [p].[EstablishmentId], [p].[ExternalEntityId], [p].[HasContextualLegalEntityAssociation], [p].[LegalEntityId], [p].[OperationId], [p].[ScopeId], [p].[SpecificDepartmentId], [p].[SpecificUserId], [p].[UserId]
FROM (
    SELECT TOP(2) [u].[Id], [u].[ContractEndDate], [u].[ContractStartDate], [u].[CultureId], [u].[DepartmentId], [u].[DisplayName], [u].[EmployeeNumber], [u].[EstablishmentId], [u].[FirstName], [u].[JobTitle], [u].[LastName], [u].[LegalEntityId], [u].[Login], [u].[Mail], [u].[ManagerId], [u].[Name], [u].[PictureId], [u].[SeniorityDate], [u].[SocioprofessionalGroupId], [t].[GuidToken]
    FROM [PopleeTalent].[Tokens] AS [t]
    INNER JOIN [PopleeTalent].[Users] AS [u] ON [t].[OwnerId] = [u].[Id]
    WHERE [t].[GuidToken] = @__token_0
) AS [t0]
LEFT JOIN [PopleeTalent].[Permissions] AS [p] ON [t0].[Id] = [p].[UserId]
ORDER BY [t0].[GuidToken], [t0].[Id]',N'@__token_0 uniqueidentifier',@__token_0='AF672886-2BCF-458F-BD62-E4AB1AFA8200'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [f].[Id], [f].[Description], [f].[FormElementDiscriminator], [f].[Name], [f].[OwnerRight], [f].[ReviewerRight], [f].[ReviewsCampaignId], [f].[SortOrder], [f].[SectionId], [t].[Id], [t].[Discriminator], [t].[AllowsComment], [t].[AllowsMultiple], [t].[HRFileKey], [t].[MaxLabel], [t].[MaxValue], [t].[MinLabel], [t].[MinValue], [t].[AllowsAnswer], [t].[Id0], [l].[Id], [l].[Description], [l].[FormElementId], [l].[Name], [l].[SortOrder], [g].[Id], [g].[EndDate], [g].[FormElementId], [g].[PeriodType], [g].[StartDate], [t0].[Id], [t0].[BooleanComparisonValue], [t0].[DateComparisonValue], [t0].[Discriminator], [t0].[FilterPropertyType], [t0].[Name], [t0].[NumberComparisonValue], [t0].[OperandType], [t0].[PropertyId], [t0].[StringComparisonValue], [t0].[FormElementId], [t0].[Id0], [t0].[EntryId], [t0].[EntryName], [t0].[FormElementFilterId], [t0].[SectionFilterId]
FROM [PopleeTalent].[FormElementsLegacy] AS [f]
LEFT JOIN (
    SELECT [f0].[Id], [f0].[Discriminator], [f0].[AllowsComment], [f0].[AllowsMultiple], [f0].[HRFileKey], [f0].[MaxLabel], [f0].[MaxValue], [f0].[MinLabel], [f0].[MinValue], [f0].[AllowsAnswer], [f1].[Id] AS [Id0]
    FROM [PopleeTalent].[FormElementsLegacy] AS [f0]
    INNER JOIN [PopleeTalent].[FormElementsLegacy] AS [f1] ON [f0].[Id] = [f1].[Id]
    WHERE [f0].[Discriminator] IS NOT NULL
) AS [t] ON [f].[Id] = CASE
    WHEN [t].[Discriminator] IS NOT NULL THEN [t].[Id]
END
LEFT JOIN [PopleeTalent].[ListFormElementEntries] AS [l] ON CASE
    WHEN (([t].[Discriminator] IS NOT NULL) AND ([t].[AllowsComment] IS NOT NULL)) AND ([t].[AllowsMultiple] IS NOT NULL) THEN [t].[Id]
END = [l].[FormElementId]
LEFT JOIN [PopleeTalent].[GoalFormElementPeriods] AS [g] ON CASE
    WHEN (([t].[Discriminator] IS NOT NULL) AND ([t].[AllowsComment] IS NOT NULL)) AND ([t].[AllowsMultiple] IS NOT NULL) THEN [t].[Id]
END = [g].[FormElementId]
LEFT JOIN (
    SELECT [r].[Id], [r].[BooleanComparisonValue], [r].[DateComparisonValue], [r].[Discriminator], [r].[FilterPropertyType], [r].[Name], [r].[NumberComparisonValue], [r].[OperandType], [r].[PropertyId], [r].[StringComparisonValue], [r].[FormElementId], [f2].[Id] AS [Id0], [f2].[EntryId], [f2].[EntryName], [f2].[FormElementFilterId], [f2].[SectionFilterId]
    FROM [PopleeTalent].[ReviewFormFilters] AS [r]
    LEFT JOIN [PopleeTalent].[FilterComparisonValues] AS [f2] ON [r].[Id] = [f2].[FormElementFilterId]
    WHERE [r].[Discriminator] = N''FormElementFilter''
) AS [t0] ON [f].[Id] = [t0].[FormElementId]
WHERE ([f].[FormElementDiscriminator] = N''ReviewFormElement'') AND ([f].[ReviewsCampaignId] = @__campaignId_0)
ORDER BY [f].[SortOrder], [f].[Id], [t].[Id], [t].[Id0], [l].[Id], [g].[Id], [t0].[Id]',N'@__campaignId_0 uniqueidentifier',@__campaignId_0='95576E9E-F4D4-4C1A-B059-08D9E6DB7E46'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
WHERE (([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND ([r].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')',N'@__principal_Id_0 int',@__principal_Id_0=0
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [f].[Id], [f].[Description], [f].[FormElementDiscriminator], [f].[Name], [f].[OwnerRight], [f].[ReviewerRight], [f].[ReviewsCampaignId], [f].[SortOrder], [f].[SectionId], [t].[Id], [t].[Discriminator], [t].[AllowsComment], [t].[AllowsMultiple], [t].[HRFileKey], [t].[MaxLabel], [t].[MaxValue], [t].[MinLabel], [t].[MinValue], [t].[AllowsAnswer], [t].[Id0], [s].[Id], [l].[Id], [l].[Description], [l].[FormElementId], [l].[Name], [l].[SortOrder], [s].[Description], [s].[Name], [s].[ReviewsCampaignId], [s].[SortOrder], (
    SELECT COUNT(*)
    FROM [PopleeTalent].[Reviews] AS [r]
    INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
    CROSS APPLY (
        SELECT [a].[Id], [a].[Comment], [a].[ContributionId], [a].[CreatedAt], [a].[Discriminator], [a].[FormElementId], [a].[GoalAnswer_Comment], [a].[LastModifiedAt], [a].[OwnerId]
        FROM [PopleeTalent].[Answers] AS [a]
        WHERE [a].[ContributionId] = (
            SELECT TOP(1) [c].[Id]
            FROM [PopleeTalent].[Contributions] AS [c]
            WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
    ) AS [t0]
    WHERE (((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_1))) AND ([r].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t0].[FormElementId] = [f].[Id])) AND (((([t0].[Comment] IS NOT NULL) AND ([t0].[Comment] <> N'''')) OR EXISTS (
        SELECT 1
        FROM [PopleeTalent].[AnswerValues] AS [a0]
        WHERE ([t0].[Id] = [a0].[AnswerId]) AND ([a0].[Discriminator] <> N''TextAnswerValue''))) OR EXISTS (
        SELECT 1
        FROM [PopleeTalent].[AnswerValues] AS [a1]
        WHERE ([t0].[Id] = [a1].[AnswerId]) AND (([a1].[Discriminator] = N''TextAnswerValue'') AND (([a1].[TextAnswerValue_Value] IS NOT NULL) AND ([a1].[TextAnswerValue_Value] <> N'''')))))), CASE
    WHEN [t].[Discriminator] = N''TextFormElement'' THEN 0
    ELSE (
        SELECT COUNT(*)
        FROM [PopleeTalent].[Reviews] AS [r1]
        INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r2] ON [r1].[ReviewsCampaignId] = [r2].[Id]
        CROSS APPLY (
            SELECT [a2].[Id], [a2].[Comment], [a2].[ContributionId], [a2].[CreatedAt], [a2].[Discriminator], [a2].[FormElementId], [a2].[GoalAnswer_Comment], [a2].[LastModifiedAt], [a2].[OwnerId]
            FROM [PopleeTalent].[Answers] AS [a2]
            WHERE [a2].[ContributionId] = (
                SELECT TOP(1) [c0].[Id]
                FROM [PopleeTalent].[Contributions] AS [c0]
                WHERE (([c0].[Discriminator] = N''ReviewContribution'') AND ([r1].[Id] = [c0].[ReviewId])) AND ([c0].[OwnerId] = [r1].[ReviewerId]))
        ) AS [t1]
        WHERE (((([r1].[Status] = 8) AND (([r2].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r1].[OwnerId] <> @__principal_Id_1))) AND ([r1].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t1].[FormElementId] = [f].[Id])) AND (([t1].[Comment] IS NOT NULL) AND ([t1].[Comment] <> N'''')))
END
FROM [PopleeTalent].[FormElementsLegacy] AS [f]
LEFT JOIN (
    SELECT [f0].[Id], [f0].[Discriminator], [f0].[AllowsComment], [f0].[AllowsMultiple], [f0].[HRFileKey], [f0].[MaxLabel], [f0].[MaxValue], [f0].[MinLabel], [f0].[MinValue], [f0].[AllowsAnswer], [f1].[Id] AS [Id0]
    FROM [PopleeTalent].[FormElementsLegacy] AS [f0]
    INNER JOIN [PopleeTalent].[FormElementsLegacy] AS [f1] ON [f0].[Id] = [f1].[Id]
    WHERE [f0].[Discriminator] IS NOT NULL
) AS [t] ON [f].[Id] = CASE
    WHEN [t].[Discriminator] IS NOT NULL THEN [t].[Id]
END
LEFT JOIN [PopleeTalent].[Section] AS [s] ON [f].[SectionId] = [s].[Id]
LEFT JOIN [PopleeTalent].[ListFormElementEntries] AS [l] ON CASE
    WHEN (([t].[Discriminator] IS NOT NULL) AND ([t].[AllowsComment] IS NOT NULL)) AND ([t].[AllowsMultiple] IS NOT NULL) THEN [t].[Id]
END = [l].[FormElementId]
WHERE (([f].[FormElementDiscriminator] = N''ReviewFormElement'') AND ([f].[ReviewsCampaignId] = @__reviewsCampaignId_0)) AND [t].[Discriminator] IN (N''ListFormElement'', N''SynthesisFormElement'', N''RangeFormElement'', N''StarFormElement'', N''TextFormElement'', N''HRFileDataFormElement'')
ORDER BY [f].[Id], [t].[Id], [t].[Id0], [s].[Id]',N'@__principal_Id_1 int,@__reviewsCampaignId_0 uniqueidentifier',@__principal_Id_1=0,@__reviewsCampaignId_0='95576E9E-F4D4-4C1A-B059-08D9E6DB7E46'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [a0].[Value], COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[FormElementId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
INNER JOIN [PopleeTalent].[AnswerValues] AS [a0] ON [t].[Id] = [a0].[AnswerId]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND ([r].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t].[FormElementId] = @__8__locals1_answerReport_FormElement_Id_2)
GROUP BY [a0].[Value]',N'@__principal_Id_0 int,@__8__locals1_answerReport_FormElement_Id_2 uniqueidentifier',@__principal_Id_0=0,@__8__locals1_answerReport_FormElement_Id_2='744DE29E-C759-41C4-7C89-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[Comment], [a].[FormElementId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND ([r].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ((([t].[FormElementId] = @__answerReport_FormElement_Id_2) AND NOT (EXISTS (
    SELECT 1
    FROM [PopleeTalent].[AnswerValues] AS [a0]
    WHERE [t].[Id] = [a0].[AnswerId]))) AND (([t].[Comment] IS NOT NULL) AND ([t].[Comment] <> N'''')))',N'@__principal_Id_0 int,@__answerReport_FormElement_Id_2 uniqueidentifier',@__principal_Id_0=0,@__answerReport_FormElement_Id_2='744DE29E-C759-41C4-7C89-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [a0].[Value], COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[FormElementId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
INNER JOIN [PopleeTalent].[AnswerValues] AS [a0] ON [t].[Id] = [a0].[AnswerId]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND ([r].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t].[FormElementId] = @__8__locals1_answerReport_FormElement_Id_2)
GROUP BY [a0].[Value]',N'@__principal_Id_0 int,@__8__locals1_answerReport_FormElement_Id_2 uniqueidentifier',@__principal_Id_0=0,@__8__locals1_answerReport_FormElement_Id_2='C341E06F-B208-4DCC-7C8B-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[Comment], [a].[FormElementId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND ([r].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ((([t].[FormElementId] = @__answerReport_FormElement_Id_2) AND NOT (EXISTS (
    SELECT 1
    FROM [PopleeTalent].[AnswerValues] AS [a0]
    WHERE [t].[Id] = [a0].[AnswerId]))) AND (([t].[Comment] IS NOT NULL) AND ([t].[Comment] <> N'''')))',N'@__principal_Id_0 int,@__answerReport_FormElement_Id_2 uniqueidentifier',@__principal_Id_0=0,@__answerReport_FormElement_Id_2='C341E06F-B208-4DCC-7C8B-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT [a0].[Value], COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[FormElementId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
INNER JOIN [PopleeTalent].[AnswerValues] AS [a0] ON [t].[Id] = [a0].[AnswerId]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND ([r].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ([t].[FormElementId] = @__8__locals1_answerReport_FormElement_Id_2)
GROUP BY [a0].[Value]',N'@__principal_Id_0 int,@__8__locals1_answerReport_FormElement_Id_2 uniqueidentifier',@__principal_Id_0=0,@__8__locals1_answerReport_FormElement_Id_2='FB009CD7-2279-4DF8-7C8C-08D9E6DB7E48'
go
exec sp_reset_connection 
go
exec sp_executesql N'SELECT COUNT(*)
FROM [PopleeTalent].[Reviews] AS [r]
INNER JOIN [PopleeTalent].[ReviewsCampaigns] AS [r0] ON [r].[ReviewsCampaignId] = [r0].[Id]
CROSS APPLY (
    SELECT [a].[Id], [a].[Comment], [a].[FormElementId]
    FROM [PopleeTalent].[Answers] AS [a]
    WHERE [a].[ContributionId] = (
        SELECT TOP(1) [c].[Id]
        FROM [PopleeTalent].[Contributions] AS [c]
        WHERE (([c].[Discriminator] = N''ReviewContribution'') AND ([r].[Id] = [c].[ReviewId])) AND ([c].[OwnerId] = [r].[ReviewerId]))
) AS [t]
WHERE ((([r].[Status] = 8) AND (([r0].[IsVisibleByOwner] = CAST(1 AS bit)) OR ([r].[OwnerId] <> @__principal_Id_0))) AND ([r].[ReviewsCampaignId] = ''95576e9e-f4d4-4c1a-b059-08d9e6db7e46'')) AND ((([t].[FormElementId] = @__answerReport_FormElement_Id_2) AND NOT (EXISTS (
    SELECT 1
    FROM [PopleeTalent].[AnswerValues] AS [a0]
    WHERE [t].[Id] = [a0].[AnswerId]))) AND (([t].[Comment] IS NOT NULL) AND ([t].[Comment] <> N'''')))',N'@__principal_Id_0 int,@__answerReport_FormElement_Id_2 uniqueidentifier',@__principal_Id_0=0,@__answerReport_FormElement_Id_2='FB009CD7-2279-4DF8-7C8C-08D9E6DB7E48'
go