CODEWITHSUNDEEP
c#asp.net-mvc-3linq
Mizbella
Mizbella

Reputation: 946

Linq to select data from one table not in other table

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]'.

Upvotes: 12

Views: 56212

Answers (4)

sali
sali

Reputation: 9

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 :

select * 
from tbl_Ware 
where id in (
    select ware_ID tbl_Buy where tbl_Ware.title like '% mtrTxtwareTitle.Text %')

Upvotes: 1

Pranav
Pranav

Reputation: 146

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

Upvotes: 7

Major Byte
Major Byte

Reputation: 4101

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.

Upvotes: 33

Kirill Bestemyanov
Kirill Bestemyanov

Reputation: 11964

I will rewrite 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.

Upvotes: 9

Related Questions