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

问题描述

我想进行一个查询，该查询将返回我的实体以及与其关联的实体之一的数量.例如:

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;

我的联系人"实体具有一个名为FormsCount的属性，但由于未在表中命名为该列，因此未映射该属性.是否可以编写一个LINQ查询，使我返回具有填充的其他FormsCount属性的Contact实体?

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?

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

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

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

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

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

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.

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

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?

谢谢， 米哈尔

推荐答案

您对投影的看法是正确的.

You are right about the projection.

如果Contact具有导航属性Forms，则可以进行投影:

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可以在一个SQL查询中处理这两种情况.

EF will handle both situations in one SQL query.

这篇关于LINQ和Entity Framework代码优先的SQL子查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！