How to select specific columns in Entity Framework Core

When writing SQL queries we should avoid SELECT * if we don’t need all columns as it can have a negative affect on performance. As we know SELECT * is shorthand for selecting all columns, so the issue isn’t with the * itself, but rather that it makes it so easy to just select everything without thinking about it. We should always just pull back the specific columns that we need for our use case.

Entity Framework Select approaches

In Entity Framework Core we can select specific columns by using either anonymous types or DTOs. The image below (click for a larger view in a new window) shows three ways to pull back data using Entity Framework Core.

– 1 –
A very common way of querying EF Core which pulls back all columns. Equivalent to Select * in SQL so no need to specify column names etc. OK in some scenarios such as pulling back reference or lookup items but can be problematic if you only need a few columns as you are returning unneeded data.

– 2 –
Select the multiple columns you want into an anonymous type. Anonymous types are a way to put data into a class without having to define it. Simple, self-contained but downside is you can’t pass it around.

– 3 –
Select the multiple columns you want into a DTO (data transfer object). Simple approach in which you define a custom class for whatever columns you want. You can return it and pass it around as its a normal class.

Side Note

I’ve used String.Contains in the Entity Framework Select snippets above but use this with care as it creates a query that is non-sargable (due to the LIKE having a wildcard at the start) meaning it won’t be able to use an index on the title field if there was one.

Consider String.StartsWith which in a lot of cases may meet your requirements. If you have a large table and really do need full substring search look into full text indexes which you can query in Entity Framework Core with something like…

.Where(c => EF.Functions.FreeText(c.LastName,”Smith”))