Dv Query

Skill: Query — Read and Analyze Dataverse Records

This skill uses Python exclusively. Do not use Node.js, JavaScript, or any other language for Dataverse scripting. See the overview skill's Hard Rules.

SDK-First Rule for Reads

All reads use the SDK — not `urllib`, `requests`, or raw HTTP. This is the same rule as dv-data's SDK-First Rule, applied to reads. If you find yourself writing urllib.request or get_token() for a query, STOP — the SDK handles it. The only exceptions are $apply aggregation and N:N $expand, documented below.

How to Answer Data Questions

When the user asks a question about their data, pick the approach by what they're asking, not by which API you know:

Key principle: Let the server do the work. For single-table aggregation, use $apply — it runs server-side and returns only grouped results. For cross-table questions, use client.dataframe.get() with minimal $select on each table, then pd.merge() — the merge itself is sub-second; the bottleneck is network transfer, which $select minimizes.

Always query the live Dataverse environment. Do not query local copies, cached files, or source databases when the user expects results from Dataverse. The data in Dataverse is the source of truth.

---

SQL Queries — client.query.sql()

client.query.sql() uses the Dataverse Web API ?sql= parameter — a limited SQL subset (same limitations as MCP read_query). It does NOT support GROUP BY, JOINs, HAVING, DISTINCT, or subqueries. Results are capped at ~5,000 rows.

When to use: Fast filtered reads on tables with <5K rows. For these, it's significantly faster (~2-6s) than page iteration or DataFrames because it's a single HTTP call.

# Fast filtered read on small tables (<5K rows)
results = client.query.sql(
    "SELECT TOP 100 name, estimatedvalue "
    "FROM opportunity "
    "WHERE statecode = 0 "
    "ORDER BY estimatedvalue DESC"
)
for r in results:
    print(f"{r['name']}: ${r.get('estimatedvalue', 0):,.0f}")

Do NOT use for: Tables >5K rows (results silently truncated), aggregation (no GROUP BY), or cross-table queries (no JOINs). Use $apply for single-table aggregation and client.dataframe.get() + pd.merge() for cross-table.

Skill boundaries

---

Setup

import os, sys
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_client

# get_client sets a plugin attribution context on the User-Agent header.
# Do not modify the context value — it is a closed schema for server-side
# telemetry (app/skill/agent). Never include secrets or PII.
client = get_client("dv-query")

get_client(skill) handles auth, environment URL, and plugin attribution (User-Agent tagging). See scripts/auth.py. For scripts that run to completion, wrap the returned client in a with statement for automatic connection cleanup.

---

Field Name Casing Rule

Getting this wrong causes 400 errors.

• System table navigation properties (e.g., parentaccountid, ownerid): lowercase
• Custom lookup navigation properties: case-sensitive, match $metadata SchemaName (e.g., new_AccountId)

---

Query Records (multi-page)

client.records.get() is the primary read method — works on all SDK versions (b6+). It returns a page iterator for multi-record queries and a single Record for by-GUID fetch. Always use `select=` to limit columns.

for page in client.records.get(
    "new_ticket",
    select=["new_name", "new_priority", "new_status"],
    filter="new_status eq 100000000",
    orderby=["new_name asc"],
    top=50,
):
    for r in page:
        print(r["new_name"], r["new_priority"])

client.records.get() returns a page iterator — always iterate pages and then records within each page. Each record is a Record object that supports dict-like access: r["column"], r.get("column"), r.keys(). Do not use r.data.get() — use r.get() directly.

---

Fetch a Single Record by ID

record = client.records.get("new_ticket", "<record-guid>",
    select=["new_name", "new_priority", "new_status"])
print(record["new_name"])

---

$select with Lookup Columns (GUID-free display)

To show display names instead of GUIDs, request the formatted value annotation via include_annotations:

