-1

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

I have to find all those 'ActorName' from "actorlist" which are not in 'Actor1','Actor2','Actor3','Actor4' column. What will be LInq query for this result? Note that "addrecord" table have only one row.

please help me.....

dilipkumar1007
  • 329
  • 4
  • 22
  • possible duplicate of [Linq to Entities - Sql "IN" clause](http://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause) –  Mar 24 '14 at 08:25

1 Answers1

1

This is one possible LINQ to get what you described in question (method syntax) :

DataContext.actorlists
           .Select(o => o.ActorName)
           .Where(o => !DataContext
                             .addrecords
                             .Any(p => p.Actor1 == o.ActorName ||
                                       p.Actor2 == o.ActorName ||
                                       p.Actor3 == o.ActorName ||
                                       p.Actor4 == o.ActorName))

or if you prefer query syntax, you can try this :

from a in DataContext.actorlists
where !(from b in DataContext.addrecords
        where b.Actor1 == a.ActorName
              || b.Actor2 == a.ActorName
              || b.Actor3 == a.ActorName
              || b.Actor4 == a.ActorName).Any()
select a.ActorName
har07
  • 88,338
  • 12
  • 84
  • 137
  • public static List comedianlist(int iid) { List newlst = new List(); 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; } – dilipkumar1007 Mar 24 '14 at 05:15
  • is showing error at .any() that "linq a query body must end with a select clause or a group clause" please have a check and hep me sir.. – dilipkumar1007 Mar 24 '14 at 05:19