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

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 i开发者_开发百科s greatly appreciated :)

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.

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!

I think the following query would work. You can inner join the array of languages to the languages table as follows. Linq lets you do this. See:

int[] languages = // populate languages
var query = from p in db.TBL_PROFILE
            join link in db.TBL_Languages_Links on link.ProfileID equals p.ProfileID
            join lang in db.TBL_LANGUAGES on link.LanguageID equals lang.LanguageID
            join arrlang in languages on lang.LanguageName equals arrlang
            where p.ACTIVE == true
            select p;