CODEWITHSUNDEEP
c#sql-serverlinq
J4X
J4X

Reputation: 119

LINQ Left and Right Join Query

This is my sql query, how can I generate this query into LINQ:

SELECT 
    TimeTable.StartTime,
    sum(Booking.Quantity) as Total
FROM PromotionSlot
RIGHT JOIN TimeTable
ON PromotionSlot.StartHour = TimeTable.StartTime
LEFT JOIN Booking
ON PromotionSlot.ID = Booking.PromotionSlotID
GROUP BY TimeTable.StartTime

Result:

|StartTime    |Total|
---------------------
 9             NULL
 10            NULL
 11            2
 12            2
 13            NULL
 14            NULL
 15            NULL
 16            NULL
 17            NULL
 18            NULL
 19            NULL
 20            NULL
 21            NULL

This is what I attempted, I'm not sure that the structure of the linq is correct with my SQL query. But I faced error about The cast to value type 'Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type. Please guide me, thank you all in advance! 

var bookingdata = 
    (from ps in dc.PromotionSlots
     join t in dc.TimeTables on ps.StartHour equals t.StartTime into Ts
     join bo in dc.Bookings on ps.ID equals bo.PromotionSlotID into Bs
     from time in Ts.DefaultIfEmpty()                         
     from book in Bs.DefaultIfEmpty()
     group new {time,book} by time.StartTime into NewGroup
     select new dataView
     {
         StartTime = NewGroup.Key,
         numBookings = NewGroup.Select(a => a.book!=null? a.book.Quantity: 0).Sum()
     }).ToList();

Here is my dataView model

public class dataView
{
    public int? StartTime { get; set; }
    public int? numBookings { get; set; }
}

UPDATED:

changed StartTime in my dataView model to int? and this is the result by using console.log()

Format: @:console.log("* " + "@item.StartTime" + ":" + "@item.numBookings");
* :0
* 10:0
* 11:2
* 12:2

I found out the reason why the above console.log() will appear this result. I tried to change my SQL Query RIGHT JOIN TimeTable to LEFT JOIN TimeTable. The return result totally the same like the output from my LINQ.

Upvotes: 1

Views: 2065

Answers (1)

jwatts1980
jwatts1980

Reputation: 7356

I think the problem you're hitting is that in ad-hoc query results, any column value can be null (eg., the Total column in your results example has mixed values, int and null). But C# is not so lenient, an a null value cannot be stuffed into a integer value.

I think that in your dataView type, the numBookings property is probably set to int, and an int type in C# is not nullable.

I believe if you change it to a nullable int, like:

public int? numBookings { get; set; }

then that may fix your error. If not that, then the following might work:

numBookings = NewGroup.Select(a => a.book!=null ? (a.book.Quantity ?? 0): 0).Sum() ?? 0

But I am not sure about this last one.

UPDATE

What if you update your LINQ query to use LEFT JOIN for both joins, like so:

var bookingdata = (
    from t in dc.TimeTables 
    join ps in dc.PromotionSlots on t.StartTime equals ps.StartHour into Ts
    from time in Ts.DefaultIfEmpty()
    join bo in dc.Bookings on time.ID equals bo.PromotionSlotID into Bs
    from book in Bs.DefaultIfEmpty()
    group new {time, book} by time.StartTime into NewGroup
    select new dataView
    {
        StartTime = NewGroup.Key,
        numBookings = NewGroup.Select(a => a.book!=null? a.book.Quantity: 0).Sum()
    }
).ToList();

Does this make the results more consistent with the SQL query?

Upvotes: 2

Related Questions