This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, I've noticed a bug in Lightspeed regarding SQL generation of LINQ queries involving the Distinct() and Count() operators. I'm using Postgresql 9 and have confirmed against the latest nightly build. If I want to do this in LINQ: ( from a in TableA join b in TableB on a.TableBId equals b.Id where a.FieldFromA == 'foobar' select b.FieldFromB ).Distinct().Count();
The SQL that gets generated is: SELECT COUNT(DISTINCT t0.FieldFromB) FROM tablea t0 INNER JOIN tableb t1 ON t0.tablebid = t1.id WHERE t0.fieldfroma = 'foobar' Note there are two problems: the first - and main - one is that the incorrect alias is used. The second, possibly related one is that the field name does not use consistent casing. (As recommended in previous posts, I've got a custom naming strategy to lower-case all names for Postgresql -- removing this does not have any effect on this error). There is a partial workaround: basically do a .Distinct().AsEnumerable().Count() and it will work correctly. However, that is not workable in the long run due to AsEnumerable() getting potentially a large number of results back to the application server.
Thanks, |
|
|
|
|
Thanks for reporting this, I have included a fix for tonights nightly build which should address this issue. Please let us know if this doesnt correct the issue for you.
Jeremy |
|
|
|
|
Thanks Jeremy -- I've finally got around to confirming this! It did fix the issue.
Best regards, |
|
|
|
