0

I am moving to EF Core and trying to call a stored procedure with 2 parameters.

The current setup for 1 parameter works fine.

Here is the error I see:

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

Here is my repository call:

public IQueryable<PartsGridDTO> GetPartsGridQueryable(int partId, int groupId)
{
    return MoreContext.SpPartsGridDetailYourCases
                      .FromSqlRaw($"EXECUTE dbo.GetProductsByPartAndGroup {partId}, {groupId}")
                      .Select(s => new PartsGridDTO
                                   {
                                       PartId = partId,
                                       //....
                                   }).AsQueryable();
}

I've tried some fixes from here:

Include with FromSqlRaw and stored procedure in EF Core 3.1

but none of those work.

I am returning AsQueryable() up until the controller and then calling .ToListAsync().

I tried adding .IgnoreQueryFilters(); right before .AsQueryable(), but I still see the error.

Any thoughts? Am I passing in the parameters wrong? Is it an EF Core issue or?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pg2727
  • 149
  • 1
  • 10
  • https://www.entityframeworktutorial.net/efcore/working-with-stored-procedure-in-ef-core.aspx – Zaha Aug 02 '20 at 22:35
  • @JuanPablo: I tried these and it still gives me the same error :-( `.FromSqlRaw($"GetProductsByPartAndGroup @p0, @p1", partId, groupId)` – pg2727 Aug 02 '20 at 22:44
  • @JuanPablo and `.FromSqlRaw($"GetProductsByPartAndGroup @p0 @p1", partId, groupId)` – pg2727 Aug 02 '20 at 22:44

1 Answers1

0

There were 3 items until I resolved the issue:

  1. I had extra properties that were not within the return of the stored procedure. I guess that causes the error I posted above.

  2. (A sub-issue) I also only needed to return items that the grid was going to display. Meaning the grid might show only 5 of the 8 values that are returned within the stored procedure. Having those extra columns in the return items of stored procedure called caused this error later: InvalidOperationException: The required column 'Address' was not present in the results of a 'FromSql' operation

  3. Here is the final look of my stored proc code:

         return Context.EntityExample.FromSqlRaw($"dbo.GetProductsByPartAndGroup {partId}, {groupId}")
pg2727
  • 149
  • 1
  • 10