dotnet/efcore

Why is a type discriminator not supported in TPT mapping strategy?

gumbarros opened this issue · 12 comments

gumbarros commented

While configuring an EF Core entity using Table-per-Type (TPT) mapping strategy, I attempted to set up a type discriminator for easier identification of the derived entity types. However, it seems this feature is not supported when using TPT. Below is the configuration code I used:

modelBuilder.Entity<MenuItemEntity>()
    .ToTable("MenuItem", Schema)
    .UseTptMappingStrategy()
    .HasDiscriminator<MenuItemType>("Type")
    .HasValue<FileMenuItemEntity>(MenuItemType.File)
    .HasValue<FolderMenuItemEntity>(MenuItemType.Folder)
    .HasValue<FormElementMenuItemEntity>(MenuItemType.FormElement)
    .HasValue<PluginMenuItemEntity>(MenuItemType.Plugin)
    .HasValue<PowerBIMenuItemEntity>(MenuItemType.PowerBI)
    .HasValue<ReportMenuItemEntity>(MenuItemType.Report)
    .HasValue<UrlMenuItemEntity>(MenuItemType.Url);

Upon running the application, the configuration fails, and after reviewing the documentation, it seems TPT does not allow a discriminator column.

System.InvalidOperationException: The mapping strategy 'TPT' specified on 'MenuItemEntity' is not supported for entity types with a discriminator.

This raises the following questions:

  1. Why is a discriminator column not supported when using the TPT mapping strategy? I want to have this discriminator column at MenuItemEntity for SQL reports outside my app.
  2. Are there any underlying technical limitations or design decisions behind this?

Understanding the rationale behind this limitation would be helpful for designing better solutions or workarounds for applications that need TPT with type discriminators.

roji commented

TPT means that you get a "table per type", i.e. each CLR type has its own table in the database. That means the table in which the row is present is enough to know which type is represented by the row, and a discriminator column isn't necessary. A discriminator column in necessary in TPH, where multiple entity types are mapped to the same table, and therefore need to be distinguished (or discriminated).

Why exactly are you looking to use a discriminator with TPT?

gumbarros commented

TPT means that you get a "table per type", i.e. each CLR type has its own table in the database. That means the table in which the row is present is enough to know which type is represented by the row, and a discriminator column isn't necessary. A discriminator column in necessary in TPH, where multiple entity types are mapped to the same table, and therefore need to be distinguished (or discriminated).

Why exactly are you looking to use a discriminator with TPT?

My coworker wants to query (using pure SQL outside of .NET) a polymorphic MenuItem and he wants to know what kind it is without the 'ugly' query generated by EF Core:

DECLARE @__menuId_0 uniqueIdentifier = '3893d510-d370-45fa-89c1-4c7f93628c62';

SELECT [m].[Id], [m].[IconId], [m].[IsNew], [m].[Name], [m].[NewUntil], [m].[Order], [m].[ParentId], [m0].[Behavior], [m0].[SelectedFileId], [m1].[ExpandedByDefault], [m1].[IsGrouper], [m2].[DefaultState], [m2].[ElementName], [m3].[PluginMenuItemId], [m4].[ConnectionId], [m4].[Filter], [m4].[ReportId], [m4].[WorkspaceId], [m5].[ReportId], [m6].[Behavior], [m6].[Url], CASE
    WHEN [m6].[Id] IS NOT NULL THEN N'UrlMenuItemEntity'
    WHEN [m5].[Id] IS NOT NULL THEN N'ReportMenuItemEntity'
    WHEN [m4].[Id] IS NOT NULL THEN N'PowerBIMenuItemEntity'
    WHEN [m3].[Id] IS NOT NULL THEN N'PluginMenuItemEntity'
    WHEN [m2].[Id] IS NOT NULL THEN N'FormElementMenuItemEntity'
    WHEN [m1].[Id] IS NOT NULL THEN N'FolderMenuItemEntity'
    WHEN [m0].[Id] IS NOT NULL THEN N'FileMenuItemEntity'
END AS [Discriminator]
FROM [Infinity].[MenuItem] AS [m]
LEFT JOIN [Infinity].[MenuItemFile] AS [m0] ON [m].[Id] = [m0].[Id]
LEFT JOIN [Infinity].[MenuItemFolder] AS [m1] ON [m].[Id] = [m1].[Id]
LEFT JOIN [Infinity].[MenuItemDataDictionary] AS [m2] ON [m].[Id] = [m2].[Id]
LEFT JOIN [Infinity].[MenuItemPlugin] AS [m3] ON [m].[Id] = [m3].[Id]
LEFT JOIN [Infinity].[MenuItemPbi] AS [m4] ON [m].[Id] = [m4].[Id]
LEFT JOIN [Infinity].[MenuItemReport] AS [m5] ON [m].[Id] = [m5].[Id]
LEFT JOIN [Infinity].[MenuItemUrl] AS [m6] ON [m].[Id] = [m6].[Id]
WHERE [m].[Id] = @__menuId_0

Using a type discriminator would help querying outside of EF and maybe would help improve TPT performance inside of EF if it used that column internally and didn't have to do all the LEFT JOINs.

ajcvickers commented

@gumbarros Can you provide an example of a actual query where using the discriminator is useful?

  • 👍1
gumbarros commented

