Geek asks and answers

Linq to select data from one table not in another table

Hi, I have the following code to select data from one table and not in another 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;

Get the value inside listString. But there was an error when adding listString to the 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]'.
+5
source share
3 answers

First, could you update your question using the whole method so that we can see what can happen to ViewBag? Since your code should work fine, assigning any value to the ViewBag is usually not a problem:

    ViewBag.property1 = 0;
    ViewBag.property1 = "zero";

works just fine. ViewBagis dynamic. Now you can get this error if you later try to determine ViewBag.ddlUserIdwhat is actually the wrong type.

, , , . , ( > 100.000) User db.Users, Fi. result1 result2 , > 100 000 User , > 100 000 Fi . . , - . , / SQL- , , .. UserID. , :

    var list = (from user in db.Users
                where !db.Fi.Any(f => f.UserID == user.UserID)
                select user.UserName).ToList()

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]
    )}

- , ...

:

    var list = (from user in db.Users
                where !db.Fi.Any(f => f.UserID == user.UserID)
                select user.UserName).ToList()

-:

    var list = db.Users.Where(user => !db.Fi.Any(f => f.UserID == user.UserID))
               .Select(user => user.UserName).ToList()

( , ):

    var list = db.Users.Where(user => !db.Fi.Select(f => f.UserID)
                                            .Contains(user.UserID))
                              .Select(user => user.UserName).ToList();

SQL, , .

+15

, .

var result=(from e in db.Users
            select e.UserID).Except(from m in db.Fi
                                    select m.UserID).ToList();
+4

I rewrote it to linq extension methods:

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.

+3
source

More articles:


All Articles