How would use a stored procedure with query types?

[THammond9]

This document only shows an example of a view. How would you use a stored procedure with query types to return something that is not "*" and more than one column?

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: ace630fc-7310-10b7-39bb-26fcfe3f3950
• Version Independent ID: dbe96b32-0589-0f8c-6558-11a065d84dff
• Content: Query Types - EF Core
• Content Source: entity-framework/core/modeling/query-types.md
• Product: entity-framework
• GitHub Login: @anpete
• Microsoft Alias: divega

[adamrkerr]

You can do this, given you have a DbQuery<BlogPostsCount> BlogPostCounts in you model:

modelBuilder.Query<BlogPostsCount>().ToQuery(() => BlogPostCounts.FromSql(<SQL to execute stored procedure>));

[mtsiakiris]

What about passing parameters to the sp on runtime?

[ngoctubk]

You can pass parameters to SP on runtime like this:

1. In your Context class, add OnModelCreating

protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Query();
}

2. Call SP:

var testList = Context.Query().AsNoTracking().FromSql(
"SP_Name @p0, @p1",
"p0 value",
"p1 value"
).ToList();

[mtsiakiris]

Thank you for the information.
BTW, I am using views with EF Core, I should have figured this out earlier.
But, one more question arises, how can you handle multiple result sets from an SP?
Haven't tried that so far... I suppose EF preserves the last one?

[ngoctubk]

I don't know how to handle multiple result sets but in this case EF will get the first result set to list.

[Moorepheus]

We are just looking into EF Core 2.x. I am trying to understand the use of Stored Procedure calls and how to use them with EF Core. While this example shows how to return a query result from a stored procedure. How would you set one up that returns the return results of a stored procedure. We have a few stored procedures that do bulk updates or other things that don't have a query result, but does return a return value (in some cases a bool, string, int, etc...). Examples would be greatly appreciated.

[progmars]

I'm also having the same issue. My stored proc returns a list of records without any unique key and I need to map it to my entity because field names in the database are chaotic (legacy database).

So, as usual, I add:

    public class MySprocResultEntityConfiguration : IEntityTypeConfiguration<MySprocResultEntity>
    {
        public void Configure(EntityTypeBuilder<MySprocResultEntity> builder)
       {
            ... mapping all ugly db field names here; not using ToTable or ToView because the entity comes from sproc

But EF throws on initialization:

System.InvalidOperationException: The entity type 'MySprocResultEntity' requires a primary key to be defined.
   at Microsoft.EntityFrameworkCore.Infrastructure.ModelValidator.ValidateNonNullPrimaryKeys(IModel model)

So, how do I add custom mapping to my query type that has no primary key?

[progmars]

Oh, I just found IQueryTypeConfiguration, which was not documented in the article - I just replaced IEntityTypeConfiguration with IQueryTypeConfiguration, mapped my entity to my stored proc results without HasKey, and used the following code:

var sprocResults = await _dbContext.Query<MySprocResultEntity>()
                .FromSql("Get_MySprocResultEntities @Param1 = {0}, @Param2 = {1}", p1, p2)
                .AsNoTracking()  // I guess, this is redundant but I like to be explicit
                .ToListAsync();

I think IQueryTypeConfiguration should be added to the Query Types article.

[gopala000]

@progmars How did you define the query expression to builder.ToQuery() for stored procedure? I'm going through the same to call a sproc. A sample will be helpful.

[progmars]

@gopala000
In my case, I didn't use ToQuery at all. I just defined all my entity fields using builder.Property and that was enough.

[gopala000]

@progmars Thanks for your response. I'm looking for how to map the sproc and parameters using builder.*. In addition looking for how to pass the parameter values as well.

[ajcvickers]

Duplicate of #969