SQL to LINQ convertion with full outer join

Question

SELECT* FROM Event FULL OUTER JOIN Booking ON Event.eventId = Booking.eventId
WHERE(Booking.userId = 3) AND (Booking.bookingId IS NULL) 
OR (Event.eventId IS NULL) 
OR (Booking.eventId IS NULL)

This is what i have converted so far however i have encounter an error at "&& bookings.bookingId == null"

The error message is " Operator '&&' cannot be applied to operands of type 'int' and 'bool"

How do you fix it?

            User student = (User)Session["user"];
        var db = new EMS.Models.dbEMSEntities();
        IQueryable<Event> query =  from events in db.Events
                                   join bookings in db.Bookings
                                   on events.eventId equals bookings.eventId
                                   where bookings.userId = student.userId 
                                   && bookings.bookingId == null || events.eventId == null || bookings.eventId == null

I guess first you need to place bookings.bookingId == null || events.eventId == null || bookings.eventId == null in parenthesis like && (bookings.bookingId == null || events.eventId == null || bookings.eventId == null) — Mukund, Jan 27 '16 at 06:14
your `bookings.bookingId` is not nullable, try to change the property to `int?` or change the filter to `&& bookings.bookingId == 0` — vantian, Jan 27 '16 at 06:14
bookings.userId = student.userId and this should be bookings.userId == student.userId . "== " you need to use — Mukund, Jan 27 '16 at 06:15
http://stackoverflow.com/questions/5489987/linq-full-outer-join/13503860#13503860 — Johan Larsson, Jan 27 '16 at 06:26

score 1 · Answer 1 · answered Jan 27 '16 at 06:17

where bookings.userId = student.userId && bookings.bookingId == null || events.eventId == null || bookings.eventId == null

is being evaluated as

where bookings.userId = (student.userId && bookings.bookingId == null) || events.eventId == null || bookings.eventId == null

Because of this, (int)userId && (bool)(bookingId == null) is comparing an int against a bool which violates the language Make sure you use "==" as an evaluation and not the assignment "=".

score 0 · Answer 2 · answered Jan 27 '16 at 06:23

0

   User student = (User)Session["user"];
        var db = new EMS.Models.dbEMSEntities();
        IQueryable<Event> query =  from events in db.Events
                                   join bookings in db.Bookings
                                   on events.eventId equals bookings.eventId
                                   where (bookings.userId == student.userId)
                                   && (bookings.bookingId == null) || (events.eventId == null) || (bookings.eventId == null)

answered Jan 27 '16 at 06:23

Sanu Antony

364
4
15

I have tried your method, thank you it solve the error message however it did not return any output would you have any idea why? – Terry Jan 27 '16 at 06:55
The output will be dependent now on the data, from the intention of the query it looks like that you are looking for students who haven't made a booking for any events, but their userID must still exist in the bookings table..it's possible within the data that this condition does not exist! – Phill Jan 27 '16 at 07:36
Can you send the table structure and sample data. Then only i can figure out what's the issue. – Sanu Antony Jan 27 '16 at 08:14
@Phill Yes there is data existing when i tried the query – Terry Jan 27 '16 at 13:55

SQL to LINQ convertion with full outer join

2 Answers2