Parameters not passed to BCS External List via REST API

Question

I have two external lists connected via external content types which are connected to stored procedures in SQL Server. I believe I have them set up identically (though evidently not because one is not working).

the working procedure looks like this

select id = row_number() over (order by U.UserID)
    , UserIdValue = U.UserID
    , UserIdDisplay = '@userId=' + @userId
    , SiteIdValue = U.SiteID
    , SiteIdDisplay = U.SiteID
from User_Site U
INNER JOIN Final_Site F On F.SiteID=U.SiteID
where U.UserID = coalesce(@userId,U.UserID)

and when I call it through the REST API like this

var listName = 'SiteListForUser'
var itemType = "SP.Data." + listName.charAt(0).toUpperCase() + listName.split(" ").join("").slice(1) + "ListItem";
    var item = {
        "__metadata": { "type": itemType }
        };

$.ajax({
type: 'GET',
contentType: "application/json;odata=verbose",
url: "myUrl/_api/web/lists/getbytitle('SiteListForUser')/items?$filter=(SiteIdValue eq 'aSiteId') and (UserIdValue eq 'MyUserName')&$top=200",
data: JSON.stringify(item),
headers: {
    "Accept": "application/json;odata=verbose",
    },
async: "false",
success: function (data) {
           console.log(data);

        }

});

data.d.results has one record (as expected) and it is

{
   [functions]: ,
   __metadata: { },
   AttachmentFiles: { },
   BdcIdentity: "_bunchofnumbers",
   ContentType: { },
   FieldValuesAsHtml: { },
   FieldValuesAsText: { },
   FieldValuesForEdit: { },
   File: { },
   FileSystemObjectType: 0,
   FirstUniqueAncestorSecurableObject: { },
   Folder: { },
   Id: 0,
   ParentList: { },
   RoleAssignments: { },
   SiteIdDisplay: "aSiteId",
   SiteIdValue: "aSiteId",
   UserIdDisplay: "@userId=myUserName",
   UserIdValue: "myUserName"
}

so far so good. Now my second stored procedure looks like this

select top 2000 id = ROW_NUMBER() over (order by U.UserId, U.SiteId, F.TaskId)
    , UserIdValue = U.UserID
    , UserIdDisplay = '@userId=' + @userId
    , SiteIdValue = U.SiteID
    , SiteIdDisplay = '@siteId=' + @siteId
    , TaskIdValue = F.TaskID
    , TaskIdDisplay = F.TaskID
from User_Site U
INNER JOIN Final_Site_Task F On F.SiteID=U.SiteID
where U.UserID= coalesce(@userId, U.UserID) 
    and U.SiteId= coalesce(@siteId, U.SiteId)

and when I call it from JS

var listName = 'TaskListForUserSite'
var itemType = "SP.Data." + listName.charAt(0).toUpperCase() + listName.split(" ").join("").slice(1) + "ListItem";
    var item = {
        "__metadata": { "type": itemType }
        };

$.ajax({
type: 'GET',
contentType: "application/json;odata=verbose",
url: "myUrl/_api/web/lists/getbytitle('TaskListForUserSite')/items?$filter=(SiteIdValue eq 'aSite') and (UserIdValue eq 'myUserName')&$top=200",
data: JSON.stringify(item),
headers: {
    "Accept": "application/json;odata=verbose",
    },
async: "false",
success: function (data) {
           console.log(data);
        }

});

I get 0 results in data.d.results. If I remove the top 2000 from my stored procedure I get an error because BCS can't return more then 2000 results. I then try (with the top back in place)

"myUrl/_api/web/lists/getbytitle('TaskListForUserSite')/items?$filter=(SiteIdValue eq 'aSite')"

Where I get 26 results but each one looks basically like this

{
   [functions]: ,
   __metadata: { },
   AttachmentFiles: { },
   BdcIdentity: "__bunchofnumbers",
   ContentType: { },
   FieldValuesAsHtml: { },
   FieldValuesAsText: { },
   FieldValuesForEdit: { },
   File: { },
   FileSystemObjectType: 0,
   FirstUniqueAncestorSecurableObject: { },
   Folder: { },
   Id: 0,
   ParentList: { },
   RoleAssignments: { },
   SiteIdDisplay: null,
   SiteIdValue: "aSiteId",
   TaskIdDisplay: "a task Id 123",
   TaskIdValue: "a task Id 123",
   UserIdDisplay: null,
   UserIdValue: "myUserName"
}

