LightSpeed 3.0 LINQ Projection

I have the following function:

        public IQueryable<State> FindAvailableStates()
        {
            const bool isAvailable = true;

            var states = from s in UnitOfWork.States
                         join ss in UnitOfWork.StateStatuses on s.Id equals ss.StateId
                         where ss.IsAvailable == isAvailable
                         select s;

            return states;
        }

In my MVC controller I have the following code:

FindStatesAvailable().Select(
                    state => new
                    {
                        state.Id,
                        state.Description
                    }).OrderBy(s => s.Description)

The SQL trace shows all columns of both table s and ss. Is there a way to return just the two column shown in the projection rather than all columns from the join?

Thanks.

If you update to the latest nightly build you will find we have recently added in support for passing down these projections to the server in multi entity scenarios where it is possible for us to do so. This should cover your query above.

Jeremy

Thank you.

I just ran a quick test and it doesn't seem to be producing SQL that's any different. I'm using LightSpeed v3.0.1009.12888.

This is what's coming across the profiler:

exec sp_executesql N'SELECT
  [t0].[Id] AS [t0.Id],
  [t0].[Code] AS [t0.Code],
  [t0].[CreatedOn] AS [t0.CreatedOn],
  [t0].[DeletedOn] AS [t0.DeletedOn],
  [t0].[Description] AS [t0.Description],
  [t0].[UpdatedOn] AS [t0.UpdatedOn],
  [t1].[StateId] AS [t1.StateId],
  [t1].[CreatedOn] AS [t1.CreatedOn],
  [t1].[DeletedOn] AS [t1.DeletedOn],
  [t1].[IsAvailable] AS [t1.IsAvailable],
  [t1].[UpdatedOn] AS [t1.UpdatedOn]
FROM
  [oq].[State] [t0]
INNER JOIN
  [oq].[StateStatus] [t1]
ON
  [t0].[Id] = [t1].[StateId]
WHERE
  ([t1].[IsAvailable] = @p0 AND [t0].[DeletedOn] IS NULL)
ORDER BY
  [t0].[Description]',N'@p0 bit',@p0=1

Ah, indeed. That is because you have a chained select there which is a different expression tree altogether ;)

Ive added in some additional checks which should cover this into the latest nightly build (20100128 and above) if you are able to review that. Unfortunately the nature of handling LINQ expressions and the associated projection chain means that we have to assume we can't handle it natively unless we check for cases in which we can. If you have other examples where you believe it should be handled but isn't, let us know and we can review them.

Jeremy