How to use LINQ similar to SQL in query for my case

Question

0.00/5 (No votes)

See more:

I have Reader Class containing
int ReaderId, string ReaderName, ……………
Then I have List<reader> 'readersList' containing records of Reader class.

I create DataTable readersDataTable with 2 columns ReaderId and ReaderName

I want to obtain those records from readersList where ReaderId not present in readersDataTable. (In other wants I want to exclude those records from readersList whose ReaderId present in readersDataTable.) I want LINQ functions similer to SQL IN query for my case.

Hi All, FYI

1) Class Defn

C#

public class Reader
{
  int ReaderId;
  string ReaderName;
  // More members
}

2)

C#

List readersList = ReaderService.GetReadersOfSheduler(); // readersList contains Reader objects

3)

C#

DataTable readersDataTable = DataService.GetReadersFromId(this.Id).Tables[0]; 
// returns DataTable with 2 columns ReaderId and ReaderName of course with some records.

Now I want to obtain those records from readersList where ReaderId not present in readersDataTable using LINQ.

Thanks in advance.

What I have tried:

Tried several options of LINQ but cant find suitable one.

Posted 15-Mar-16 1:00am

Umesh AP

Updated 15-Mar-16 10:55am

Andy Lanng

v3

Add a Solution

Comments

Umesh AP 15-Mar-16 7:04am

For my simplicity, i create comma separated string of ReaderIds to achieve SQL Similar functionality ==> SELECT * FROM Readers where ReaderId NOT IN @ReaderIds.
Any suggestion.

Andy Lanng 15-Mar-16 7:06am

You can use a simple Where Clause:
mySet.Where(s=>!MyIdSet.Contains(s.Id))

Umesh AP 15-Mar-16 7:34am

@Andy - Can you explain what u want to say. Please give code snippet with proper object name if possible.

Andy Lanng 15-Mar-16 7:40am

You first. I could only give a generic example because I have no idea what your code looks like.
Click the "Improve Question" button below your original post and add the code you currently have. I'll then write a solution for you

Umesh AP 15-Mar-16 8:16am

Hi Andy, FYI

1) Class Defn
public class Reader
{
int ReaderId;
string ReaderName;
// More members
}

2) List<reader> readersList = ReaderService.GetReadersOfSheduler(); // readersList contains Reader objects

3) DataTable readersDataTable = DataService.GetReadersFromId(this.Id).Tables[0];
// returns DataTable with 2 columns ReaderId and ReaderName of course with some records.

Now I want to obtain those records from readersList where ReaderId not present in readersDataTable using LINQ.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jinesh sam · Answer 1 · 2016-03-15T09:39:00

Hi Umesh,

Try this

C#

List<reader> lstreader = new List<reader>()
{ new Reader { ReaderId=1,ReaderName="A"},
new Reader { ReaderId=2,ReaderName="B"},
new Reader { ReaderId=3,ReaderName="C"},
new Reader { ReaderId=4,ReaderName="D"}
};

DataTable dt = new DataTable();
dt.Columns.Add("ReaderId", typeof(int));
dt.Columns.Add("ReaderName", typeof(string));
dt.Rows.Add(5, "E");
dt.Rows.Add(6, "F");
dt.Rows.Add(2, "B");
dt.Rows.Add(3, "D");


var result = lstreader.Where(r => !dt.AsEnumerable()
                    .Select(t => t.Field<int>("ReaderId"))
                    .ToArray<int>()
                    .Contains(r.ReaderId))
                    .ToList<reader>();

Matt T Heffron · Answer 2 · 2016-03-15T10:55:00

If your lists are more than non-trivial in size, avoid O(n * m) algorithms.
This one is only O(n + m).
(Borrowing the value setup from jinesh's Solution.)

C#

List<Reader> lstreader = new List<Reader>()
{ new Reader { ReaderId=1,ReaderName="A"},
new Reader { ReaderId=2,ReaderName="B"},
new Reader { ReaderId=3,ReaderName="C"},
new Reader { ReaderId=4,ReaderName="D"}
};
 
DataTable dt = new DataTable();
dt.Columns.Add("ReaderId", typeof(int));
dt.Columns.Add("ReaderName", typeof(string));
dt.Rows.Add(5, "E");
dt.Rows.Add(6, "F");
dt.Rows.Add(2, "B");
dt.Rows.Add(3, "D");

var result = lstreader.Except(dt.AsEnumerable().Select(t => t.Field<int>("ReaderId")));

Edit: MTH -- Oops: Not enough thinking, .Except() isn't quite correct for this case.
Instead try (this still is O(n + m)):

C#

var dtReaderIds = dt.AsEnumerable().Select(t => t.Field<int>("ReaderId"));
HashSet<int> excludedIds = new HashSet<int>(dtReaderIds);
IEnumerable<Reader> result = lstreader.Where(r => !excludedIds.Contains(r.ReaderId));

How to use LINQ similar to SQL in query for my case

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0