SolvedPrivate

Left join in LINQ

Posted on 2014-12-15
9
42 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 62

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

 
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 62

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 62

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 62

Expert Comment

by:Fernando Soto
ID: 40502710
Not a problem, glad to help.
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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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