Select from 2 tables with count()

Question

Dictionary table:

ID	Name
1	English
2	Italian

Word table references DictionaryID:

ID	DictionaryId	Word
1	1	A
2	1	B
3	2	C

I want to achieve a single query with a left join between Dictionary and Word table and as result, all fields from Dictionary table, and only the count of rows from the Word table in a field called WordNumber as in:

public class Dictionary
{
    public long ID { get; set; }

    [Collation("NOCASE")]
    public string Name { get; set; }

    [Ignore]
    public long WordNumber { get; set; }
}

I have a function that returns the list of dictionaries based on a text search and this is working:

public async Task<List<Dictionary>> GetDictionariesForSearchAsync(string search)
{
    await Init();
    return await Database.Table<Dictionary>()
        .Where(w => w.Name.ToLower().Contains(search.ToLower()))
        .ToListAsync();
}

For each Dictionary I read the number of words:

public async Task<long> GetRecordNumberForDictionary(long id)
{
    long num = await Database.Table<Word>().Where(i => i.DictionaryId == id).CountAsync();
    return num;
}

I'm duplicating queries (a single query is more efficient). I can't find a function like GroupBy to group and count Word.

William's code gets me this:

Answer 1

I'm wondering if you tried to use the 'GroupJoin' method which allows you to perform a left join and group the results based on a specific key.

In your case, you want to group the 'Words' table by 'DictionaryId' and count the number of words for each group.

public async Task<List<Dictionary>> GetDictionariesForSearchAsync(string search)
{
    await Init();

    var dictionaries = await (
        from dict in Database.Table<Dictionary>()
        where dict.Name.ToLower().Contains(search.ToLower())
        join wordGroup in
            (
                from word in Database.Table<Word>()
                group word by word.DictionaryId into wordGroup
                select new { DictionaryId = wordGroup.Key, WordCount = wordGroup.Count() }
            )
        on dict.ID equals wordGroup.DictionaryId into wordCounts
        from wordCount in wordCounts.DefaultIfEmpty()
        select new Dictionary
        {
            ID = dict.ID,
            Name = dict.Name,
            WordNumber = (wordCount != null) ? wordCount.WordCount : 0
        }
    ).ToListAsync();

    return dictionaries;
}