LINQ or Entity Framework creates unbounded SQL statement?

You don't say what DatabaseView is, but my wild guess based on your results is that it's not an ObjectQuery.Skip() won't. Calling AsQueryable() on an enumerable is not enough to make this happen For example, this: var foo = MyObjectContext.SomeEntitySet.AsEnumerable().AsQueryable(). Take(10) won't put the TOP in the SQL But this: var bar = MyObjectContext.SomeEntitySet.

Take(10) will Once again: You haven't said what DatabaseView is Try this operation directly on your ObjectContext and you will see that it works. The bug is in the code you use to assign DatabaseView which you haven't shown us.

You don't say what _DatabaseView is, but my wild guess based on your results is that it's not an ObjectQuery. Which would explain your problem. ObjectQuery will convert to SQL; IEnumerable.Skip() won't.

Calling AsQueryable() on an enumerable is not enough to make this happen. For example, this: var foo = MyObjectContext.SomeEntitySet.AsEnumerable().AsQueryable(). Take(10); ...won't put the TOP in the SQL.

But this: var bar = MyObjectContext.SomeEntitySet. Take(10); ... will. Once again: You haven't said what _DatabaseView is.

Try this operation directly on your ObjectContext and you will see that it works. The bug is in the code you use to assign _DatabaseView, which you haven't shown us.

Yep, pretty sure that's the case here. – Morteza Manavi Oct 25 '10 at 16:09 nope.. _DatabaseView is an IQueryable object. And I even did AsQueryalble() in the end of each sorting, filtering.... so it is 100% IQueryable.

– Nazar Gargol Oct 25 '10 at 16:58 Maybe I wasn't clear enough. AsQueryable() won't fix the problem. I'll clarify the answer for you.

– Craig Stuntz Oct 25 '10 at 17:37 I've found some similar code in here: squaredroot. Com/2008/04/08/updated-pagedlist-class and this code does everything on IEnumerable. – Nazar Gargol Oct 26 '10 at 7:06 @Nazar, no, that is not how Troy Goode's PagedList class works.

Read the code more carefully. I'm quite familiar with this code and it does not "do everything on IEnumerable. " – Craig Stuntz Oct 26 '10 at 12:07.

The only way you can actually change the SQL that is being used would be to write your own and use that instead of using the generated SQL. You mean the Skip and Take portions of the LINQ not being converted to SQL. I think it is because of the way that you are doing the LINQ.

Try something like (From l In DataBaseView Select new SomeViewModel { Date = l. Date, Details = l. Details, Level = l.

LevelName, Id = l. ViewID, Message = l. Message, ProjectName = l.

ProjectName, StatusId = l. StatusID, StatusName = l. StatusName }).

Skip(50). Take(25) Instead and see if it makes a difference in the generated code. Edit Somehow I missed the portion where you said it should be SQL that takes 25 records.

LINQ Parser definitely consider the Skip and Take methods in your LINQ to Entities query and produces the correct expression tree and then Object Services converts the expression tree to a command tree which will be passed to the database provider for the specific SQL query generation. In this case this 2 methods affecting the generated SQL with a WHERE Extent1. Row_number > 50 and SELECT TOP (25) for Skip and Take respectively.

Now, are you sure that you are looking at the right trace in the Profiler? I suggest take a look at ObjectQuery. ToTraceString method by writing the below code before going to Profiler and then debug through your code and examine the value of sql variable: var query = _DatabaseView.

Select(l=> new SomeViewModel { Date = l. Date, Details = l. Details, Level = l.

LevelName, Id = l. ViewID, Message = l. Message, ProjectName = l.

ProjectName, StatusId = l. StatusID, StatusName = l. StatusName}) .

Skip(50) . Take(25)); string sql = (query as ObjectQuery).ToTraceString().

If you can't get the SQL to perform well enough with the proper indexes then you could always try writing a stored procedure and just calling that from LINQ.

I have already written some stored procedure for this one and all the Indexes are set... so the only problem that is left is with correct skip-take logic. – Nazar Gargol Oct 25 '10 at 14:59 You can add paging to a stored procedure pretty easily. Use a Common Table Expression with the ROW_NUMBER() function.

– Dismissile Oct 25 '10 at 15:40 Unnecessary, here, and hurts composability. – Craig Stuntz Oct 25 '10 at 15:44 I would prefer to keep paging where it is. This way I keep my architecture more clean.

– Nazar Gargol Oct 25 '10 at 17:01.

Try moving the Skip and Take before the Select.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.