score:1

Accepted answer

Maybe this can help. Where db is the database context:

(
            from pv1 in db.PropertyValues
            from pv2 in db.PropertyValues.Where(a=>a.Property_Id==pv1.Property_Id && pv1.Revision<pv2.Revision).DefaultIfEmpty()
            join p in db.Properties
                on pv1.Property_Id equals p.Id
            where pv2.Id==null
            orderby p.Id
            select new
            {
                p.Id,
                pv1.StringValue,
                pv1.Revision
            }
    );

score:0

if you want to use multiple conditions (less than expression) in join you can do this like

from pv1 in db.PropertyValues
 join pv2 in db.PropertyValues on new{pv1.Property_ID, Condition = pv1.Revision < pv2.Revision} equals new {pv2.Property_ID , Condition = true} into temp
from t in temp.DefaultIfEmpty()
            join p in db.Properties
                on pv1.Property_Id equals p.Id
            where t.Id==null
            orderby p.Id
            select new
            {
                p.Id,
                pv1.StringValue,
                pv1.Revision
            }

score:1

Next to optimizing a query in Linq To Entities, you also have to be aware of the work it takes for the Entity Framework to translate your query to SQL and then map the results back to your objects.

Comparing a Linq To Entities query directly to a SQL query will always result in lower performance because the Entity Framework does a lot more work for you.

So it's also important to look at optimizing the steps the Entity Framework takes.

Things that could help:

  • Precompile your query
  • Pre-generate views
  • Decide for yourself when to open the database connection
  • Disable tracking (if appropriate)

Here you can find some documentation with performance strategies.


Related Articles