Help using LINQ to select all elements that match all elements on a linked table that are inside an array

Question

This ones a bit odd so I'll try and explain it first in plain English. I have three tables.

TBL_PROFILE

TBL_LANGUAGES

TBL_LANGUAGES_LINK

where the relationship is TBL_PROFILE --> TBL_LANGUAGE_LINK <-- TBL_LANGUAGES

so if a person speaks 3 languages, they would have three entries in TBL_LANGUAGE_LINK.

Basically I'm passing a string array of language IDs and I need to select all profiles that speak ALL the languages in that array, not just one of them.

Heres what I came up with

from p in db.TBL_PROFILEs                        
where p.ACTIVE == true 
   && p.TBL_LANGUAGES_LINKs.All(x => languages.Contains(x.LANGUAGE_ID.ToString())) == true
select p;

(FYI 'languages' is an array of strings)

To me this seems logical :s "Select all profiles where all elements in the languages_link fall within the languages array"

For some reason the results I receive are every record in TBL_PROFILE which I'm having difficulty explaining.

I've attached the LINQ generated SQL below for additional info (apologies if the answer is obvious - my SQL skills arent the best)

    {SELECT [t0].[PROFILE_ID], [t0].[USER_ID].........

    FROM [dbo].[TBL_PROFILE] AS [t0]
    WHERE ([t0].[ACTIVE] = 1) AND (NOT (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[TBL_LANGUAGES_LINK] AS [t1]
    WHERE ((
        (CASE 
            WHEN (CONVERT(NVarChar,[t1].[LANGUAGE_ID])) IN (@p0, @p1) THEN 1
            ELSE 0
         END)) = 0) AND ([t1].[PROFILE_ID] = [t0].[PROFILE_ID])
    )))
}

Any help or advice is greatly appreciated :)

Answer 1

try this.

from p in db.TBL_PROFILEs                        
where p.ACTIVE == true 
join l in db.TBL_Languages_Links on
p.ProflieID equals l.ProflieID
where languages.Contains(l=>l.Language_ID.ToString())
select p;

the answer is based upon lot of assumptions about the foreign keys of the table and the fact that u r comparing strings to ID fields.

Answer 2

Doubtful its the most efficient way to do it but it returns profiles that have languages in the languages table that exactly match all elements in the provided array. It also excludes elements that only match some of the criteria which was the original goal. Basically im now starting with a full set of profiles and trimming off elements as I go.

var query = from p in db.TBL_PROFILEs
            where p.ACTIVE == true
            select p;


foreach (int language in languages)
{
     query = query.Where(p => p.TBL_LANGUAGES_LINKs.Where(
                                               x =>
                                               x.LANGUAGE_ID == language)
                                               .Count() == 1);
}

A better way to do this would still be appreciated!