Article 4 — Implementing SharePoint “Upsert” with PnP/CSOM (.NET) (WPF Study & Rating App)

This final article in the series shows how to implement the same Upsert behavior (create/update an item by VideoId) using PnP/CSOM in .NET—an approach widely used in enterprise SharePoint automation tools.

As always for a public post:

No real tenant URLs
No real client IDs/secrets
No real list names
Placeholder-friendly and safe to publish

Why PnP/CSOM?

PnP/CSOM is a strong choice when:

you want to work with a familiar SharePoint object model (lists, items, fields)
you prefer higher-level abstractions over raw HTTP calls
you already have SharePoint tooling in .NET (migration utilities, provisioning tools, etc.)
you want to avoid hand-crafting REST/Graph payloads

If your audience is “SharePoint developers and tool builders,” this approach is highly relatable.

What We’re Building (Same Outcome)

Given a VideoRecord (VideoId, Title, Url, Rating, Status, Notes, etc.):

Query SharePoint list for an item where VideoId == record.VideoId
If found → update fields
If not found → create item

This is the identical pattern you used in Graph and REST—just implemented with CSOM/PnP.

Core Concepts in CSOM

1) ClientContext

CSOM works through a ClientContext bound to your site URL.

2) CAML Query

The most typical way to query list items is with CAML, e.g.:

“Where VideoId equals <value>”

3) Updates

To update an item:

set field values on ListItem
call Update()
execute the query

Authentication (Public-Safe Options)

For SharePoint Online + desktop apps, you’ll typically see:

MSAL (interactive) → obtain tokens
then create a CSOM ClientContext that uses an access token

Public article note: code below focuses on the pattern. Your real project will plug in your actual token acquisition flow.

Recommended Project Structure

Add a SharePoint layer:

SharePointPnPContextFactory (creates authenticated ClientContext)
PnPVideoRepository (upsert by VideoId)
VideoRecord DTO (same as other articles)

Keep MVVM clean: the ViewModel calls repository methods.

DTO (same as previous articles)

			
public sealed class VideoRecord
{
    public string VideoId { get; set; }
    public string Title { get; set; }
    public string VideoUrl { get; set; }
    public string Channel { get; set; }
    public string ThumbnailUrl { get; set; }
    public string SearchQuery { get; set; }
    public int Rating { get; set; }
    public string Status { get; set; }
    public string Notes { get; set; }
    public VideoRecord()
    {
        VideoId = "";
        Title = "";
        VideoUrl = "";
        Channel = "";
        ThumbnailUrl = "";
        SearchQuery = "";
        Rating = 0;
        Status = "ToWatch";
        Notes = "";
    }
}

		

Step 1 — Create a Token-Based ClientContext (Pattern)

In modern SharePoint Online, you can attach a bearer token to CSOM requests using the ExecutingWebRequest event.

			
using Microsoft.SharePoint.Client;
using System;
public sealed class SharePointContextFactory
{
    private readonly Func<string> _getAccessToken;
    public SharePointContextFactory(Func<string> getAccessToken)
    {
        _getAccessToken = getAccessToken;
    }
    public ClientContext Create(string siteUrl)
    {
        var ctx = new ClientContext(siteUrl);
        ctx.ExecutingWebRequest += (sender, args) =>
        {
            args.WebRequestExecutor.RequestHeaders["Authorization"] = "Bearer " + _getAccessToken();
        };
        return ctx;
    }
}

		

In real implementations you’ll likely use Func<Task<string>> and handle token refresh. For a public article, this pattern is enough to explain.

Step 2 — Load the Target List

			
using Microsoft.SharePoint.Client;
public static List GetListByTitle(ClientContext ctx, string listTitle)
{
    var list = ctx.Web.Lists.GetByTitle(listTitle);
    ctx.Load(list);
    ctx.ExecuteQuery();
    return list;
}

		

Step 3 — Query Item by VideoId with CAML

Here’s a CAML query that searches by a text field VideoId.

			
using Microsoft.SharePoint.Client;
public static ListItem FindByVideoId(ClientContext ctx, List list, string videoId)
{
    var caml = new CamlQuery();
    caml.ViewXml =
        "<View>" +
            "<Query>" +
                "<Where>" +
                    "<Eq>" +
                        "<FieldRef Name='VideoId' />" +
                        "<Value Type='Text'>" + SecurityElement.Escape(videoId) + "</Value>" +
                    "</Eq>" +
                "</Where>" +
            "</Query>" +
            "<RowLimit>1</RowLimit>" +
        "</View>";
    var items = list.GetItems(caml);
    ctx.Load(items);
    ctx.ExecuteQuery();
    if (items.Count > 0) return items[0];
    return null;
}

		

Key detail: use SecurityElement.Escape(videoId) to avoid XML issues in CAML.

Step 4 — Create Item (if not found)

			
using Microsoft.SharePoint.Client;
public static ListItem CreateVideoItem(List list)
{
    var createInfo = new ListItemCreationInformation();
    var item = list.AddItem(createInfo);
    return item;
}

		

