The code you provided looks good ... not sure whats wrong. Can you create a little repro and attach it to the issue?

The code you provided looks good ... not sure whats wrong. Can you create a little repro and attach it to the issue?

@michaelstaib here I prepared small repro for you, this time using SQL Lite instead of Azure SQL. Migrations and DB seeding should be executed on startup.

These are all possible queries to execute on BananaCakePop.

query getAll {
  all {
    id
    name
  }
}

query getById {
  byId(id: 1) {
    id
    name
  }
}

query getByIdX {
  byIdX(id: 1) {
    id
    name
  }
}

If you check getById query, you can see, that the generated SQL statement is:

SELECT "a"."Id", "a"."Name"
      FROM "Accounts" AS "a"
      WHERE "a"."Id" = @__id_0

And if you check getByIdX, you will see that the generated SQL looks like:

 SELECT "a"."Id", "a"."Country", "a"."CreatedAt", "a"."Email", "a"."LastChangedAt", "a"."Name"
      FROM "Accounts" AS "a"
      WHERE "a"."Id" = @__id_0
      LIMIT 2

As I said in previous post, for me it looks like 1st query loads all the items that satisfy condition to memory, and then performs SingleOrDefault on data that are already in memory. In 2nd case, there is different query, where SingleOrDefault is performed on DB layer, but projection isn't working.

I may be wrong, or maybe I don't understand how each of those SQL statements works, so that's why I am asking for help.

@michaelstaib @PascalSenn

I looked into this a bit (using mostly v13), and there does appear to be quite a critical issue with the [UseFirstOrDefault] attribute. [UseSingleOrDefault] is less affected since that is usually used when filtering on unique values.

[UseFirstOrDefault]

This executes the query without a limit. This means that it could load an entire table into memory ⚠️, only to return a single row.

It should be setting the limit/take to 1.

[UseSingleOrDefault]

This also executes the query without a limit. If the column is unique, then it's not an issue. If the column is not unique, then it will load all matching data ⚠️, and then return a Sequence contains more than one element. error (expected).

Ideally, it should be setting the limit/take to 2.

@xmasctf For getByIdX, projection won't work since you're not returning an IQueryable.