CODEWITHSUNDEEP
c#.netasp.net-coreentity-framework-core
Josh Deshazer
Josh Deshazer

Reputation: 91

500 Error calling scalar database function from .net core 2.1

I am trying to call a scalar function that I have in my database from my .NET Core 2.1 Web API to get an employee's director; the function in my database expects the employee id as input, and returns the id of the director. It works just fine and is called dbo.fn_GetDirectorID.

I wanted to make an endpoint in my Web API to call this function get the directorID then send back the employee object of that director to my client. So I was looking for online resources and I came across this I was like wow it's possible so let's try it out so I followed their tutorial I now have a GetDirectorID database function in my context like the link described 

[DbFunction("fn_GetDirectorID", "dbo")]
public static int GetDirectorID(int id)
{
    throw new NotImplementedException();
}

Then in my controller I was going to do something like this 

[HttpGet("GetDirector/{id}")]
public async Task<IActionResult> GetDirector([FromRoute] int id)
{
    var directorID = KPContext.GetDirectorID(id);

    var director = await _context.Employees.FindAsync(directorID);

    if (director == null)
    {
        return NotFound();
    }

    return Ok(director);
}

but when I call the I throw the error because I get to the throw new NotIMplementedException, but I am not sure what to do here because the tutorial is saying this should call your function and work. Also I did do 

int directorID = KPContext.GetDirectorID(id)

Can anyone help? I would greatly appreciate it.

I also just tried this 

 [HttpGet("GetDirector/{id}")]
        public async Task<IActionResult> GetDirector([FromRoute] int id)
        {


            var director = await _context.Employees.FindAsync(KPContext.GetDirectorID(id));

            if (director == null )
            {
                return NotFound();
            }

            return Ok(director);
        }

Upvotes: 2

Views: 375

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205719

Scalar functions like this can only be used inside LINQ to Entities query (cannot be executed client side, must be a part of SQL query).

Which means you cannot use FindAsync, so use FirstOrDefaultAsync or SingleOrDefaultAsync instead:

var director = await _context.Employees
    .SingleOrDefaultAsync(e => e.Id == KPContext.GetDirectorID(id));

This also has advantage over FindAsync that you can eager load (Include / ThenInclude) related data if you have such.

Also please note (not mentioned in the link) that if the scalar function is defined in a class other than the target DbContext derived class, it won't be registered automatically, so you'll need to add something like this to your DbContext derived class OnModelCreating override (if it's not the KPContext class of course):

modelBuilder.HasDbFunction(() => KPContext.GetDirectorID(default(int)));

For more info, see Database scalar function mapping documentation.

Upvotes: 4

josh
josh

Reputation: 398

I would try a linq query because I don't think pure EF is supported yet. 

   [HttpGet("GetDirector/{id}")]
    public async Task<IActionResult> GetDirector([FromRoute] int id)
    {
        var director =  from p in _context.Employees where p.EmployeeId == KPContext.GetDirectorID(id) select p;

        if (director == null )
        {
            return NotFound();
        }

        return Ok(director);
    }

this should get you what you need let me know if it works for you!! cheers and have a good day.

Upvotes: 1

Related Questions