LINQ Join with multiple AND conditions

Question

I want to join two entities in my MVC application for data Processing through the LINQ join.

For that I am trying to write the query like,

from enumeration in db.Enumerations
join cust in db.Customers on ( enumeration.Value equals cust.lkpStatus &&       
enumeration.EnumerationTypeID.Contains('Cust')

But I am getting Problem with this Query, So please give me some suggestion on this.

http://stackoverflow.com/questions/3020442/linq-joining-in-c-sharp-with-multiple-conditions — CAD bloke, Apr 05 '16 at 01:16

score 36 · Answer 1 · answered Nov 01 '13 at 08:26

36

Join should be made like this:

var joinQuery =
from t1 in Table1
join t2 in Table2
  on new { t1.Column1, t1.Column2 } equals new { t2.Column1, t2.Column2 }
...

answered Nov 01 '13 at 08:26

IDeveloper

1,249
2
13
22

3

I did this example and have an error in "join" .. it says The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'Join'. Do you know what is it? – Diego May 16 '17 at 19:00
IEnumerable model = from users in _db.Users join groupMembers in _db.GroupMembers on new { users.User_id } equals new { groupMembers.User_id } Both cases is long type – Diego May 17 '17 at 14:26
If that's a compete code than you miss `select` in your LINQ query. If not, please post the full LINQ query. – IDeveloper May 18 '17 at 11:51
IEnumerable model = from users in _db.Users join groupMembers in _db.GroupMembers on new { users.User_id } equals new { groupMembers.User_id } where groupMembers.User_id == null select new UserDropDownSearch { User_Id = users.User_id, LastName = users.LastName, }; – Diego May 18 '17 at 15:13
2

I believe that the problem is that `User_Id` property on `GroupMembers` is of type `long?` while `User_id` on `Users` is of type `long`. That prevents inner join from working and your case is an outer join. As I see you want to get users who are not in any group. Simpliest way to do it is to create in DB foreign key between Users and GroupMembers and let the property Groups on User object handle a reference to a groups users participates in. So to find users without groups all you have to write is: `var users = db.Users.Where(u => !u.Groups.Any())` – IDeveloper May 22 '17 at 05:35

score 9 · Accepted Answer · edited Nov 01 '13 at 07:36

9

Try this solution:

from enumeration in db.Enumerations.Where(e => 
                                          e.EnumerationTypeID.Contains('Cust'))
join cust in db.Customers on enumeration.Value equals cust.lkpStatus
select enumeration;

edited Nov 01 '13 at 07:36

Kjartan

18,591
15
71
96

answered Nov 01 '13 at 07:35

alexmac

19,087
7
58
69

What if the condition will be something like ----------------------------------- from enumeration in db.Enumerations.Where(x=>x.EnumerationTypeID.Equals("Customer.lkpStatus")) join cust in db.Customers on (enumeration.Value equals cust.lkpStatus ) && cust.ID equals data. select enumeration; – Rahul_RJ Nov 01 '13 at 08:11
No, in this case use this solution: from enumeration in db.Enumerations.Where(x=>x.EnumerationTypeID.Equals("Customer.lkpStatus")) join cust in db.Customers.Where(c => c.ID == data) on enumeration.Value equals cust.lkpStatus select enumeration; – alexmac Nov 01 '13 at 08:14
But in this case it is not able to do join Because "enumeration.Value" is a string While "cust.lkpStatus" is an int value. – Rahul_RJ Nov 01 '13 at 09:38
I thought enumeration.Value is a int value. How do you want to join tables in one of the fields is not FK/PK!? – alexmac Nov 01 '13 at 09:43
I have tried something like this-------------------------- var temp= (from enumeration in db.Enumerations join cust in db.Customers on Convert.ToInt32(enumeration.Value) equals cust.lkpStatus where (cust.ID==data.ID && enumeration.EnumerationTypeID.Contains("Customer.lkpStatus") ) select enumeration).FirstOrDefault(); ------------------------------------------- But its also not working correctly – Rahul_RJ Nov 01 '13 at 09:46
@alexMac, Suppose if the Cust value is dynamic and Cust's Id is a foreign key in the enumeration table then How you can tell the Linq Query? Is it possible to fetch by using the Join in LINQ? – Md Aslam Apr 22 '20 at 13:57

score 3 · Answer 3 · answered Nov 01 '13 at 07:43

3

This one?

var data =     from c in db.Enumerations
               from d in db.Customers
               where c.Value.Equals(d.lkpStatus)
               && c.EnumerationTypeID.Contains('Cust')
               select c;

answered Nov 01 '13 at 07:43

praga2050

729
9
18

`where` does an inner join and will exclude results where the second column is NULL. – Dan Bechard Jul 11 '16 at 15:08

score -1 · Answer 4 · edited Jan 16 '19 at 11:49

-1

This works

var data = from c in db.Enumerations from d in db.Customers where c.Value==d.lkpStatus && c.EnumerationTypeID.Contains('Cust') select c;

edited Jan 16 '19 at 11:49

roschach

8,390
14
74
124

answered Jan 16 '19 at 10:23

user10921782

1

This answer is a duplicate of a previous answer. – Daniele Santi Jan 16 '19 at 10:38

LINQ Join with multiple AND conditions

4 Answers4