Compound Indexes defined from Data Annotations causing no OrderBy Clause

May 22, 2013 at 8:18 PM
Edited May 22, 2013 at 8:21 PM
Underlying VFPOLEDB provider throwing error TOP requires an ORDER BY (Error 1867)

If I Define

[Column("Firstname"]
public string Firstname{ get; set; }
[Column("Lastname"]
public string Lastname{ get; set; }
[Key, Column("Serialno")]
public string Serialno{ get; set; }

then
db.Customers.FirstOrDefault(p => (p.Firstname== "Foo" && p.Lastname== "Bar"));
Works ok

If I Create a compound key
[Key, Column("Firstname", Order = 0)]
public string Firstname{ get; set; }
[Key, Column("Lastname", Order = 1)]
public string Lastname{ get; set; }
[Column("Serialno")]
public string Serialno{ get; set; }

then
db.Customers.FirstOrDefault(p => (p.Firstname== "Foo" && p.Lastname== "Bar"));
fails throwing error TOP requires an ORDER BY

and adding

db.Customers.FirstOrDefault(p => (p.Firstname== "Foo" && p.Lastname== "Bar")).OrderBy(x => x.AnyIndexedField).First();

pseudo fixes it.

Looking at the trace listener logs, I see the additional OrderBy clause causes an second linq query (ie)

SELECT TOP 1 P1.Firstname, P1... (FROM SELECT E1.Firstname, E1.....) Where (E1.Firstname...) ORDERBY....

You wouldn't think changing the model would cause Linq queries to break at a SQL level (and its slower)

Is there a way to have the compound index atleast default to the key components so it would be a normal

SELECT TOP 1 E1.Firstname,E1... From Customer Where (E1.Firstname = ...linq_0) and (E1.Lastname = ...linq_1) Order By E1.Firstname,E1.Lastname

Any other work Arounds?

Thanks,
Mike
Coordinator
May 23, 2013 at 9:36 AM
The only work around is to include the order by explicitly instead of relying on the VFP EF Provider to inject it for you. I'll work on a fix for this issue.
May 23, 2013 at 1:22 PM
I added it for now,

Thanks Again.
Mike