Any()-bug

Hi,

I have build the following query:

        IQueryable<WG> query = uow.WGs.Where(w => !w.WGH.IsFlag);
        query = query.Where(v => (v.WGH.Lo1.LoCs.Where(l => l.Code.ToLower().Equals("abc".ToLower())).Any()));
        query = query.Where(v => (v.WGH.Lo2.LoCs.Where(l => l.Code.ToLower().Equals("abc".ToLower())).Any()));
        query.Select(v => v.WGHId).Distinct().ToArray();

It is not very nice, but automatically generated. The model has the following entities: LoC, Lo, WGH, WG and the following 1..n associations:

WGH -> WGs

Lo1 (Lo) -> WGH1s (WGH)

Lo2 (Lo) -> WGH2s (WGH)

Lo -> LoCs

The resulting SQL is as follows:

SELECT DISTINCT
  t0.WGHId AS [t0.WGHId]
FROM
  WG t0
INNER JOIN
  (SELECT
    LoC.Id,
    LoC.Code,
    LoC.LoId
  FROM
    LoC
  WHERE
    LOWER(LoC.Code) = 'abc'
  ) q1
ON
  t0.Id = q1.Id
WHERE
  NOT (EXISTS (
    SELECT
      WGH.*
    FROM
      WGH
    WHERE
      WGH.Id = t0.WGHId AND
      WGH.IsFlag = True
  ))

The problem can be found in the bold part. As t0 is a WG and q1 is a Loc "t0.Id = q1.Id" cannot work.

There is a workaround:

        IQueryable<WG> query = uow.WGs.Where(w => !w.WGH.IsFlag);
        query = query.Where(v => (v.WGH.Lo1.LoCs.Any(l => l.Code.ToLower().Equals("abc".ToLower()))));
        query = query.Where(v => (v.WGH.Lo2.LoCs.Any(l => l.Code.ToLower().Equals("abc".ToLower()))));
        query.Select(v => v.WGHId).Distinct().ToArray();

will result in the following correct sql:

SELECT DISTINCT
  WG.WGHId AS [WG.WGHId]
FROM
  WG
WHERE
  ((NOT (EXISTS (
    SELECT
      WGH.*
    FROM
      WGH
    WHERE
      WGH.Id = WG.WGHId AND
      WGH.IsFlag = True
  )) AND EXISTS (
    SELECT
      WGH.*
    FROM
      WGH
    WHERE
      WGH.Id = WG.WGHId AND
      EXISTS (
        SELECT
          Lo.*
        FROM
          Lo
        WHERE
          Lo.Id = WGH.Lo1Id AND
          EXISTS (
            SELECT
              LoC.*
            FROM
              LoC
            WHERE
              LoC.LoId = Lo.Id AND
              LOWER(LoC.Code) = 'abc'
          )
      )
  )) AND EXISTS (
    SELECT
      WGH.*
    FROM
      WGH
    WHERE
      WGH.Id = WG.WGHId AND
      EXISTS (
        SELECT
          Lo.*
        FROM
          Lo
        WHERE
          Lo.Id = WGH.Lo2Id AND
          EXISTS (
            SELECT
              LoC.*
            FROM
              LoC
            WHERE
              LoC.LoId = Lo.Id AND
              LOWER(LoC.Code) = 'abc'
          )
      )
  ))

Hi Dennis,

Thanks for the description, I have been able to replicate the behavior you are seeing. The reason this is happening is that because the original query has a .Where clause it is treated as a subquery. With subqueries we look to see if there is a natural association between the entity type in the outer query and the entity type in the inner query, if none is found then it falls back to an Id = Id comparison (generally this would occur when you are nesting a query on the same entity).

The second query works because the criteria is translated as part of the Any operator directly so it isnt treated as a subquery.

We will have a think about how to tackle this one, unfortunately it is not going to be a simple fix so I will have to let you know once we have an update for this.

I presume when you have said that the query is being auto-generated that this is coming from a component you are using? So you are unable to use the syntax you have described for the second query as a workaround for now?

Jeremy

Fortunatly it is our own component and I have changed the creation as described. But we have used queries like this in further parts of the code and it is very difficult to identify every location, as this problem only occurs on concatenated queries.

Hi,

as you have published LightSpeed 4 now, we are evaluating a switch to the new version. Will the bug be fixed there?

Regards,

Dennis

Hi Dennis,

We would be intending to make this update on 3.x which would then be patched forward into 4. So you wouldnt need to specifically upgrade for this.

Jeremy