Complex query problem

Hi,

we just tried a bit more complex query:

MyUnitOfWork.TPCs.Where(c => c.Date >= from && c.Date <= till).Select(t => t.TV).Where(tr => tr.s.Any(s => s.P.Number == n)).ToList();

But we only get an exception: Path component [s] not found in type [TPC]

I think the query is ok, but LightSpeed does not like the construct.

Regards,

Dennis

Thanks for drawing our attention to this.  The problem is coming from the t => t.TV projection which is not being resolved correctly.  The easiest workaround for this is to rework the query to avoid projecting a parent entity, e.g.:

MyUnitOfWork.TVs
                      .Where(tv => tv.TPCs.Any(c => c.Date >= from && c.Date <= till)
                                              && tv.s.Any(s => s.P.Number == n))
                      .ToList();

Let us know if you still see problems with this transformation of the query.

The problem is not solved completely. Lightspeed now accepts the query, but the following SQL is created:

SELECT
  TV.Id AS [TV.Id]
FROM
  TV
WHERE
  (EXISTS (
    SELECT
      TPC.*
    FROM
      TPC
    WHERE
      TPC.TVId = TV.Id AND
      ((CASE WHEN TPC.Date >= '04.05.2011 00:00:00' THEN True WHEN NOT (TPC.Date >= '04.05.2011 00:00:00') THEN False ELSE NULL END) = True AND (CASE WHEN TPC.Date <= '18.05.2011 00:00:00' THEN True W
HEN NOT (TPC.Date <= '18.05.2011 00:00:00') THEN False ELSE NULL END) = True)
  ) AND EXISTS (
    SELECT
      TVthroughS.*
    FROM
      TVthroughS
    WHERE
      TVthroughS.TVId = TV.Id AND
      EXISTS (
        SELECT
          P.*
        FROM
          P
        WHERE
          P.Id = S.PId AND
          P.Number = 5
      )
  ))

As you can see the bold part is not correct and therefore rejected by the sql-server. We are using MSSQL 2008 through your MSSQL2005 connector. You can still use the example project I send you to reproduce the problem.

Hi Dennis,

Thanks for the update. This is due to a limitation when traversing associations after passing through a through-association. I have applied an update to include the appropriate functionality to support this and this will be available with tonights nightly build.

Jeremy

Hi Jeremy,

the new version works fine with your query. The old query ist still not working. Will you fix this in the future?

Regards,

Dennis

Hi Dennis,

Yes, support for the original scenario is in the works. I will post an update once we have made progress on this - hopefully it shouldnt take too long to get something into the nightly builds for you :)

Jeremy

Hi Dennis,

I have committed a candidate improvement to enable support for the original scenario. If you are able to grab a nightly build and test this out your feedback would be appreciated.

Jeremy

I just had time to pick up the latest nightly and our original query seems to work fine.

Thanks