0

TLDR of the question:
Is it possible to use Graph to query a SharePoint list, which contains lookups that would need to be fetched from a different SharePoint list?
The "old" SharePoint API can do that in one request.

Follow up question as a result of my attempts to work around that limitation:
Why does Graph not allow me to ask for multiple list entries by ID?
This makes literally no sense to me.

Background for the question:

I've been given the task to move a small SharePoint app from the normal SharePoint API over to the Graph API, so the features could be expanded into incorporating Exchange too. I've never worked with either prior to this, so I didn't really have any idea what I was getting into.

And while I did succeed in finding equivalent queries to Graph for everything that was needed so far, do I also start to doubt that Graph is seriously intended to be used for SharePoint access.

Lists are the best example. The SharePoint API offers resolving LoopupId values when requesting multiple items.

Graph doesn't even offer that when requesting an item directly, let alone multiple. To make things worse, after I wrote my own lookup routine that picks the columns that are lookups, and having to manually tell it where to find the values for that, I discovered that Graph won't even let me request multiple items by ID...

At first I tried to chain id eq '<id>' requests, because even $batch requests are limited to 20 individual requests, limiting the amount of items I could lookup to that at most. But filtering 'id' is apparently unintended. https://graph.microsoft.com/v1.0/sites/{site}/lists/{list}/items?$filter=id+eq+'67' results in "General exception while processing", which I've never even seen as a response until this.

I then tried the in keyword: https://graph.microsoft.com/v1.0/sites/{site}/lists/{list}/items?$filter=id+in+('67') which results in "Invalid request".

After that I thought I could be smart an add a calculated column which copies the item's id and index on that, but guess what: can't set an index on that column in the first AND it also refuses filtering on that on top. Not even offering the header fix for indexing on unindexed columns, nope. Outright complains that the field isn't usable.

With all this, I feel like I will have to settle for a hybrid approach, unless I'm seriously missing something here. I thought that having to write my own LookupId resolver was bad, but being unable to even optimize the requests to return all matching items from a list in one request at least, and instead having to request every single item EACH, because filtering by id is forbidden and the ONLY access by id is singular, just gives me the feeling that Graph was never meant to be used for SharePoint lists at all.

BloodyRain2k
  • 177
  • 1
  • 3
  • 11

2 Answers2

1

If there is any sufficient reason why you have to filter by id instead of using GET .../items/{id} as many times as needed, you may try to filter by fields/ID, but don't use $ before filter parameter for that case - it won't work, so try

GET https://graph.microsoft.com/v1.0/sites/{site}/lists/{list}/items?filter=fields/ID eq 67

with setting header Prefer=HonorNonIndexedQueriesWarningMayFailRandomly

Also, getting multiple items from large lists may cause issues, so for that case I'd add &top=5000

kadis
  • 181
  • 6
  • Interestingly enough, this works without needing that header. Maybe I set an index for that column or something. Too bad that `in ( )` isn't supported this way, but being able to chain a handful of `eq N` is already an improvement. But I am curious about that header now, because you make it sound like there won't be errors below 5000 results / entries, where as M$ made it sound like it would literally fail randomly. Now I wonder which is it really? – BloodyRain2k Mar 01 '23 at 11:30
  • Glad it helped. But what do you mean by "it would literally fail randomly"? header Prefer and top=5000 are separate topics and issues, if I got your correctly – kadis Mar 02 '23 at 10:26
  • I meant Microsoft's description of using that header on columns that aren't indexed, therefore requiring the header in the first place. They didn't specify a number and instead just wrote "large lists", which can mean "whatever", depending on your definition of a large list. So to me it sounded like it might already fail at 500 entries. – BloodyRain2k Mar 03 '23 at 09:22
  • 1
    @BloodyRain2k yes, it seems that it may fail randomly, though in my project it was fine for list with 5500 items. as for _top_ parameter, that is a workaround for the following issue which is claimed to appear even for indexed lists (probably if list is not optimized) https://learn.microsoft.com/en-us/answers/questions/576522/getting-the-attempted-operation-is-prohibited-beca – kadis Mar 03 '23 at 14:37
0

Is there an actual question here?

Microsoft has been recommending Graph for working with SharePoint Online for a while now. Though it is true there are shortcomings at present, Microsoft is constantly investing in improving Graph whereas older methods like CSOM are deprecated and are no longer updated.

Cornelius J. van Dyk
  • 681
  • 4
  • 12
  • Interesting, couldn't edit my comment from a few minutes ago. Anyways, I've tried to improve the question as it was indeed not very clear. Let me know if it's better now. – BloodyRain2k Feb 15 '23 at 16:23