How to convert this sql to Linq C#

i have the sql below that i would like to convert to C# using li开发者_StackOverflow中文版nq. Can someone tell me how this best can be done with linq?

select A.field1, A.Field2, A.Field4,  A.Field5,  A.Field4 ,A.Field6
from MPhoneParts A

where A.Field3= 'Batteri' AND NOT EXIST(

select * from MPhoneParts B where 

B.Field3='cover'
A.Field2= B.Field2 AND 
A.Field4= B.Field4 AND
B.Field6='Production354')

Cheers Mike

There may be a better approach (quite possibly using a join...), but:

var query = from a in db.MPhoneParts
            where a.Field3 == "Batteri" &&
                  !db.MPhoneParts.Any(b => b.Field3 == "cover" &&
                                           a.Field2 == b.Field2 &&
                                           a.Field4 == b.Field4 &&
                                           b.Field6 == "Production354")
            select a;

I assume your question is mistyped and you meant to check for the NOT IN clause.

here is the solution on how to write the NOT IN... in linq, hope this helps:

The NOT IN clause in LINQ to SQL

var qry =
    from a in db.PhoneParts
    where a.Field3 == "Batteri"
    && !db.PhoneParts.Any(b =>
             b.Field3 == "cover"
          && b.Field6 == "Production354"
          && b.Field2 == a.Field2
          && b.Field4 == a.Field4)
    select new { a.Field1, a.Field2, a.Field4, a.Field5, a.Field6 };

But thoughts:

• Field1 thru Field6 are horrible names
• you can use the existing SQL via db.ExecuteQuery