Geeks With Blogs

News This is the *old* blog. The new one is at blog.sixeyed.com

Elton Stoneman
This is the *old* blog. The new one is at blog.sixeyed.com
Recently I've been putting together a generic approach for paging the response from a WCF service. Paging changes the service signature, so it's not as simple as adding a behavior to an existing service in config, but the complexity of the paging is isolated in a generic base class.
We're using the Entity Framework talking to SQL Server, so when we ask for a page using LINQ's .Take() method we get a nice efficient SQL query for just the rows we want, with minimal impact on SQL Server and network traffic. We use the maximum ID of the record returned as a high-water mark (rather than using .Skip() to go to the next record), so the approach caters for records being deleted between page requests.
In the paged response we include a HasMorePages indicator, computed by comparing the max ID in the page of results to the max ID for the whole resultset - if the latter is bigger, then there are more pages.
In some quick performance testing, the paged version of the service performed much more slowly than the unpaged version, which was unexpected. We narrowed it down to the code which gets the max ID for the full resultset - instead of building an efficient MAX() SQL query, EF was returning the whole resultset and then computing the max ID in the service layer.
It's easy to reproduce - take this AdventureWorks query:
            var context = new AdventureWorksEntities();
            var query = from od in context.SalesOrderDetail
                        where od.ModifiedDate >= modified
                         && od.SalesOrderDetailID.CompareTo(id) > 0
                        orderby od.SalesOrderDetailID
                        select od;
 
We can find the maximum SalesOrderDetailID like this:
            var maxIdEfficiently = query.Max(od => od.SalesOrderDetailID);
 
which produces our efficient MAX() SQL query. If we're doing this generically and we already have the ID function in a Func:
            Func<SalesOrderDetail, int> idFunc = od => od.SalesOrderDetailID;
            var maxIdInefficiently = query.Max(idFunc);
 
This fetches all the results from the query and then runs the Max() function in code. If you look at the difference in Reflector, the first call passes an Expression to the Max(), while the second call passes a Func. So it's an easy fix - wrap the Func in an Expression:
            Expression<Func<SalesOrderDetail, int>> idExpression = od => od.SalesOrderDetailID;
            var maxIdEfficientlyAgain = query.Max(idExpression);
 
- and we're back to running an efficient MAX() statement.
Evidently the EF provider can dissect an Expression and build its equivalent in SQL, but it can't do that with Funcs.

 

Posted on Thursday, June 3, 2010 9:38 PM EF , LINQ | Back to top


Comments on this post: Use Expressions with LINQ to Entities

# wow
Requesting Gravatar...
Love it. Thanks.
Left by tiffany on Jun 27, 2010 5:12 AM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
Nice tutorial. I have gained a lot of new things from this post. Thanks a bunch for this incredible article.
Left by Oral Medicine Radio Diagnosis on Jul 01, 2010 1:50 AM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
This is an enlightening blog post that has opened my eyes to new avenues that need to be explored.This explanation is interesting and beyond arguable. Thanks for sharing this post with us. Keep blogging.
Left by platinum protection on Jul 29, 2010 2:04 AM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
Merci pour ce blog fantastique. Où d’autre peut-on obtenir ce genre d’infos écrites de manière parfaite ? J’ai une présentation que je suis travaille actuellement sur, et j’ai été à l’affût de telles informations.
Left by pressure washer reviews on Mar 02, 2011 1:11 PM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
I am searching awesome news and idea. What I have found from your site,it is actually highly content. You have spent long time for this post. I do not like to waste time by commenting but how can I stop me reading this post. So thanks for sharing .
Left by hair replacement for men on Mar 03, 2011 12:16 PM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
I like the meaning of education and knopwing that is good for our future make me love education more. I want to be a good student and want to have a good live. Thanks for sharing.
Left by product reviews on Mar 04, 2011 1:07 PM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
Nice, you have put a lot of work into this.
Left by Payday Loan Houston on Oct 09, 2011 9:36 PM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
You guys should think of arranging some gallery to exhibit them out there! Personally I adore custom essays on this topic because they help us arise the awareness in people.
Left by tory burch outlet online on Oct 23, 2011 3:58 AM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...

I’m still learning from you, while I’m trying to reach my goals. I beyond doubt love reading all that is written on your blog. Keep the stories coming. I loved it!
Left by tory burch outlet locations on Oct 23, 2011 3:58 AM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
This post have me much interested to start using expressions. LINQ to entities but just learned some basic ideas from some of my folks.
Left by food business on Nov 04, 2011 4:28 PM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
From your article, you can learn a lot of philosophy of life. Many aspects of your life, the article has its own unique perspective, and these ideas are worth learning from. This article of the classic collection.
Left by Hegn on Dec 23, 2011 7:03 PM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
I tried this query " var context = new AdventureWorksEntities();
var query = from od in context.SalesOrderDetail
where od.ModifiedDate >= modified
&& od.SalesOrderDetailID.CompareTo(id) > 0
orderby od.SalesOrderDetailID
select od;
"

but it's not actually working here
Left by real estate toronto on Dec 26, 2011 4:25 AM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
I agree with you. You have given to us with such a large collection of information.
Great work you have done by sharing them to all.
Left by editing service on Feb 10, 2012 10:32 AM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
well, I guess this one is going to be something that we should be proud of.
Left by seo web design services on Mar 18, 2012 5:57 PM

# re: Use Expressions with LINQ to Entities
Requesting Gravatar...
I have gained a lot of new things from this post.
Left by Amlopine on Jul 11, 2012 4:26 PM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net