how to use 'IN' clause in linq

Question

public static List<mainISRC> comedianlist(int iid)
{
     List<mainISRC> newlst = new List<mainISRC>();
     ISRCManagementDBEntities1 dbcontext = new ISRCManagementDBEntities1();
     newlst=(from z in dbcontext.Comedians
             where !(from b in dbcontext.mainISRCs 
             where b.id==iid && b.Actor1==z.Comedian1 || b.Comedian1==z.Comedian1 || b.Comedian3==z.Comedian1 || b.Comedian4==z.Comedian1).Any()
             select z.Comedian1).ToList();            
     return newlst;
}

I have a table name "comedian" with column 'id','Comedian' and 'IsActive' which contain 50 numbers of rows and also I have another table name "mainISRC" with column 'id','Actor1','Actor2','Actor3','Actor4'. 'id' column in "actorlist" and 'iid' column in "addrecord" are not same.

I have to find all those 'comedian' from "comedian" which are not in 'Actor1','Actor2','Actor3','Actor4' column. What will be the Linq query for this?

[`Contains()`](http://msdn.microsoft.com/en-us/library/system.linq.enumerable.contains(v=vs.110).aspx) — Robert Harvey, Mar 24 '14 at 05:44

Amit Bisht · Accepted Answer · 2014-03-24T07:03:52.873

1

Final Update Code

Sorry code is not tested but it may help you or gives you a direction

  List<int> liComedianId = new List<int> ();
liComedianId = dbcontext.Comedians.Select(s => (int)s.Id).ToList();

List<mainISRC> limainISRC = new List<mainISRC> ();
limainISRC = dbcontext.mainISRCs.ToList();

var d = ((from a in limainISRC.Select(s => s.Actor1).ToList()
select a).TolList().Union
            (from b inlimainISRC.Select(s => s.Actorb).ToList()
select b).Tolist()).ToList();
d = d.Distinct();

licomedianId =  licomedianId.Select(s => !d.Contain(s));

List<comedian> LIcomedianFinal = new List<comedian> ();
LIcomedianFinal =  dbcontext.Comedians.ToList();
var FinalList =(from a in  LIcomedianFinal .ToList()
                join b in d.TolIst()
                on a.Id equlas d).ToList();

edited Mar 24 '14 at 07:03

answered Mar 24 '14 at 05:41

Amit Bisht

4,870
14
54
83

this code is not working sir. Please help me sir.. – dilipkumar1007 Mar 24 '14 at 05:49
try it with && operator – Amit Bisht Mar 24 '14 at 05:49
yes sir.. I have modified your code as List liComedian = new List(); liComedian = dbcontext.Comedians.ToList(); List limainISRC = new List(); limainISRC = (from z in dbcontext.mainISRCs where z.id == iid select z).ToList(); var FilteredList = limainISRC.Where(w => !liComedian.Contains(w.Actor1) || !liComedian.Contains(w.Actor2) || !liComedian.Contains(w.Actor3)).ToList(); – dilipkumar1007 Mar 24 '14 at 05:58
is there any error you got or you're not getting correct output – Amit Bisht Mar 24 '14 at 05:59
The best overloaded method match for 'System.Collections.Generic.List.Contains(ISRCMANAGER.Models.Comedian)' has some invalid arguments – dilipkumar1007 Mar 24 '14 at 05:59
sir this is the code I have update but it is showing same error what I have explained above.. List liComedian = new List(); liComedian = dbcontext.Comedians.ToList(); List limainISRC = new List(); limainISRC = (from z in dbcontext.mainISRCs where z.id == iid select z).ToList(); var FilteredList = limainISRC.Where(w => !liComedian.Contains(w.Comedian1) || !liComedian.Contains(w.Comedian2) || !liComedian.Contains(w.Comedian3) || !liComedian.Contains(w.Comedian4)).ToList(); – dilipkumar1007 Mar 24 '14 at 06:03
try my new updated code.. in your code you were comparing liComedian.Contains(w.Comedian1) where liComedian is List of type Comedian .. thus the error you got is becoz of you are comparing an object of class with an int type comedian1 .. thus change the type of your Licomedian to List and then you compare it – Amit Bisht Mar 24 '14 at 06:09
@user3270379 did you got what is said – Amit Bisht Mar 24 '14 at 06:17
This is my update query. Please take it carefully ..... I have to find all those comedian from Comedian table whose are not in mainISRC table with column name 'Comedian1','Comedian2','Comedian3','Comedian4'. Below I have paste my code which currently have no any error but I am not getting required record from Comedian table.. – dilipkumar1007 Mar 24 '14 at 06:32
ISRCManagementDBEntities1 dbcontext = new ISRCManagementDBEntities1(); List liComedianId = new List(); liComedianId = dbcontext.Comedians.Select(s=>s.Comedian1).ToList(); List limainISRC = new List(); limainISRC = dbcontext.mainISRCs.Where(x=> x.id==iid).ToList(); var FilteredList = limainISRC.Where(w => !liComedianId.Contains(w.Comedian1) || !liComedianId.Contains(w.Comedian2) || !liComedianId.Contains(w.Comedian3) || !liComedianId.Contains(w.Comedian4)).ToList(); – dilipkumar1007 Mar 24 '14 at 06:32
what should i do with this code – Amit Bisht Mar 24 '14 at 06:36
I have to find all those comedian from Comedian table whose are not in mainISRC table with column name 'Comedian1','Comedian2','Comedian3','Comedian4'. This is the result I want to find from this code. Please help me sir.... its too urgent for me.. – dilipkumar1007 Mar 24 '14 at 06:39

how to use 'IN' clause in linq

1 Answers1

Final Update Code