Invalid column name "RowNumber" when selecting from View using FirstOrDefault() and additionally eager load does not work

Tested on: todays nightly build and 14/06 nightly.

I am having a serious (showstopper) problem with a query which joins the view attached to an entity with another entity.

I mocked up an example app to demonstrate the problem - it is attached and it inserts example data.

CategoryView is a View attached to a CategoryInCategory table, representing many to many relation of Category table entries.

The query is fairly simple - reads from view with condition, slelects one relation from view and also adds a condition on top of it, returning only one row:

var collie = uow.CategoryView.Where(x => x.InCategoryId == dogsId)
  .Select(x => x.Category)
  .Where(x => x.Name == "Collie" && x.Status == 1)
  .FirstOrDefault();

The SQL is:

exec sp_executesql N'SELECT
  t0.*
FROM
  (
    SELECT
      t0.Id,
      t0.Name,
      t0.Status,
      t1.Id,
      t1.CategoryId,
      t1.InCategoryId,
      t1.Position,
      t1.Relation
    FROM
      Category t0
    INNER JOIN
      CategoryInCategory t1
    ON
      t0.Id = t1.CategoryId
    WHERE
      t1.InCategoryId = @p1
  )
  t0
WHERE
  RowNumber <= @p0;

SELECT
  CategoryInCategory.Id,
  CategoryInCategory.CategoryId,
  CategoryInCategory.InCategoryId,
  CategoryInCategory.Position,
  CategoryInCategory.Relation
FROM
  CategoryInCategory
WHERE
  EXISTS (
    SELECT
      t0.*
    FROM
      (
        SELECT
          t0.*,
          ROW_NUMBER() OVER(ORDER BY t0.Id) as RowNumber
        FROM
          CategoryView t0
        WHERE
          (t0.Name = @p2 AND t0.Status = @p0)
      )
      t0
    WHERE
      t0.Id = CategoryInCategory.InCategoryId AND
      RowNumber <= @p0
  );

SELECT
  t1.Id,
  t1.CategoryId,
  t1.InCategoryId,
  t1.Position,
  t1.Relation
FROM
  CategoryInCategory t1
WHERE
  EXISTS (
    SELECT
      t0.*
    FROM
      Category t0
    INNER JOIN
      CategoryInCategory CategoryInCategory0
    ON
      t0.Id = CategoryInCategory0.CategoryId
    WHERE
      t0.Id = t1.InCategoryId AND
      CategoryInCategory0.InCategoryId = @p1
  )',N'@p0 int,@p1 int,@p2 nvarchar(6)',@p0=1,@p1=403,@p2=N'Collie'

The errors are:

The column 'Id' was specified multiple times for 't0'.
Invalid column name 'RowNumber'.
Invalid column name 'Name'.
Invalid column name 'Status'.

So there are couple problems with it and I wonder why there are 3 queries and a first query does not use view - only view has nested information about the hierarchy of objects? This looks completely wrong and number of fetched objects is far exceeding what's expected (if it worked).

The expected and working query would look like below:

exec sp_executesql N'SELECT
  t0.*
FROM
  (
    SELECT
      t0.Id,
      t0.Name,
      t0.Status,
      t1.Id AS Id1, -- change here alias
      t1.CategoryId,
      t1.InCategoryId,
      t1.Position,
      t1.Relation,
      ROW_NUMBER() OVER(ORDER BY t0.Id) as RowNumber -- rownumber missing
    FROM
      Category t0
    INNER JOIN
      CategoryView t1
    ON
      t0.Id = t1.CategoryId
    WHERE
      t1.InCategoryId = @p1
      AND t0.Name = @p2
  )
  t0
WHERE
  RowNumber <= @p0;
',N'@p0 int,@p1 int,@p2 nvarchar(6)',@p0=1,@p1=403,@p2=N'Collie'

Why the view is not treated as a table in the first place, it would probably solve some of the issues above...

There is another issue, possibly related (example 1). When I select from view LightSpeed ignores Eager Load Collection property, possibly also Aggregates. Seemingly the view is not used like a table but as a source of Ids for further querying.

When do you plan to change it so the view will behave just like a normal table ?

Thanks for reporting this and the repro project - We are having a look at this and I will post an update once we have an update.

Jeremy

Thanks, looking forward to it.

Hi Jeremy,

Do you possibly have any updates on this issue?

Thanks

Unfortunately nothing yet. I am still working on trying to get this resolved.

Jeremy

Hello again,

Sorry to ask but we just kick started .Net development and within a week we would need to make some models using views functional. I suspect the change to make views behave like tables should not be that dramatic but I did not yet had a chance to look into the internals of Lightspeed structures. Do you think the feature can be ready (bug-free) soon or should we rather look for alternative solutions?

Many thanks,

Sorry for the delay in getting back to you on this.

The problem is stemming from the way in which we translate the LINQ statement into a LightSpeed query, in particular because you have introduced a traversal (.Select(x => x.Category)) which is confusing the translated query into targeting the Category entity rather than the CategoryView view over CategoryInCategory entity.

I am still looking into getting the translation issue resolved but since it is not an easy fix I still cant offer any timeframe around this.

As a simple workaround for this you can switch to forcing enumeration after expressing the view w/ criteria part of the query and then have the remainder evaluated server side. To mitigate against additional queries set the "Eager Load BackReference" property of the CategoryInCategory's Category association to true which will force the associated Category to be eager loaded with the result from the view call.

e.g. From the repro project, we would introduce a .ToList() call on the second line of the LINQ statement:

var collie = uow.CategoryView.Where(x => x.InCategoryId == dogsId)
                    .ToList()
                    .Select(x => x.Category)
                    .Where(x => x.Name == "Collie" && x.Status == 1)
                    .FirstOrDefault();

In terms of views providing the same functionality when working with tables, this is already the case. The only change we make when a view name is supplied for the query is to use that as an effective alias for the table name.

Jeremy

Thanks Jeremy, sorry for not replying sooner - we have decided to switch back to Entity Framework (code first) as for now we only use SQL Server, our application model is not that complex but the queries are (and will be even more when it comes to statistics calculation and retrieval). Thanks for your support.