notice that SiteIdDisplay and UserIdDisplay are null indicating that my filter parameters did not get passed to the stored procedure at all. Instead all the 'filtering' was done after the results were returned to BCS.

So how do I set up my external content type so that input parameters are mapped to stored procedure parameters?

Answer 1

Like Melanie Culver alluded to a CAML query can be used to pass the parameters directly to the stored procedure.

My external content type has an input parameter like this

and I can specify my input parameter by name in JavaScript like:

var viewXml = '<View Name="{my view GUID}" >' 
viewXml += '    <Method Name="GetTaskListForUserSite">'
viewXml += '        <Filter Name="FilterUserId" Value="' + currentUserId.innerHTML + '" />'
viewXml += '        <Filter Name="FilterSiteId" Value="' + siteId + '" />'
viewXml += '    </Method>'
viewXml += '    <Query />'
viewXml += '</View>'

 $.ajax({
    type: 'POST',
    contentType: "application/json;odata=verbose",
    url: "myUrl/_api/web/lists/getByTitle('TaskListForUserSite')/renderlistdata(@viewXml)?@viewXml='"+viewXml+"'",
    headers: {
        "Accept": "application/json;odata=verbose",
        "X-RequestDigest": $("#__REQUESTDIGEST").val()
        },
    async: "true",
    success: function (data) {

            var results= JSON.parse(data.d.RenderListData);
            console.log(results);

            },
    error: function (data){
        console.log(data);
        console.log('error');
    }

    });

The /items?$filter=someField eq 'someValue' syntax which I was using before will request all data from the data source then do the filtering in SharPoint (but before it is passed to the client) which means it is very easy to hit the GetItems command, too.

Database response throttled. Maximum number of rows that can be read through the database system utility is 2000

limit. Using a CAML query passes these parameters directly to the stored procedure and filtering is done in SQL Server before SP sees any data. I was able to verify this by returning my input parameters in the response data set and checking they matched what was passed in.

I'm also using a view in the renderlistdata call but I suspect it would similarly work with GetItems

Answer 2

I had the exact same challenge yesterday and found a solution. I came across this blog post: https://blogs.msdn.microsoft.com/chjain/2013/03/01/getting-data-from-large-external-lists/

One problem I had though was, that if I didn't list all the fields in the view fields, I got a KeyNotFound exception.

I put the CAML query in the solution suggested here: Combine REST query "GetItems" action with OData $filter parameter

Works like a charm and is super performant, since the SQL Server is doing the filtering now.

Answer 3

Due to the previous posted solutions, I was able to get this to work. Thank you! Below is my solution. I have an external list based on an external content type (ECT) that accepts an ID parameter. I did not need to specify the name of the SharePoint list view GUID in the Name attribute of the View element; only the name of the "Read List" operation of the ECT.

function getData(siteUrl, listName, id){

    var caml = '<View>' 
        + '<Method Name="myProcedureName">'
        + '<Filter Name="ID" Value="' + id + '" />'
        + '</Method>'
        + '<Query />'
        + '</View>';

    getFormDigest().then(function (digest){
        $.ajax({
            type: 'POST',
            contentType: "application/json;odata=verbose",
            url: siteUrl + "/_api/web/lists/getbytitle('" + listName + "')/renderlistdata(@viewXml)?@viewXml='" + caml + "'",
            headers: {
                'Accept': 'application/json;odata=verbose',
                'Content-Type': 'application/json;odata=verbose',
                'X-RequestDigest': digest.d.GetContextWebInformation.FormDigestValue,
                'X-HTTP-Method': 'POST'
            },
            success: function (data) {
               var results= JSON.parse(data.d.RenderListData);

               document.getElementById('testOutput').innerHTML = JSON.stringify(results, null, "\t");
            },
            error: function (data){
                console.log(data);
                console.log('error');
            }
        });
    });
}

function getFormDigest(){
    return $.ajax({
        url: siteUrl + '/_api/contextinfo',
        method: 'POST',
        headers: { 'Accept': 'application/json; odata=verbose' }
    });    
}