HasDbFunction, table value function without a mapped entity class

Question

Everything I've found so far, if you are calling a table value function the return value must be an IQueryable. For example:

public IQueryable<AlbumsByGenre> ufn_AlbumsByGenre_ITVF(int genreId)
    => FromExpression(() => ufn_AlbumsByGenre_ITVF(genreId));

Most often when I'm using a table value function the table type that is returns is a DTO. That is, it doesn't match any actual tables in the database. Here is a example:

CREATE FUNCTION dbo.ufn_AlbumsByGenre_ITVF(@GenreId int)
RETURNS TABLE
AS
RETURN(  
    SELECT 
      ar.ArtistName,
      al.AlbumName,
      g.Genre
    FROM Genres g 
      INNER JOIN Albums al
        ON g.GenreId = al.GenreId 
      INNER JOIN Artists ar 
        ON al.ArtistId = ar.ArtistId
    WHERE g.GenreId = @GenreId
);

Creating an entity for the return type results in an unnecessary, unused, and unwanted table in the database. In this instance the table name is "AlbumsByGenre".

Is there any way to have the return type be an unmapped type to prevent the unnecessary table?

Answer 1

Currently (as of EF Core 6.0) the type must be a model type (with or without key). There are plans for Raw SQL queries for unmapped types for EF Core 7.0 which might or might not allow the mapping you are asking for.

So for now your type must be registered in the model (cannot be unmapped). But creating associated table is not mandatory and can be avoided by configuring it with ToView(null), e.g.

modelBuilder.Entity<AlbumsByGenre>()
    .HasNoKey() // keyless
    .ToView(null); // no table or view