Not functioning LINQ not in "contains" query

Question

Going nuts over this.

Simple query but its just not working. I simply need to get the company units that are not currently assigned to any users.

IDs are GUID type.

When debugging I see that there are 2 objects in companyUnits and 1 in userCompanyUnits. I'm trying to get that 1 companyUnit ( that doesn't exist in userCompanyunit) to show. Instead I get no results back.

Any idea what's wrong with the query? Maybe i can't compare based on guids ?

var companyUnitsList = from x in companyUnits
                       where !(from c in userCompanyUnits
                               select c.CompanyUnit.Id).Contains(x.Id)
                        select x;

Thanks in advance !

Your syntax looks exactly right. Can you run profiler to see what sql query is being generated? — Jon Crowell, May 11 '12 at 02:46
Try using the Except operator instead: http://stackoverflow.com/a/183812/138938 — Jon Crowell, May 11 '12 at 02:52
Thank you. I still don't know why my and the Except approach didn't work for me but I just added a new linq method to my knowledge library. thanks for that. — InspiredBy, May 11 '12 at 16:56
Yes, the solution below worked for me. Thank you for your time. — InspiredBy, May 11 '12 at 19:35

score 1 · Accepted Answer · answered May 11 '12 at 03:41

Here's a way of doing it without using the Except operator, and needing a custom IEqualityComparer:

        List<Tuple<int, string>> allUnits = new List<Tuple<int, string>>();
        allUnits.Add(new Tuple<int, string>(1, "unit1"));
        allUnits.Add(new Tuple<int, string>(2, "unit2"));
        allUnits.Add(new Tuple<int, string>(3, "unit3"));

        List<Tuple<int, string>> assignedUnits = new List<Tuple<int, string>>();
        assignedUnits.Add(new Tuple<int, string>(2, "unit2"));

        var unassigned = allUnits.Where(m => !assignedUnits.Any(asgn => asgn.Item1 == m.Item1));

//Yields unit1 and unit3

Item1 corresponds to Unit.ID

All great answers but this is the one that actually worked. Thank you! — InspiredBy, May 11 '12 at 16:54

score 1 · Answer 2 · answered May 11 '12 at 03:47

1

Since you have it tagged as sql, I'm assuming this is queryable's and running against the database. Depending on the ORM and whether you have navigation properties defined and usable, you could do:

var notAssigned =
    from x in companyUnits
    where x.Users.Any() == false
    select x;

IOW, whatever navigation property or collection exists to tie the company unit to the assigned user(s) is what you would use in the query (and would most likely translate to a sql 'exists')

answered May 11 '12 at 03:47

James Manning

13,429
2
40
64

Interesting approach, thank you. It doesn't work with my specific scenario though. – InspiredBy May 11 '12 at 16:30
You could always make a query for the ones that have users assigned then do all.Except(areAssigned) – James Manning May 11 '12 at 22:19

score 1 · Answer 3 · answered May 11 '12 at 03:49

1

    var list1 = from s in new String[] {"ABC1","ABC2", "ABC3", "ABC4"} select new {Field1=s,Id = Guid.NewGuid().ToString()}  ;
    var list2 = new String[] {"ABC3","ABC4", "ABC5", "ABC6"} ;


    var requiredList = (from i1 in list1
                    from i2 in list2 
                    where !i1.Field1.Contains(i2)
                    select i1).Distinct();

answered May 11 '12 at 03:49

Tilak

30,108
19
83
131

There is no Contains method method available in the where clause on the field unfortunately – InspiredBy May 11 '12 at 16:27

Not functioning LINQ not in "contains" query

3 Answers3