Solved

Left Join LINQ

Posted on 2011-09-02
14
351 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
  • 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to count occurrences of each item in an array.

785 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