I've been doing some testing here. I got the just the Web API from nuget added a controller, changed the return type to IQueryable<T> pointed to my repository, grabbed SQL Trace. Output below:
HTTP GET request url .../portal/api/products/getproducts?$skip=100&$top=50&$orderby=title
SQL Trace
SELECT TOP (50)
[Project1].[C1] AS [C1],
[Project1].[Id] AS [Id],
[Project1].[TenantId] AS [TenantId],
[Project1].[Title] AS [Title],
[Project1].[Description] AS [Description],
[Project1].[SKU] AS [SKU],
[Project1].[Sell] AS [Sell],
[Project1].[Buy] AS [Buy],
[Project1].[Entity_Id] AS [Entity_Id]
FROM ( SELECT [Project1].[Id] AS [Id], [Project1].[TenantId] AS [TenantId], [Project1].[Title] AS [Title], [Project1].[Description] AS [Description], [Project1].[SKU] AS [SKU], [Project1].[Sell] AS [Sell], [Project1].[Buy] AS [Buy], [Project1].[Entity_Id] AS [Entity_Id], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[Title] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[TenantId] AS [TenantId],
[Extent1].[Title] AS [Title],
[Extent1].[Description] AS [Description],
[Extent1].[SKU] AS [SKU],
[Extent1].[Sell] AS [Sell],
[Extent1].[Buy] AS [Buy],
[Extent1].[Entity_Id] AS [Entity_Id],
1 AS [C1]
FROM [dbo].[Products] AS [Extent1]
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 100
ORDER BY [Project1].[Title] ASC
Apparently, BigShelp answer my question. However, I woul like to see a smaller example( without knockout and others...)
.....Apparently....just apparently. It is more an example of using the SPA pattern...than of exposing an IQueryable...that is why it comes with a lot of knockout.js....
The point is that the BigShelf, like all SPA applications uses the DbDataController instead of the base ApiController. Now the DbDataController manage all DB context for you...This leave room for some doubts:
DbDataController furnish to the devloper...the db context ready to be use, so it is able to manage the lifetime of the dbcontext properly. But what if I espoce my my DB IQueryable through a standard ApiController? How can I manage the DBContext associate
to the IQueryable? If I dispose the context before returning the IQueryable, then when the ApiController try to use it, it gill get a disposed object exception. On the other side if I don't dispose it...the webApi controller that doesn't know about it, has
no possibility to dispose it...so it will not be disposed at all. My Question is...When and how can I dispose it? In the end request event?
I thrust that if the client furnish adequate paging information, paging will be done efficiently...but what if the client doesn't supply any paging? The only protection is the
ResultLimitAttribute. Does the ResultLimitAttribute acts by adding paging to the queryable, and thus prevent retrieval of big amount of Data from the database, or it simply discard already retrieved results? In the second case...it
is not a good protection and we must add furher protection.
I have not seen any example of exposing a true IQueryable with a base ApiController (that is not a DbDataController)...This might help to solve my doubts
I have defined a SafeQuery type to help protectin from "dangerous" questions coming from the client. It acts as a wrapper aroung any IQueryable..new SafeQuery(..anyqueryable here..), and it may reject unwished filtering and sorting requests with the help
of information provided with attributes. I am ready to make it available for the benefit of alls...however I need an answer to the above questions before releasing it.
I've been doing some testing here. I got the just the Web API from nuget added a controller, changed the return type to IQueryable<T> pointed to my repository, grabbed SQL Trace.
How do you disposed the DB context?
Have you tried what happens if the client provide no paging information and if the results listed are limited by the ResultLimitAttribute. Does the ResultLimitAttribute acts by applying some paging? Or it just discards some results after they have been retrieved?
The DB context should be handled automatically by EF or L2S (but can be manually closed if you prefer).
If the cient provides no paging information and the results listed are limited by the ResultLimit Attribute, you simply get results up to that attribute value. Whatever the client requests it will always be reduced by the attribute. For example, if the client
requested 20 using $top=20 and the attribute was set to 5, then only the top 5 of those results would be returned. Profiling sql gives this:
SELECT TOP (5)
[top].[userAccountId] AS [userAccountId],
[top].[forename] AS [forename],
[top].[surname] AS [surname]
FROM ( SELECT TOP (20) [c].[userAccountId] AS [userAccountId], [c].[forename] AS [forename], [c].[surname] AS [surname]
FROM [dbo].[tblUserAccount] AS [c]
) AS [top]
XIII
All-Star
182690 Points
23458 Posts
ASPInsiders
Moderator
MVP
Re: Iqueryable exposed directly
Feb 19, 2012 05:25 PM|LINK
Hi,
Me too. Likely Scott will release the samples he showed soon via his blog or so.
Grz, Kris.
Interested in Azure, ASP.NET (MVC), jQuery, WCF, EF, MS SQL, ...
Keep the forums clean: report to the moderation team!
benaw
Member
211 Points
95 Posts
Re: Iqueryable exposed directly
Feb 24, 2012 05:46 PM|LINK
I've been doing some testing here. I got the just the Web API from nuget added a controller, changed the return type to IQueryable<T> pointed to my repository, grabbed SQL Trace. Output below:
Does what it says on the tin!
ignatandrei
All-Star
134973 Points
21638 Posts
Moderator
MVP
Re: Iqueryable exposed directly
Feb 25, 2012 03:00 AM|LINK
Hi benaw
I am glad it worked for you. Could you share the example? Put on a blog post ...
Ravi theja
Member
30 Points
20 Posts
Re: Iqueryable exposed directly
Mar 22, 2012 10:43 AM|LINK
How can we get the count?
francesco ab...
All-Star
20912 Points
3279 Posts
Re: Iqueryable exposed directly
Mar 28, 2012 12:23 PM|LINK
....
.....Apparently....just apparently. It is more an example of using the SPA pattern...than of exposing an IQueryable...that is why it comes with a lot of knockout.js....
The point is that the BigShelf, like all SPA applications uses the DbDataController instead of the base ApiController. Now the DbDataController manage all DB context for you...This leave room for some doubts:
I have not seen any example of exposing a true IQueryable with a base ApiController (that is not a DbDataController)...This might help to solve my doubts
I have defined a SafeQuery type to help protectin from "dangerous" questions coming from the client. It acts as a wrapper aroung any IQueryable..new SafeQuery(..anyqueryable here..), and it may reject unwished filtering and sorting requests with the help of information provided with attributes. I am ready to make it available for the benefit of alls...however I need an answer to the above questions before releasing it.
Mvc Controls Toolkit | Data Moving Plug-in Videos
francesco ab...
All-Star
20912 Points
3279 Posts
Re: Iqueryable exposed directly
Mar 28, 2012 12:27 PM|LINK
How do you disposed the DB context?
Have you tried what happens if the client provide no paging information and if the results listed are limited by the ResultLimitAttribute. Does the ResultLimitAttribute acts by applying some paging? Or it just discards some results after they have been retrieved?
Mvc Controls Toolkit | Data Moving Plug-in Videos
TonyDDeacon
Member
2 Points
1 Post
Re: Iqueryable exposed directly
Apr 26, 2012 01:15 PM|LINK
Francesco.
The DB context should be handled automatically by EF or L2S (but can be manually closed if you prefer).
If the cient provides no paging information and the results listed are limited by the ResultLimit Attribute, you simply get results up to that attribute value. Whatever the client requests it will always be reduced by the attribute. For example, if the client requested 20 using $top=20 and the attribute was set to 5, then only the top 5 of those results would be returned. Profiling sql gives this:
SELECT TOP (5)
[top].[userAccountId] AS [userAccountId],
[top].[forename] AS [forename],
[top].[surname] AS [surname]
FROM ( SELECT TOP (20) [c].[userAccountId] AS [userAccountId], [c].[forename] AS [forename], [c].[surname] AS [surname]
FROM [dbo].[tblUserAccount] AS [c]
) AS [top]