for page in client.records.get("opportunity",
    select=["name", "estimatedvalue", "_parentaccountid_value"],
    include_annotations="OData.Community.Display.V1.FormattedValue",
):
    for r in page:
        account_name = r.get("_parentaccountid_value@OData.Community.Display.V1.FormattedValue")
        print(f"{r['name']} — {account_name}")

You MUST pass `include_annotations` — without it, the Prefer: odata.include-annotations header is not sent and formatted values are not in the response. Use "*" for all annotations or the specific annotation name above.

Formatted values are available for lookup, choice, status, and owner fields.

---

$expand — Resolve Lookup to Full Related Record

for page in client.records.get("opportunity",
    select=["name", "estimatedvalue"],
    expand=["parentaccountid($select=name)"],   # nested $select avoids fetching all account columns
):
    for r in page:
        account = r.get("parentaccountid") or {}
        print(f"{r['name']} — {account.get('name', 'Unknown')}")

Always use nested $select inside $expand — without it, Dataverse returns every column on the related entity, which wastes bandwidth and memory.

$expand with multiple custom lookups

for page in client.records.get(
    "new_ticket",
    select=["new_name", "new_priority", "new_status"],
    expand=["new_CustomerId($select=new_name)", "new_AgentId($select=new_name)"],  # nested $select + case-sensitive nav props
):
    for r in page:
        customer = r.get("new_CustomerId") or {}
        agent    = r.get("new_AgentId") or {}
        print(f"{r['new_name']} | {customer.get('new_name','')} | {agent.get('new_name','')}")

expand uses the Navigation Property Name (new_CustomerId), not the lowercase logical name (new_customerid). Using lowercase causes a 400 error.

---

Advanced query patterns (Web API only)

For aggregations and many-to-many expansion, the SDK doesn't have direct support — use raw Web API. See `references/web-api-advanced.md` for full code samples.

Quick reference:

• `$expand` on N:N relationships: GET /<entitySet>?$expand=<n:n_nav>($select=...) — single page only; follow @odata.nextLink for >5,000 results.
• `$apply` for aggregations: runs server-side, returns grouped results in one call. Patterns: groupby((col),aggregate(metric with sum as total)), aggregate($count as count), aggregate(amount with average as avg). 50K source-record limit.
• Cross-table aggregation: $apply only works within one entity set. Use client.dataframe.get(entity, select=[...]) per table → pd.merge() → groupby(). Always pass select=; without it transfers 10-20× more data.

QueryBuilder — Fluent Query API (SDK b8+)

Available in PowerPlatform-Dataverse-Client b8+. Chainable builder for complex queries that would be awkward as a single OData URL or FetchXML string. Full reference and examples in `references/querybuilder.md`.

Jupyter Notebook Setup

For interactive querying in notebooks (auth + DataverseClient + DataFrame display), see `references/jupyter-setup.md`.

Common Query Errors

For HttpError handling in SDK scripts, see the error handling pattern in dv-data.

---

Windows Scripting Notes

• ASCII only in .py files — curly quotes and em dashes cause SyntaxError on Windows.
• No `python -c` for multiline code — write a .py file instead.
• Generate GUIDs in scripts: str(uuid.uuid4()), not shell backtick substitution.

Jupyter Notebook Setup

Auth note: Notebooks do not have a scripts/ directory, so scripts/auth.py is not available. Use InteractiveBrowserCredential directly — this is the intended exception to the scripts/auth.py rule. For scripts (.py files), always use scripts/auth.py.

# Cell 1: Setup
import os
from azure.identity import InteractiveBrowserCredential
from PowerPlatform.Dataverse.client import DataverseClient

credential = InteractiveBrowserCredential()
client = DataverseClient(
    base_url="https://<org>.crm.dynamics.com",  # replace with your org URL
    credential=credential,
)

# Cell 2: Load data into pandas (direct DataFrame, no manual iteration)
df = client.dataframe.get("account",
    select=["name", "industrycode", "revenue", "numberofemployees"],
)
df.head()

