0

I have a scenario where I need to create raw query towards CosmosDb. For the sake of this question, I have a simplified call:

CosmosQueryableExtensions.FromSqlRaw(db.ProjectFolders, "SELECT VALUE c FROM c WHERE c.Site = \"mysite\"")

but it generates query with undesired subquery:

info: Microsoft.EntityFrameworkCore.Database.Command[30102]
      Executed ReadNext (784.3342 ms, 2.85 RU) ActivityId='3f8b0dfd-09f5-4fd3-99e3-3bae8edbe06e', Container='Items', Partition='?', Parameters=[]
      SELECT c
      FROM (
          SELECT VALUE c FROM c WHERE c.Site = "mysite"
      ) c

Is this by design, irrelevant, or am I doing something wrong?

Jussi Palo
  • 848
  • 9
  • 26
  • It is by design. What if you put `SELECT * FROM ...`, EF Core do not parse SQL, it will put your query in subquery and specify all needed fields and their order in the way that can simplify objects materialsation. – Svyatoslav Danyliv Dec 02 '22 at 16:29

1 Answers1

0

Explaination :- FromSQLRow generates query with in subquery and its by design.

Composing query with LINQ, EF Core will consider passed LINQ SQL query as subquery over the database. Composing SQL query with in LINQ starts with SELECT but can't have SQL like features such as

  • Trailing semicolon (;)
  • ORDER BY clause

References :-

Naveen Sharma
  • 349
  • 2
  • 4