SolvedPrivate

Left join in LINQ

Posted on 2014-12-15
9
46 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 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
disable data migrations in visual studio 2017 4 52
Coding for the first time 9 71
C# XML Get Values 4 37
How to force output to ascii 2 41
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
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…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

696 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