Howto program a LINQ expression which effectively does an aggregate subquery with optional cardinal relationship

LINQ! Yes, Also I fell in love with linq. 

So here is my first try. And as you fans know, I like to dig into subjects.

Maybe, the title is a little incorrect, but I wanted a query that returns one record, using a subquery in one statement!

Using SQL syntax, this would look like the query just below.: Yes, It seems I can dream SQL, but it was shocking to see how I underestimated the LINQ syntax which took some extra hears from my head.

The query returns events, that have not already been booked full. (There are still some places left)

SELECT [t0].[id], [t0].[maxNumberofParticipants], [t0].[OwerId], [t0].[Description], [t0].[StartTime], [t0].[EndTime],
[t0].[orderId] FROM [dbo].[Activity] AS [t0]
WHERE ([t0].[OwerId] = @p0) AND ([t0].[maxNumberofParticipants] > @p1) AND ([t0].[maxNumberofParticipants] > (ISNULL((
    SELECT SUM([t2].[value])
    FROM (
        SELECT [t1].[countOfPersons] AS [value], [t1].[activityId]
        FROM [dbo].[ActivityParticipant] AS [t1]
        ) AS [t2]
    WHERE [t2].[activityId] = [t0].[id]

I could make this a stored proc, and execute this easily. But that was in my previous life.

So, how to express this using a LINQ query?

The trick with the ISNULL function is to -cast- the countOfPersons field, to a nullable type! Since the GetValueOrDefault() is available only to nullable data types we must cast it to an int? datatype.
The LINQ provider, will translate it finally, when it sends the actual SQL to SQL Server using TSQL function 'COALESC'.
bt.w., it will not use the ISNULL function. 

public IEnumerable<Activity> getActivityParticipableByOwnerWith(Guid ownerGuid)

var query = from a in Activities
where a.OwerId == ownerGuid && a.maxNumberofParticipants > 0 && a.maxNumberofParticipants >
ActivityParticipants.Where(aid => aid.activityId == => (int?)aid.countOfPersons).GetValueOrDefault(0)
select a ;

return query;

Kuddo's to Khaled Moawad, who very patiently helped me to improve the syntax.  b.t.w. guys/girls. Always try it hard yourself, before you ask the global community for support. That makes your brains retain better :)

My next goal is to make LINQ like a natural language for me, like SQL was :)

blog comments powered by Disqus