Why does calling a T-SQL function using Entity Framework Core always return -1?

Question

I am using ASP.NET Core and C# if that matters. When I get to the following line in my controller:

var validLogin = context.Database.ExecuteSqlRaw($"SELECT * FROM dbo.[valid_login]('{username}','{password}')");

I will always get -1 returned. I also used ExecuteSqlCommand and got the same result. I have tried creating both table defined and scalar functions and in either case it just returns -1.

The strangest part is that it works in SSMS. When I hit the breakpiont this is the query being sent:

{SELECT * FROM dbo.[valid_login]('test@email.com','password')}

When I run that in SSMS I get the correct result of 1. If it matters here is the latest version of the function. But like I said I get the exact same results with a scalar function.

ALTER FUNCTION valid_login
    (@email varchar(1000), 
     @password varchar(1000))
RETURNS TABLE 
AS
    RETURN 
        (SELECT 
             CASE WHEN (SELECT COUNT(*) FROM [user] 
                        WHERE @email = email_address 
                          AND HASHBYTES('SHA2_512', CONCAT(password_salt, @password)) = password_hash) = 1
                     THEN 1
                     ELSE 0
             END AS valid)
GO

Clearly, one of two things must be happening. Either Entity Framework Core is changing my SQL code before running it, or I am not using the correct method to get a result from a function. I don't think it's the second one as I do not see a simple context.Database.RunSqlFunction() method or anything like it. And if it's the first one I am not knowledgeable enough to know why that is happening.

Really, I just need to know how to get the result of a T-SQL function in ASP.NET Core using Entity Framework Core. So if you can let me know the simplest way to do that, or what modifications I need to make to make this work, that would be appreciated.

user_11392290 · Answer

You always get 1 , because it is the number of selected rows.

If you want to get some data from DB you have to use this algorithm:

Create a special class to get data from sp. This class should have all properties that you select in your query string. You don't need to select everything. Just select what you need.
```
public class ResultData
{
public string Property1 {get;set;}
public string Property2 {get;set;}
.....
.....
}
```

Add to dbContext ResultData DbSet and config no key like this

public virtual DbSet ResultData { get; set; }

....

modelBuilder.Entity(e =>
        {
            e.HasNoKey();
        });

And this a sample function to get data

public async Task> GetData(int id, string name)
{
    return await _context.Set()
             .FromSqlInterpolated($"SELECT  Property1, Property2 FROM dbo.[valid_login]('{username}','{password}')")
            .ToArrayAsync();
}

I highly recommend you to use parameters instead of string values.

Why does calling a T-SQL function using Entity Framework Core always return -1?

1 Answers