CODEWITHSUNDEEP
c#linqentity-framework
cellik
cellik

Reputation: 2136

mixing database and object query in linq and provide paged results

I need to build a query that provides paged results. Part of filtering occurs in the database and part of it occurs in objects that are in memory.

Below is a simplified sample that shows what I could do i.e. run a linq query against the database and then further filter it using the custom code and then use skip/take for paging but this would be very inefficient as it needs to load all items that match the first part of my query.

    Things.Where(e=>e.field1==1 && e.field2>1).ToList()
            .Where(e=>Helper.MyFilter(e,param1,param2)).Skip(m*pageSize).Take(pageSize);

MyFilter function uses additional data that is not located in the database and it is run with additional parameters (paramX in the above example)

Is there a preferred way to handle this situation without loading the initial result fully in memory.

Upvotes: 2

Views: 838

Answers (3)

TehBoyan
TehBoyan

Reputation: 6890

.ToList()

You are converting your query into a memory object i.e. list and thus causing the query to execute and then you provide the paging on the data.

You can put it all in one Where clause:

Things.Where(e=>e.field1==1 && e.field2>1
            && e=>Helper.MyFilter(e)).Skip(m*pageSize).Take(pageSize);

and then .ToList(). That way you will give Linq to Sql a chance to generate a query and get you only the data that you want.

Or there is a particular reason why you want to do just that - converting to a memory object and then filtering? Although I don't see the point. You should be able to filter out the results that you don't want in the Linq to Sql query before you actually execute it against the database.

EDIT

As I can see from the discussion you have several options.

If you have a lot of data and do more reads than writes it might be wise to save the results from Helper.MyFilter into the database on insert if it's possible. That way you can increase performance on select as you will not pull all the data from the database and also you will have a more filtered data on the SELECT itself.

Or you can take another approach. You can put Helper class in a separate assembly and reference that assembly from SQL Server. This will enable you to put the paging logic in your database and use your code as well.

Upvotes: 0

kmk
kmk

Reputation: 613

To support Jason's answer above - entity framework supports .Skip().Take(). So send it all down to the db level and convert your where into something EF can consume.

If your where helper is complicated use Albahari's predicate builder:

http://www.albahari.com/nutshell/predicatebuilder.aspx

or the slightly easier to use Universal Predicate Builder:

http://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/ based on the above.

Upvotes: 1

Jason Meckley
Jason Meckley

Reputation: 7591

yes, query and page at the database level. whatever logic is in Helper.MyFilter needs to be in the sql query.

the other option, which is more intrusive to your code base. is to save the view model, as well as the domain entity when the entity changes. part of the view model would contain the result of Helper.MyFilter(e) so you can quickly and efficiently query for it.

Upvotes: 1

Related Questions