select in select with Linq

Question

Hello i just want to know how to write correctly with linq to entity. i have a case:

select * from Contacts
 where PersonalNumber in (
 SELECT c.PersonalNumber
 from contacts c
 group by c.PersonalNumber
 having count(c.PersonalNumber)=1)

and want to write with Linq:

var PersonalCounts = from c in contacts
                                     group c by c.PersonalNumber into g
                                     select new {PersonalCount = g.Count() };
                var contacts =
                    contacts
                    .Where(s => s.PersonalNumber = (PersonalCounts = 1))
                    .Select(s => s.First());

I just need to select data where count of the personal number = 1. Can someone help me please?

I suspect you want something like `PersonalCounts.Where(z => z.Count() == 1).SelectMany(z => z);` — mjwills, Dec 05 '18 at 23:55
Possible duplicate of [Linq with group by having count](https://stackoverflow.com/questions/2078736/linq-with-group-by-having-count) — mjwills, Dec 05 '18 at 23:56

Eric Lippert · Accepted Answer · 2018-12-06T00:22:35.233

You're overthinking the problem. Let's break it down into two steps. First, group the contacts by number:

var q1 = from c in contacts 
         group c by c.PersonalNumber;

OK, we now have a sequence of groups, where each group is a sequence of contacts that have the same number.

Now, we want the groups that have a single contact in the sequence, and we want that contact:

var q2 = from g in q1 
         where g.Count() == 1 
         select g.First();

Alternatively, we could use a select-many to extract the single contact from the list:

var q2 = from g in q1 
         where g.Count() == 1 
         from c in g
         select c;

And we're done; we could stop here. If we wanted though, how could we make this into one query?

We notice that we are feeding q1 into q2, so we could combine them like this:

var q = from g in (
          from c in contacts 
          group c by c.PersonalNumber) 
         where g.Count() == 1 
         select g.First();

But this is actually harder to read. In this situation it is better to use a query continuation with into:

var q = from c in contacts
        group c by c.PersonalNumber into g
        where g.Count() == 1
        select g.First();

Or, using a select-many:

var q = from c in contacts
        group c by c.PersonalNumber into g
        where g.Count() == 1
        from c in g
        select c;

And we're done; we've got a sequence of contacts where each contact's number appears exactly once.

The benefit of a query continuation is that every operation runs in the same order that it appears on the page:

We start with the contacts
We group them
We filter the groups
We extract the contacts from the remaining groups

That's what you want in a LINQ query: you want the order of the items in the query to match the logical order of the operations that happen to the collection of data.

Note that my last "comprehension" format solution here is logically exactly the same as the "fluent" form in Anu Viswam's answer. My preference is to use the comprehension syntax when it is convenient to do so, but many people prefer the fluent form; choose whichever one makes most sense to you.

thank you you have made my night, i've been thinking about it for so long. thanks again — zura, Dec 06 '18 at 00:15
@zura: The trick for LINQ query expressions is to break it down into a series of logical steps, and make a query for each. Then string them all together, and then see if you can simplify it. — Eric Lippert, Dec 06 '18 at 00:16

Anu Viswan · Answer 2 · 2018-12-06T00:15:52.133

4

You can use following.

contacts
    .GroupBy(x=> x.PersonalNumber)
    .Where(x=>x.Count()==1)
    .SelectMany(x=>x);

edited Dec 06 '18 at 00:15

answered Dec 06 '18 at 00:09

Anu Viswan

17,797
2
22
51

Thanks @Anu thats correct too. I didn't know about .selectMany, thats why i had a mistake. I appreciate that – zura Dec 06 '18 at 12:19

select in select with Linq

2 Answers2