CODEWITHSUNDEEP
linq
Rajesh
Rajesh

Reputation: 459

Linq query on related entities

this may be a very basic question but I haven't used Linq much so do need help. I want to retrieve a list of records based on the related record count.

for example:

I have contact entity which has 1:N relationship with a 'Role' entity. I want to select contacts that have

  1. No related roles
  2. only 1 related role

How do I write this using Linq

from contact in context.ContactSet
where <contact.Roles has no records> -- ?? 
select contact;

Please help.

Upvotes: 1

Views: 534

Answers (4)

Tim Croydon
Tim Croydon

Reputation: 1896

The following will select all contacts that have exactly 0 or 1 items in the Roles collection. Depending on the type of collection and the data access technology the Count property may by a method, e.g. Count(). (Count also has overloads so you can conditionally count items, e.g. .Count(x => x.SomeProperty && !x.SomeOtherProperty)

from contact in context.ContactSet
where contact.Roles.Count <= 1
select contact;

Upvotes: 1

dknaack
dknaack

Reputation: 60556

Description

You can use the Count() or Any() method

Count() - Returns the number of elements in a sequence.

Any() - Determines whether any element of a sequence satisfies a condition.

Sample

Count()

Query Syntax

from contact in context.ContactSet
where contact.Roles.Count() == 0
select contact;

Method Syntax

context.ContactSet.Where(c=> c.Roles.Count() != 0));

Any()

Query Syntax

from contact in context.ContactSet
where !contact.Roles.Any()
select contact;

Method Syntax

context.ContactSet.Where(c=> !c.Roles.Any());

There is no difference in performance between method and query syntax both will compiled into the same.

More Information

Upvotes: 2

cuongle
cuongle

Reputation: 75326

You can check by using Count() <= 1

Linq style:

from contact in context.ContactSet
where contact.Roles.Count() <= 1
select contact;

Method style:

context.ContactSet.Where(c => c.Roles.Count() <= 1);

Upvotes: 1

silvo
silvo

Reputation: 4009

To select contacts with no roles:

from contact in context.ContactSet
where !contact.Roles.Any()
select contact;

or simply:

context.ContactSet.Where(c=> !c.Roles.Any());

To select contacts with n roles assigned:

from contact in context.ContactSet
where contact.Roles.Count()==n
select contact;

or simply:

context.ContactSet.Where(c=> c.Roles.Count()==n));

Upvotes: 1

Related Questions