如何在我的情况下使用类似于SQL的LINQ查询 [英] How to use LINQ similar to SQL in query for my case

问题描述

我有 Reader 类包含

int ReaderId，字符串ReaderName，...............

然后我有 List<读者> 'readersList'包含Reader类的记录。



我用2列ReaderId和ReaderName创建 DataTable readersDataTable





我想从readersList获取那些记录，其中ReaderId不在readersDataTable中。 （在其他方面我想要从readersList中读取ReaderId的readersList中排除那些记录。）我想要LINQ函数similer到SQL IN查询我的情况。



大家好，我是



1）Class Defn

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

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

2）

2)

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

3）

3)

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

现在我想从readersList获取那些记录，其中ReaderId不存在于使用LINQ的readersDataTable中。



先谢谢。



我的尝试：



尝试了几种LINQ选项，但找不到合适的选项。

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.

推荐答案

嗨Umesh，



试试这个

Hi Umesh,

Try this

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>();

如果您的列表大小超过非平凡值，请避免 O（n * m）算法。

这个只有 O（n + m）。

（从jinesh的解决方案中借用值设置。）

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.)

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")));

编辑：MTH - 哎呀：想法不够， .Except（）不是对于这种情况非常正确。

相反尝试（这仍然是 O（n + m））：

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

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));

这篇关于如何在我的情况下使用类似于SQL的LINQ查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！