Properties from "Select" function in LINQ query not automatically projected into SQL - which leads to wrong results

Hi,

I'm currently having an issue where a specific combination of Join, Select and Distinct in a LINQ query causes LightSpeed to produce duplicate results (thus ignoring the Distinct). I have attached a small sample project that demonstrates the issue.

It's a simple WinForms app, built against a local SQL Express database (a SQL script to create the DB and sample values is part of the solution), using LightSpeed 4.0.

The issue exists in the current LightSpeed 4 release and the latest nightly build.

Any ideas / workarounds?

Thanks,

Chris

Hi Chris,

We dont seem to have received the attachment for this - would you be able to add it to the forum thread or email it through to us and we can have a look in to the issue.

Thanks!

Jeremy

Oh, sorry.

There you go...

Thanks Chris,

Have had a look at the test project and the query is actually producing the expected result. The difference you are seeing is because you are asking for 2 different things to happen.

For the result which you are deeming to be correct, you are asking for just the Title property which means we can handle this as a server side query and the distinct aspect then filters down the titles accordingly.

For the result which you are deeming to be incorrect, you are performing a client side projection by projecting into a new object where the full set of properties from the two joined entities need to be in scope to satisfy your request. So the SQL query that is generated reflects this, and as a result there is no filtering being done of the entities at the server leading to "duplicates" returning up when you perform the client side projection. For these cases you will want to perform a .Distinct() call when you subsequently select out the subject to perform the filter client side.

Jeremy

Performing the .Distinct() call after the client-side projection breaks the pager, because now only 3 results remain instead of the expected 5.

I guess the example didn't demonstrate why I went with the client-side projection in the first place: I need to select a number of properties (not just the title) from the Articles table, so that the resulting SQL query looks like this:

SELECT
  t0.*
FROM
  (
    SELECT
      t0.*,
      ROW_NUMBER() OVER(ORDER BY t0.Title) as RowNumber
    FROM
      (
        SELECT DISTINCT
          t0.ArticleId,
          t0.Title
        FROM
          Articles t0
        INNER JOIN
          ArticleSubjects t1
        ON
          t0.ArticleId = t1.ArticleId
        WHERE
          t1.SubjectId IN (1, 3)
      )
      t0
  )
  t0
WHERE
  RowNumber > 0 AND
  RowNumber <= 5

How cold this be achieved through LINQ? My approach so far was to use client-side projections in the following way:

                    IList<test> articleTitles = articles
                        .Select(x => new test
                        {
                            id = x.Id,
                            article = x.Title,
                        })
                        .ToList();

You seem to suggest that there's a better way.

Cheers,

Chris

If you use anonymous types rather than known types LightSpeed will keep this as a server side projection.

e.g.

var articleTitles = articles.Select(x => new {

                            id = x.Id,

                            article = x.Title,

                        })

                        .ToList();

Jeremy

Hi Jeremy,

This solution seems to work, thanks.

Since I need the client-side object afterwards for further processing, I require a second select after the ToList() - it's not pretty, but at least it generates the correct results.

However, I'm facing the exact same issue now with a count() query:

The beginning of the query stays the same:

                //Query articles
                IQueryable<Article> articles = uow.Articles;
                //Find all articles that are linked to a set of given subjects
                articles = articles.Join(uow.ArticleSubjects,
                    article => article.Id,
                    subject => subject.Id.ArticleId,
                    (article, subject) => new { Article = article, Subject = subject })
                    .Where(x => selectedSubjects.Contains(x.Subject.Id.SubjectId))
                    .Select(x => x.Article)
                    .Distinct();

When I now do a

int count = articles.Count();

I get 9 as a result, even though only 6 distinct articles exist in the database. The only workaround I could find so far is to use something like

int count = articles.Select(x => x.Id).ToList().Count();

Again - not as I would expect.

It seems like the

                    .Select(x => x.Article)
                    .Distinct();

part of the LINQ Join() statement is not properly projected into SQL. Or am I missing something again?

Thanks,

Chris

Hi Jeremy,

I had another look into the issue, since I was also getting wrong results with several different Count() and OrderBy() queries.

You are right that I was asking for two different things to happen. However, the .Select(x => x.Article).Distinct() part of the LINQ query should have produced identical results.

LightSpeed translates the Join() statement into Select t0.*,t1.*, thereby ignoring the last 2 statements of the LINQ query (.Select(x => x.Article).Distinct()). The correct SQL translation would be Select Distinct t0.*.

I have extended the demo project - added two more test cases (showing a Count() and OrderBy() going wrong) and the correct SQL queries for comparison (see attached).

While I was playing with the different LINQ options, trying to find a workaround, I also discovered another, unrelated bug: If I try to execute a server-side skip() and take() while also using a server-side OrderBy(), then the ordering is being ignored completely.

Thanks,
Chris

Hi Chris,

Thanks for the updates - unfortunately it will be a few days before I can have a look at this but will post an update once I have had a chance to go over the test project and assess whats going on there.

Thanks!

Jeremy

Hi Chris,

Ive added in an update which will be available in the next nightly build which resolves the Count related issue in the test project. We will now create a subquery when Count detects there is a projection occuring which can be handled server side.

Still looking into the other issues so will advise when we have an update on those.

Cheers,

Jeremy

Great, thanks for working through this.

Hi Chris,

Ive added in an update which will be available in the next nightly build which resolves the Order Error 2 related issue in the test project.

For Order Error 1 you should add a .ToList() to the end of your query (as you have in #2). When evaluating if we can handle the projection server side, if there are non anonymous or user defined types specified as part of the LINQ query other than right at the end of the projection chain then we will determine that we can handle this server side. If there are user defined or anonymous types earlier in the chain (which they are in that example because you do a select new { Id = x.Id, Title = x.Title } followed by a select x.Title, then we defer this to a client side projection. Additionally DISTINCT is not being run client side, the filtering behavior is just because when LightSpeed hydrates the entity a second time it doesnt add it into the result set (since it is already there).

Cheers,

Jeremy

Hi Jeremy,

Sorry for the long delay - I only got to work on the issue again yesterday.

I can confirm that the "Order Error 2" issue is solved with the current Nightly Build (November 28, 2011). However, the Count() issue remains. In addition, both "Order Error 1" and "Order Error 2" queries ignore the "Descending" part of the LINQ statement.

Since the original problem - the duplicate results - was still not fixed with this Nightly Build, I went back to the roots and looked at the Join() statement again. As it turns out, most of the issues disappear when I replace

            articles = articles.Join(uow.ArticleSubjects,
                article => article.Id,
                subject => subject.Id.ArticleId,
                (article, subject) => new { Article = article, Subject = subject })
                .Where(x => selectedSubjects.Contains(x.Subject.Id.SubjectId))
                .Select(x => x.Article)
                .Distinct();

with

articles = articles.Where(x => x.ArticleSubjects.Any(y => selectedSubjects.Contains(y.Id.SubjectId))).Distinct();

Now the generated SQL statements are correct, and distinct results are being generated. The only remaining issue is the "Descencing" order, which is still ignored.

I stumbled across another bug while porting this solution back into our main application (Exception: "TODO: non-funcable thing being funced"), but will open another thread for that.

Edit: Here's the other thread: http://www.mindscapehq.com/forums/thread/233809

Cheers,

Chris

Hi Chris,

We have added a fix for the descending issue and this will be available in the next nightly build. Let us know how you get on with this.

Jeremy

Thanks, that fixed it.