Entity Framework Core 1.0 - Table Valued Functions and LINQ Composition

Entity framework Core (EFCore) has been out for a few weeks now after hitting RTM on June 27th 2016. That was also the same day that ASP.NET Core was released. EFCore has brought many changes, to say the least. It was renamed to Core instead the logical continuation from Entity Framework, because it's a total rewrite. Very similar to ASP.NET Core.

The rewrite brought many benefits with speed being the biggest one. EF Core is simpler and a lot more powerful, even though it's still early days and some things are not working as expected or are missing. If you're wondering whether EFCore or EF6 is better for your project, have a look at the feature comparison chart in the official [documentation](https://docs.efproject.net/en/latest/efcore-vs-ef6/choosing.html" target="_blank).

I've recently started using EF Core whenever possible and it's great to be able to take advantage some of the new features. Today, I'll be talking about using [Table Value Functions](https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx" target="_blank) (TVFs) with EF Core and LINQ. The cool thing about this feature is that you can use EF Core to get data from a TVF and then automatically filter those results further using LINQ. Let's look at the code below as an example why this capability is so important:

The part we're particularly interested in is the GetMatchingPostsByTitle(string searchTerm); and in particular the LINQ query:

posts = context.Posts
	.FromSql("SELECT * FROM dbo.GetMatchingPostByTitle({0})", searchTerm)
	.Where(p => p.BlogId == 1)
	.OrderByDescending(p => p.CreateDate)
	.ToList();

Apart from convenience to be able to filter the dataset with LINQ inline, but this significantly improves performance by pushing the extra filtering to the SQL server. And here's the proof attached below:

The where and order by are both executed on the database server before the result is returned to the calling code. This significantly improves performance and makes the whole application more efficient.

And before you ask, this, unfortunately, doesn't work with stored procedures. The team is already hard at work trying to add more functionality to Entity Framework Core so I'm sure we'll see even further improvements with subsequent releases. As more feature are released, I will try to blog about them and keep you up-to-date.

Finally, you can find the solution available on GitHub [here](https://github.com/cmatskas/efcoretest" target="_blank). The code was written using .NET Core and EF Core so you may need to update your tooling to the latest version.