How to improve LINQ performance for EF

Question

I have two classes: Property and PropertyValue. A property has several values, where each value is a new revision.

When I get a set of properties, I want to include the latest revision of the value for each property.

in T-SQL, this can be very efficiently done as follows:

SELECT p.Id, pv1.StringValue, pv1.Revision FROM dbo.PropertyValues pv1 LEFT JOIN dbo.PropertyValues pv2 ON pv1.Property_Id = pv2.Property_Id AND pv1.Revision < pv2.Revision JOIN dbo.Properties p ON p.Id = pv1.Property_Id WHERE pv2.Id IS NULL ORDER BY p.Id 

The “magic” in this query is to concatenate for less than a condition and search for strings without the result called by LEFT JOIN.

How can I accomplish something like this with LINQ to EF?

The best I could come up with was:

 from pv in context.PropertyValues group pv by pv.Property into g select g.OrderByDescending(p => p.Revision).FirstOrDefault() 

It produces the correct result, but about 10 times slower than the other.

Answer 1

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 } ); 

Answer 2

Along with query optimization in Linq To Entities, you should also be aware of the work required for the Entity Framework to translate your query into SQL and then return the results back to your objects.

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

Therefore, it is also important to optimize the steps that the Entity Framework takes.

What might help:

• Pre-copy the request
• Prepare submissions
• Decide for yourself when you need to open a database connection.
• Turn off tracking (if necessary)

Here you can find documentation with performance strategies.

Answer 3

if you want to use several conditions (less expression) in the connection, you can do this, for example

 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 }