view for many to many

    1 /****** Object:  View [dbo].[vwTalent]    Script Date: 02/18/2009 15:11:11 ******/

    2 IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vwTalent]'))

    3 DROP VIEW [dbo].[vwTalent]

    4 GO

    5 /****** Object:  View [dbo].[vwTalent]    Script Date: 02/18/2009 15:11:15 ******/

    6 SET ANSI_NULLS ON

    7 GO

    8 SET QUOTED_IDENTIFIER ON

    9 GO

   10   

   11  

   12

   13 CREATE VIEW [dbo].[vwTalent]

   14 AS

   15 SELECT t.TalentID

   16 ,  t.FirstName 

   17 , (SELECT   l.LanguageName +  ', ' AS [text()]

   18         FROM [TalentLanguage] tl

   19         JOIN [Language] l ON tl.LanguageID = l.LanguageID 

   20         WHERE tl.TalentID = t.TalentID 

   21         ORDER by l.LanguageID

   22         FOR XML PATH('') 

   23    ) as LanguagesString

   24 , (SELECT   Convert(nvarchar(10), l.LanguageID) +  ', ' AS [text()]

   25         FROM [TalentLanguage] tl

   26         JOIN [Language] l ON tl.LanguageID = l.LanguageID 

   27         WHERE tl.TalentID = t.TalentID 

   28         FOR XML PATH('') 

   29    ) as LanguageIdsString  

   30 FROM Talent as t  

   31 WHERE t.Active = '1'

   32

   33 GO

select * from dbo.vwTalent

ID	FirstName	LanguagesString	LanguageIdsString
-----	-----------	--------------------	-----------
1	Jake	English,	1,
6	test	Chinese, Japanese, Romanian,	8, 9, 10,
7	Daren	English,	1,
32	Jim	NULL	NULL
33	Andy	English, Spanish, French, German, Polish, Portuguese,	1, 2, 3, 4, 5, 6,
34	Jeff	English,	1,

now you can call

  204 private IQueryable<Talent> AddSearch(IQueryable<Talent> t, string s)
  205 {
  206     return t.Where(tal =>  tal.LanguagesString.ToLower().Contains(s)  );
  207 }