Prerequisites:

pip install --upgrade PowerPlatform-Dataverse-Client pandas matplotlib seaborn azure-identity

pandas>=2.0.0 is a required dependency of the SDK (since b7) and is installed automatically with --upgrade.

QueryBuilder — Fluent Query API (SDK b8+)

Version check: QueryBuilder requires SDK version b8 or later (pip show PowerPlatform-Dataverse-Client → Version ≥ 0.1.0b8). If you're on b7 or earlier, client.query.builder() does not exist — use client.records.get() instead (documented above). Do NOT introspect the SDK with dir() or inspect to discover APIs — if a method isn't documented here, it doesn't exist in the installed version.

QueryBuilder offers composable filters, OR/AND logic, and .to_dataframe() in one chain. It calls client.records.get() internally — it is a convenience layer, not a replacement.

# Basic — flat record iteration
for record in client.query.builder("opportunity") \
        .select("name", "estimatedvalue", "statuscode") \
        .filter_eq("statuscode", 1) \
        .order_by("estimatedvalue", descending=True) \
        .top(100) \
        .execute():
    print(record["name"], record["estimatedvalue"])

Direct DataFrame result — combines query + pandas handoff in one call:

df = client.query.builder("opportunity") \
    .select("name", "estimatedvalue", "statuscode") \
    .filter_eq("statuscode", 1) \
    .to_dataframe()

Composable filter expressions — for OR/AND logic:

from PowerPlatform.Dataverse.models.filters import eq, gt

active_or_pending = (eq("statecode", 0) | eq("statecode", 1)) & gt("estimatedvalue", 10000)

df = client.query.builder("opportunity") \
    .select("name", "estimatedvalue") \
    .where(active_or_pending) \
    .to_dataframe()

Paged execution — when you need per-page control:

for page in client.query.builder("opportunity").select("name").execute(by_page=True):
    for record in page:
        print(record["name"])

---

Pandas DataFrame Handoff

Prefer `client.dataframe.get()` for any read that involves analysis, verification, comparison, or export. Use client.records.get() with page iteration only when you need per-page processing (e.g., streaming to a file) or when the table is too large to fit in memory.

Always pass `select=` when calling `client.dataframe.get()` or `client.records.get()`. Omitting select returns every column — on a 100K-row table with 20 columns, this transfers 10-20x more data than needed and turns a 15-second query into a 90-second query. Only request the columns you need.

Use client.dataframe.get() to pull Dataverse records directly into a pandas DataFrame — no manual page iteration needed:

import pandas as pd

# Returns a fully consolidated DataFrame (all pages)
df = client.dataframe.get("opportunity",
    select=["name", "estimatedvalue", "statuscode", "_parentaccountid_value"],
)
print(df.groupby("statuscode")["estimatedvalue"].agg(["count", "sum", "mean"]))

DataFrame write-back — update or create records from a DataFrame. These are write operations — agents consulting dv-data for writes should also check here for the DataFrame variant. Note: DataFrame write-back supports create and update only — not upsert. For idempotent imports with alternate keys, use client.records.upsert() with UpsertItem (see dv-data).

# Update records — DataFrame must include the primary key column
client.dataframe.update("opportunity", df_updates, id_column="opportunityid")

# Create records — returns a Series of new GUIDs
guids = client.dataframe.create("opportunity", df_new_records)

Fallback (manual page iteration) — use only when you need per-page processing. Prefer client.dataframe.get() above for the common case:

all_records = []
for page in client.records.get("opportunity",
    select=["name", "estimatedvalue", "statuscode"],
):
    all_records.extend([dict(r) for r in page])  # convert Record objects to dicts
df = pd.DataFrame(all_records)

Web API advanced — $expand on N:N and $apply aggregation

Note: These raw Web API examples fetch a single page only. If results exceed one page (~5000 records), you must follow @odata.nextLink in a loop to get all records. For most N:N and aggregation queries, a single page is sufficient.

