LINQ和Entity Framework Code First中的SQL子查询结果[英] SQL subquery result in LINQ and Entity Framework Code First

本文是小编为大家收集整理的关于LINQ和Entity Framework Code First中的SQL子查询结果的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我想进行一个查询,该查询将返回我的实体以及它们的关联实体之一.例如:

select *, (select COUNT(*) from Forms where Contact_Id = Contacts.Id) 
as FormsCount from Contacts;

我的联系人实体有一个名为 FormsCount 的属性,但它没有被映射,因为表中没有这样命名的列.是否可以编写一个 LINQ 查询来返回我填写了附加 FormsCount 属性的联系人实体?

或者,如果我可以在单独的字段中获取 FormsCount 值并且可以手动将它们复制到实体中,我会很高兴.查询的结果可能是这种形式,例如:

{
  Contact Contact;
  int FormsCount;
}

然后我可以遍历结果并将 FormsCount 复制到 Contact.也许这可以通过使用投影来实现?

我知道如何使用 2 个查询来做到这一点:a) 首先获取联系人实体b) 为第一个查询中返回的联系人获取对或联系人 ID 和 FormsCount.

但我想使用一个查询来做到这一点.此外,我不希望 FormsCount 属性始终填写在我的联系人实体中,我希望对此进行控制.有什么想法吗?

谢谢,迈克尔

推荐答案

你说得对.

如果 Contact 有一个导航属性 Forms 你可以投影:

from c in context.Contacts
select new { Contact = c, FormsCount = c.Forms.Count() }

如果没有,您将不得不使用子查询:

from c in context.Contacts
select new
{
  Contact = c, 
  FormsCount = context.Forms.Count(f => f.Contact_Id == c.Id)
}

EF 将在一个 SQL 查询中处理这两种情况.

本文地址:https://www.itbaoku.cn/post/1556745.html

问题描述

I want to make a query that'll return me entities and additionally a number of one of their associated entities. For example:

select *, (select COUNT(*) from Forms where Contact_Id = Contacts.Id) 
as FormsCount from Contacts;

My Contact entity has a property named FormsCount, but it isn't mapped since there's no column named like that in the table. Is it possible to write one LINQ query that'll return me Contact entities with the additional FormsCount property filled in?

Alternatively, I'd be happy if I could get the FormsCount values in a separate field and I can copy them to the entities manually. The result from the query could be in this form for example:

{
  Contact Contact;
  int FormsCount;
}

Then I can iterate over the results and copy FormsCount to Contact. Maybe this can be achieved by using projections?

I know how to do that using 2 queries: a) fetch contact entities first b) fetch pairs or contact ID and FormsCount for contacts returned in the first query.

But I'd like to do that using one query. Also, I don't want the FormsCount property to be always filled in my Contact entity, I want to have a control over that. Any ideas?

Thanks, Michal

推荐答案

You are right about the projection.

If Contact has a navigation property Forms you can project:

from c in context.Contacts
select new { Contact = c, FormsCount = c.Forms.Count() }

If not, you'll have to use a subquery:

from c in context.Contacts
select new
{
  Contact = c, 
  FormsCount = context.Forms.Count(f => f.Contact_Id == c.Id)
}

EF will handle both situations in one SQL query.