EF Core Reporting Views: Design the Row Before Writing the Query

Reporting queries often start with a simple request: show one row per user and activity, with many additional "context" fields related to the user and/or action involved.

The difficulty is that most of those values do not come from one-to-one relationships. Each activity may have additional attempts, detailed results, or progress/content information, and the user may have an overall status value you need to get elsewhere. Joining them directly doesn't create a richer row. It creates combinations.

By the time Entity Framework Core receives that result, developers may be fighting duplicate rows, adding Distinct(), or asking EF Core to project nested collections that the provider cannot translate.

The better starting point is not the LINQ expression. It is the contract for one row.

Define the Grain First

Every reporting view needs a clearly stated grain. For example:

One row represents one purchased course, one user, and one course activity.

That statement determines which columns identify a row and how every related collection must be handled. If UserPurchasedCourseId and CourseUnitActivityId define the row, then every other source must contribute no more than one value to that combination.

A useful review question is: Can this join return more than one record for the row I am building? If the answer is yes, the relationship must be reduced before it joins the main result.

Why Direct Joins Multiply Rows

Imagine one activity with three content items, two quiz attempts, and four completion action logs. Joining those tables directly can produce as many as 24 intermediate rows for one activity.

The database is not returning duplicates in the strict sense. Each combination is different. The result is wrong only because the query intended to return one activity row.

Adding DISTINCT at the end can hide some symptoms, but it does not repair the contract. If the rows contain different scores, dates, or content types, they are not duplicates and cannot be collapsed safely.

This is why flattening collections is an architectural decision rather than a formatting step.

Reduce Each Collection Before Joining

Each one-to-many relationship usually needs one explicit rule:

• Latest record: select the most recent quiz result or action log.
• Aggregate: count incomplete prerequisites or completion attempts.
• Existence: return whether a homework assignment exists.
• Combined scalar: return a comma-separated list of distinct content types.
• Separate detail query: leave the collection out of the view when callers genuinely need every item.

For a latest-record rule, a window function makes the choice visible:

WITH RankedQuizResults AS
(
    SELECT
        Result.UserCourseUnitActivityCompletionId,
        Result.Score,
        Result.DateCompletedByStudentUtc,
        ROW_NUMBER() OVER
        (
            PARTITION BY Result.UserCourseUnitActivityCompletionId
            ORDER BY Result.DateCompletedByStudentUtc DESC,
                     Result.UserCourseActivityQuizResultId DESC
        ) AS RowNumber
    FROM dbo.UserCourseActivityQuizResults AS Result
)
SELECT
    UserCourseUnitActivityCompletionId,
    Score,
    DateCompletedByStudentUtc
FROM RankedQuizResults
WHERE RowNumber = 1;

The second ordering column is important. If two records share the same timestamp, the query still needs a deterministic winner.

Use Existence Checks for Flags

A Boolean such as IsHomework does not require the homework row to become part of the main result. Joining the table can multiply records when more than one match is possible. An EXISTS expression communicates the real requirement:

CAST
(
    CASE WHEN EXISTS
    (
        SELECT 1
        FROM dbo.UserCourseHomeworkItems AS Homework
        WHERE Homework.UserPurchasedCourseId = UPC.UserPurchasedCourseId
          AND Homework.CourseUnitActivityId = CUA.CourseUnitActivityId
    )
    THEN 1 ELSE 0 END
    AS bit
) AS IsHomework

The explicit bit cast also gives EF Core a predictable SQL type to map to bool.

Do Not Ask One Projection to Be Both a Row and a Collection

EF Core can translate many joins, filters, aggregates, and groupings, but not every LINQ shape has a relational equivalent. A particularly fragile pattern is projecting a keyless reporting row together with a nested collection, then applying Distinct() or GroupBy().

A relational result is still a sequence of rows. To construct a nested collection reliably, EF Core needs enough identity information to correlate child rows with the correct parent. Keyless view models deliberately have no EF key, and a projection that removes identifying columns can make that correlation impossible.

There are three practical options:

1. Return the collection as a scalar produced by SQL, such as a distinct aggregated list.
2. Return a flat result with all identifiers, materialize it, and group it in memory after the database query is complete.
3. Query the primary reporting rows first and load detail collections separately.

The right choice depends on data volume and how the result will be used. What matters is choosing the boundary intentionally.

Map the View as a Read-Only Contract

A reporting view can be mapped as a keyless entity type:

modelBuilder.Entity<UserCourseHierarchyRow>(entity =>
{
    entity.HasNoKey();
    entity.ToView("UserCourseHierarchy", "dbo");
});

This correctly tells EF Core that the type is a read-only query source. It does not mean the underlying result can ignore identity. Include the columns that describe the row's logical identity even when EF Core does not track the type.

Those identifiers help consumers join results, detect unexpected duplication, and safely perform a second query when detail collections are needed.

Inspect the SQL and Test the Contract

A reporting query should be tested for more than whether it returns data. Create cases with multiple children in every participating collection. Verify that the expected row count remains stable, that latest-record rules are deterministic, and that missing optional data does not remove the primary row.

When composing LINQ over the view, inspect the generated SQL with ToQueryString(). Confirm that filters remain server-side and that an innocent projection has not created an expensive join or forced unnecessary data into memory.

Also test the view directly. If the SQL view already violates its declared grain, no EF Core mapping can restore the lost meaning.

The Practical Rule

A reporting view should not expose the accidental shape produced by a series of joins. It should expose a deliberate contract.

Define one row first. Reduce every collection to one value, one aggregate, or a separate detail query before it reaches that row. Preserve the logical identifiers even when the EF type is keyless. Then let EF Core filter and project from a result whose meaning is already stable.

Once the row has a clear grain, duplicate data and translation failures become much easier to diagnose. Without that grain, Distinct() is often only hiding the real problem.

Have you encountered a complex situation that required custom views?  What led you to that path and how did you approach the conversion?