SolvedPrivate

Left join in LINQ

Posted on 2014-12-15
9
40 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

746 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now