CODEWITHSUNDEEP
sqllinq-to-sqljoin
Mr A
Mr A

Reputation: 6768

Problems with Linq-to-SQL join query

var grid = new System.Web.UI.WebControls.GridView();
        grid.DataSource = from booking in bookings
                         join f in getallAttendees on booking.UserID equals f.UserID into fg
                         from fgi in fg.DefaultIfEmpty() //Where(f => f.EventID == booking.EventID)
                          where
                              booking.EventID == id

                          select new
                          {
                              EventID = booking.EventID,
                               UserID = booking.UserID,
                              TrackName = booking.Name,
                              BookingStatus = booking.StatusID,
                               AttendeeName = booking.FirstName,
                             // name = account.FirstName,
                              AmountPaid = booking.Cost,
                              AttendeeAddress = booking.DeliveryAdd1,
                              City = booking.DeliveryCity,
                               Postcode = booking.Postcode,
                              Date = booking.DateAdded,
                              product = fgi == null ? null : fgi.productPurchased
                         };

Booking table design:

ID, EventID, UserID, BookingStatus, AmountPaid, AttendeeAddress, City, Date

Product table design:

ID, EventID, SecondDriver, Apples, Bananas, SecondItem

The association between booking and product table is many to many and the foreign key is UserID

The above Linq-to-SQL query returns the data of only users whose booking.UserID equals att.UserID, what I want is to return all the users of that particular event also with apples and banana fields if they have bought else fill null in that columns.

Any possible solutions or examples will be highly appreciated.

Upvotes: 1

Views: 215

Answers (1)

Aducci
Aducci

Reputation: 26644

I think you are looking for an equivalent to a Left Join. You can accomplish this by using the DefaultIfEmpty() extension method. Modify your join statement and add another from in like so:

join at in getallAttendees on booking.UserID equals at.UserID into attg
from att in attg

In your select statement you can use the ternary operator ? : like so:

SecondItem = att == null ? null : att.SecondItem,

Edit

Your bookings and getallAttendees IQueryable need to come from the same context if you want to join them together.

Otherwise you will need to use getallAttendees.ToList() and bookings.ToList()

Upvotes: 1

Related Questions