0

I see a lot of information on ASP.Net Core Identity storing DATA with the stored procedure and I tried and use the same method to retrieve / Get all or a single user detail from AspNetUsers table in SQL, but did not succeed. below is my code.

My Stored Procedure

alter PROCEDURE [dbo].[spEmployeeDetails]


@EmployeeID varchar(50) = ''

AS
BEGIN
    IF(@EmployeeID !='')
    BEGIN
        SELECT * FROM AspNetUsers WHERE EmployeeId like @EmployeeID + '%'
    END
    ELSE
    BEGIN
        SELECT * FROM AspNetUsers
    END
END

Model class


 public class AppUsers:IdentityUser
    {
      
        public string EmployeeId { get; set; }  
        public string FName { get; set; }  
        public string LName { get; set; }  
        public string Nationality { get; set; }  
        public string PositionTitle { get; set; }  
        public string Department { get; set; }
        public string DepartmentCode { get; set; }
        public string PosCode { get; set; }
        public string Grade { get; set; }
}

service class


  public AppUsers GetaUser(string id)
        {
          
            var getuser = context.AppUsers.FromSqlRaw($"spEmployeeDetails {id}").ToList();

            return getuser.FirstOrDefault();

        }

the error I am getting is

InvalidOperationException: 'FromSqlRaw' or 'FromSqlInterpolated' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.

I tried this code also but no luck

     IEnumerable<AppraisalUsers> objd = context.AppraisalUsers.FromSqlRaw($"spEmployeeDetails {id}").AsEnumerable<AppraisalUsers>();

can anyone help me to retrieve a user from AspNetUsers table generated by Identity framework

I am working on Core3.1

ProgrammingLlama
  • 36,677
  • 7
  • 67
  • 86
Isoftmaster
  • 3,175
  • 2
  • 11
  • 15
  • 1
    Have you seen [this question](https://stackoverflow.com/questions/28599404/how-to-run-stored-procedures-in-entity-framework-core)? Does it solve your problem? – ProgrammingLlama Dec 16 '21 at 08:02
  • Why are you asking for a stored procedure when you already have a better option? Just use the LINQ query you need. Either `context.AppUsers.ToList()` or `context.AppUsers.FirstOrDefault(u=>u.EmployeeID=5)` – Panagiotis Kanavos Dec 16 '21 at 08:05
  • In any case, the error is clear. The SQL you used can't be used inside another query. `FromSqlRaw` doesn't execute the query, it allows you to use it as the base of a LINQ query. What you tried to execute is `select * from (spEmployeeDetails @id)` which is invalid. Stored procedures can't be used in SELECT queries. – Panagiotis Kanavos Dec 16 '21 at 08:10
  • @PanagiotisKanavos thank you, but I am working on a project where I have to make a complex SQL query using multiple tables and end result will be from AspNetUsers table model that's why I can use LINQ query, and if I use the same FromSqlRaw with other table generated my me is working fine but only this identity framework generated table is making issue for me :( – Isoftmaster Dec 17 '21 at 18:22

1 Answers1

1

Instead of FromSqlRaw you should use FromSqlInterPolated to stop sql injection attacks.

You can try adding the SQL Parameter name like so

context.AppUsers.FromSqlInterPolated ($"exec spEmployeeDetails @EmployeeID={id}")

You could also write this as pure ef core code as

return context.AppUsers.FirstOrDefault(e => e.EmployeeId == id);
Roman
  • 11,966
  • 10
  • 38
  • 47
Herman Vos
  • 121
  • 2
  • 5