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
SolvedPrivate

Left join in LINQ

Posted on 2014-12-15
9
43 Views
Last Modified: 2016-02-15
I really don't want to move this to a stored proc. I want to do a left join in LINQ. This works but how can I add a left join to it?

 var consumerList = (from data in _dataContext.Consumers
               join s in _dataContext.StateProvinces on data.StateId equals s.Id
               join c in _dataContext.Countries on data.CountryId equals c.Id
               join p in _dataContext.Promotions on data.PromotionId equals p.Id
               join t in _dataContext.SubmissionStatus on data.SubmissionStatusId equals t.Id
               join r in _dataContext.Products on data.ProductId equals r.Id
                               orderby data.CreateDate descending
  select new 
    {....

Open in new window


This is the SQL statement. The left join is at the end

select * from Consumer c
               inner join StateProvince s on c.StateId = s.Id
               inner join Country co on c.CountryId = co.Id
               inner join Promotions p on c.PromotionId = p.Id
               inner join SubmissionStatus st on c.SubmissionStatusId = st.Id
               inner join Products pc on c.ProductId = pc.Id
			   left  join ProductsInPromotion pp on pp.ProductId = c.ProductId and pp.PromotionId = c.PromotionId

Open in new window

0
Comment
Question by:Camillia
  • 4
  • 4
9 Comments
 
LVL 1

Expert Comment

by:psdesignadmin
ID: 40501866
Here is a sample of a left join using LINQ

void Main()
{

  List<Book> bookList = new List<Book>
  {
      new Book{BookID=1, BookNm="DevCurry.com Developer Tips"},
      new Book{BookID=2, BookNm=".NET and COM for Newbies"},
      new Book{BookID=3, BookNm="51 jQuery ASP.NET Recipes"},
      new Book{BookID=4, BookNm="Motivational Gurus"},
      new Book{BookID=5, BookNm="Spiritual Gurus"}
  };

  List<Order> bookOrders = new List<Order>{
      new Order{OrderID=1, BookID=1, PaymentMode="Cheque"},
      new Order{OrderID=2, BookID=5, PaymentMode="Credit"},
      new Order{OrderID=3, BookID=1, PaymentMode="Cash"},
      new Order{OrderID=4, BookID=3, PaymentMode="Cheque"},
      new Order{OrderID=5, BookID=5, PaymentMode="Cheque"},
      new Order{OrderID=6, BookID=4, PaymentMode="Cash"}
  };
  
  var orderForBooks = from bk in bookList
            join ordr in bookOrders
            on bk.BookID equals ordr.BookID
            into a
            from b in a.DefaultIfEmpty(new Order())
            select new
            {
                bk.BookID,
                Name = bk.BookNm,
                b.PaymentMode
            };

}

public class Book
{
    public int BookID { get; set; }
    public string BookNm { get; set; }
}

public class Order
{
    public int OrderID { get; set; }
    public int BookID { get; set; }
    public string PaymentMode { get; set; }
}

Open in new window


~Tim
0
 
LVL 7

Author Comment

by:Camillia
ID: 40501872
sample is not gonna help me. I've seen samples. I need my query working.
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40501970
Hi Camilla;

See if this is what you are looking for.

var consumerList = (from data in _dataContext.Consumers
                    join s in _dataContext.StateProvinces on data.StateId equals s.Id
                    join c in _dataContext.Countries on data.CountryId equals c.Id
                    join p in _dataContext.Promotions on data.PromotionId equals p.Id
                    join t in _dataContext.SubmissionStatus on data.SubmissionStatusId equals t.Id
                    join r in _dataContext.Products on data.ProductId equals r.Id
                    join pp in _dataContext.ProductsInPromotion on  data.ProductId equals pp.ProductId into pInfoGroup
                    orderby data.CreateDate descending
                    select new 
                    {
                        //....
                        // PInfo will be a collection and will be empty collection if no records are in it
                        PInfo = pInfoGroup.DefaultIfEmpty() 
                    };

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 7

Author Comment

by:Camillia
ID: 40501989
I have this in "select new" section. How can I get this working? I want a column called "rebate" from it. If it's too much hassle, I just move this to a stored proc. How do I use that "PInfo" here to get the individual fields?

var consumerList = (from data in _dataContext.Consumers
               join s in _dataContext.StateProvinces on data.StateId equals s.Id
               join c in _dataContext.Countries on data.CountryId equals c.Id
               join p in _dataContext.Promotions on data.PromotionId equals p.Id
               join t in _dataContext.SubmissionStatus on data.SubmissionStatusId equals t.Id
               join r in _dataContext.Products on data.ProductId equals r.Id
               select new
               {
                   data.Id,
                   data.FirstName,
                   data.LastName,
                   data.Address,
                   data.City,
                   data.PostalCode,
                   state = s.Name,
                   stateId = s.Id,
                   country = c.Name,
                   countryId = c.Id,
                   data.EmailAddress,
                   promotion = p.Code,
                   data.PhoneNumber,
                   data.PurchaseDate,
                   data.RetailLocation,
                   data.ProductSerialNumber,
                   status = t.Code,
                   product = r.ProductModel,
                   data.ConfirmationCode,
                   t.Code,
                   data.CreateDate,

                   PromotionId = p.Id,
                   SubmissionStatusId = t.Id,
                   ProductId = r.Id,
                   data.Filename



               }).Where(x => x.Id == id);

Open in new window

0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40501995
Not understanding your question. Are you saying that from the ProductsInPromotion you want a to return a field called rebate?
0
 
LVL 7

Author Comment

by:Camillia
ID: 40501999
I'm saying you have this

 PInfo = pInfoGroup.DefaultIfEmpty() 

Open in new window


But I have
 select new
               {
                   data.Id,
                   data.FirstName,
                   data.LastName,
                   data.Address,
                   data.City,
                   data.PostalCode,
                   state = s.Name,
                   stateId = s.Id,
                  ...

Open in new window


How can I keep my code and use what you have? how does your line of code comes into play?
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40502019
Hi Camilla;

Just place your Select lines into the one I have as shown below.

var consumerList = (from data in _dataContext.Consumers
                    join s in _dataContext.StateProvinces on data.StateId equals s.Id
                    join c in _dataContext.Countries on data.CountryId equals c.Id
                    join p in _dataContext.Promotions on data.PromotionId equals p.Id
                    join t in _dataContext.SubmissionStatus on data.SubmissionStatusId equals t.Id
                    join r in _dataContext.Products on data.ProductId equals r.Id
                    join pp in _dataContext.ProductsInPromotion on  data.ProductId equals pp.ProductId into pInfoGroup
                    orderby data.CreateDate descending
                    select new 
                    {
                         data.Id,
                         data.FirstName,
                         data.LastName,
                         data.Address,
                         data.City,
                         data.PostalCode,
                         state = s.Name,
                         stateId = s.Id,
                         country = c.Name,
                         countryId = c.Id,
                         data.EmailAddress,
                         promotion = p.Code,
                         data.PhoneNumber,
                         data.PurchaseDate,
                         data.RetailLocation,
                         data.ProductSerialNumber,
                         status = t.Code,
                         product = r.ProductModel,
                         data.ConfirmationCode,
                         t.Code,
                         data.CreateDate,
                         PromotionId = p.Id,
                         SubmissionStatusId = t.Id,
                         ProductId = r.Id,
                         data.Filename
                         PInfo = pInfoGroup.DefaultIfEmpty() 
                    }).Where(x => x.Id == id);

Open in new window

0
 
LVL 7

Author Comment

by:Camillia
ID: 40502344
thanks, let me try
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40502710
Not a problem, glad to help.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 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