usesser usesser - 10 months ago 60
C# Question

LINQ get rows from a table that don't exist in another table when using group by?

I have two three tables books, authors and groups.

books: bookId, name, authorId....

authors: authorId, firstName, LastName....

groups: groupID, name, authorId...

i need to select all

that doesn't belong to any groups and doesn't have less than 3 books published between two dates.

List<int> authorId= new List<int>(db.Books.GroupBy(x => x.authorId)
.Where(x => x.Any(y => y.datePublished <= now && y.datePublished >= date) && x.Count() > 2)
.Select(x => x.FirstOrDefault().authorId)).Distinct().ToList();

Answer Source

I guess this would work:

List<int> authorId = (List<int>)db.Books
    .Where(x => x.datePublished >= InitialDate)
    .Where(x => x.datePublished <= FinalDate)
    .GroupBy(x => x.authorId)
    .Select(g => new { authorId = g.Key, count = g.Count() })
    .Where(g => g.Count >= 3)

I make every single step so you can see it better, fell free to optimized as needed. The key thing here is the new { authorId = g.Key, count = g.Count() }.