C# Linq从一张表中选择数据而不是另一张表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16248189/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq to select data from one table not in other table
提问by Mizbella
Hi i have the following code to select data from one table not in other table
嗨，我有以下代码可以从一张表中选择数据而不是另一张表
var result1 = (from e in db.Users
select e).ToList();
var result2 = (from e in db.Fi
select e).ToList();
List<string> listString = (from e in result1
where !(from m in result2
select m.UserID).Contains(e.UserID)
select e.UserName).ToList();
ViewBag.ddlUserId = listString;
Am getting value inside listString .But got error while adding listString to viewbag.
我在 listString 中获取值。但是在将 listString 添加到 viewbag 时出错。
Unable to cast object of type 'System.Collections.Generic.List`1[System.String]' to type 'System.Collections.Generic.IEnumerable`1[Main.Models.Admin.User]'.
采纳答案by Major Byte
First, could you update your question with the entire method so that we can see what might be going on with the ViewBag? Because your code should work just fine, assigning whatever value to the ViewBag is no problem normally:
首先，您能否用整个方法更新您的问题，以便我们可以了解ViewBag? 因为您的代码应该可以正常工作，所以通常为 ViewBag 分配任何值都没有问题：
ViewBag.property1 = 0;
ViewBag.property1 = "zero";
works just fine. ViewBagis dynamic. Now, you could get that error if you would later try to assing ViewBag.ddlUserIdto something that actually is the wrong type.
工作得很好。ViewBag是动态的。现在，如果您稍后尝试对ViewBag.ddlUserId实际上是错误类型的东西进行分析，您可能会遇到该错误。
I would like you to rewrite your statement as well, let me explain why. Assume for a moment that you have a lot ( > 100.000) of Userrecords in your db.Usersand we assume the same for Fias well. In your code, result1and result2are now two lists, one containing >100.000 Userobjects and the other >100.000 Fiobjects. Then these two lists are compared to each other to produce a list of strings. Now imagine the resource required for your web server to process this. Under the assumption that your actually using/accessing a separate SQL server to retrieve your data from, it would be a lot better and faster to let that server do the work, i.e. producing the list of UserID's.
For that you'd either use Kirill Bestemyanov's answer or the following:
我也希望你重写你的陈述，让我解释一下原因。假设您有很多（> 100.000）条User记录db.Users，我们也假设相同Fi。在你的代码，result1而result2现在是两个列表，一个包含> 100.000User对象和其他> 100.000Fi对象。然后将这两个列表相互比较以生成字符串列表。现在想象一下您的 Web 服务器处理它所需的资源。假设您实际使用/访问单独的 SQL 服务器来从中检索数据，让该服务器完成工作（即生成用户 ID 列表）会更好更快。为此，您可以使用 Kirill Bestemyanov 的答案或以下内容：
var list = (from user in db.Users
where !db.Fi.Any(f => f.UserID == user.UserID)
select user.UserName).ToList()
This will produce just one query for the SQL server to execute:
这将只生成一个供 SQL 服务器执行的查询：
SELECT
[Extent1].[UserName] AS [UserName]
FROM [dbo].[Users] AS [Extent1]
WHERE NOT EXISTS (SELECT
1 AS [C1]
FROM [dbo].[Fi] AS [Extent2]
WHERE [Extent2].[UserID] = [Extent1].[UserID]
)}
which in the end is what you want...
这到底是你想要的......
Just to clarify more:
只是为了澄清更多：
var list = (from user in db.Users
where !db.Fi.Any(f => f.UserID == user.UserID)
select user.UserName).ToList()
can be written as the following lambda expression as well:
也可以写成下面的 lambda 表达式：
var list = db.Users.Where(user => !db.Fi.Any(f => f.UserID == user.UserID))
.Select(user => user.UserName).ToList()
which from the looks of it is slightly different from Kirill Bestemyanov's answer (which I slightly modified, just to make it look more similar):
从外观上看，它与 Kirill Bestemyanov 的答案略有不同（我对其进行了略微修改，只是为了使它看起来更相似）：
var list = db.Users.Where(user => !db.Fi.Select(f => f.UserID)
.Contains(user.UserID))
.Select(user => user.UserName).ToList();
But, they will in fact produce the same SQL Statement, thus the same list.
但是，它们实际上会产生相同的 SQL 语句，从而产生相同的列表。
回答by Kirill Bestemyanov
I will rewrite it to linq extension methods:
我将其重写为 linq 扩展方法：
List<string> listString = db.Users.Where(e=>!db.Fi.Select(m=>m.UserID)
.Contains(e.UserID))
.Select(e=>e.UserName).ToList();
try it, it should work.
试试吧，它应该工作。
回答by Pranav
Try this it is very simple.
试试这个很简单。
var result=(from e in db.Users
select e.UserID).Except(from m in db.Fi
select m.UserID).ToList();
回答by sali
var res = db.tbl_Ware.where(a => a.tbl_Buy.Where(c => c.tbl_Ware.Title.Contains(mtrTxtWareTitle.Text)).Select(b => b.Ware_ID).Contains(a.ID));
This mean in T-SQL is:
这在 T-SQL 中的意思是：
SELECT * FROM tbl_Ware WHERE id IN (SELECT ware_ID, tbl_Buy WHErE tbl_Ware.title LIKE '% mtrTxtwareTitle.Text %')
