Provide interfaces methods for Repositories

[agkoutis]

Hello,

Correct me if I'm wrong, but based on the current implementation, Handler Controllers etc. perform several queries based on Get all and then perform a LINQ to get the requested result.

For example, in the case of users :

var user = await _userRepository.Get(u => u
.Include(u => u.Consents).ThenInclude(c => c.Scopes).ThenInclude(c => c.AuthorizedResources)
.Include(u => u.Sessions)
.Include(u => u.Realms)
.Include(u => u.Groups)
.Include(u => u.OAuthUserClaims)

which means in every query we need to perform a "Select *" from the user's table (maybe for 1k records or more) and then LINQ to find the current user.

If there was a specific Interface that a dev could implement custom queries wouldn't it be more efficient in terms of performance, instead of using callbacks functions? (example replace Linq with SQL queries)

This also will provide the functionality to replace Entity Framework with any ORM

[simpleidserver]

Indeed, controllers and handlers use the Get operation to retrieve the correct user.

There is no risk of performance problems because the LINQ Expression is translated into an SQL QUERY by EF Core. The filtering doesn't occur in memory. For example:

LINQ EXPRESSION :

await _userRepository.Get(u => u.Include(u => u.Consents).ThenInclude(c => c.Scopes).ThenInclude(c => c.AuthorizedResources).Include(u => u.Groups).Include(u => u.OAuthUserClaims).AsNoTracking().FirstOrDefaultAsync(u => u.Name == subject, cancellationToken));

SQL STATEMENT :

-- First query
exec sp_executesql N'SELECT [g].[Id], [g].[Claims], [g].[ClientId], [g].[CreateDateTime], [g].[Realm], [g].[ScopeId], [g].[SerializedAuthorizationDetails], [g].[Status], [g].[UpdateDateTime], [g].[UserId], [t].[Id]
FROM (
    SELECT TOP(1) [u].[Id]
    FROM [Users] AS [u]
    WHERE [u].[Name] = @__subject_0
) AS [t]
INNER JOIN [Grants] AS [g] ON [t].[Id] = [g].[UserId]
ORDER BY [t].[Id], [g].[Id]',N'@__subject_0 nvarchar(4000)',@__subject_0=N'administrator'

-- Second query
exec sp_executesql N'SELECT [a].[Id], [a].[ConsentId], [a].[Scope], [t].[Id], [g].[Id]
FROM (
    SELECT TOP(1) [u].[Id]
    FROM [Users] AS [u]
    WHERE [u].[Name] = @__subject_0
) AS [t]
INNER JOIN [Grants] AS [g] ON [t].[Id] = [g].[UserId]
INNER JOIN [AuthorizedScope] AS [a] ON [g].[Id] = [a].[ConsentId]
ORDER BY [t].[Id], [g].[Id], [a].[Id]',N'@__subject_0 nvarchar(4000)',@__subject_0=N'administrator'

etc...

As you can see, multiple SQL queries are executed because the QuerySplittingBehavior is set to SplitQuery in the Program.cs file.

Other ORMs such as SqlSugar can be used (https://github.com/DotNetNext/SqlSugar/tree/e5fb3cb36395908e7c24cc218554542e2f378f11/Src/Asp.Net/SqlSugar/ExpressionsToSql).
The requirement is to be able to translate LINQ expressions into SQL Query statements :)

[agkoutis]

Yes, thanks for the help,

Is there any change in the feature versions of decoupling controllers from retrieving/mapping data so we can support additional frameworks and custom mappers?

[simpleidserver]

I have created Ticket #624 to relocate the logic for retrieving resources (users/clients) via LINQ into specific classes.
We will initiate this process with the UserInfo controller in Release 4.6.

For your information, Release 4.5 is scheduled to be published this week, either on the 14th or 15th of November

[agkoutis]

Great news!

Thanks again!