在实体框架中使用包含语句时选择特定的列 [英] select specific columns when using include statement with entity framework

问题描述

当我需要层次关系（父子关系）时，通常在EF查询中使用Include语句。

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

示例：

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
}

我尝试将查询优化为以下内容代码，但是如您所见，Projects实体是Customer的 child 实体，因此在查询中没有要选择的特定列。显然，它是一个集合。

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?

请注意，我的YeagerTechDB.ViewModels.Customers模型由驻留在Customer和Project实体中的所有列组成。

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;
            }
        }

1名儿童的答案#p

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
}

2个儿童实体的答案#p

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
}

编辑＃3

Edit #3

推荐答案

选中此链接即可了解更多详细信息。简而言之，诀窍是使用.Select（）和匿名类型来限制所需的列。在下面的示例中，首先Select（）实际上是这样做的：

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();

这篇关于在实体框架中使用包含语句时选择特定的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！