Queryable Functions

[ajcvickers]

Moved from discussion #21423

Question

From @pmiddleton

@ajcvickers I have been emailing with Jon Smith about queryable functions in regards to the update of his book. He was wondering why they don't make the What's New list in your weekly status updates. Is the feature going to make 5.0 or are you guys going to let it bake internally for a release?

PS - love the biweekly live streams and my 5 year old daughter want's to come over and play with your Star Wars toys!

---

Replies

From @AndriySvyryd

It's almost done, see #20051

---

From @ajcvickers

@pmiddleton What would be good for us to include in the "what's new" section? Could you write some initial text with maybe some examples? I can then massage it into an appropriate form for "what's new" and it can also form a starting point for the docs.

---

From @pmiddleton

Sure I can throw something together. When do you need it by?

---

From @ajcvickers

Sometime this week would be good so we can use it for preview 7.

---

From @pmiddleton

ok. I can get you something by Wednesday. Does that work for you?

---

From @pmiddleton

@ajcvickers How is something like this? It could use some wordsmithing as that is not my strong suit.

Queryable functions adds the ability to call table valued functions in Entity Framework Core as first class citizens.

They allow you to map a .Net method to a table valued function, as defined in a sql server database, just as you currently can do with scalar methods. You can then fully composable the TVF in your linq queries. This opens a new set of query types which were not possible to perform before in linq. For our example we will run a full recursive query which wasn't possible until now.

We will use the ubiquitous employee to manager recursive query for our example.

First we define a self-referencing employee entity.

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsDeveloper { get; set; }

    public int? ManagerId { get; set; }
    public virtual Employee Manager { get; set; }
}

We can then seed this table with the following data

modelBuilder.Entity<Employee>().HasData(
                    new { Id = 1, Name = "Sally", IsDeveloper = false },
                    new { Id = 2, Name = "Fred",  IsDeveloper = false, ManagerId = 1 },
                    new { Id = 3, Name = "James", IsDeveloper = false, ManagerId = 1 },
                    new { Id = 4, Name = "Emma",  IsDeveloper = true,  ManagerId = 2 },
                    new { Id = 5, Name = "John",  IsDeveloper = false, ManagerId = 2 },
                    new { Id = 6, Name = "Mia",   IsDeveloper = true,  ManagerId = 3 },
                    new { Id = 7, Name = "David", IsDeveloper = true,  ManagerId = 3 }
                );

We now have a 3 level hierarchy with Sally at the top with 2 direct reports Fred and James. They in turn each have 2 reports.

Lets say we need to query that returns all of the employees that work for each manager who are developers. This is now easy to do with a table valued function. We can do that with a recursive query in a TVF as such.

create FUNCTION GetReports(@employeeId int)
RETURNS @reports TABLE
(
	Name nvarchar(50) not null,
	IsDeveloper bit not null
)
AS
begin
	WITH cteEmployees AS 
	(
		SELECT id, name, managerId, isDeveloper
		FROM employees
		WHERE id = @employeeId
		UNION ALL
		SELECT e.id, e.name, e.managerId, e.isDeveloper
		FROM employees e
		INNER JOIN cteEmployees cteEmp ON cteEmp.id = e.ManagerId
	)

	insert into @reports 
	select name, isDeveloper
	FROM cteEmployees
	where id != @employeeId

	return
end

Now we just need to map that TVF so it is callable from Entity Framework.

We can begin by first defining a class to hold the return data from the TVF.

public class Report
{
	public string Name { get; set; }
	public bool IsDeveloper { get; set; }
}

Then we can define the queryable function's .Net method signature.

public IQueryable<Report> GetReports(int managerId)

Note that the property names and types must line up with the TFV RETURNS TABLE definition.

The difference between this and a scalar function is that you now return IQueryable. The T is a type that maps the return data from the TVF, which in our case is the Report class we just created.

Now we can build the body of our .Net method. The full code for the method looks like this.

public IQueryable<Report> GetReports(int managerId)
{
	return FromExpression(() => GetReports(managerId));
}

The body of the method is is another departure from how scalar functions work. Due to how Linq is implemented by the compiler you need to pass into Entity Framework a lambda expression which wraps the call to the queryable function.
This allows Entity Framework to bind the method call into the queries expression tree. You achieve this by using the new FromExpression method which is defined on DbContext. The lambda needs to call the .Net method passing the same parameters.

The last setup piece we need to perform is registering the queryable function in OnModelCreating just like we do with scalar functions. We also need to tell Entity Framework that the return type we defined Report does not have a primary key defined.

modelBuilder.HasDbFunction(typeof(EmployeeContext).GetMethod(nameof(GetReports)));
modelBuilder.Entity(typeof(Report)).HasNoKey();

We can now build our query. Note that we could have easily performed the IsDeveloper check in the TVF but want to show that the results of the TVF are fully composable in the Linq query.

from e in context.Employees
from rc in context.GetReports(e.Id)
where rc.IsDeveloper == true
select new
{
  ManagerName = e.Name,
  EmployeeName = rc.Name,
})

This will translate into the following query

SELECT [e].[Name] AS [ManagerName], [r].[Name] AS [EmployeeName]
FROM [Employees] AS [e]
CROSS APPLY [dbo].[GetReports]([e].[Id]) AS [r]
WHERE [r].[IsDeveloper] = CAST(1 AS bit)

When we iterate over the results we will see that Sally, Fred, and James have 3, 1, and 2 reports who are developers respectively. Sally's results will be a superset of Frd and James's results.

---

From @ErikEJ

Looks great, maybe define the return class before the IQueryable?

---

From @pmiddleton

Ok I reordered the sections so the return class is defined before IQueryable

---

From @ajcvickers

Thanks @pmiddleton for doing this--looks great. I realized this will probably be announced in preview 8, because the final review changes this week didn't make the cut for preview 7.

[julielerman]

Hi fellas! Where in the docs did Paul's great writeup land. It's a great example for self-referencing queries I'd like to point to but I can't find it. Thanks

[pmiddleton]

@julielerman - It made it into one of the "what's new" updates from Jeremy. Here is the link

https://devblogs.microsoft.com/dotnet/announcing-entity-framework-core-ef-core-5-0-preview-8/

[julielerman]

Thanks. I linked to it in an article that will get published on the Pluralsight blog. Also with nods to you, Paul. :)

[pmiddleton]

Awesome - let me know the link once it goes up.