Defining a Defining Query in EF Core 2.1

I have to cut out some text from a too-long article I’ve written for a magazine (links when it’s published), so here is a simple example of using the new ToQuery method for creating a defining query in EF Core 2.1 (currently in Preview 2).

ToQuery is associated with the new Query Type feature that allows you to use types that are not mapped to a table in the database and are therefore not true entities, don’t require a key and are not change tracked.

I’m starting with a simple model that includes these two entities, which are mapped to tables in my DbContext.

public class Team
{
public int TeamId { get; set; }
public string Name { get; set; }
public string TwitterAlias { get; set; }
public List Members { get; set; }
}

public class TeamMember
{
public int TeamMemberId { get; set; }
public string Name { get; set; }
public string Role { get; set; }
public int TeamId { get; set; }
public TimeSpan TypicalCommuteTime { get; private set; }
public void CalculateCommuteTime (DateTime start, DateTime end)
{
TypicalCommuteTime = end.Subtract(start);
}
}

You’ll need to pre-define the type being used for the defining query, mine will have the Name and TypicalCommuteTime for the team member.

public class TeamCommute
{
public TeamCommute(string name, TimeSpan commuteTime)
{
Name = name;
TypicalCommuteTime = commuteTime;
}
public string Name { get; set; }
public TimeSpan TypicalCommuteTime { get; set; }
}

You can define queries directly in OnModelBuilding using either raw sql with FromSql or a LINQ query inside a new method called ToQuery. Here’s an example of using a Query type with a defining query and Linq:

modelBuilder.Query()   .ToQuery(() =>
   TeamMembers.Select(m => new TeamCommute( m.Name, m.TypicalCommuteTime ) )

With this query defined on the TeamCommute class, you can now use that in queries in your code for example:

var commutes = context.Query().ToList();

Keep I mind that you can’t define both a ToQuery and a ToView mapping on the same type.