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.

Solution 1

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;

Solution 2

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 }
...

Solution 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;

Comments

[Rahul_RJ]

What if the condition will be something like ----------------------------------- from enumeration in db.Enumerations.Where(x=>x.EnumerationTypeID.Equals("Custome‌​r.lkpStatus")) join cust in db.Customers on (enumeration.Value equals cust.lkpStatus ) && cust.ID equals data. select enumeration;

[alexmac]

No, in this case use this solution: from enumeration in db.Enumerations.Where(x=>x.EnumerationTypeID.Equals("Custome‌​r.lkpStatus")) join cust in db.Customers.Where(c => c.ID == data) on enumeration.Value equals cust.lkpStatus select enumeration;

[Rahul_RJ]

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]

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]

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

[Dan Bechard]

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

[Diego]

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]

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]

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

[Diego]

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]

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

[Daniele Santi]

This answer is a duplicate of a previous answer.

[Md Aslam]

@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?