CODEWITHSUNDEEP
entity-frameworklinqpad
user1351848
user1351848

Reputation: 137

Performance: LinqPad and Entity Framework generate different SQL for same Linq request

I think I understand from LinqPad doc that it uses a different linq to tsql translator than Entity Framework. LinqPad's is actually more efficient in at least one case! Here's the details:

LinqPad generates the following simple sql query:

SELECT [t0].[personId]
FROM [Person] AS [t0]
WHERE (NOT (([t0].[deleted]) = 1)) AND ([t0].[masterPersonId] = @p0)
ORDER BY [t0].[personId] DESC

For this C# linq code:

int? state = null;
string realmId = null;
int? reviewerId = null;
bool? deleted = false;
long? parentPersonId = 1275660779659;

var query = from person in Persons
     where 
     (!deleted.HasValue || person.Deleted == deleted) && 
     (!state.HasValue || person.personState == state) &&
     (!parentPersonId.HasValue || person.masterPersonId == parentPersonId) && 
     (realmId == null || person.realmId == realmId) &&
     (reviewerId == null ||(person.reviewerId == reviewerId ))
     orderby person.personId descending
     select person.personId;

So you can see LinqPad translates the linq statement above and removes extraneous sql when a parameter value is null. Nice!

EF however always generates this regardless of null parameters: 

SELECT 
[Extent1].[personId] AS [personId]
FROM [dbo].[Person] AS [Extent1]
WHERE (@p__linq__0 IS NULL OR [Extent1].[deleted] = @p__linq__1) AND 
(@p__linq__2 IS NULL OR [Extent1].[personState] = @p__linq__3) AND 
(@p__linq__4 IS NULL OR [Extent1].[masterPersonId] = @p__linq__5) AND 
(@p__linq__6 IS NULL OR [Extent1].[realmId] = @p__linq__7) AND 
((@p__linq__8 IS NULL) OR ([Extent1].[reviewerId] = @p__linq__9))

It makes for a slower query. We were hoping to use LinqPad to evaluate generated sql for EF, but obviously not if the results will be different. It looks like we can target a custom assembly for an EF connection in LinqPad. I'll play around with that to see if we can at least bring the sql queries together.

Anyone ever travel this road or know of an EF setting we can take advantage of? We're running EF4.

Thanks in advance.

Upvotes: 3

Views: 1043

Answers (1)

user1351848
user1351848

Reputation: 137

I found this excellent webcast from the author of LinqPad where he mentions this very issue. http://oreilly.com/pub/e/1295

I was using Linq To SQL translation not EF's.

Upvotes: 1

Related Questions