LINQ to SQL查询使用“NOT IN”

Can LINQ to SQL query using NOT IN?

LINQ to SQL查询可以使用NOT IN吗？

e.g., SELECT au_lname, state FROM authors WHERE state NOT IN ('CA', 'IN', 'MD')

例如，SELECT au_lname，state FROM authors WHERE state NOT IN（'CA'，'IN'，'MD'）

4 个解决方案

#1

    List<string> states = new List<string> { "CA", "IN", "MD" };
    var q = from a in authors
            where !states.Contains(a.state)
            select new { a.au_lname, a.state };

要么

   var q = authors.Where( a => !states.Contains( a.state ) )
                  .Select( a => new { a.au_lname, a.state } );

#2

You can do it with Contains:

你可以用Contains来做：

       var states = new[]  {"CA", "IN", "MD"};
       var query = db.Authors.Where(x => !states.Contains(x.state));

#3

here's an example:

这是一个例子：

NorthwindDataContext dc = new NorthwindDataContext();
dc.Log = Console.Out;
var query =
    from c in dc.Customers
    where !(from o in dc.Orders
            select o.CustomerID)
           .Contains(c.CustomerID)
    select c;
foreach (var c in query) Console.WriteLine( c );

#4

Yes!

是!

Here's an example from code we already had written:

这是我们已编写的代码示例：


            List<long> badUserIDs = new List { 10039309, 38300590, 500170561 };
            BTDataContext dc = new BTDataContext();
            var items = from u in dc.Users
                        where !badUserIDs.Contains(u.FbUserID)
                        select u;

The generated SQL turns out to be:

生成的SQL原来是：

{SELECT [t0].[UserID], [t0].[FbUserID], [t0].[FbNetworkID], [t0].[Name], FROM [dbo].[Users] AS [t0] WHERE NOT ([t0].[FbUserID] IN (@p0, @p1, @p2)) }

{SELECT [t0]。[UserID]，[t0]。[FbUserID]，[t0]。[FbNetworkID]，[t0]。[Name]，FROM [dbo]。[Users] AS [t0] WHERE NOT（[ t0]。[FbUserID] IN（@ p0，@ p1，@ p2））}

#1

    List<string> states = new List<string> { "CA", "IN", "MD" };
    var q = from a in authors
            where !states.Contains(a.state)
            select new { a.au_lname, a.state };

要么

   var q = authors.Where( a => !states.Contains( a.state ) )
                  .Select( a => new { a.au_lname, a.state } );

#2