CODEWITHSUNDEEP
c#ef-core-2.1dbfunctions
Shadam
Shadam

Reputation: 1122

Call DbFunction with EF core 2.1

I try to call a scalar function stored in my DB. Here is my code:

public class PronosticDbContext : DbContext
{
    public PronosticDbContext(DbContextOptions<PronosticDbContext> options) : base(options)
    {

    }

    [DbFunction(FunctionName = "GetPlayerPointsForDay", Schema = "dbo")]
    public static int GetPlayerPointsForDay(int dayId, int userId) => throw new NotSupportedException();    
}

The call :

private IQueryable<PlayerRankingData> GetPlayerRanking(int? dayId)
{
    return Context.Days.Where(x => x.Id == dayId.Value).Select(x => new PlayerRankingData
    {
        // Some properties
        Users = x.Season.Users.Select(us => new PlayerRankingData.User
        {
            // Other properties
            Last = PronosticDbContext.GetPlayerPointsForDay(x.Id, us.User.Id),
            // Others again
        }).OrderByDescending(u => u.Points).ThenByDescending(u => u.Last).ThenBy(u => u.Login)
    });
}

I don't understand why but I always go to the NotSupportedException, my scalar function is never called. Why did I miss ?

I also tried like this, but same result...

modelBuilder
    .HasDbFunction(typeof(PronosticDbContext).GetMethod(nameof(GetPlayerPointsForDay)))
    .HasSchema("dbo")
    .HasName(nameof(GetPlayerPointsForDay));

Upvotes: 3

Views: 3800

Answers (1)

Shadam
Shadam

Reputation: 1122

Ok, I got it.

The thing is you can't call the scalar function directly (I don't know why), it must be encapsulate in a linq query, like this :

Last = Context.Users.Select(u => PronosticDbContext.GetPlayerPointsForDay(x.Id, us.User.Id)).FirstOrDefault()

And that's all. No need to declaration in the DbContext, this is enough :

[DbFunction]
public static int? GetPlayerPointsForDay(int dayId, int userId) => throw new NotSupportedException();

Upvotes: 6

Related Questions