Problems with Linq-to-SQL join query

Question

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.

Aducci · Accepted Answer · 2011-07-05 15:07:10Z

1

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()

edited Jul 5, 2011 at 15:07

answered Jul 4, 2011 at 16:22

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr A Over a year ago

still getting The specified LINQ expression contains references to queries that are associated with different contexts. error

Mr A Over a year ago

ok that works with some minor problems i will try to solve that , if not will bother you again ;), meantime will accept ur answer

Collectives™ on Stack Overflow

Problems with Linq-to-SQL join query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related