none
how to write where not in linq ? RRS feed

  • Question

  • Hi ,

    How can i write the following subquery in linq ?

    SELECT * FROM Table1
    WHERE MyID NOT IN (SELECT ID FROM Table2)

     


    Thanks Arindam Chakraborty .Net Designer & Developer. Mumbai, India.
    • Moved by litdev Sunday, May 8, 2011 9:01 AM Not a SmallBasic question (From:Small Basic)
    Sunday, May 8, 2011 2:34 AM

Answers

  • Hi Arindam;

    Ah, now I understand why my first code snippet did not work for you. You have posted your question in the Forum Linq to SQL and so I assumed that you were using Linq to SQL where the code I posted would have worked. But in fact you are using Linq to EF, Linq to Entity Framework, which does not support allot of the other method syntax that is supported by Linq to SQL. When posting questions for Linq to Entity Framework use the following forum for those questions:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework

    As of Entity Framework 4.0 the use of Contains method as used in my first solution will work as shown. For Entity Framework 3.5 use the below version to get the same results.

    var results = from t1 in context.Table1
           where context.Table2.Any( t2 => t2.MyID == t1.MyID)
           select t1;	
    

    Fernando

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, May 10, 2011 6:50 PM

All replies

  • Sorry, this is the Small Basic forum. 
    ~~AirWaves!!~~
    Sunday, May 8, 2011 4:29 AM
  • Hi ArindamChakraborty;

    The following Linq query will give you what you want.

    var results = from t1 in context.Table1
           where !( from t2 in context.Table2
                select t2.MyID ).Contains(t1.MyID)
           select t1;
    
    

    Fernando

     

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Sunday, May 8, 2011 4:30 PM
  • Hi Fernandoo,

    The above query did not work, it throws compilation error ..

    Cannot convert lambda expression to type 'string' because it is not a delegate type


    Thanks Arindam Chakraborty .Net Designer & Developer. Mumbai, India.
    Monday, May 9, 2011 8:27 PM
  • Can you post the code as you implemented it in your code please. As you can see there is not Lambda expression in my code.

    Thanks;

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, May 9, 2011 8:51 PM
  • Also what is the data type of your variable MyID in your code?

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, May 9, 2011 8:53 PM
  • Hi Fernandoo,

    Here is my code

    var employeeList = from emp in context.tbEmployee
                                       where !(from con in context.tbConsultant
                                               select con.EmployeeID).Contains(emp.EmployeeID)
                                       select emp;
     

    i want to fetch all those employee who are not added in tbConsultant table.

    EmployeeID data type is long.

     

     


    Thanks Arindam Chakraborty .Net Designer & Developer. Mumbai, India.
    Monday, May 9, 2011 9:00 PM
  • Hi Arindam;

    I just created two table in my SQL server with EmployeeID having a bigint data type on the server and having the Long data type in the model filled it with some data and ran it with the query I posted and it ran without issue. I suspect something else is going on here. What version of the .Net Framework are you running? What version of Visual C# are you running?

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, May 10, 2011 4:11 AM
  • Hi Fernando,

    I am using framework 3.5 , c# 3.5.

    I have regenerated the entity model,now the compilation error has gone.

    but while running the query i am getting the runtime error :

    "LINQ to Entities does not recognize the method 'Boolean Contains[Int64](System.Linq.IQueryable`1[System.Int64], Int64)' method, and this method cannot be translated into a store expression."

    I think .Contains would have work if the datatype were string, i had experienced similar stuff with .Contains earlier.

    but as you are saying the query working fine at your end, i wonder how, or wht wrong am i doing!!


    Thanks Arindam Chakraborty .Net Designer & Developer. Mumbai, India.
    Tuesday, May 10, 2011 9:56 AM
  • Hi Arindam;

    Ah, now I understand why my first code snippet did not work for you. You have posted your question in the Forum Linq to SQL and so I assumed that you were using Linq to SQL where the code I posted would have worked. But in fact you are using Linq to EF, Linq to Entity Framework, which does not support allot of the other method syntax that is supported by Linq to SQL. When posting questions for Linq to Entity Framework use the following forum for those questions:

    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework

    As of Entity Framework 4.0 the use of Contains method as used in my first solution will work as shown. For Entity Framework 3.5 use the below version to get the same results.

    var results = from t1 in context.Table1
           where context.Table2.Any( t2 => t2.MyID == t1.MyID)
           select t1;	
    

    Fernando

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Tuesday, May 10, 2011 6:50 PM
  • Hi Fernando,

    Thanks a lot, It worked.

     


    Thanks Arindam Chakraborty .Net Designer & Developer. Mumbai, India.
    Thursday, May 19, 2011 8:41 PM
  • Not a problem, glad to help.

     


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, May 19, 2011 8:59 PM
  • Thanks Fernando, This helps me a lot.
    Thursday, September 1, 2016 3:59 PM