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.

stackoverflow.com/questions/3020442/…

CAD bloke
– CAD bloke

2016-04-05 01:16:22 +00:00
Commented Apr 5, 2016 at 1:16 — CAD bloke
– CAD bloke, Commented Apr 5, 2016 at 1:16

IDeveloper · Accepted Answer · 2013-11-01 08:26:28Z

37

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 1, 2013 at 8:26

IDeveloper

1,3092 gold badges13 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Diego Over a year ago

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 Over a year ago

IEnumerable<UserDropDownSearch> 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

IDeveloper Over a year ago

If that's a compete code than you miss select in your LINQ query. If not, please post the full LINQ query.

Diego Over a year ago

IEnumerable<UserDropDownSearch> 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, };

IDeveloper Over a year ago

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

Kjartan · Accepted Answer · 2013-11-01 07:36:44Z

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 1, 2013 at 7:36

Kjartan

19.2k16 gold badges75 silver badges102 bronze badges

answered Nov 1, 2013 at 7:35

alexmac

19.7k7 gold badges64 silver badges74 bronze badges

6 Comments

Rahul_RJ Over a year ago

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;

alexmac Over a year ago

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;

Rahul_RJ Over a year ago

But in this case it is not able to do join Because "enumeration.Value" is a string While "cust.lkpStatus" is an int value.

alexmac Over a year ago

I thought enumeration.Value is a int value. How do you want to join tables in one of the fields is not FK/PK!?

Rahul_RJ Over a year ago

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

|

praga2050 · Accepted Answer · 2013-11-01 07:43:53Z

6

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 1, 2013 at 7:43

praga2050

7739 silver badges18 bronze badges

1 Comment

Dan Bechard Over a year ago

where does an inner join and will exclude results where the second column is NULL.

roschach · Accepted Answer · 2019-01-16 11:49:14Z

-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, 2019 at 11:49

roschach

9,63617 gold badges92 silver badges141 bronze badges

answered Jan 16, 2019 at 10:23

user10921782

1

1 Comment

Daniele Santi Over a year ago

This answer is a duplicate of a previous answer.

Collectives™ on Stack Overflow

LINQ Join with multiple AND conditions

4 Answers 4

5 Comments

6 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

6 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related