Linq to select data from one table not in other table

asp.net-mvc-3, c#, linq

Solution

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.property1 = 0;
    ViewBag.property1 = "zero";

works just fine. `ViewBag` is dynamic. Now, you could get that error if you would later try to assing `ViewBag.ddlUserId` to something that actually is the wrong type.

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 `User` records in your `db.Users` and we assume the same for `Fi` as well. In your code, `result1` and `result2` are now two lists, one containing >100.000 `User` objects and the other >100.000 `Fi` objects. 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:

    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:

    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:

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

    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.

Problem

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. ``` Unable to cast object of type 'System.Collections.Generic.List`1[System.String]' to type 'System.Collections.Generic.IEnumerable`1[Main.Models.Admin.User]'. ```

Original source