Thursday, April 14, 2011

Use Table Valued Parameters instead of IN clause (SQL Server Stack Overflow Issue)

Every SQL batch has to fit in the Batch Size Limit: 65,536 * Network Packet Size.
Your query is also limited by run time conditions. It will cause stack overflow because x IN (a,b,c) is nothing but x=a OR x=b OR x=c which creates an expression tree similar to x=a OR (x=b OR (x=c)), so it will become too deep for a large number of OR conditions. SQL 7 would hit a Stack Overflow at about 10k values in the IN, but now a days stacks are much deeper (because of x64), so it can go pretty deep.

Still if the IN clause is getting generated dynamically prefer Table Valued Parameters or use XML data type.

Saturday, February 19, 2011

Wednesday, February 16, 2011

IncludeTotalCount Silverlight RIA Service (Server Side Paging)

If you are using Server Side Paging (may be at database or service end) and returning limited set of results you may find it difficult to get the TotalItemCount of your entities.

But the answer is simple enough:

//Enable IncludeTotalCount to true in View/ViewModel before calling the data context to load data

EntityQuery query = _assessmentContext.XQuery(1, this._pageIndex, this._pageSize);
query.IncludeTotalCount = true; // Specify that service need to collect the total item count somehow

//Load your Entity asynchronously here

// Enable your service to set an out parameter for total item count:

public IQueryable Xs(int xID, int pageIndex, int pageSize, out int totalCount)
        totalCount = 110;

That's it you are done you can now get the total item count in the load operation result:

private void XsLoadingCompleted(LoadOperation lo)
            this.BusyContent = ApplicationStrings.LoadingXs;

            if (!lo.HasError)
                this.XItemCount = lo.TotalEntityCount; // total item count that you set through the out parameter //in your domain service.