Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
SolvedPrivate

Left join in LINQ

Posted on 2014-12-15
9
Medium Priority
?
54 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
[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
  • 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 64

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

Expert Comment

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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Screencast - Getting to Know the Pipeline

610 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