import os, sys, json, urllib.request
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_token, get_plugin_headers, load_env  # get_token + get_plugin_headers — SDK cannot do this

load_env()
env = os.environ["DATAVERSE_URL"].rstrip("/")
token = get_token()

# Tickets with their linked KB articles (N:N)
url = (f"{env}/api/data/v9.2/new_tickets"
       f"?$select=new_name"
       f"&$expand=new_ticket_kbarticle($select=new_title)")
headers = get_plugin_headers("dv-query", token)
headers.update({"OData-MaxVersion": "4.0", "OData-Version": "4.0", "Accept": "application/json"})
req = urllib.request.Request(url, headers=headers)
with urllib.request.urlopen(req, timeout=150) as resp:
    data = json.loads(resp.read())
    for ticket in data["value"]:
        articles = [a["new_title"] for a in ticket.get("new_ticket_kbarticle", [])]
        print(f"{ticket['new_name']}: {', '.join(articles)}")

---

$apply Aggregation (Web API — SDK does not support)

Use `$apply` for any single-table aggregation — "which X has the most Y", "total by group", "top N", "average per category". This runs server-side and returns only the grouped results. One HTTP call, no client-side processing. Limit: 50,000 source records per aggregation.

Common $apply patterns:

import os, sys, json, urllib.request
sys.path.insert(0, os.path.join(os.getcwd(), "scripts"))
from auth import get_token, get_plugin_headers, load_env  # get_token + get_plugin_headers — SDK does not support $apply

load_env()
env = os.environ["DATAVERSE_URL"].rstrip("/")
token = get_token()
_base_headers = get_plugin_headers("dv-query", token)
_base_headers.update({"OData-MaxVersion": "4.0", "OData-Version": "4.0", "Accept": "application/json"})

def apply_query(entity_set, apply_expr):
    """Run a $apply aggregation query. Returns list of result dicts."""
    url = f"{env}/api/data/v9.2/{entity_set}?$apply={apply_expr}"
    req = urllib.request.Request(url, headers=_base_headers.copy())
    with urllib.request.urlopen(req, timeout=150) as resp:
        return json.loads(resp.read()).get("value", [])

# Example 1: Count and sum by status
results = apply_query("opportunities",
    "groupby((statuscode),aggregate($count as count,estimatedvalue with sum as total_value))")
for row in results:
    print(f"Status {row['statuscode']}: {row['count']} records, ${row['total_value']:,.0f}")

# Example 2: Top accounts by total deal value
results = apply_query("opportunities",
    "groupby((_parentaccountid_value),aggregate(estimatedvalue with sum as total))")
top = sorted(results, key=lambda r: r.get("total", 0), reverse=True)[:10]
for r in top:
    print(f"Account {r['_parentaccountid_value']}: ${r['total']:,.0f}")

When `$apply` won't work (cross-table questions, complex transforms):

$apply only works within a single entity set. For cross-table aggregation, use client.dataframe.get() with minimal $select on each table, then pd.merge(). The merge itself is sub-second; the bottleneck is network transfer, which $select minimizes:

import pandas as pd

# Only the columns needed — always pass select= on large tables
df_a = client.dataframe.get("prefix_tablea",
    select=["prefix_keycolumn", "prefix_metric"])
df_b = client.dataframe.get("prefix_tableb",
    select=["prefix_keycolumn", "prefix_dimension"])

merged = pd.merge(df_a, df_b, on="prefix_keycolumn")
top = merged.groupby("prefix_dimension")["prefix_metric"].sum().nlargest(10)
print(top)

Performance rules for client-side processing:

• Always use $select — fetching all columns on 100K rows transfers 10-20x more data than needed
• Use client.dataframe.get(), not raw HTTP page iteration
• pandas merge + groupby on 100K-300K rows takes seconds — the bottleneck is network transfer, not Python processing