'FromSqlRaw or FromSqlInterpolated was called with non-composable SQL and with a query composing over it' when using OwnsOne()

user_519413 0
Viewed 5272

I have several Stored Procedures which I use to search the database. Each returns several fields to do with the amount of data found/returned. The models look like this:

// simplified
public abstract class SearchResult {
  public int RowCount { get; set; }
  public int FilteredRowCount { get; set; }
  // other properties
}

public class FooSearch {
  public string Id { get; set; }
  public string Foo { get; set; }
  public SearchResult Result { get; set; }
}

Then in the DBContext I'm using OwnsOne() to link the classes, as per this MS guide

public DbSet<FooSearch> FooSearch { get; set; }

// OnModelCreating
builder.Entity<FooSearch>().OwnsOne(t => t.Result);

Finally I make the SP call:

var searchResult = db.FooSearch.FromSqlRaw("EXECUTE [Search].[Foo] {0}, {1}", foo, bar).ToList();

However this last step is giving me the following error:

System.InvalidOperationException: 'FromSqlRaw or FromSqlInterpolated was called with non-composable 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.'

Changing ToList() to AsEnumerable() makes no difference to the result.

If I remove the OwnsOne() and the Result property from the FooSearch class then the SP works and I get results. What is the cause of this issue, and how can I fix it?

entity-framework-core c#
2 Answers

I faced the same issue and fixed it after reading the issue #18232.

You need to add .IgnoreQueryFilters() after FromSqlRaw. Like that:

var searchResult = db.FooSearch
.FromSqlRaw("EXECUTE [Search].[Foo] {0}, {1}", foo, bar)
.IgnoreQueryFilters()
.ToList();
user_94431620

First Of all, you need to inform the model builder that your entity is a Keyless entity

  1. modelBuilder.Entity<FooSearch>().HasNoKey().ToView(null)
  2. when using a keyless entity or executing RawSQL, EF will map all of the properties mentioned in the query to your "FooSearch", how would ef map to SearchResult object?
  3. remove the SearchResult and replace it with fields you need to select.
user_117825740
Related