I'm working with EF 4.1 code first with an existing database. I have a class that looks like this:
class myClass
{
[Key]
[Column("SomeID", Order=0)]
public int SomeID { get; set; }
[Key]
[Column("Dt", Order=1)]
public DateTime StartDate { get; set; }
public String SomeValue { get; set; }
public int SomeOtherValue { get; set; }
}
I'm trying to get the values (i.e. SomeValue, SomeOtherValue) for the record with the latest StartDate for a specific SomeID. My first attempt at the query was this:
(from x in myContext.myClassDbSet
where x.SomeID == myVariable
orderby x.StartDate descending
select x).FirstOrDefault()
This works fine, but I realized that it's not optimal, so I tried changing it to the following:
(from x in myContext.myClassDbSet
where x.SomeID == myVariable
&& x.StartDate == (from x2 in myContext.myClassDbSet
where x2.SomeID == x.SomeID
select x2.StartDate).Max()
select x).FirstOrDefault()
The SQL that LINQPad generates for this second query can be faster than the first one with the appropriate index on the table (which I'm willing to create). The problem is that this second query throws a SystemArgumentException with the message:
Object of type 'System.Data.Objects.ObjectQuery'1[myClassDbSet]' cannot be converted to type 'System.Data.Entity.DbSet'1[myClassDbSet]'.
This is thrown in the query declaration, not when trying to execute it.
The weirdest thing is that the same query (literally copy and pasted) in a different class works fine. Anyone know what I'm doing wrong?
