2

I have a query that in T-SQL it is

SELECT *                         
  FROM rpm_scrty_rpm_usr ru                                         
WHERE ru.inact_ind = 'N'                       
  AND email_id IS NOT NULL
  AND wwid IS NULL
  AND LTRIM(RTRIM (email_id)) <> ''
  AND dflt_ste_id NOT IN (25,346,350,352,353,354,355,357,358,366,372,411)

When I have been converting it to LINQ, I have everything except the "NOT IN"

var querynonSystem = (from ru in Rpm_scrty_rpm_usrs
                where ru.Inact_ind == "N" && ru.Email_id != null && ru.Wwid == null && ru.Email_id.Trim() != ""
                && ru.Dflt_ste_id != 25
                select ru).Count();

I did temporarily put in this line && ru.Dflt_ste_id != 25

However I need to have AND dflt_ste_id NOT IN (25,346,350,352,353,354,355,357,358,366,372,411)

I am seeing a lot of different code like this lambda where !(list2.Any(item2 => item2.Email == item1.Email))

Then var otherObjects = context.ItemList.Where(x => !itemIds.Contains(x.Id));

For my linq query, how can I do this Not In in simple manner?

Improve this question

2 Answers 2

Reset to default
3

You can use Contains with !. In addition, if you just want to count rows, you can use Count.

var ids = new List<int> {25, 346, 350, 352, 353, 354, 355, 357, 358, 366, 372, 411};

var querynonSystem = XXXcontext.Rpm_scrty_rpm_usrs.Count(x => 
   x.Inact_ind == "N" &&
   x.Email_id != null &&
   x.Wwid == null &&
   x.Email_id.Trim() != "" &&
   !ids.Contains(x.Dflt_ste_id));

From comment: if you want to retrieve all, you can still use Where and Select.

var querynonSystem = XXXcontext.Rpm_scrty_rpm_usrs.Where(x => 
   x.Inact_ind == "N" &&
   x.Email_id != null &&
   x.Wwid == null &&
   x.Email_id.Trim() != "" &&
   !ids.Contains(x.Dflt_ste_id)).Select(x => x).ToList();

FYI: you cannot call Rpm_scrty_rpm_usrs table class to query. Instead, you need DbContext or some other repository.

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

I might want to use .ToList() as well FYI , most likely i will or neither and then create other filtering like var querynonSystemCount = querynonSystem.Count();
I do it in Linqpad first , which connects to sql server, then i put into visual studio so Rpm_scrty_rpm_usrs = db.rpm_usr ( db is the context , and rpm_usr is the property of dbset which is based on poco .... )
Awesome! So also if I have another query in which I want to INCLUDE , instead of NOT IN ( ....) I want IN( .... ) I would do same but remove !` from the !ids.Contains so that it is ids.Contains right?
Correct. Contain for IN and !Contain for NOT IN
public int? dflt_ste_id { get; set; } is causing a problem
|
0

There is no "not in" operator unless the type of the query is the same as the type you want to filter against (in which case you could use except). Here it is not. You're working on an IEnumerable and you want to filter on it's ID so a list of int. The where with lambda and contains is your best bet and WILL be translated to an in on the SQL side by most providers.

var FilterIds = new List<int>{1,2,3,4,344,3423525};

var querynonSystem = (from ru in Rpm_scrty_rpm_usrs
            where ru.Inact_ind == "N" && ru.Email_id != null && ru.Wwid == null && ru.Email_id.Trim() != ""
            && ru.Dflt_ste_id != 25
            select ru)
            // Use this
            .Where(ru=>!FilterIds.Any(id=>ru.dflt_ste_id ==id))                
            // Or this
            .Where(ru=>!FilterIds.Contains(ru.dflt_ste_id))
            .Count();
Improve this answer

7 Comments

.Where(ru=>!FilterIds.Contains(id=>ru.dflt_ste_id ==id)) ?
Cannot convert lambda expression to type 'int' because it is not a delegate type
@JeremyMiller Sorry either change contains with any or keep contains and only pass ru.dfit_ste_id to it not a lambda
@JeremyMiller Fixed my answer to illustrate it
Hey, how can i add a Join , i'm getting error .Join(Rpm_scrty_emp_info, z => z.Wwid, ei => ei.Wwid, (z, ei) => new{z,ei}) .Where(z => z.Inact_ind == "N" && z.Wwid != null)
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.