Linq: Getting missing records

Question

So heres my case i got 2 tables one with Languages one with Resources and what i want to do is to find all Resources that is missing on each language...

The default language i wanna go after is languageId = 1

Table "Resources"

Id    Key    Value    LanguageId
1     k1     test     1
2     k1     test     2
3     k1     test     3

Table "Languages"

Id   
1
2
3
4
5
6
7

language contains about 10 rows of languages.

Now what i wanna do is to take out those resources that dosent exists (ex results)

Key   Value   LanguageId
k1    test    1
k1    test    2
k1    test    3
k1    null    4
k1    null    5
k1    null    6
k1    null    7

Any suggestions how to solve this problem, ive tried with GroupJoin And SelectMany but no good results.

Answer 1

It feels to me like actually your structure isn't quite right yet: you should have a third table of ResourceKeys, with k1 in. That way when you add a resource key, the first thing you add is an entry in ResourceKeys, at which point all languages will be missing that translation. Otherwise you have no way of representing "a resource with no translations" which is a meaningful concept IMO.

You can then cross-join Language and ResourceKeys, and group join against Resources:

var query = from language in Languages
            from resourceKey in ResourceKeys
            join resource in Resources
                 on new { LangId = language.Id, ResId = resourceKey.Id }
                 equals new { LangId = resource.LanguageId,
                              ResId = resource.Key }
                 into values
            select new { LangId = language.Id,
                         ResId = resourceKey.Id,
                         Value = values.SingleOrDefault() };

Answer 2

from lang in context.Languages
where !context.Resources.Contains(r => r.LanguageId == lang.LanguageId)
select lang