CODEWITHSUNDEEP
c#.netentity-frameworklinq
Gem Vanstone
Gem Vanstone

Reputation: 3

How can I get only records with a unique property using entity framework and linq?

public class Person
{
     public string firstName;
     public string lastName;
}

I want a list of all Persons with a unique first name.

Persons table

Tom Haverford
Tom Baker
Amy Pond
Amy Santiago
Trish Walker
Chidi Anagonye

The query should return

Trish, Chidi

I've tried using Distinct and a combination of GroupBy and Select, but those return Trish, Chidi, Tom, Amy.

Upvotes: 0

Views: 151

Answers (2)

TRK
TRK

Reputation: 198

You can use group by and count functionality together for this :

1. Get a list of all persons from DB : 

var personList = (from p in db.Person select p).ToList(); // I assumed here that your db obj name is 'db' and table name is 'Person'

2. Now apply group by query to get the count of first names :

var q = from x in personList
    group x by x.firstName into g
    let count = g.Count()
    select new {Count = count, Name = g.First().firstName };

3. Now you can get your final result like this : 

var finalResult = (from p in q where p.Count == 1 select p).ToList();

Happy Coding...

Upvotes: 0

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14228

Demo on dotnet fiddle

You can Group by then count number of duplicated items. After that, you can get the item with count value equals to 1 like below.

    var arr = new []
    {
        new Person {    firstName = "Tom", lastName = "Haverford" },
        new Person {    firstName = "Tom", lastName = "Baker"},
        new Person {    firstName = "Amy", lastName = "Pond" },
        new Person {    firstName = "Amy", lastName = "Santiago"},
        new Person {    firstName = "Trish", lastName = "Walker"},
        new Person {    firstName = "Chidi", lastName ="Anagonye" }
    };

    var result = arr.GroupBy(p => p.firstName).Select(g => new { Name = g.Key, Count = g.Count()});
    foreach(var item in result.Where(p => p.Count == 1))
      Console.WriteLine(item.Name);

Output

Trish
Chidi

Upvotes: 1

Related Questions