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.

  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

4 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

  2. I’m having the same exact problem as Luke was having (or maybe still does). I’m wondering if he was able to get through it. I’ve been pulling my hair out for most of the day trying to figure this out. Any help would be appreciated.

Leave a Reply to Danny Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.