@gumbarros Can you provide an example of a actual query where using the discriminator is useful?

    SELECT 
    U.FirstName, 
    'Menu' Tipo, 
    M.Name, 
    null RoleId 
    FROM Infinity.Users as U WITH (NOLOCK)
    INNER JOIN Infinity.MenuUserClaims as UC WITH (NOLOCK) 
    ON UC.Id = U.Id
    INNER JOIN Infinity.MenuItem as M WITH (NOLOCK) 
    ON M.Id = UC.PermissionId

    UNION ALL

    SELECT 
    U.FirstName, 
    'Menu' Tipo, 
    M.Name, 
    UR.RoleId 
    FROM Infinity.Users as U WITH (NOLOCK)
    INNER JOIN Infinity.UserRoles as UR WITH (NOLOCK)
    ON UR.UserId = U.Id
    INNER JOIN Infinity.MenuRoleClaims as MUR WITH (NOLOCK)
    ON MUR.Id = UR.RoleId
    INNER JOIN Infinity.MenuItem as M WITH (NOLOCK)
    ON M.Id = MUR.PermissionId

The query is a lot larger and complex, but this is just a part of it. At 'Menu' Tipo, we want to recover the type of the menu (File, Folder, FormElement, Plugin, PowerBI, Report, Url). We don't want to do a LEFT JOIN in every possibility just to know its type, make a lot more sense a discriminator column.

For context, at the first query we are showing all allowed access to the menu by user and in the other one, by roles.

gumbarros commented

About what I said about performance, I looked at the existing issues and I believe it is related to this issue #24483. When we get a List<MyBaseClass> I agree that Joins are necessary, but when we retrieve a single record from MyBaseClass, the discriminator column would perhaps help EF.

roji commented

@gumbarros can you post the exact LINQ query you have in mind, which would translate to the above? Especially to understand what you mean by "but when we retrieve a single record from MyBaseClass"?

gumbarros commented

@gumbarros can you post the exact LINQ query you have in mind, which would translate to the above? Especially to understand what you mean by "but when we retrieve a single record from MyBaseClass"?

This is the equivalent LINQ:

    public async Task<MenuItem?> GetAsync(Guid menuId)
    {
        var menuItem = await dbContext
            .MenuItems
            .AsNoTracking()
            .FirstOrDefaultAsync(m => m.Id == menuId);
        return menuItem;
    }

The actual equivalent SQL is this:

DECLARE @__menuId_0 uniqueIdentifier = '3893d510-d370-45fa-89c1-4c7f93628c62';

SELECT [m].[Id], [m].[IconId], [m].[IsNew], [m].[Name], [m].[NewUntil], [m].[Order], [m].[ParentId], [m0].[Behavior], [m0].[SelectedFileId], [m1].[ExpandedByDefault], [m1].[IsGrouper], [m2].[DefaultState], [m2].[ElementName], [m3].[PluginMenuItemId], [m4].[ConnectionId], [m4].[Filter], [m4].[ReportId], [m4].[WorkspaceId], [m5].[ReportId], [m6].[Behavior], [m6].[Url], CASE
    WHEN [m6].[Id] IS NOT NULL THEN N'UrlMenuItemEntity'
    WHEN [m5].[Id] IS NOT NULL THEN N'ReportMenuItemEntity'
    WHEN [m4].[Id] IS NOT NULL THEN N'PowerBIMenuItemEntity'
    WHEN [m3].[Id] IS NOT NULL THEN N'PluginMenuItemEntity'
    WHEN [m2].[Id] IS NOT NULL THEN N'FormElementMenuItemEntity'
    WHEN [m1].[Id] IS NOT NULL THEN N'FolderMenuItemEntity'
    WHEN [m0].[Id] IS NOT NULL THEN N'FileMenuItemEntity'
END AS [Discriminator]
FROM [Infinity].[MenuItem] AS [m]
LEFT JOIN [Infinity].[MenuItemFile] AS [m0] ON [m].[Id] = [m0].[Id]
LEFT JOIN [Infinity].[MenuItemFolder] AS [m1] ON [m].[Id] = [m1].[Id]
LEFT JOIN [Infinity].[MenuItemDataDictionary] AS [m2] ON [m].[Id] = [m2].[Id]
LEFT JOIN [Infinity].[MenuItemPlugin] AS [m3] ON [m].[Id] = [m3].[Id]
LEFT JOIN [Infinity].[MenuItemPbi] AS [m4] ON [m].[Id] = [m4].[Id]
LEFT JOIN [Infinity].[MenuItemReport] AS [m5] ON [m].[Id] = [m5].[Id]
LEFT JOIN [Infinity].[MenuItemUrl] AS [m6] ON [m].[Id] = [m6].[Id]
WHERE [m].[Id] = @__menuId_0

I was thinking if this is possible:

First we check the discriminator column:

SELECT [m].[Discriminator]
FROM [Infinity].[MenuItem] AS [m]
WHERE [m].[Id] = @__menuId_0

Knowing that the first query returned that this is a report, a second query is executed:

SELECT [m].[Id], [m].[IconId], [m].[IsNew], [m].[Name], [m].[NewUntil], [m].[Order], [m].[ParentId], 
       [m5].[ReportId]
FROM [Infinity].[MenuItem] AS [m]
INNER JOIN [Infinity].[MenuItemReport] AS [m5] ON [m].[Id] = [m5].[Id]
WHERE [m].[Id] = @__menuId_0