select specific columns when using include statement with entity framework

Question

When I need a hierarchal (parent-child) relationship, I typically use the Include statement in my EF query.

Example:

DbContext.Customers.Include("Projects");

This is fine, but the Customers and Projects entities always brings back all the columns.

I know that the below query will bring back specific columns in the parent table, but I'm also trying to bring back only specific columns in the child table. If I use the intellisense on the Projects it is obviously a collection and does not give specific properties to select.

from c in Customers
let Projects = c.Projects.Where (p => p.Notes != null)
where Projects.Any()
select new
{
    c.UserName,
    Projects
}

I tried refining the query to the below code, but as you can see, the Projects entity is a child entity of the Customers and therefore, does not have a specific column to select in the query. It obviously is a collection.

Is there a way to bring back just specific columns in each of the entities when using an Include in your query?

Note that my YeagerTechDB.ViewModels.Customers model is made up of all columns that reside in the Customer and Project entities.

public List<YeagerTechDB.ViewModels.Customers> GetCustomerProjects()
        {
            try
            {
                using (YeagerTech DbContext = new YeagerTech())
                {
                    var customer = DbContext.Customers.Include("Projects").Select(s =>
                        new YeagerTechDB.ViewModels.Customers()
                        {
                            CustomerID = s.CustomerID,
                            ProjectID = s.ProjectID,
                            UserName = s.UserName,
                            Name = s.Projects.,
                        });

                     return customer.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

ANSWER #1 FOR 1 CHILD ENTITY

from c in Customers
let Projects = c.Projects.Where (p => p.Notes != null)
where Projects.Any()
select new
{
    c.UserName,
    Projects
}

ANSWER #2 FOR 2 CHILD ENTITIES

from c in Customers
let highValueP =
    from p in c.Projects
    where p.Quote != null
    select new { p.ProjectID, p.Name, p.Quote }
where highValueP.Any()
from p in Projects
let highValuet =
    from t in p.TimeTrackings
    where t.Notes != null
    select new { t.ProjectID, t.Notes }
where highValuet.Any()
select new 
{
    c.CustomerID,
    Projects = highValueP,
    TimeTrackings = highValuet
}

Edit #3 enter image description here

score 28 · Accepted Answer · answered Apr 04 '15 at 17:50

28

Check this link for more details. In short, the trick is to use .Select() and anonymous type to restrict the columns you want. In the example below first Select() is actually doing this:

var results = context.Products
        .Include("ProductSubcategory")
        .Where(p => p.Name.Contains(searchTerm)
                    && p.DiscontinuedDate == null)
        .Select(p => new
                        {
                            p.ProductID,
                            ProductSubcategoryName = p.ProductSubcategory.Name,
                            p.Name,
                            p.StandardCost
                        })
        .AsEnumerable()
        .Select(p => new AutoCompleteData
                            {
                                Id = p.ProductID,
                                Text = BuildAutoCompleteText(p.Name,
                                    p.ProductSubcategoryName, p.StandardCost)
                            })
        .ToArray();

answered Apr 04 '15 at 17:50

Borys Generalov

2,255
17
19

That makes perfect sense... Thanks.... Can you or anyone else tell me what's wrong with EDIT #3 in my answer? Your answer and my #1 & #2 answers work fine for anonymous types, but what if I want to stick in inside a Model? – sagesky36 Apr 04 '15 at 18:45
@sagesky36, a litle it late, but I think you miss the "new" key in your Select clause. – Piyey May 31 '17 at 17:36
2

What if you're including multiple children of `Products`? E.G. If instead of `ProductSubcategory` you had `ProductSubCategories` and you want to select only the name of each of those categories. – xr280xr May 08 '18 at 15:21
2

This "works", but it causes a query per parent item instead of doing a join. – Timothy Gonzalez Jun 06 '18 at 14:05
@Timoty Gonzalez how do you implement this to use join? – ed22 Sep 16 '21 at 08:19
Do I need .AsEnumerable() inbetween? – ed22 Sep 16 '21 at 08:39

select specific columns when using include statement with entity framework

1 Answers1

Linked