Solved

Left Join LINQ

Posted on 2011-09-02
14
357 Views
Last Modified: 2012-05-12
from M in Member
                            join MFL in FriendsList
                                on M.MemberID equals MFL.FriendID

Open in new window


Is this situation sufficient for left join in LINQ.
0
Comment
Question by:KaranGupta
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
14 Comments
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472717
var result = from M in Member
join MFL in FriendsList
on M.MemberID equals MFL.FriendID into MemberFrdList
from MF in MemberFrdList.DefaultIfEmpty()
select new                          
{
MemberName = MF.Name,
.....
....                      
};
0
 

Author Comment

by:KaranGupta
ID: 36472742
Hi sonawanekiran

Where can I add where condition
0
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472758
var result = from M in Member
join MFL in FriendsList
on M.MemberID equals MFL.FriendID into MemberFrdList
// here condition goes like where M.name =="MyName"
from MF in MemberFrdList.DefaultIfEmpty()
select new                          
{
MemberName = MF.Name,
.....
....                      
};
0
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 

Author Comment

by:KaranGupta
ID: 36472826
Hi sonawanekiran
How can I put the where condition for FriendsList table


0
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472840
What condition you want?
You can do like this
where MFL.ID == 123
0
 

Author Comment

by:KaranGupta
ID: 36472853
Hi

var searchResult = (from M in Member
                            join MFL in FriendsList
                                on M.MemberID equals MFL.FriendID into MemberList
                                where (M.MemberFullName.Contains(searchString) || 
                                            M.MemberEmailID.Contains(searchString))
                                            && M.IsMemberActive == 1 && M.MemberID != ConfigurationKeys.MemberID
                                            && MFL.MemberID == ConfigurationKeys.MemberID
                                from result in MemberList.DefaultIfEmpty() 
                                select new {M.MemberID,M.MemberFullName,M.MemberEmailID,result.IsRelationApproved});

Open in new window


I am using this query to get the value from 2 tables, but I am getting the error. Secondly once we get the records in the variable searchresult how can I bind it to a gridview.
0
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472877
1) What error you are getting
2)

1:
2:
3:
4:
5:
6:
7:
8:
9:
var searchResult = (from M in Member
                            join MFL in FriendsList
                                on M.MemberID equals MFL.FriendID into MemberList
                                where (M.MemberFullName.Contains(searchString) ||
                                            M.MemberEmailID.Contains(searchString))
                                            && M.IsMemberActive == 1 && M.MemberID != ConfigurationKeys.MemberID
                                            && MFL.MemberID == ConfigurationKeys.MemberID
                                from result in MemberList.DefaultIfEmpty()
                                select new {M.MemberID,M.MemberFullName,M.MemberEmailID,result.IsRelationApproved}).ToList();


GridView1.DataSource = searchResult;
GridView1.DataBind();
0
 

Author Comment

by:KaranGupta
ID: 36472887
The name 'MFL' does not exist in the current context
0
 
LVL 17

Expert Comment

by:sonawanekiran
ID: 36472889
I think there is syntax error at where condition .
Try this
(from M in Member
                            join MFL in FriendsList
                                on M.MemberID equals MFL.FriendID into MemberList
                                where (
                                            (M.MemberFullName.Contains(searchString) || M.MemberEmailID.Contains(searchString)) 
                                            && M.IsMemberActive == 1 && M.MemberID != ConfigurationKeys.MemberID
                                            && MFL.MemberID == ConfigurationKeys.MemberID
                                      )
                                            
                                from result in MemberList.DefaultIfEmpty() 
                                select new {M.MemberID,M.MemberFullName,M.MemberEmailID,result.IsRelationApproved});

Open in new window

0
 

Author Comment

by:KaranGupta
ID: 36472908
Hi

I am getting the same error again but when I change the code to

 var searchResult = (from M in Member
         join MFL in FriendsList
             on M.MemberID equals MFL.FriendID into MemberList
         where (
                     (M.MemberFullName.Contains(searchString) || M.MemberEmailID.Contains(searchString))
                     && M.IsMemberActive == 1 && M.MemberID != ConfigurationKeys.MemberID                     
               )
                            from result in MemberList.DefaultIfEmpty()
                            where result.MemberID == ConfigurationKeys.MemberID
                select new { M.MemberID, M.MemberFullName, M.MemberEmailID, result.IsRelationApproved }).ToList();

Open in new window


then I am not getting any compile time error but I am getting runtime error
LINQ to Entities does not recognize the method
'System.Collections.Generic.IEnumerable`1[Model.FriendsList] DefaultIfEmpty[FriendsList](System.Collections.Generic.IEnumerable`1[Model.FriendsList])' method, and this method cannot be translated into a store expression.
0
 

Author Comment

by:KaranGupta
ID: 36476944
Hi

any upates
0
 
LVL 19

Accepted Solution

by:
Shahan Ayyub earned 500 total points
ID: 36505241
Hi!

Why not use "Left" keyword with join like this:
http://codingsense.wordpress.com/2009/03/08/left-join-right-join-using-linq/
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
Does your audience prefer people in photos or no people? How can you best highlight what you’re selling? What are your competitors doing, and what can you do that is different and unique from them?  Continue reading to learn how to make your images …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question