Step 5 — Update Fields (Common Mapping)

			
using Microsoft.SharePoint.Client;
public static void MapFields(ListItem item, VideoRecord record)
{
    // Title is built-in
    item["Title"] = record.Title;
    // Your custom columns (internal names assumed)
    item["VideoId"] = record.VideoId;
    item["VideoUrl"] = record.VideoUrl;          // If Hyperlink field: you may need FieldUrlValue
    item["Channel"] = record.Channel;
    item["ThumbnailUrl"] = record.ThumbnailUrl;
    item["SearchQuery"] = record.SearchQuery;
    item["Rating"] = record.Rating;
    item["Status"] = record.Status;
    item["Notes"] = record.Notes;
}

		

Note about Hyperlink fields

In SharePoint, a “Hyperlink or Picture” field may require:

			
item["VideoUrl"] = new FieldUrlValue { Url = record.VideoUrl, Description = "Open" };

Whether you need FieldUrlValue depends on how you created that column. For public writing, mention this as a nuance.

Step 6 — Upsert Method (Create or Update)

			
using Microsoft.SharePoint.Client;
using System.Security;
public sealed class PnPVideoRepository
{
    private readonly SharePointContextFactory _ctxFactory;
    private readonly string _siteUrl;
    private readonly string _listTitle;
    public PnPVideoRepository(SharePointContextFactory ctxFactory, string siteUrl, string listTitle)
    {
        _ctxFactory = ctxFactory;
        _siteUrl = siteUrl;
        _listTitle = listTitle;
    }
    public void Upsert(VideoRecord record)
    {
        using (var ctx = _ctxFactory.Create(_siteUrl))
        {
            var list = ctx.Web.Lists.GetByTitle(_listTitle);
            ctx.Load(list);
            ctx.ExecuteQuery();
            // Find existing
            var caml = new CamlQuery();
            caml.ViewXml =
                "<View>" +
                    "<Query>" +
                        "<Where>" +
                            "<Eq>" +
                                "<FieldRef Name='VideoId' />" +
                                "<Value Type='Text'>" + SecurityElement.Escape(record.VideoId) + "</Value>" +
                            "</Eq>" +
                        "</Where>" +
                    "</Query>" +
                    "<RowLimit>1</RowLimit>" +
                "</View>";
            var items = list.GetItems(caml);
            ctx.Load(items);
            ctx.ExecuteQuery();
            ListItem item;
            if (items.Count > 0)
            {
                item = items[0]; // update
            }
            else
            {
                item = list.AddItem(new ListItemCreationInformation()); // create
                item["VideoId"] = record.VideoId; // ensure key set early
            }
            MapFields(item, record);
            item.Update();
            ctx.ExecuteQuery();
        }
    }
    private static void MapFields(ListItem item, VideoRecord record)
    {
        item["Title"] = record.Title;
        item["VideoId"] = record.VideoId;
        item["Channel"] = record.Channel;
        item["ThumbnailUrl"] = record.ThumbnailUrl;
        item["SearchQuery"] = record.SearchQuery;
        item["Rating"] = record.Rating;
        item["Status"] = record.Status;
        item["Notes"] = record.Notes;
        // If your column is Hyperlink type, prefer FieldUrlValue:
        // item["VideoUrl"] = new FieldUrlValue { Url = record.VideoUrl, Description = "Open" };
        item["VideoUrl"] = record.VideoUrl;
    }
}

		

Integrating with WPF MVVM (Save Button Per Card)

Your ViewModel should convert a VideoCard to VideoRecord and call repository:

SaveVideoCommand
CommandParameter="{Binding}"

Pseudo-viewmodel logic:

			
private void SaveCard(VideoCard card)
{
    var record = new VideoRecord
    {
        VideoId = card.VideoId,
        Title = card.Title,
        VideoUrl = card.VideoUrl,
        Channel = card.ChannelTitle,
        ThumbnailUrl = card.ThumbnailUrl,
        SearchQuery = this.Query,
        Rating = card.Rating,
        Status = card.Status,
        Notes = card.Notes
    };
    _repo.Upsert(record);
    card.SavedToSharePoint = true;
}

		

In real apps, do this async and avoid blocking UI.

PnP/CSOM Pitfalls (Common Ones)

1) Field internal names mismatch

Your SharePoint column display name might be “Video Id” but internal name is Video_x0020_Id.

Fix: verify internal names in list settings or by inspecting fields via CSOM.

2) Hyperlink fields

As mentioned, hyperlink columns may require FieldUrlValue.

3) Choice values must match

Choice “Status” must match your actual choices.

4) Performance

Index VideoId. CAML queries on non-indexed columns can become slow at scale.

When You’d Prefer PnP/CSOM over REST/Graph

You’re already writing SharePoint tooling in .NET
You want fewer raw HTTP details
Your environment already standardizes on PnP patterns
You need additional object model features beyond list CRUD

Series Wrap-Up

You now have three complete “Upsert” approaches for the same app idea:

Microsoft Graph — modern, cross-M365, long-term recommended
SharePoint REST — native, transparent, SharePoint-centric
PnP/CSOM — productive .NET tooling model for SharePoint-heavy work