Record LINQ Queries. Connects VS Navigation Properties

Question

I am trying to get a better understanding of Linq queries and Entity Framework (4.1). Please take a look at the two queries. Both queries return the name of the car type (CarType.Name)

In the first request, I used union and ignored the CarType navigation property

from c in Cars.AsEnumerable(). Where(e => e.CarId == Guid.Parse("0501cc96-5610-465d-bafc-16b30890c224")) join ct in CarTypes on c.CarTypeId equals ct.CarTypeId select new CarType { Name = ct.Name } 

Secondly, I used the CarType navigation property

 from c in Cars.AsEnumerable() where c.CarId == Guid.Parse("0501cc96-5610-465d-bafc-16b30890c224") select new CarType { Name = c.CarType.Name } 

I ran both in LinqPad, so there is a Guid.Parse function.

When I run them, the first statement is faster. LinqPad reports 00: 00: 036. The second statement is slower, and LinqPad reports 00: 00: 103

Looking at the results, it seems that Linq queries that use connections instead of navigation properties are faster. Is it really so? Please someone attach some light to this. Are there any general guidelines, best practices that I should follow when writing Linq queries?

thanks

Answer 1

Since you are calling .AsEnumerable() , queries are not evaluated using LINQ to Entities, but rather LINQ to Objects.

This means that the first one is likely to complete two rounds: one to pull all the cars and one to pull all the CarTypes. He then performs the connection locally using any LINQ algorithm for the objects used for such operations.

The second probably does N + 1 round-trip, where N is the number of CarTypes. You will travel around the world to capture all the cars, and every time one of these cars has a CarTypeId that the Entity Framework has not loaded yet, it returns to the database to select this CarType.

If you use the SQL tab in LINQPad, you can see all the LINQ queries that are executed by your program.

The best practice that you should apply in this case is not to call .AsEnumerable() on an Entity Framework object set. Instead, compose your entire query, and then call .ToList() at the end to commit the results. You are probably calling .AsEnumerable() as a workaround because Guid.Parse() does not work inside the LINQ to Entities query, but you can easily remove this part from the query. In LINQPad, press Ctrl - 2 to switch to C # Statement (s) mode, and then run the query as follows:

 var guid = Guid.Parse("0501cc96-5610-465d-bafc-16b30890c224"); var carTypeNames = (from c in Cars where c.CarId == guid select new CarType { Name = c.CarType.Name }).ToList(); carTypeNames.Dump(); 

These two queries should have roughly equivalent performance when executed correctly, so you should prefer navigation properties, as they are more concise and readable. Or, according to your preference, you can turn the query around and make it based on the CarType collection:

 var guid = Guid.Parse("0501cc96-5610-465d-bafc-16b30890c224"); var carTypeNames = (from ct in CarTypes where ct.Cars.Any(c => c.CarId == guid) select new CarType { Name = c.CarType.Name }).ToList(); carTypeNames.Dump(); 

Update

Avoid creating an entity object as follows:

 public class CarTypeSummary { public string Name{get;set;} } void Main() { var guid = Guid.Parse("0501cc96-5610-465d-bafc-16b30890c224"); var carTypeNames = (from ct in CarTypes where ct.Cars.Any(c => c.CarId == guid) select new CarTypeSummary { Name = c.CarType.Name }).ToList(); carTypeNames.Dump(); } 

In production code, it is often nice to separate your API from the underlying data type to give you more room for change, without having to change the code anywhere.

 public interface ICarTypeSummary{string Name{get;}} public class CarTypeSummary : ICarTypeSummary { public string Name{get;set;} } public ICarTypeSummary GetCarTypeSummaryForCar(Guid guid) { return (from ct in CarTypes where ct.Cars.Any(c => c.CarId == guid) select new CarTypeSummary { Name = c.CarType.Name }).FirstOrDefault(); } 

Thus, if in the future you decide that you want to return the real CarType in order to use the Entity Framework caching mechanisms, you can change your implementation without entering the API:

 // Make the Entity class implement the role interface public partial class CarType : ICarTypeSummary {} public ICarTypeSummary GetCarTypeSummaryForCar(Guid guid) { return CarTypes.FirstOrDefault( ct => ct.Cars.Any(c => c.CarId == guid)); }