Need Equivalent Linq Query For My Sql Query

SELECT * FROM Tbl_Vulpith_Registration WHERE MemId NOT IN (select MemId from Tbl_PublicLink);

Solution 1:

Try this snippet below:

var query = from c in dc.Tbl_Vulpith_Registration
            where !(from o in dc.Tbl_PublicLink
                    select o.MemId )    
                   .Contains(c.MemId )    
            select c;

or using Extension method:

var resultList= Tbl_Vulpith_Registration.Where(p => !Tbl_PublicLink.Any(p2 => p2.MemId == p.MemId));

Solution 2:

For translating SQL to LINQ query comprehension:

1. Translate FROM subselects as separately declared variables.
2. Translate each clause in LINQ clause order, translating monadic operators (DISTINCT, TOP, etc) into functions applied to the whole LINQ query.
3. Use table aliases as range variables. Use column aliases as anonymous type field names.
4. Use anonymous types (new { }) for multiple columns
5. Left Join is simulated by using a into join_variable and doing another from from the join variable followed by .DefaultIfEmpty().
6. Replace COALESCE with the conditional operator and a null test.
7. Translate IN to .Contains() and NOT IN to !...Contains()
8. SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
9. SELECT fields must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
10. Proper FULL OUTER JOIN must be handled with an extension method.

So your SQL query translates to:

var ExcludeMemIds = from pl in Tbl_PublicLink select pl.MemId;
var ans = from vr in Tbl_Vulpith_Registration
          where !ExcludeMemIds.Contains(vr.MemId)
          select vr;