dataverse-queries

dataverse-queries

This skill provides guidance on querying data from Microsoft Dataverse. Use when users ask about "Dataverse query", "OData filter", "Dataverse SQL", "FetchXML", "query Dataverse records", "Dataverse filter syntax", "search Dataverse", or need help constructing queries.

Query Methods

The Dataverse SDK supports two query methods:

1. OData queries - Standard Web API query syntax
2. SQL queries - T-SQL-like syntax (read-only)

OData Query Basics

# Basic query - returns all records (paged)
pages = client.get("account")

# With parameters
pages = client.get(
    "account",
    select=["name", "telephone1"],      # Columns to return
    filter="statecode eq 0",            # Filter expression
    orderby=["name asc"],               # Sort order
    top=100,                            # Max records
    expand=["primarycontactid"]         # Related records
)

# Process results
for page in pages:
    for record in page:
        print(record["name"])

OData Filter Syntax

Comparison Operators

Operator	Description	Example
eq	Equal	statecode eq 0
ne	Not equal	name ne null
gt	Greater than	revenue gt 1000000
ge	Greater or equal	createdon ge 2024-01-01
lt	Less than	quantity lt 10
le	Less or equal	amount le 500

Logical Operators

# AND
filter="statecode eq 0 and revenue gt 1000000"

# OR
filter="name eq 'Contoso' or name eq 'Fabrikam'"

# NOT
filter="not contains(name, 'test')"

# Combined
filter="(statecode eq 0) and (revenue gt 1000000 or numberofemployees gt 100)"

String Functions

# Contains
filter="contains(name, 'Contoso')"

# Starts with
filter="startswith(name, 'A')"

# Ends with
filter="endswith(emailaddress1, '@contoso.com')"

Date Filters

# Specific date
filter="createdon ge 2024-01-01"

# Date range
filter="createdon ge 2024-01-01 and createdon lt 2024-02-01"

# Today (use ISO format)
from datetime import datetime
today = datetime.now().strftime("%Y-%m-%d")
filter=f"createdon ge {today}"