How to fetch non-entity data using DTO from Stored Procedure

Question

I have written a stored procedure which returns below data -

    Datex       Score  
2023-04-05  1
2023-04-12  1
2023-04-19  1
2023-04-26  2
2023-05-03  2
2023-05-10  4
2023-05-17  4
2023-05-24  4
2023-05-31  5
2023-06-07  6

I wish to call this using C# EF

I created a DTO object as

public class CummGraphDTO
    {
        
        public DateTime Datex { get; set; }
        public int? Score { get; set; }
        
    }

Now I am trying to call it with

await _context.Database.SqlQuery<CummGraphDTO>($"exec dbo.GetCummulative").ToListAsync();

However, it does not seem to work. What is the correct way to fetch the data?

Muhammad Imran Ansari · Answer

In Entity Framework the Database.SqlQuery method is used to execute raw SQL queries and map the results to entity types. However since you are using a stored procedure there is a different approach to execute it and map the results to your DTO object. You can use the FromSqlRaw method to execute the stored procedure and map the results to your DTO class. Here's an example of how you can modify your code to fetch the data correctly: var result = await _context.CummGraphDTO.FromSqlRaw(exec dbo.GetCummulative).ToListAsync(); Make sure that your CummGraphDTO class is defined as a DbSet in your DbContext class: public DbSet CummGraphDTO { get; set; } This code assumes that your stored procedure returns the expected columns and their types match the properties in your DTO class. Hope this helps! Happy Coding.

Dave Bell · Answer

Thank you it works very well.