CODEWITHSUNDEEP
sqllinqentity-frameworkef-code-first
Michał Fronczyk
Michał Fronczyk

Reputation: 1891

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;

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

Upvotes: 2

Views: 3316

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109119

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.

Upvotes: 6

Related Questions