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<TeamCommute>()   .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<TeamCommute>().ToList();

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

3 thoughts on “Defining a Defining Query in EF Core 2.1

  1. Hi Julie,

    In my dbcontext I have
    builder.Query().ToView(“vwAllOrgchartWithUnits”);

    and lower down
    public DbQuery OrgChartNodes { get; set; }

    OrgChartNode’s properties match the fields in vwAllOrgCHartUnits.

    Then in OnGetAsync() in my razor page I have

    string orgCode = User.Identity.GetOrgCode();

    OrgChartNodes = await _db.OrgChartNodes
    .Where(o => o.OrgCode.Equals(orgCode))
    .AsNoTracking()
    .ToListAsync();

    My context version of OrgChartNodes has the correct number of rows, but the properties of each object are null. This causes the local version to fail with a null reference exception. Can you point me in the right direction? Using 2.1 rc1

Leave a Reply

Your email address will not be published. Required fields are marked *