CODEWITHSUNDEEP
c#linqentity-framework
vlad
vlad

Reputation: 4778

Entity Framework inconsistency - works here, not there

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?

Upvotes: 3

Views: 217

Answers (1)

MikeKulls
MikeKulls

Reputation: 3049

You are probably not going to like this answer but I think if you are going to this much trouble to optimise the sql that linq is outputting then it is easier just to write it in sql.

Upvotes: 1

Related Questions