CODEWITHSUNDEEP
c#sqlsql-serverlinqlinq-to-sql
fibertech
fibertech

Reputation: 357

LINQ2SQL Any(), NOT EXISTS Problems

I have a problem with my linq2sql while grabbing rows from database by condition select n rows from table1 where table2 doesn't contain any rows by condition table1.id != table2.accId and table1.action != "action_constant" i have such a code in my c#:

query = (from accsArray in db.Accs
         where accsArray.Valid == 1 &&
               !String.IsNullOrEmpty(accsArray.Password) && 
               accsArray.vip_rec == 0 || accsArray.data_col == null &&
               !db.Reaktors.Any(rk => rk.Action == action &&
                                      rk.Account_id == accsArray.id)

         select new DbAcc
         {
             id = accsArray.id,
             Login = accsArray.Login,
             Password = accsArray.Password,
             System = accsArray.System,
             //FriendsCount = Convert.ToInt32(accsArray.Friends_count)
         }).Take(count).OrderBy(acc => acc.id);

!db.Reaktors.Any(rk => rk.Action == action && rk.Account_id == accsArray.id) this doesn't work, while looking for my clean sql code i see this:

SELECT [t2].[id], [t2].[Login], [t2].[System], [t2].[Password]
FROM (SELECT TOP (50) [t0].[id], [t0].[Login], [t0].[System], [t0].[Password]
      FROM [Collider].[dbo].[Accs] AS [t0]
      WHERE (([t0].[Valid] = 1)
             AND ([t0].[Password] <> '')
             AND ([t0].[Login] <> '')
             AND ([t0].[data_col] = 0))
         OR (([t0].[data_col] IS NULL)
             AND (NOT (EXISTS(SELECT NULL AS [EMPTY]
                              FROM [dbo].[Reaktor] AS [t1]
                              WHERE [t1].[Actionself] = '_take_acc_dc'
                                AND ([t1].[Account_id] = [t0].[id])))))) AS [t2]
ORDER BY [t2].[id]

I'm afraid of SELECT NULL AS [EMPTY] FROM [dbo].[Reaktor] especially of NULL AS [EMPTY] so i think that's why it doesn't work correctly.

Upvotes: 1

Views: 361

Answers (1)

fibertech
fibertech

Reputation: 357

The solution is to place paranthesis right and to order conditions. The good working code will look like this:

query = (from accsArray in db.Accs
         where !db.Reaktors.Any(rk => (rk.Actionself == action 
                                       && rk.Account_id == accsArray.id)) 
               && accsArray.Valid == 1 
               && accsArray.Password != String.Empty 
               && accsArray.Login != String.Empty 
               && accsArray.data_col == 0
         select new DbAcc
         {
             id = accsArray.id,
             Login = accsArray.Login,
             Password = accsArray.Password,
             System = accsArray.System,
         }).Take(count).OrderBy(acc => acc.id);

Upvotes: 2

Related Questions