How to check child table contains any item from array

Question

Hi I have two tables Recommendation and RecommendationMemberDetails. Both table contains MemberId. Recommendation table contains my id as MemberId and RecommendationMemberDetail table will contain other peoples Id as MemberId with recommendationId. Recommendation ---------------------------- RecommendationId (PK) MemberId RecommendationMemberDetail ------------------------------------------- Id RecommendationId MemberId Now I will select multiple contact from Contact list and I need to fetch such Recommendation Where MemberId of Recommendation or RecommendationMemberDetails contains MemberId from contactlist. Following is the query. var data1 = _context.Recommendations.Where(x => listMembers.Contains(x.MemberId) || (x.RecommendationMemberDetail.Count() > 0 && listMembers.Contains(x.RecommendationMemberDetail.Select(y => y.MemberId).ToString())) && x.CSM_Approve != p); Please suggest.

Tapan Patel · Answer

All what you need is the join using Lambda or Linq. Take a look at below query and try implementing it. vardata=context.Recommendation.Join(context.RecommendationMemberDetail recommendation=>recommendation.MemberId recommendationMemberDetail=>recommendationMemberDetail.MemberId (recommendationrecommendationMemberDetail)=> new {lyourfields} ).Where(conditions=>tablename.CSM_Approve!= p ) As a side note: If these is your data structure for the table then I believe you have denormalized data. You need to normalize it. You can see that there are data duplications. Please mark it as an answer if addresses the issue.