HasDbFunction, table value function without a mapped entity class-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

yes just create a class that has ArtistName, AlbumName and Genre however it should not be an IQueryable just a List or IEnumerable or an ICollection

I queryable is a delayed querybuilder for a table or view.

SQLFunctions results are not further queryable in SQL server so just drop the iquerable