Wrong number of rows returned on Ordered data

Hi

I have the following model:

Obligation: 

RefData: - defined as ref data (cached)

RefData - OneToMany association - Obligation, nullable true

I have one Obligation with associated RefData and one without

When I fetch Obligations and do not order, 2 rows are returned

When I do the same, but order the results by RefData.Name, I only get one row returned

I have looked at the SQL pumped out by LS and it is performing an inner join on ref data when this should be a left outer join - or am I missing something ?

SELECT
  Obligation.Id AS [Obligation.Id],
  Obligation.Name AS [Obligation.Name],
  Obligation.RefDataId AS [Obligation.RefDataId]
FROM
  Obligation
INNER JOIN  --*** this should be left outer because Obligation.RefDataId is nullable***

  RefData
ON
  Obligation.RefDataId = RefData.Id
ORDER BY
  (CASE WHEN Obligation.RefDataId IS NULL THEN '' WHEN NOT (Obligation.RefDataId IS NULL) THEN RefData.Name ELSE NULL END)

I have a sample app, which demonstrates the problem

Can you help ?

Thanks

Patrick

Could you post your query?  Alternatively if you have a repro ready to go then you can attach it via the Options tab (please remove all binaries first).  Thanks!

Hi Ivan, app attached, cheers

Thanks.  You've got a number of tests in there -- I'm assuming This_does_not_return_correct_number_of_rows is the one you need us to look at, right?

Hi Ivan

Yes the test 'This_does_not_return_correct_number_of_rows' is the one to look at, although I am having some issues with the others, can we go through those after this one ?

Cheers

Patrick

Hi Patrick,

Thanks for drawing this to our attention.  You can work around it yourself by performing the join explicitly in your query, or we have a candidate fix coming in the next nightly build.  Please let us know if you still see problems!

Hi Ivan

I just got back to work and tested the fix, works perfectly thank you very much.

Could you please take a look at the other tests in the app I sent you. I am trying to project, using a method/property on the domain object. A straight select works fine, however if I use an OrderBy I get various errors.

Many thanks

Patrick

Hi Ivan

Any update on this ?

Cheers Patrick

The issue you're hitting is that a traversal in a projection can't be translated to the server, so it is being resolved client-side.  (If you look at the SQL log you will see the initial Obligation query followed by RefData queries if it's not already cached.)  This means that if an Obligation has a null RefData, you will get a null reference exception as the normal property access mechanism tries to traverse it.  Change your code to:

select new ProjectionModel {
  RefDataName = ob.RefData == null ? String.Empty : ob.RefData.Name,
}

and you should be good to go.

Using ob.RefDataName and ob.GetRefDataName() in your queries doesn't work because in the first case there is no corresponding field (remember LightSpeed is all about the fields, not the properties) and in the second case there is no translation from GetRefDataName() to a SQL function.  In some cases you can register a SQL translation for a custom function but I don't think that would be possible in this case.